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

In this issue, the editor will bring you what to do about the dead lock caused by concurrent insert operations. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Description

Dead lock often appears in an online business recently. The relevant information is as follows:

2016-06-15 20:28:25 7f72c0043700InnoDB: transactions deadlock detected, dumping detailed information.

2016-06-15 20:28:25 7f72c0043700

* * (1) TRANSACTION:

TRANSACTION 151506716, ACTIVE 30 sec inserting

Mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct (s), heap size 1184, 2 row lock (s), undo log entries 1

MySQL thread id 1467337, OS thread handle 0x7f72a84d6700, query id 308125831 IP address 1 fold-sys update

Insert into t (a _ dint _ b _ c, addtime)

Values

(63,27451092 and 120609109 now ())

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 46 page no 693076 n bits 664 index `unq_fk_ key` of table `dbname`.`t` trx id 151506716 lock_mode X locks gap before rec insert intention waiting

* * (2) TRANSACTION:

TRANSACTION 151506715, ACTIVE 30 sec inserting, thread declared inside InnoDB 1

Mysql tables in use 1, locked 1

4 lock struct (s), heap size 1184, 2 row lock (s), undo log entries 1

MySQL thread id 1477334, OS thread handle 0x7f72c0043700, query id 308125813 IP address 2 fold-sys update

Insert into t (a _ dint _ b _ c, addtime)

Values

(63,27451092 and 120609109 now ())

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 46 page no 693076 n bits 664 index `unq_fk_folder_fk_video_ seq` of table `folder`.`t _ mapping_folder_ video` trx id 151506715 lock mode S locks gap before rec

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 46 page no 693076 n bits 664 index`unq _ fk_ key`of table `dbname`.`t` trx id 151506715 lock_mode X locks gap before rec insert intention waiting

* WE ROLL BACK TRANSACTION (2)

Preliminary analysis

1,122,120 initiated the same insert operation at the same time, and the data are exactly the same, and a _ c happens to be uniq key.

2. Is it a bit doubtful that we have GAP lock at the RC level? Looked up the documents.

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable theinnodb_locks_unsafe_for_binlog system variable (which is now deprecated). Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

Set the innodb_locks_unsafe_for_binlog or RC level to turn off gap

The latter part can be understood as foreign keys under the RC level and GAP will also be generated when double checking.

Reproduce this deadlock

5.5.19-55-log Percona Server (GPL), Release rel24.0, Revision

Tx_isolation=READ-COMMITTED

Innodb_locks_unsafe_for_binlog=OFF

Create an experimental table

CREATE TABLE `lock` (

`id` bigint (20) NOT NULL AUTO_INCREMENT

`a`smallint (5) unsigned NOT NULL DEFAULT'0'

`b`int (11) NOT NULL DEFAULT'0'

`c`int (11) NOT NULL DEFAULT'0'

`d` datetime NOT NULL DEFAULT '0000-00-0000: 00001

PRIMARY KEY (`id`)

UNIQUE KEY `unq_b_c_ a` (`b`, `c`, `a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Transaction T1

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into deadlock (a dint bjorn c) values (1m 2pm 3)

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

Transactions and locks

# at this time, an intention exclusive lock (IX) is added to the table deadlock

-TRANSACTION 4F23D, ACTIVE 20 sec

1 lock struct (s), heap size 376,0 row lock (s), undo log entries 1

MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root

TABLE LOCK table `yujx`.`lock`trx id 4F236 lock mode IX

Transaction T2

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into deadlock (a _

# you will be waiting here

Transactions and locks

-TRANSACTION 4F23E, ACTIVE 3 sec inserting

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 376,1 row lock (s), undo log entries 1

MySQL thread id 7, OS thread handle 0x408d8940, query id 243 localhost root update

Insert into deadlock (a meme bpene c) values (1meme 2pm 3)

# transaction T2 adds an intention exclusive lock (IX) to the table deadlock, and the corresponding shared lock needs to be acquired when checking the unique constraint of unq_b_c_a, but the corresponding record is locked by T1, and the S lock is waiting to be acquired here (# Note: insert is currently reading, so the read will be blocked by X lock. If you are reading a snapshot, you don't have to wait for the X lock)

-TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F237 lock mode S waiting

-

TABLE LOCK table `yujx`.`lock`trx id 4F237 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F237 lock mode S waiting

-TRANSACTION 4F23D, ACTIVE 37 sec

# transaction T1 adds an intention exclusive lock (IX) and record lock (X) to the table deadlock

2 lock struct (s), heap size 376,1 row lock (s), undo log entries 1

MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root

TABLE LOCK table `yujx`.`lock`trx id 4F236 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F236 lock_mode X locks rec but not gap

-

Transaction T3

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into deadlock (a dint bjorn c) values (1m 2pm 3)

# you will be waiting here

Transactions and locks

-TRANSACTION 4F23F, ACTIVE 3 sec inserting

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 376,1 row lock (s), undo log entries 1

MySQL thread id 8, OS thread handle 0x41976940, query id 245 localhost root update

Insert into deadlock (a meme bpene c) values (1meme 2pm 3)

-TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

# similarly, transaction T3 is the same as transaction T2 above for transactions and lock waits, transaction T1 causes T2 and T3 waits

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F238 lock mode S waiting

-

TABLE LOCK table `yujx`.`lock`trx id 4F238 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F238 lock mode S waiting

-TRANSACTION 4F23E, ACTIVE 31 sec inserting

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 376,1 row lock (s), undo log entries 1

MySQL thread id 7, OS thread handle 0x408d8940, query id 243 localhost root update

Insert into deadlock (a meme bpene c) values (1meme 2pm 3)

-TRX HAS BEEN WAITING 31 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F237 lock mode S waiting

-

TABLE LOCK table `yujx`.`lock`trx id 4F237 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F237 lock mode S waiting

-TRANSACTION 4F23D, ACTIVE 65 sec

2 lock struct (s), heap size 376,1 row lock (s), undo log entries 1

MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root

TABLE LOCK table `yujx`.`lock`trx id 4F236 lock mode IX

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F236 lock_mode X locks rec but not gap

Transaction T1 for rollback

# transaction T1 for rollback

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

# insert success for transaction T2

Mysql > insert into deadlock (a dint bjorn c) values (1m 2pm 3)

Query OK, 1 row affected (10.30 sec)

# transaction T3 returned deadlock error

Mysql > insert into deadlock (a dint bjorn c) values (1m 2pm 3)

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

DEADLOCK information

-

LATEST DETECTED DEADLOCK

-

160620 11:38:14

* * (1) TRANSACTION:

TRANSACTION 4F23E, ACTIVE 48 sec inserting

Mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct (s), heap size 1248, 2 row lock (s), undo log entries 1

MySQL thread id 7, OS thread handle 0x408d8940, query id 297 localhost root update

Insert into deadlock (a meme bpene c) values (1meme 2pm 3)

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F23E lock_mode X insert intention waiting

* * (2) TRANSACTION:

TRANSACTION 4F23F, ACTIVE 30 sec inserting

Mysql tables in use 1, locked 1

4 lock struct (s), heap size 1248, 2 row lock (s), undo log entries 1

MySQL thread id 8, OS thread handle 0x41976940, query id 300 localhost root update

Insert into deadlock (a meme bpene c) values (1meme 2pm 3)

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F23F lock mode S

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_ a`of table `yujx`.`lock` trx id 4F23F lock_mode X insert intention waiting

* WE ROLL BACK TRANSACTION (2)

As mentioned above, we can only see that transaction T2 and transaction T3 eventually cause deadlock;T2 to wait for the record lock corresponding to the unique key of unq_b_c_a (X lock). T3 holds S lock on the record corresponding to `unq_b_c_ a`, and T3 is also waiting for the corresponding X lock to be acquired. Finally, T3 was ROLL BACK and sent back the prompt message of DEAD LOCK.

To sum up

1. The DEADLOCK-related information seen by SHOW ENGINE INNODB STATUS\ G will only return the information of the last two transactions. In fact, it is possible that more transactions will eventually lead to deadlocks.

2. When three (or more) transactions insert the same table, one of them will return a deadlock error message if there is a uniq key constraint on the corresponding field of insert and the first transaction rollback.

3. The cause of deadlock

L T1 acquired X lock and insert succeeded

L T2 tried to insert, check the duplicate key to get the S lock, but failed to get the S lock, join the waiting queue, wait for T1

L T3 tried to insert, check the duplicate key to get the S lock, but failed to get the S lock, join the waiting queue, wait for T1

L T1 rollback, T1 release lock, then T2 and T3 acquire S lock successfully, check duplicate-key, then INSERT attempts to acquire X lock, but T2 and T3 have acquired S lock, resulting in T2, T3 deadlock

4. To avoid this DEADLOCK;, we all know that the problem of deadlock is usually caused by the logic of business processing. Since it is uniq key, and the same program on many different servers has exactly the same value for its insert, the logic itself is not perfect. Therefore, to solve this problem:

Ensure that business programs do not insert the same values into the same uniq key table concurrently at the same point in time.

B. The above experiments show that the deadlock is generated because of the rollback of the first transaction, and find out the cause of rollback.

C. minimize the time it takes to complete the transaction

Final conclusion

When three (or more) transactions insert the same table, if there is a uniq key constraint on the field corresponding to insert and the first transaction rollback, then one of them will return a deadlock error message.

The above is the dead lock caused by the concurrent insert operation shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.