模擬兩個事務發生死鎖
還是使用官方提供的actor表，其中actor_id是主鍵
| 時間 | 事務T1 | 事務T2 |
|---|---|---|
| 1 | begin; select * from actor where actor_id=1 for update; | |
| 2 | begin; select * from actor where actor_id=2 for update; | |
| 3 | select * from actor where actor_id=2 for update; 被阻塞 | |
| 4 | select * from actor where actor_id=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 回滾掉 | |
| 5 |
|
通過 show engine innodb status; 查看
分許結果寫在#后面
LATEST DETECTED DEADLOCK
------------------------
2021-11-14 19:14:42 0x7f9f9b651700
#死鎖的發生時間2021-11-14 19:14:42
*** (1) TRANSACTION:
TRANSACTION 3957, ACTIVE 39 sec starting index read
#死鎖發生時的第一個事務，事務id3957，活躍39秒，正在執行starting index read，此事務id比下一個事務id小，說明該事務是T1
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1038, OS thread handle 140323910289152, query id 10706 localhost root statistics
select * from actor where actor_id=2 for update
#發生死鎖時，此事務正在執行的sql
#此事務當前正在等待獲取的鎖信息
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3957 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
#前面說過，lock_mode X locks rec but not gap waiting Record lock 表示X型的record鎖
表示當前事務需要獲取X型的record鎖，正在等待獲取到它
0: len 2; hex 0002; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
*** (2) TRANSACTION:
TRANSACTION 3958, ACTIVE 31 sec starting index read
#死鎖發生時第二個事務信息，該事務id3958，此ID比上一個小，也能得知該事務是T2，活躍31秒，正在執行starting index read操作
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1039, OS thread handle 140323483621120, query id 10708 localhost root statistics
#發生死鎖時，該事務正在執行的sql
select * from actor where actor_id=1 for update
#此事務已經獲取到的鎖
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3958 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
#已獲取的鎖lock_mode X locks rec but not gap Record lock 記錄鎖
0: len 2; hex 0002; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
#此事務等待的鎖
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3958 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0001; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b0110; asc + ;;
3: len 8; hex 50454e454c4f5045; asc PENELOPE;;
4: len 7; hex 4755494e455353; asc GUINESS;;
5: len 4; hex 43f23ed9; asc C > ;;
#innodb決定回滾第二個事務，也就是T2
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 3959
Purge done for trx's n:o < 3930 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421799341400576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421799341399664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421799341398752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 3957, ACTIVE 273 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1038, OS thread handle 140323910289152, query id 10706 localhost root
TABLE LOCK table `sakila`.`actor` trx id 3957 lock mode IX
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3957 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0001; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b0110; asc + ;;
3: len 8; hex 50454e454c4f5045; asc PENELOPE;;
4: len 7; hex 4755494e455353; asc GUINESS;;
5: len 4; hex 43f23ed9; asc C > ;;
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3957 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0002; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
--------這個結果分成三部分：
(1) TRANSACTION，是第一個事務的信息；
(2) TRANSACTION，是第二個事務的信息；
(3)WE ROLL BACK TRANSACTION (2)，是最終的處理結果，表示回滾了第二個事務。
第一個事務的信息中：WAITING FOR THIS LOCK TO BE GRANTED，表示的是這個事務在等待的鎖信息；
index PRIMARY of table `sakila`.`actor`,說明在等的是表actor上的主鍵上的記錄鎖；
lock_mode X locks rec but not gap waiting Record lock 表示這個語句要自己加一個X性的record鎖，當前狀態是等待中；
n_fields 6; compact format; info bits 0
0: len 2; hex 0002; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
n_fields 6 表示這個記錄是六列
第一行是主鍵值
0: len 2; hex 0002; asc ;; 是第一個字段，也就是 actor_id。值是十六進制0002，也就是 2；
第二行是最近一次修改這行的事務id
1: len 6; hex 000000000ef8; asc ;; 計算得出是 3832
后面就是其它每個字段的值
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
查詢主鍵，可以驗證得出確實是主鍵為2的這一行的值
mysql> select * from actor where actor_id=2 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
這兩行里面的 asc 表示的是，接下來要打印出值里面的“可打印字符”。
第一個事務信息就只顯示出了等鎖的狀態，在等待 (主鍵值等于2) 這一行的鎖。
當然你是知道的，既然出現死鎖了，就表示這個事務也占有別的鎖，但是沒有顯示出來。別著急，我們從第二個事務的信息中推導出來。
第二個事務顯示的信息要多一些：
“ HOLDS THE LOCK(S)”用來顯示這個事務持有哪些鎖；
index PRIMARY of table `sakila`.`actor` trx id 3958 lock_mode X locks rec but not gap
Record lock 表示鎖是在表actor主鍵索引 actor_id上；
0: len 2; hex 0002; asc ;; 表示這個事務持有主鍵=2的記錄鎖；
WAITING FOR THIS LOCK TO BE GRANTED，表示在等 (actor_id=1) 這個記錄鎖。
index PRIMARY of table `sakila`.`actor` trx id 3958 lock_mode X locks rec but not gap waiting Record lock
表示鎖是在表actor主鍵索引 actor_id上；
0: len 2; hex 0001; asc ;;
由此可以得出在等 (actor_id=1) 這個記錄鎖。
從上面這些信息中，我們就知道：事務T1等待主鍵為2的記錄鎖，事務T2持有主鍵值為2的記錄鎖，等待主鍵為1的記錄鎖，innodb選擇將事務T2回滾。
