Mysql insert鎖異常

2019-11-09 13:34:23

字體：大中小

來源：轉載

供稿：網友

今天自己在MySQL庫中遇到一個insert報鎖等待超時的異常，再網上搜了下，查到這篇文章，和自己場景一樣，同樣是在表中存在聯合主鍵，雖然對問題有了解釋，但未找到解決辦法。

原文地址有趣的insert死鎖

昨天看到一個很有意思的死鎖，拿來記錄下：

環境:deadlock on

事務隔離級別: read commited

表結構：

root@test 08:34:01>show create table lingluo/G*************************** 1. row ***************************       Table: lingluoCreate Table: CREATE TABLE `lingluo` (  `a` int(11) NOT NULL DEFAULT '0',  `b` int(11) DEFAULT NULL,  `c` int(11) DEFAULT NULL,  `d` int(11) DEFAULT NULL,  PRIMARY KEY (`a`),  UNIQUE KEY `uk_bc` (`b`,`c`)) ENGINE=InnoDB DEFAULT CHARSET=gbk1 row in set (0.00 sec)session 1:
root@test 08:45:51>select * from lingluo;+--------+------+------+------+| a      | b    | c    | d    |+--------+------+------+------+|      1 |    2 |    3 |    4 ||    500 |  100 |  200 |   43 ||   1000 |   10 |   20 |   43 ||  10001 |   21 |   21 |   32 || 100202 |  213 |  213 |  312 || 100212 |  214 |  214 |  312 |+--------+------+------+------+6 rows in set (0.00 sec)root@test 08:46:38>begin;Query OK, 0 rows affected (0.00 sec)root@test 08:47:04>insert into lingluo values(100213,215,215,312);Query OK, 1 row affected (0.00 sec)session 2:
root@test 08:46:02>begin;Query OK, 0 rows affected (0.00 sec)root@test 08:47:20>insert into lingluo values(100214,215,215,312);Query OK, 1 row affected (12.77 sec)session3:
root@test 08:46:24>begin;Query OK, 0 rows affected (0.00 sec)root@test 08:47:23>insert into lingluo values(100215,215,215,312);session 1 rollback前:
---TRANSACTION 4F3D6F33, ACTIVE 3 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1MySQL thread id 18124715, OS thread handle 0x7fea34912700, query id 1435660081 localhost root updateinsert into lingluo values(100215,215,215,312)------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock mode S waiting------------------TABLE LOCK table `test`.`lingluo` trx id 4F3D6F33 lock mode IXRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock mode S waiting---TRANSACTION 4F3D6D24, ACTIVE 5 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost root updateinsert into lingluo values(100214,215,215,312)------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode S waiting------------------TABLE LOCK table `test`.`lingluo` trx id 4F3D6D24 lock mode IXRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode S waiting---TRANSACTION 4F3D4423, ACTIVE 33 sec2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1MySQL thread id 18124692, OS thread handle 0x7fe73c89a700, query id 1435651549 localhost rootTABLE LOCK table `test`.`lingluo` trx id 4F3D4423 lock mode IXRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D4423 lock_mode X locks rec but not gap/****
session 1上的轉為顯式鎖：lock_mode X locks rec but not gap
session 2等待的鎖：lock mode S waiting
session 3等待的鎖：lock mode S waiting
***/
session 1 rollback
session 2插入成功
session 3:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction這個時候show engine innodb status:
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost rootTABLE LOCK table `test`.`lingluo` trx id 4F3D6D24 lock mode IXRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode SRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode SRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock_mode X insert intentionRECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock mode S locks gap before rec死鎖信息：
------------------------LATEST DETECTED DEADLOCK------------------------130701 20:47:57*** (1) TRANSACTION:TRANSACTION 4F3D6D24, ACTIVE 13 sec inserting, thread declared inside InnoDB 1mysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost root updateinsert into lingluo values(100214,215,215,312)*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock_mode X insert intention waiting*** (2) TRANSACTION:TRANSACTION 4F3D6F33, ACTIVE 11 sec inserting, thread declared inside InnoDB 1mysql tables in use 1, locked 14 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1MySQL thread id 18124715, OS thread handle 0x7fea34912700, query id 1435660081 localhost root updateinsert into lingluo values(100215,215,215,312)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock mode S*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock_mode X insert intention waiting*** WE ROLL BACK TRANSACTION (2)原因:
s1 , type_mode=1059     //s2為s1轉換隱式鎖為顯式鎖
s2,  type_mode=1282    //檢查重復鍵，需要加共享鎖，被s1 block住，等待S鎖
s3,  type_mode=1282    // 被s1 block住，等待S鎖
 
s1, type_mode=547       //s1回滾，刪除記錄，lock_update_delete鎖繼承，
s2, type_mode=546        //創建s鎖  LOCK_GAP | LOCK_REC | LOCK_S
s3, type_mode=546        //創建s鎖   LOCK_GAP | LOCK_REC | LOCK_S
s2, type_mode=2819   // LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION
s3, type_mode=2819   //  LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION
當s1回滾后，s2和s3獲得s鎖，但隨后s2和s3又先后請求插入意向鎖，因此鎖隊列為：
s2(S GAP)<—s3(S GAP)<—s2(插入意向鎖)<–s3(插入意向鎖)   s3(s鎖),s2(x鎖),s3(x鎖)形成死鎖。
這樣的死鎖不光出現在unique key，還包括primary key（unique key的特殊形式）
印風的博客里有更詳細的代碼級別的記錄，有興趣的可以看下
另外如果：deadlock off的話,即使session 1rollback了，session 2和session 3還是處于等待的狀態，除非超過了innodb_lock_wait_timeout的時間，報
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
這個情況和官方的這篇文章是一樣的http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html