Shulou(Shulou.com)05/31 Report--

This article mainly talks about "deadlock in RR mode of MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "deadlock under MySQL's RR mode".

I. asking questions

The following construction method asks why there is no deadlock in RC mode and deadlock in RR mode.

Drop table tt;CREATE TABLE `tt` (`id` int (11) NOT NULL, `c1` int (11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into tt values (1d1); session 1session 2begin

Select * from tt where C1 # 1 for update

Update tt set id=2 where c1o1

Begin;select * from tt where C1 / 1 for update; blocking select * from tt where C1 / 1 for update

Deadlock rollback 2. analysis method first analyze the first sentence of session 1:

Select * from tt where C1 # 1 for update

Locking behavior after execution

RR

-TRANSACTION 231106, ACTIVE 9 sec3 lock struct (s), heap size 1160, 2 row lock (s) MySQL thread id 11, OS thread handle 140737153623808, query id 303 localhost rootTABLE LOCK table `test`.`tt` trx id 231106 lock mode IXRECORD LOCKS space id 127 page no 4 n bits 72 index C1 of table `test`.`tt` trx id 231106 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 800001; asc; 1: len 4 Hex 80000001; asc; RECORD LOCKS space id RECORD LOCKS space id page no 3 n bits 72 index PRIMARY of table `test`.`tt` trx id 231106 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 4; hex 80000001; asc; 1: len 6; hex 00000386c0; asc; 2: len 7; hex aa0000003f0110; asc?; 3: len 4; hex 80000001; asc

RC

-TRANSACTION 231105, ACTIVE 7 sec3 lock struct (s), heap size 1160, 2 row lock (s) MySQL thread id 11, OS thread handle 140737153623808, query id 295 localhost rootTABLE LOCK table `test`.`tt` trx id 231105 lock mode IXRECORD LOCKS space id 127 page no 4 n bits 72 index C1 of table `test`.`tt` trx id 231105 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 800001; asc; 1: len 4 Hex 80000001; asc; RECORD LOCKS space id RECORD LOCKS space id page no 3 n bits 72 index PRIMARY of table `test`.`tt` trx id 231105 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 4; hex 80000001; asc; 1: len 6; hex 00000386c0; asc; 2: len 7; hex aa0000003f0110; asc?; 3: len 4; hex 80000001; asc

We can see that because C1 is the primary key, the locking method is LOCK_X | LOCK_REC_NOT_GAP anyway, and the same is true on the primary key. Is to lock the records of the secondary unique index and the primary key.

Then analyze the second sentence of session 1:

Update tt set id=2 where c1o1

Locking behavior after execution

This sentence is more important. A delete and insert operation will be done on the second-level unique index C1, that is, the original 1Magi 1 record will be marked as del flag, and the 2Power1 record will be inserted at the same time, which will cause a lock inheritance operation (GAP LOCK will appear in the lock_rec_inherit_to_gap_if_gap_lock call) But there will also be uniqueness checking before, so it also involves the existence of LOCK_S locks and next key lock (uniqueness checking for secondary indexes is always next key lock). The del flag here is also an important reason for deadlocks. (for the update operation of the secondary index, the record is always deleted and then inserted, and the primary key determines whether the new record can be accommodated.)

RR

-TRANSACTION 231106, ACTIVE 1626 sec5 lock struct (s), heap size 1160, 5 row lock (s), undo log entries 2MySQL thread id 11, OS thread handle 140737153623808, query id 305 localhost rootTABLE LOCK table `test`.`tt` trx id 231106 lock mode IXRECORD LOCKS space id 4n bits 72 index C1 of table `test`.`tt`trx id 231106 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 800001; asc 1: len 4; hex 80000001; asc; RECORD LOCKS space id 1273n bits 72 index PRIMARY of table `test`.`tt` trx id 231106 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 80000001; asc; 1: len 6; hex 0000000386c2; asc; 2: len 7; hex 2c000000410dca; asc, A; 3: len 4; hex 80000001; asc RECORD LOCKS space id 127th page no 4 n bits 72 index C1 of table `test`.`tt` trx id 231106 lock mode S (LOCK_S) locks gap and rec (LOCK_ ORDINARY [next _ key_lock]) Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000001; asc; 1: len 4; hex 80000001; RECORD LOCKS space id 1272n bits 72 index C1 of table `test`.`tt` trx id 231106 lock mode S (LOCK_S) locks gap before rec (LOCK_GAP) Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000001; asc;; 1: len 4; hex 80000002; asc

RC

5 lock struct (s), heap size 1160, 5 row lock (s), undo log entries 2MySQL thread id 11, OS thread handle 140737153623808, query id 316 localhost rootTABLE LOCK table `test`.`tt` trx id 231123 lock mode IXRECORD LOCKS space id 128n page no 4 n bits c1 of table `test`.`tt`trx id 231123 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000001; asc; 1: len 4; hex 80000001 Asc;; RECORD LOCKS space id 128page no 3 n bits 72 index PRIMARY of table `test`.`tt` trx id 231123 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 80000001; asc;; 1: len 6; hex 00000386d3; asc;; 2: len 7; hex 3700003206e2; asc 72; 3: len 4; hex 80000001 asc; RECORD LOCKS space id 128page no 4 n bits 72 index C1 of table `test`.`tt` trx id 231123 lock mode S (LOCK_S) locks gap and rec (LOCK_ ORDINARY [next _ key_lock]) Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000001; asc; 1: len 4; hex 80000001; RECORD LOCKS space id 128 page no 4 n bits 72 index C1 of table `test`.`tt` trx id 231123 lock mode S (LOCK_S) locks gap before rec (LOCK_GAP) Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000001; asc;; 1: len 4; hex 80000002; asc

There is no difference between RR and RC locking here, because they are both unique values, and lock inheritance is all LOCK_S on the secondary index | LOCK_ order [next _ key_lock], but there will be a difference below.

Then analyze the first sentence of session 2:

Select * from tt where C1 # 1 for update

In fact, at this time, there are two records of c1Secret1, of which only one is marked as deleted and one is not submitted, all of which need to be accessed.

Then the blocking behavior is:

RR

LOCK WAIT 2 lock struct (s), heap size 1160, 1 row lock (s) MySQL thread id 10, OS thread handle 140737153824512, query id 350localhost root statisticsselect * from tt where C1 for update- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 129page no 4 n bits 72 index C1 of table `test`.`tt`trx id 231146 lock_mode X (LOCK_X) locks gap and rec (LOCK_ ORDINARY [next _ key_lock]) waiting (LOCK_WAIT) Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format Info bits 320: len 4; hex 80000001; asc;; 1: len 4; hex 80000001; asc

RC

LOCK WAIT 2 lock struct (s), heap size 1160, 1 row lock (s) MySQL thread id 10, OS thread handle 140737153824512, query id 325 localhost root statisticsselect * from tt where C1 for update- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 128 page no 4 n bits 72 index C1 of table `t est`.`tt` trx id 231128 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) waiting (LOCK_WAIT) Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format Info bits 320: len 4; hex 80000001; asc;; 1: len 4; hex 80000001; asc

We can see here that for the unique key C1 in RR mode, 1Phone1 has been deleted. I did the debug and found that the judgment is as follows before the row_search_mvcc is locked in the function:

If (! set_also_gap_locks | | srv_locks_unsafe_for_binlog | | trx- > isolation_level isolation_level

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.