### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.cgd.order.dao.OrderSaleMapper.updateSaleOrderStatus-Inline
### The error occurred while setting parameters
### SQL: update t_order set ORDER_STATUS = ? where ORDER_ID = ?
小盆友，如果你在日志里看到這個是不是像我一樣會有很多問號？？
我一個只會寫增刪改查sql的低層次程式員有了滿奶子問號，
但是我相信啊：只要功夫深，李白碰到的老婆婆就能把鐵杵磨成針，
1.首先是要了解一些除了增刪改查之外的資料庫基礎知識
從極客上找了門MySQL實戰，如果你也想買，請聯系我，推薦人買有返現的，
這門課我是覺得很值，這兩天為了解決這個死鎖又讀了一遍有關加鎖的章節，發現了一條命令啊，這個命令會輸出很多資訊，有一節 LATESTDETECTED DEADLOCK，就是記錄的最后一次死鎖資訊，
show engine innodb status;
我就試著執行了下，好巧不巧跟那天死鎖日志里的sql一樣，不過也說明這死鎖問題挺頻繁的
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-12-17 11:20:09 7fbe339f7700
//第一個事務
*** (1) TRANSACTION:
TRANSACTION 116609954, ACTIVE 1.214 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 72830 block 64282
MySQL thread id 64282, OS thread handle 0x7fbe01f7d700, query id 1042364621 172.16.21.10 prod_orderdb updating
//以上亂七八糟的有事務的基礎資訊 大小 行數等等
//下邊一行是死鎖的其中一方的sql 決議一下ORDER_ID是表的主鍵
/* 276aedd616081752079448345e2ac7/0.1.3// */
update t_order set ORDER_STATUS = 4 where ORDER_ID = 234
//表示這個事務在等待的鎖資訊
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
//當前在等待表t_order上的 X鎖， 主鍵為 8de0b6b3a773935b
RECORD LOCKS space id 1252 page no 10646 n bits 112 index `PRIMARY` of table `t_order` trx id 116609954 lock_mode X locks rec but not gap waiting
//n_fields 115 表示記錄有115列
Record lock, heap no 10 PHYSICAL RECORD: n_fields 115; compact format; info bits 0
//第一列 基本就是主鍵了
0: len 8; hex 8de0b6b3a773935b; asc s [;;
1: len 6; hex 000006ef0a51; asc Q;;
2: len 7; hex 76000006f723b0; asc v # ;;
3: len 16; hex 32303230313231323030333139363331; asc 2020121200319631;;
4: len 4; hex 80000000; asc ;;
5: SQL NULL;
6: len 4; hex 80000002; asc ;;
7: SQL NULL;
8: len 4; hex 8000000b; asc ;;
.
..省略1xx行
.
//第二個事務資訊
*** (2) TRANSACTION:
TRANSACTION 116609912, ACTIVE 7.886 sec fetching rows
mysql tables in use 1, locked 1
36461 lock struct(s), heap size 3241512, 306688 row lock(s), undo log entries 12
MySQL thread id 72830, OS thread handle 0x7fbe339f7700, query id 1042364593 172.16.21.0 prod_orderdb Searching rows for update
//同理第二個sql
update t_order set ORDER_STATUS = 4 where EXT_ID = '232SA'
//當前事務持有的鎖
*** (2) HOLDS THE LOCK(S):
//持有N個S鎖
RECORD LOCKS space id 1252 page no 10646 n bits 112 index `PRIMARY` of table `t_order` trx id 116609912 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 115; compact format; info bits 0
0: len 8; hex 8de0b6b3a7739342; asc s B;;
1: len 6; hex 000006f1aeff; asc ;;
2: len 7; hex 64000006ce20bb; asc d ;;
3: len 16; hex 32303230313231323030333139363036; asc 2020121200319606;;
4: len 4; hex 80000000; asc ;;
5: SQL NULL;
6: len 4; hex 80000002; asc ;;
7: SQL NULL;
.
.
.
//++++++++++
Record lock, heap no 10 PHYSICAL RECORD: n_fields 115; compact format; info bits 0
0: len 8; hex 8de0b6b3a773935b; asc s [;;
1: len 6; hex 000006ef0a51; asc Q;;
2: len 7; hex 76000006f723b0; asc v # ;;
3: len 16; hex 32303230313231323030333139363331; asc 2020121200319631;;
4: len 4; hex 80000000; asc ;;
5: SQL NULL;
.
.
.
//++++++++++
此處還省略N多 //++++++++++之間的行，同時持有很多行的鎖
//等待的鎖
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
//同樣是等待X鎖 主鍵為 8de0b6b3a773935b
RECORD LOCKS space id 1252 page no 10646 n bits 112 index `PRIMARY` of table `t_order` trx id 116609912 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 115; compact format; info bits 0
0: len 8; hex 8de0b6b3a773935b; asc s [;;
1: len 6; hex 000006ef0a51; asc Q;;
2: len 7; hex 76000006f723b0; asc v # ;;
3: len 16; hex 32303230313231323030333139363331; asc 2020121200319631;;
4: len 4; hex 80000000; asc ;;
5: SQL NULL;
.
.
.
//資料庫選擇回滾成本最小的一個事務進行回滾
*** WE ROLL BACK TRANSACTION (1)
2.思考
兩條sql，對應兩條資料，八竿子打不著的兩條資料在更新的時候發生了死鎖
update t_order set ORDER_STATUS = 4 where ORDER_ID = 234
update t_order set ORDER_STATUS = 4 where EXT_ID = '232SA'
當前問題所處環境整理：
- 資料庫事務隔離級別：讀提交（READ COMMITTED）
- 表：t_order（ORDER_ID ：主鍵； EXT_ID ：非主鍵，無索引，但唯一）
- 兩條sql都是通過唯一條件篩選資料，但不是同一條資料
極客的課程肯定細致不到官網，所以我去官網搜了搜，因為英語不是很好，就搜了Lock關鍵字，把搜出來的文章挨個看了下，先看這個
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
有條件的看原文
沒條件的看這 我們只看讀提交部分：
對于UPDATE或 DELETE陳述句， InnoDB僅對其更新或洗掉的行持有鎖，
MySQL評估WHERE條件后，將釋放不匹配行的記錄鎖，
這大大降低了死鎖的可能性，但是仍然可以發生，
對于UPDATE陳述句，如果某行已被鎖定，則InnoDB 執行“semi-consistent”讀取，
將最新的提交版本回傳給MySQL，以便MySQL可以確定該行是否符合 WHERE條件 UPDATE，
如果該行匹配（必須更新），則MySQL會再次讀取該行，這一次將InnoDB其鎖定或等待對其進行鎖定，
官網例子
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
在這種情況下，表沒有索引，因此搜索和索引掃描使用隱藏的聚集索引進行記錄鎖定（請參見第15.6.2.1節“聚集索引和二級索引”），而不是使用索引列，
假設一個會話UPDATE使用以下陳述句執行 ：
# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;
還假設第二個會話 UPDATE通過在第一個會話的陳述句之后執行以下陳述句來執行：
# Session B
UPDATE t SET b = 4 WHERE b = 2;
在InnoDB執行UPDATE，它首先為每一行獲取一個排他鎖，然后確定是否對其進行修改，如果InnoDB不修改該行，則釋放鎖，否則，InnoDB保留該鎖直到事務結束，這會影響事務處理，如下所示，
使用默認REPEATABLE READ 隔離級別時:
第一個UPDATE將在其讀取的每一行上獲得一個寫（x-lock）鎖，并且不會釋放其中的任何一個：
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
第二UPDATE個嘗試獲取任何鎖的塊（因為第一個更新在所有行上都保留了鎖），并且直到第一個UPDATE提交或回滾時才繼續執行：
x-lock(1,2); block and wait for first UPDATE to commit or roll back
使用READ COMMITTED則有不同：
第一個UPDATE將在讀取的每一行上獲取一個寫（x-lock）鎖，并為未修改的行釋放x鎖：
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)
第二個UPDATE， InnoDB執行 “semi-consistent”讀取，將讀取的每一行的最新提交版本回傳給MySQL，以便MySQL可以確定該行是否符合以下 WHERE條件 UPDATE：
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock
但是，如果WHERE條件包括索引列并InnoDB使用索引，則在獲取和保留記錄鎖時僅考慮索引列，
看完了例子：雖然"semi-consistent讀取"的解釋還有點迷糊，但是我的死鎖問題已經大致有了眉目，
我的表EXT_ID就是沒有索引啊，explain了，確實是全表查的，也就是存在這個一個逐行加鎖并釋放鎖的程序，
當然后來又發現了另一篇檔案
https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-example.html
有條件的看原文去
沒條件的繼續 ：
以下示例說明了鎖定請求將導致死鎖時如何發生錯誤，該示例涉及兩個客戶端A和B，
首先，客戶端A創建一個包含一行的表，然后開始事務，
在事務中，A通過S lock 下選擇行來獲得對行的鎖定：
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i |
+------+
| 1 |
+------+
接下來，客戶端B開始事務并嘗試從表中洗掉該行：
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;
洗掉操作需要一個X鎖，但是它無法獲得X鎖，因為A目前持有S鎖，兩個鎖不兼容 ，因此該請求進入針對行和客戶端B塊的鎖請求佇列中，
最后，客戶端A還嘗試從表中洗掉該行：
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
此處發生死鎖是因為客戶端A需要X鎖才能洗掉該行，
但是，不能授予它X鎖，因為客戶端B已經有一個X鎖定請求，并且正在等待客戶端A釋放其S鎖定，
由于B事先要求鎖，所以A持有的S 鎖也不能升級X鎖，
結果， InnoDB為其中一個客戶端生成錯誤并釋放其鎖，客戶端回傳此錯誤：
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
再之后就可以授予對另一個客戶端的鎖定請求，并從表中洗掉該行，
我只能說這例子跟我的一毛一樣啊，早找到這篇文章我還查個屁的隔離級別，
官方檔案還是牛B！！！還買什么課啊！！看官網干啥！愣著啊！！
本文由博客一文多發平臺 OpenWrite 發布！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/238314.html
標籤：Java