| t1 | t2 | 備注 |
|---|---|---|
| INSERT IGNORE INTO | - | t1成功獲得uk的S鎖 DB_SUCCESS |
| - | INSERT IGNORE INTO | t2成功獲得uk的S鎖 DB_SUCCESS |
| UPDATE | - | t1嘗試獲得uk的X鎖，但沒有成功，處于等待狀態(tài) DB_LOCK_WAIT |
| - | UPDATE | t2嘗試獲得uk的X鎖，發(fā)現(xiàn)死鎖產(chǎn)生 DB_DEADLOCK |
| - | Deadlock | t2釋放S鎖 |
| 成功 | - | - |
死鎖日志如下：
LATEST DETECTED DEADLOCK ------------------------ 181208 23:00:52 *** (1) TRANSACTION: TRANSACTION 53A7, ACTIVE 162 sec starting index read mysql tables in use 1, locked 1 LOCK 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 Updating update 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 waiting Record 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 read mysql tables in use 1, locked 1 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updating update 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 S Record 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 waiting Record 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)
來詳細(xì)看一下這個(gè)死鎖日志
*** (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
事務(wù) 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
事務(wù) 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
事務(wù) 2 想獲得 uk_name 唯一索引上的 X 鎖（非 gap 鎖的記錄鎖）
跟之前理論上推斷的結(jié)論是一致的
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
標(biāo)簽：昌都 北京 荊門 駐馬店 梅河口 鄂爾多斯 黔西 陜西
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《通過唯一索引S鎖與X鎖來了解MySQL死鎖套路》，本文關(guān)鍵詞 通過,唯一,索引,鎖與,鎖,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
