Analysis of a MySQL deadlock scenario example

Preface

Recently I encountered a deadlock problem in MySQL at the RR level. I found it interesting, so I studied it and made a record.

Knowledge points involved: shared lock, exclusive lock, intention lock, gap lock, insert intention lock, lock waiting queue

Scenario

Isolation Level: Repeatable-Read

The table structure is as follows

create table t (
 id int not null primary key AUTO_INCREMENT,
 a int not null default 0,
 b varchar(10) not null default '',
 c varchar(10) not null default '',
 unique key uniq_a_b(a,b),
 unique key uniq_c(c)
);

Initialize Data

insert into t(a,b,c) values(1,'1','1');

There are two sessions A/B, and two transactions are executed in the following order

turn out

• After B executes 4, everything is still normal
• When A executes 5, it is blocked
• B then executes 6, B reports a deadlock, B rolls back, and A inserts data

You can see the deadlock information in show engine innodb status . I won’t post it here. First, I will explain the concepts of several locks and then understand the deadlock process.

Shared (S) lock/mutex (X) lock

• Shared locks allow transactions to read records
• Mutex locks allow transactions to read and write records

These two types of locks can be mixed with row locks and gap locks. Multiple transactions can hold S locks at the same time, but only one transaction can hold X locks.

Intention Lock

A table lock (also a lock mode) indicates that a transaction is about to add an S or X lock to the records of the corresponding table. SELECT ... LOCK IN SHARE MODE adds an IS lock to the table before adding an S lock to the record, and SELECT ... FOR UPDATE adds an IX lock to the table before adding an X lock to the record.

This is a MySQL lock optimization strategy. I am not very clear about the optimization point of the intention lock. Please give me some advice.

The compatibility of the two locks is as follows

Row Lock

It's very simple, just lock the corresponding row. For example, update, select for update, delete, etc. will add row locks to the rows involved to prevent other transactions from operating.

Gap Lock

At the RR isolation level, in order to prevent phantom reads, in addition to the record itself, gap locks must also be added to the gaps on both sides of the record.
For example, if there is a common index on column a and there are three records 1, 5, and 10, select * from t where a=5 for update will not only add a row lock to record 5, but also add gap locks to the gaps (1,5) and (5,10) to prevent other transactions from inserting data with a value of 5 and causing phantom reads.
When the ordinary index on a becomes a unique index, no gap lock is required because the value is unique and select * from t where a=5 for update cannot read two records.

Gap locks are compatible with each other, because if they are mutually exclusive, transaction A holds the left half (1,5) and transaction B holds the right half (1,10), then when the record a=5 in the previous example is deleted, theoretically the left and right gap locks should be merged into a new lock (1,10), then to whom does this new large-range lock belong? So the gap locks are compatible with each other, whether it is S gap lock or X gap lock

Insert intention lock

The insert intention lock is actually a special gap lock. From the previous description of the gap lock, we can know that two transactions can hold a gap lock for a period of time before the actual insert, but they cannot lock the actual insert action. Before the actual insert, MySQL will also try to obtain the insert intention lock of the corresponding record, indicating the intention to insert a value in the gap.

Insert intention lock and gap lock are mutually exclusive. For example, if transaction 1 locks the gap (1,5), transaction 2 cannot obtain the insert intention lock for a=3, so it needs to wait for the lock.

Deadlock process analysis

Next, we can analyze the deadlock process in the previous example. First, look at show engine innodb status

 *** (1) TRANSACTION:
TRANSACTION 5967, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 140528848688896, query id 537 192.168.128.1 root update
insert into t(a,b) values(0,'0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5967 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 5968, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140528848484096, query id 538 192.168.128.1 root update
insert into t(a,b) values(0,'0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5968 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5968 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (2)

Session A (ie, TRANSACTION 5967) is waiting for the insert intention lock before the record (a=1, b='1'), and session B (ie, TRANSACTION 5968) holds the gap lock before the record (a=1, b='1'), but is also waiting for the insert intention lock. What the hell is this? Isn't it weird?

Analyze the process from scratch

1. A and B start the transaction respectively
2. A first executes select * from t where a=0 and b='0' for update; ), but the record does not exist, so it becomes an exclusive gap lock (-∞,1)
3. B then executes select * from t where a=0 and b='0' for update; and also adds an IX lock first. Because the record does not exist, an exclusive gap lock (-∞,1) is added. However, since gap locks are compatible with each other, there is no block.
4. A executes insert into t(a,b) values(0,'0'); . At this time, to actually start inserting, A needs to obtain the insert intention lock on (0,'0'). Since it conflicts with the exclusive gap lock (-∞,1) held by B, the lock waits and enters the lock wait queue of the record (0,'0') (although the record does not exist)
5. B executes insert insert into t(a,b) values(0,'0'); intention lock. It finds that although B holds the exclusive gap lock (-∞,1), A also holds it. So B enters the waiting queue and waits for A to release it.
6. Ding, deadlock occurs

Deadlock information interpretation

Transaction 1 (TRANSACTION 5967), waiting to obtain the lock index uniq_a_b of table t2.t trx id 5967 lock_mode X locks gap before rec insert intention waiting, that is, the insert intention lock on the unique index uniq_a_b (lock_mode X locks gap before rec insert intention)
The lock boundary is

 0: len 4; hex 80000001; asc ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc ;;

Indicates two lines of records

• 0 and 1 represent the values ​​on uniq_a_b, a=1, b=0x31 (i.e. the ascii code of '1')
• a=1,b='1' corresponds to the primary key id=1. Because of the index structure of InnoDB, the secondary index (non-primary key index) points to the primary key index, and the primary key index points to the data, so the primary key needs to be indexed.

As for the int value bitwise OR of 0x80000000, I am not very clear why, I need an explanation from an expert

Transaction 2 (TRANSACTION 5968) holds a gap lock index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec and waits for an insert intention lock index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec insert intention, so a deadlock occurs.

In principle, the InnoDB engine rolls back the transaction that has the lowest rollback cost, but the specific criteria are not very clear (once again, we need a big shot). Here, InnoDB chooses to roll back transaction 2. At this point, the deadlock process analysis is completed

One More Thing

It’s not over yet. . . There is a magical phenomenon that if the table structure becomes

create table t (
 id int not null primary key AUTO_INCREMENT,
 a int not null default 0,
 b varchar(10) not null default '',
 c varchar(10) not null default '',
 unique key uniq_c(c),
 unique key uniq_a_b(a,b)
);
insert into t(a,b,c) values(1,1,1);

Just put the unique index uniq_c on c before uniq_a_b, then the final deadlock information will change!

 *** (1) TRANSACTION:
TRANSACTION 5801, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140528848688896, query id 380 192.168.128.1 root update
insert into t2(a,b) values(0,'0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 5 n bits 72 index uniq_a_b of table `t2`.`t2` trx id 5801 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 5802, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 140528848484096, query id 381 192.168.128.1 root update
insert into t2(a,b) values(0,'0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 56 page no 5 n bits 72 index uniq_a_b of table `t2`.`t2` trx id 5802 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 4 n bits 72 index uniq_c of table `t2`.`t2` trx id 5802 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 0; hex ; asc ;;
 1: len 4; hex 80000002; asc ;;

*** WE ROLL BACK TRANSACTION (2)

The lock that transaction 2 is waiting for has changed from the previous insert intention lock to a shared lock. What the hell?

Since I haven't read the source code, I can only infer based on the phenomenon: because the unique index of c in the table structure is before (a,b), and the value of c is not specified when inserting, the default value 0 is used. InnoDB needs to check whether there is a record of 0 first. If there is, it will report a unique key conflict, so it needs to add an S lock first, but there is already an IX lock on the record (0,'0'). Looking at the compatibility matrix above, S locks and IX locks are mutually exclusive, so it can only lock and wait.

Summarize

It looks like a simple select and insert statement, but a very complex locking mechanism is designed underneath. Understanding these locking mechanisms is helpful for writing efficient SQL (at least correct 😂)

Remaining issues:

1. What is the optimization point of intention lock?
2. In the lock information, what is 0x80000000 in the bitwise OR of the row record?
3. The order of determining mutual exclusion of locks. In scenario 1, there is a compatible gap lock on (0,'0') and a lock in the waiting queue. Which one should be determined first?
4. InnoDB's algorithm for calculating transaction rollback costs

References

• http://hedengcheng.com/?p=771
• https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
• https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-understanding-innodb-locking.html

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.