Penjelasan MySQL deadlocks

Question

Saya butuh bantuan untuk menyelesaikan situasi kebuntuan yang saya hadapi. Terima kasih atas bantuan Anda.

Saya pikir kebuntuan terkait dengan subkueri SELECT dari transaksi 2, tetapi saya tidak memahami beberapa hal:

• Mengapa memegang kunci S dan kemudian menunggu kunci X dari baris yang sama ... mengapa tidak memiliki kunci X untuk memulai?
• Bagaimanapun, mengapa transaksi 1 memblokir sesuatu? Saya berharap itu hanya membutuhkan satu kunci, oleh karena itu tidak mendapatkan kunci yang lain, dan hanya menunggu sampai kunci itu tersedia untuk diproses ... Apakah transaksi 1 benar-benar memegang kunci yang 2 sedang menunggu? Tidak masuk akal bagiku.

---

LATEST DETECTED DEADLOCK
------------------------
2020-09-09 07:56:01 2b2bf0401700
*** (1) TRANSACTION:
TRANSACTION 28039013420, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 5603884, OS thread handle 0x2b28527c6700, query id 1343987203 admin updating
UPDATE `order` SET `is_in` = 0 WHERE `order`.`id` = 2084725
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27319884 page no 45175 n bits 4 index `PRIMARY` of table `order` trx id 28039013420 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 55; compact format; info bits 0
 

*** (2) TRANSACTION:
TRANSACTION 28039013409, ACTIVE 0 sec fetching rows
mysql tables in use 4, locked 4
LOCK WAIT 435 lock struct(s), heap size 376, 39002 row lock(s)
MySQL thread id 5603883, OS thread handle 0x2b23e8e82700, query id 1343987095 admin Creating sort index
UPDATE order
            JOIN items ON items.id = order.item_id
            JOIN (              select switch_item_id, sum(quantity) total_sent from order
              inner join items on items.id = item_id
              where scenario_id =  1088
              and is_in = 1
              group by items.switch_item_id
) q on items.switch_item_id = q.switch_item_id
            SET
              total_item_quantity = q.total_sent,
            WHERE is_in = 1 and scenario_id = 1088
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27319884 page no 45175 n bits 2 index `PRIMARY` of table `order` trx id 28039013409 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 55; compact format; info bits 0
 0: len=8; bufptr=0x2b0c9748008b; hex= 80000000001fcf73; asc        s;;
0; asc     ;;
 54: SQL NULL;

[bitmap0 of 16 bytes in hex: 7c 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ]
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27319884 page no 45175 n bits 4 index `PRIMARY` of table `order` trx id 28039013409 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 55; compact format; info bits 0
 0: len=8; bufptr=0x2b0c97480302; hex= 80000000001fcf75; asc        u;;

 54: SQL NULL;

---

TERIMA KASIH BANYAK!

Answer 1

Mengapa memegang kunci S dan kemudian menunggu kunci X dari baris yang sama ... mengapa tidak memiliki kunci X untuk memulai?

Kueri kedua menggunakan subkueri pilih untuk membuat tabel turunan. Pilih kueri secara default buat kunci bersama (S) saja, kecuali Anda mengaktifkan tingkat isolasi yang dapat diserialisasikan. Anda dapat secara eksplisit menginstruksikan pilih untuk menggunakan kunci eksklusif (baik, eksklusif niat, IX) dengan menambahkan for updateklausul ke subquery, tapi saya akan berhati-hati dengan itu. Anda perlu mengevaluasi apakah subquery bisa mengunci lebih banyak record daripada bagian update dan apakah perlu mengunci record ekstra ini.

Bagaimanapun, mengapa transaksi 1 memblokir sesuatu?

Subkueri pemilihan dari kueri ke-2 menempatkan kunci S pada rekaman yang diberikan. Kemudian muncul kueri pertama yang meminta kunci X pada rekaman yang sama, yang tidak dapat diberikan segera karena kunci S sudah ada.

Saat kueri ke-2 mencoba untuk meningkatkan kunci ke X, ia menemukan bahwa itu adalah ke-2 dalam antrian untuk mendapatkan kunci X di belakang transaksi pertama. Namun, karena transaksi ke-2 masih berjalan, kueri ke-2 tidak dapat melepaskan kunci S, mencegah penyelesaian transaksi pertama.

Tolong jangan tanya mengapa mysql bekerja seperti ini karena hanya pengembang mysql yang dapat menjawab pertanyaan ini.