MySQL：死鎖一例

一、問題由來

這是我同僚問我的一個問題，在網上看到了如下案例，本案例RC RR都可以出現，其實這個死鎖原因也比較簡單，我們來具體看看：

構造資料

CREATE database deadlock_test;
use deadlock_test;
CREATE TABLE `push_token` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `token` varchar(128) NOT NULL COMMENT 'push token',
  `app_id` varchar(128) DEFAULT NULL COMMENT 'appid',
  `deleted` tinyint(1) NOT NULL COMMENT '是否已删除 0：否 1：是',
   PRIMARY KEY (`id`),
   UNIQUE KEY `uk_token_appid` (`token`,`app_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3384 DEFAULT CHARSET=utf8 COMMENT='pushtoken表';

insert into push_token (id, token, app_id, deleted) values(1,"token1",1,0);           

操作資料

|s1（TRX_ID367661）|s2（TRX_ID367662）|s3（TRX_ID367663）|

|-|-|-|

|begin; UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id = '1';|||

||begin; DELETE FROM push_token WHERE id IN (1);||

|||begin; UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id = '1';|

|commit;|||

|Query OK, 0 rows affected (0.00 sec)| Query OK, 1 row affected (17.32 sec)| ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction|

二、分析方法

我使用的分析方法是把整個加鎖的日志列印出來，當然需要用到我自己做了輸出修改的一個版本，如下：

https://github.com/gaopengcarl/percona-server-locks-detail-5.7.22

這個版本我打開了的日志記錄參數如下：

mysql> show variables like '%gaopeng%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| gaopeng_mdl_detail             | OFF   |
| innodb_gaopeng_row_lock_detail | ON    |
+--------------------------------+-------+
2 rows in set (0.01 sec)           

這樣大部分的Innodb加鎖記錄都會記錄到errlog日志了。好了下面我詳細分析一下日志：

三、分析過程

初始化的情況整個表隻有1條記錄，本表包含一個主鍵和一個唯一鍵。

1. s1（TRX_ID367661） 執行語句

begin;
UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id = '1';           

日志輸出：

2019-08-18T19:10:05.117317+08:00 6 [Note] InnoDB: TRX ID:(367661) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 6; hex 746f6b656e31; asc token1;;
 1: len 1; hex 31; asc 1;;
 2: len 8; hex 8000000000000001; asc         ;;
2019-08-18T19:10:05.117714+08:00 6 [Note] InnoDB: TRX ID:(367661) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000059c2c; asc      ,;;
 2: len 7; hex bf000000420110; asc     B  ;;
 3: len 6; hex 746f6b656e31; asc token1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 80; asc  ;;           

我們看到主鍵和唯一鍵都加鎖了，模式為LOCK_X|LOCK_NOT_GAP|如下圖：

并且這個時候資料實際上是标記删除狀态。

2. s2（TRX_ID367662） 執行語句

begin;DELETE FROM push_token WHERE id IN (1);
           

2019-08-18T19:10:22.751467+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000059c2d; asc      -;;
 2: len 7; hex 400000002a1dc8; asc @   *  ;;
 3: len 6; hex 746f6b656e31; asc token1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 81; asc  ;;
2019-08-18T19:10:22.752753+08:00 9 [Note] InnoDB: Trx(367662) is blocked!!!!!           

這個時候S2需要擷取主鍵上的:LOCK_X|LOCK_NOT_GAP| 鎖，是以被堵塞了如下圖：

3. s3（TRX_ID367663） 執行語句

begin; UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id = '1';
           

019-08-18T19:10:30.822111+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 6; hex 746f6b656e31; asc token1;;
 1: len 1; hex 31; asc 1;;
 2: len 8; hex 8000000000000001; asc         ;;
2019-08-18T19:10:30.918248+08:00 8 [Note] InnoDB: Trx(367663) is blocked!!!!!
           

這個時候S3需要擷取唯一鍵上的LOCK_X|LOCK_NOT_GAP 鎖，是以被堵塞了如下圖：

4. s1（TRX_ID367661） 執行語句

這一步完成後死鎖出現。

commit;           

日志輸出如下：

367663和367662各自擷取需要的鎖

2019-08-18T19:10:36.566733+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 6; hex 746f6b656e31; asc token1;;
 1: len 1; hex 31; asc 1;;
 2: len 8; hex 8000000000000001; asc         ;;
2019-08-18T19:10:36.568711+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000059c2d; asc      -;;
 2: len 7; hex 400000002a1dc8; asc @   *  ;;
 3: len 6; hex 746f6b656e31; asc token1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 81; asc  ;;

367663擷取主鍵鎖堵塞、367662擷取唯一鍵鎖堵塞，死鎖形成
2019-08-18T19:10:36.570313+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000059c2d; asc      -;;
 2: len 7; hex 400000002a1dc8; asc @   *  ;;
 3: len 6; hex 746f6b656e31; asc token1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 81; asc  ;;
2019-08-18T19:10:36.571199+08:00 8 [Note] InnoDB: Trx(367663) is blocked!!!!!
2019-08-18T19:10:36.572481+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 6; hex 746f6b656e31; asc token1;;
 1: len 1; hex 31; asc 1;;
 2: len 8; hex 8000000000000001; asc         ;;
2019-08-18T19:10:36.573073+08:00 9 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.           

完成這一步 s1實際上釋放了鎖， 然後我們首先看到s2擷取了主鍵上的LOCK_X|LOCK_NOT_GAP鎖，s3擷取了唯一鍵上的LOCK_X|LOCK_NOT_GAP 鎖。但是随後s3擷取主鍵上的LOCK_X|LOCK_NOT_GAP鎖堵塞，s2擷取唯一鍵上的LOCK_X|LOCK_NOT_GAP鎖堵塞。是以死鎖形成，如下圖：

好了我們看到了死鎖就這樣出現。整個分析過程我們隻要看到加鎖的日志實際上很容易就分析得出來。

最後歡迎關注我的專欄《深入了解MySQL主從原理 32講》：