歡迎光臨
每天分享高質量文章

MySQL死鎖套路:唯一索引S鎖與X鎖的愛恨情仇

利用除錯MySQL原始碼的方式來查看死鎖的過程,本文舉了個通俗易懂的例子方便大家理解,希望可以對大家有幫助。

 

毫不誇張的說,有一半以上的死鎖問題由唯一索引貢獻,後面介紹的很多死鎖的問題都跟唯一索引有關。這次我們講一段唯一索引 S 鎖與 X 鎖的愛恨情仇。

 

我們來看一個簡化過的例子

# 構造資料CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(10),  `level` int(11),  PRIMARY KEY (`id`),  UNIQUE KEY `uk_name` (`name`));INSERT INTO `t1` (`name`, `level`) VALUES ('A',0);# 出現問題的sql陳述句如下,併發情況下就會出現死鎖INSERT ignore INTO `t1` (`name`, `level`) VALUES ('A',0);update t1 set level = 1 where name = "A";

 

我們用之前介紹過的原始碼分析方式,先來看下這兩條陳述句分別加什麼鎖,然後分析死鎖形成的過程。

 

第一條陳述句

INSERT ignore INTO t1 (name, level) VALUES ('A',0);

 

在除錯中得到的結果如下

 

 

可以看到這條陳述句對唯一鍵 uk_name 加共享鎖(S鎖),而且成功。

 

第二條陳述句

 

update t1 set level = 1 where name = "A"; 通過唯一鍵更新資料庫欄位。

 

這種情況在之前的文章已經介紹過,會對唯一索引加 X 鎖,然後對主鍵索引加 X 鎖

 

 

 

這樣就可以非常輕鬆的復現死鎖的問題了,步驟如下

 

  1. 開啟兩個 session,分別 begin
  2. session1 執行INSERT ignore INTO t1 (name, level) VALUES (‘A’,0);
  3. session2 執行INSERT ignore INTO t1 (name, level) VALUES (‘A’,0);
  4. session1 執行update t1 set level = 1 where name = “A”; 進入等待狀態
  5. session2 執行update t1 set level = 1 where name = “A”;,死鎖產生,被回滾,同時事務 1 執行成功

 

詳細的鎖狀態變化如下

 

 

死鎖日誌如下:

 

LATEST DETECTED DEADLOCK------------------------181208 23:00:52*** (1) TRANSACTION:TRANSACTION 53A7, ACTIVE 162 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)MySQL thread id 12, OS thread handle 0x700010522000, query id 1424 localhost root Updatingupdate t1 set level = 1 where name = "A"*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A7 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 41; asc A;; 1: len 4; hex 80000001; asc     ;;
*** (2) TRANSACTION:TRANSACTION 53A8, ACTIVE 8 sec starting index readmysql tables in use 1, locked 13 lock struct(s), heap size 376, 2 row lock(s)MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updatingupdate t1 set level = 1 where name = "A"*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock mode SRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 41; asc A;; 1: len 4; hex 80000001; asc     ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 41; asc A;; 1: len 4; hex 80000001; asc     ;;
*** WE ROLL BACK TRANSACTION (2)

 

來詳細看一下這個死鎖日誌:

 

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A7 lock_mode X locks rec but not gap waiting

 

事務 1 想獲取 uk_name 唯一索引上的 X 鎖 (非 gap 鎖的記錄鎖)

 

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock mode S

 

事務 2 持有uk_name 唯一索引上的 S 鎖(共享鎖)

 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock_mode X locks rec but not gap waiting

 

事務 2 想獲得 uk_name 唯一索引上的 X 鎖(非 gap 鎖的記錄鎖)

 

跟之前理論上推斷的結論是一致。


    已同步到看一看
    赞(0)

    分享創造快樂