Preface The essence of deadlock is resource competition. If the order of batch insertion is inconsistent, it can easily lead to deadlock. Let's analyze this situation. For the convenience of demonstration, batch insert is rewritten as multiple inserts. Let's do a few small experiments first. The simplified table structure is as follows CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5), `b` varchar(5), PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`a`,`b`) ); Experiment 1: If two batch inserts of the same order are executed simultaneously when the record does not exist, the second one will enter the lock waiting state.
You can see the current lock status mysql> select * from information_schema.innodb_locks; +-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 31AE:54:4:2 | 31AE | S | RECORD | `d1`.`t1` | `uk_name` | 54 | 4 | 2 | '1', '1' | | 31AD:54:4:2 | 31AD | X | RECORD | `d1`.`t1` | `uk_name` | 54 | 4 | 2 | '1', '1' | +-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ When we execute the insert of transaction t1, no lock breakpoints appear. This is related to the principle of MySQL insertion.
When inserting records at t1, no locks are applied. At this time, transaction t1 has not been committed yet. When transaction t2 tries to insert, it finds this record. t2 tries to obtain the S lock and determines whether the transaction ID on the record is active. If it is active, it means that the transaction has not ended. It will help t1 upgrade its implicit lock to an explicit lock (X lock). The source code is as follows t2 Result of acquiring S lock: DB_LOCK_WAIT Experiment 2: Deadlock caused by inconsistent batch insertion order
------------------------ LATEST DETECTED DEADLOCK ------------------------ 181101 9:48:36 *** (1) TRANSACTION: TRANSACTION 3309, ACTIVE 215 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2 MySQL thread id 2, OS thread handle 0x70000a845000, query id 58 localhost root update insert into t1(a, b)values("2", "2") *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 3309 lock mode S waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 32; asc 2;; 1: len 1; hex 32; asc 2;; 2: len 4; hex 80000002; asc ;; *** (2) TRANSACTION: TRANSACTION 330A, ACTIVE 163 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2 MySQL thread id 3, OS thread handle 0x70000a888000, query id 59 localhost root update insert into t1(a, b)values("1", "1") *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 330A lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 32; asc 2;; 1: len 1; hex 32; asc 2;; 2: len 4; hex 80000002; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 330A lock mode S waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 31; asc 1;; 1: len 1; hex 31; asc 1;; 2: len 4; hex 80000001; asc ;; *** WE ROLL BACK TRANSACTION (2) How to solve such a problem? One possible solution is to insert the data after sorting at the application layer. 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. You may also be interested in:
|
<<: The complete code of the uniapp packaged applet radar chart component
>>: Detailed explanation of the construction and interface management of Docker private warehouse
This article shares the installation and configur...
In this tutorial, we use the latest MySQL communi...
Table of contents Three steps to operate the data...
Table of contents Overview CommonJS Specification...
In desperation, I suddenly thought, how is the Sin...
This article shares the specific code of jQuery t...
Table of contents 1. prototype (explicit prototyp...
1. Application of multimedia in HTML_falsh animat...
Introduction to XHTML tags <br />Perhaps you...
1. Prepare the environment (download nodejs and s...
more is one of our most commonly used tools. The ...
WebService Remote Debugging In .NET, the remote d...
Table of contents Preface start Basic layout Data...
After VMware is abnormally shut down, it prompts ...
ElasticSearch cluster supports動態請求的方式and靜態配置文件to ...