Shulou(Shulou.com)06/01 Report--

Preface

Recently encountered a deadlock problem of mysql under the RR level, feel a little interesting, studied it, and made a note.

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

Scene

Isolation level: Repeatable-Read

The table structure is as follows

Create table t (id int not null primary key AUTO_INCREMENT, an int not null default 0, b varchar (10) not null default'', c varchar (10) not null default'', unique key uniq_a_b (AMO), unique key uniq_c (c))

Initialization data

Insert into t (a recalcitrant bmenade c) values (1meme, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1m, 1h, 1h, 1m, 1h, 1h, 1m, 1h, 1h, 1h, 1h, 1h.

There are two session of Amob, which execute the two transactions in the following order

As a result,

After the execution of 4, B is still normal when An executes 5, blockB then executes 6, B reports deadlock, B rollback, An inserts data.

Deadlock information can be seen in show engine innodb status. Without posting it here, we will first 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

In fact, these two lock modes can be mixed with row lock and gap lock. Multiple transactions can hold S lock at the same time, but only one transaction can hold X lock.

Intention lock

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

This is a lock optimization strategy of mysql. It is not very clear where the optimization point of the intended lock is. Ask the boss for advice.

The compatibility of the two locks is as follows

Row lock

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

Gap lock

Under the RR isolation level, in order to prevent phantom reading, it is necessary to add a gap lock to the gap between the two sides of the record in addition to the record itself.

For example, there is a general index on column a, and there are already three records 1, 5 and 10. Select * from t where axiom 5 for update will not only add row locks to 5 records, but also add gap locks to gaps (1, 5) and (5, 10) to prevent other transactions from inserting data with a value of 5 to cause misreading.

When an ordinary index on a becomes a unique index, there is no need for a gap lock, because the value is unique and it is impossible for the select * from t where adept 5 for update to read two records.

Gap locks are compatible with each other, because if mutually exclusive, transaction A holds the left half (1Magne5) and transaction B holds the right half (1Magne10), then when the record of axiom 5 in the previous example is deleted, theoretically the left and right gap locks have to be merged into a new lock (1Magin10), so who does this new large-scale lock belong to? So gap locks are compatible with each other, whether they are S or X gap locks

Insert intention lock

Inserting an intention lock is actually a special gap lock. In the previous description of the gap lock, we can know that two transactions can hold a gap lock before the real insert, and can not lock the real insert action. Before the real insert, mysql will also try to acquire the insert intention lock of the corresponding record, indicating that there is an intention to insert a value in the gap.

Insert intention lock and gap lock mutually exclusive, for example, transaction 1 lock (1d5) this gap, transaction 2 can not obtain the insert intention lock of axiom 3, so need to lock wait.

Deadlock process analysis

Next, we can analyze the deadlock process in the previous example, first take a look at show engine innodb status

* (1) TRANSACTION:TRANSACTION 5967, ACTIVE 8 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 1136, 2 row lock (s), undo log entries 1MySQL thread id 9, OS thread handle 140528848688896, query id 537 192.168.128.1 root updateinsert into t (aMagneb) 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 waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3 Compact format; info bits 00: len 4; hex 80000001; asc; 1: len 1; hex 31; asc 1 politics; 2: len 4; hex 80000001; asc * (2) TRANSACTION:TRANSACTION 5968, ACTIVE 7 sec insertingmysql tables in use 1, locked 13 lock struct (s), heap size 1136, 2 row lock (s), undo log entries 1MySQL thread id 8, OS thread handle 140528848484096, query id 538192.168.128.1 root updateinsert into t (aforme b) values (0meme 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 recRecord lock Heap no 2 PHYSICAL RECORD: n_fields 3 Compact format; info bits 00: 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 waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000001; asc; 1: len 1; hex 31 Asc 1: len 4; hex 80000001; asc; * * WE ROLL BACK TRANSACTION (2)

Session A (that is, TRANSACTION 5967) is waiting for the insertion intention lock before the record, while session B (that is, TRANSACTION 5968) holds the gap lock before the record, but is also waiting for the insertion intention lock. What is this talking about? isn't it weird?

Start the analysis process from scratch

An and B begin respectively. Transaction A first executes select * from t where astat0 and bounded locks 0 'for update;, adding IX locks first, and then intended to add an exclusive row lock to the record (0,' 0'), but the record does not exist, so it becomes an exclusive gap lock (- ∞, 1) B and then executes select * from t where axi0 and bounded locks 0 'for update The IX lock is also added first, because the record does not exist, so an exclusive gap lock (- ∞, 1) is added, but because the gap locks are compatible with each other, no blockA executes insert into t (Ameme b) values (0meme 0') At this time, to start the real insert, A needs to obtain the insert intention lock on (0meme 0'). Due to the conflict with the (- ∞, 1) exclusive gap lock held by B, the lock waits and enters the lock waiting queue of the record (0meme 0') (although the record does not exist) B executes the insert into t (amemb) values (0memen0') To obtain the insert intention lock, it is found that although B itself holds an exclusive gap lock (- ∞, 1), An also has it, so enter the waiting queue and wait for A to release Ding, and deadlock occurs.

Interpretation of deadlock information

Transaction 1 (TRANSACTION 5967), waiting for 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 (lock_mode X locks gap before rec insert intention) on the unique index uniq_a_b

The boundary of the lock is

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

Indicates that two lines of records

0 and 1 represent the values on the uniq_a_b, and the primary key id=1 corresponding to the'1' beacon 0x31 (that is, the ascii code of'1'). Because the index structure of innodb is determined, 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 bit-by-bit or 0x80000000 on the int value, it is not very clear why, it needs to be interpreted by the boss.

Transaction 2 (TRANSACTION 5968) holds the gap lock index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec, waiting for the insertion of the intended lock index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec insert intention, so the deadlock occurs.

In principle, the innodb engine determines which transaction rolls back at a small cost, but the specific criteria are not very clear (once again, you need a boss). Here innodb chooses to roll back transaction 2. At this point, the deadlock process analysis is complete.

One More Thing

It's not over yet. The amazing thing is that if the table structure becomes

Create table t (id int not null primary key AUTO_INCREMENT, an 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 (ameme b)); insert into t (ameme bmemc) values (1meme 1)

Just put the unique index uniq_c on c in front of uniq_a_b, and the final deadlock information changes!

* (1) TRANSACTION:TRANSACTION 5801, ACTIVE 5 sec insertingmysql tables in use 1, locked 1LOCK WAIT 4 lock struct (s), heap size 1136, 3 row lock (s), undo log entries 1MySQL thread id 5, OS thread handle 140528848688896, query id 380 192.168.128.1 root updateinsert into T2 (AMagol 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 waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3 Compact format; info bits 00: len 4; hex 80000001; asc; 1: len 1; hex 31; asc 1 politics; 2: len 4; hex 80000001; asc * (2) TRANSACTION:TRANSACTION 5802, ACTIVE 4 sec insertingmysql tables in use 1, locked 13 lock struct (s), heap size 1136, 2 row lock (s), undo log entries 1MySQL thread id 6, OS thread handle 140528848484096, query id 381 192.168.128.1 root updateinsert into T2 (aforme b) values (0meme 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 recRecord lock Heap no 2 PHYSICAL RECORD: n_fields 3 Compact format; info bits 00: 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 waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 0; hex; asc; 1: len 4; hex 80000002; asc * WE ROLL BACK TRANSACTION (2)

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

Since we have not seen the source code, we can only deduce it according to the phenomenon: because the unique index of c on the table structure is in front of (aforme b), and the value of c is not specified at the time of insertion, the default value of 0 de innodb needs to be checked to see if there is a record of 0, or there is a conflict of unique keys, so we have to add S lock first, but there is already an IX lock on this record, so take a look at the previous compatibility matrix. S lock and IX lock are mutually exclusive, so we can only lock and wait.

Summary

Seemingly simple select and insert, the following designs very complex locking mechanisms. Understanding these locking mechanisms is conducive to writing efficient SQL (or at least the correct 😂).

Remaining questions:

The optimization point of the intention lock is which lock information, the row record bitwise or the 0x80000000 on the lock is the order in which locks are mutually exclusive. In scenario 1, there are compatible gap locks and locks in the waiting queue. Which one should be determined first? An algorithm for calculating transaction rollback cost by innodb

references

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

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.