數據庫

MySQL:死鎖一例

廣告
廣告

微信掃一掃,分享到朋友圈

MySQL:死鎖一例
1 0

歡迎關注我的專欄《深入理解MySQL主從原理 32講》 
具體可以點擊:ttps://j.youzan.com/yEY_Xi 

一、問題由來

這是我同事問我的一個問題,在網上看到了如下案例,本案例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條記錄,本表包含一個主鍵和一個唯一鍵。

  • 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  ;; 

我們看到主鍵和唯一鍵都加鎖了如下圖:

  • 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需要獲取主鍵上的鎖,因此被堵塞了如下圖:

  • 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需要獲取唯一鍵上的鎖,因此被堵塞了如下圖:

  • 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. 

這個時候我們看到s2和s3先是獲取了各自需要的鎖,s3獲取主鍵鎖堵塞,s2獲取唯一鍵鎖堵塞,死鎖出現。如下圖:

好了我們看到了死鎖就這樣出現。

我還沒有學會寫個人說明!

分布式Redis深度歷險-復制

上一篇

AI 數據中臺 Mega 及其應用

下一篇

你也可能喜歡

MySQL:死鎖一例

長按儲存圖像,分享給朋友

ITPUB 每周精要將以郵件的形式發放至您的郵箱


微信掃一掃

微信掃一掃
重庆时时彩官网直播开奖