MySQL死鎖處理筆記
起因
最近公司的線上POS系統的Log裡，不斷出現下面這個錯誤訊息
一直以來，我並沒有太過於在意這個問題，加上這陣子比較忙，這種情況出現的次數也不是很頻繁，所以我一直以來都是仰賴著MySQL 的死鎖檢測功能，讓MySQL自己去釋放鎖，而沒有繼續深入研究下去，今天剛好事情告一個段落，自己稍微研究一下看看能不能解決這個問題，順便做個簡單的筆記。什麼是死鎖
以白話的角度來說
你正在一家餐廳用餐，突然發現隔壁桌的客人也想要你手中的那道菜。你們互相看著對方，誰也不願意放下筷子。這時，服務員過來說，這道菜只能有一個人享用，你們必須決出勝負。
在 MySQL 資料庫中，死鎖就是這樣發生的。兩個或多個事務，就像是餐廳裡的客人，都想爭奪同一份資料。當一個事務獲得了資料的鎖，其他事務就只能等待。如果兩個事務互相等待，就形成了死鎖。
MySQL死鎖的具體表現
- 互斥條件
- 在MySQL中，死鎖通常涉及到對資料庫表中行的鎖。事務為了保證資料的一致性和完整性，會對其所操作的資料行進行加鎖。當事務A鎖定了一行資料進行更新或刪除操作時，
- 持有並等待
- 事務A持有某些資源的鎖，並且請求獲取事務B持有的資源的鎖，但事務B同時也在請求獲取事務A或其他事務持有的資源的鎖。
- 非剝奪
- 在MySQL中，一旦事務獲得了對某行資料的鎖，該鎖在事務提交或回滾之前不會被強制剝奪。也就是說，只有事務自己才能釋放它所持有的鎖。
- 迴圈等待
- 存在一個事務等待鏈，其中每個事務都在等待另一個事務釋放資源。如果這種鏈條形成閉環，則形成了死鎖。
MySQL 如何處理死鎖
MySQL 採用以下兩種主要方式來處理死鎖：
自動死鎖檢測
- 自動死鎖檢測
- InnoDB 引擎具有自動檢測死鎖並回滾其中一個事務以解鎖死局的能力。系統選擇犧牲的事務通常是基於以下兩個因素：
- 事務完成的工作量：系統會優先回滾完成的工作量較小的事務。
- 事務的優先級：如果事務設定了優先級，則系統會優先回滾優先級較低的事務。
- MySQL 的自動死鎖檢測(innodb_deadlock_detect)是預設開啟的，若要確認可以使用
SELECT @@innodb_deadlock_detect進行確認 - 死鎖檢測預設是打開的，但是在高併發的場景下，死鎖檢測會影響效能(參考網址)，因為需要不斷的檢查死鎖是否存在
- InnoDB 引擎具有自動檢測死鎖並回滾其中一個事務以解鎖死局的能力。系統選擇犧牲的事務通常是基於以下兩個因素：
- 鎖等待超時
- MySQL 提供了一個名為 innodb_lock_wait_timeout 的配置參數(預設50秒)，用於設定事務等待鎖資源的超時時間。如果一個事務等待鎖資源的時間超過了超時時間，則系統會自動回滾該事務，避免高併發場景時的資源浪費。
這邊有一個坑
在說明我踩得坑之前，先說說innodb_rollback_on_timeout這個參數在MySQL官方說明裡的描述。
MySQL官方說明
InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb-rollback-on-timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction.
InnoDB 在交易超時時，預設只會回滾最後一個語句。如果指定了 –innodb-rollback-on-timeout，交易超時會導致 InnoDB 中止並回滾整個交易。
innodb_rollback_on_timeout預設是off，因為僅會rollback最後一個語句，這會導致失去了MySQL的原子性(ACID) 舉一個例子來說,假設我們要進行一個轉帳操作，從 A 帳戶轉 100 元到 B 帳戶。這個操作可以拆分成兩個步驟：
從 A 帳戶扣除 100 元。 將 100 元加到 B 帳戶。 如果這兩個步驟都成功了，那麼轉帳操作就成功了，但是，如果其中一個步驟失敗了，那麼整個轉帳操作就應該失敗。
但是innodb_rollback_on_timeout=off的情況，變得有可能是100元沒有加到 B 帳戶，但是A帳戶卻扣了100元，除了這個問題外，由於只rollback最後一個語句(退回最後一個鎖申請)，但是實際上你整個事務還是在執行的狀態，這將會導致下面這個狀況。
CREATE TABLE my_table (
id INT PRIMARY KEY,
val VARCHAR(10)
);
INSERT INTO my_table (id, val) VALUES (1, 'init1'), (2, 'init2');
當死鎖檢測關閉的的情況下，當達到innodb_lock_wait_timeout所設定的超時時間時，因為innodb_rollback_on_timeout=off的緣故，導致僅會rollback最後一個語句，這造成這兩個Session無法釋放自己事務佔用的鎖，並且會持續執行下去。
手動解鎖
至於上面的問題，只能透過手動強制解鎖。
select trx_state, trx_started, trx_mysql_thread_id, trx_query,trx_id from information_schema.innodb_trx;
能看見類似下面的內容
+-----------+---------------------+---------------------+-----------+---------+
| trx_state | trx_started | trx_mysql_thread_id | trx_query | trx_id |
+-----------+---------------------+---------------------+-----------+---------+
| RUNNING | 2019-01-11 17:31:19 | 15 | NULL | 1294243 |
| RUNNING | 2019-01-11 17:30:27 | 14 | NULL | 1294242 |
+-----------+---------------------+---------------------+-----------+---------+
2 rows in set (0.04 sec)
trx_mysql_thread_id，kill掉那個線程
死鎖Log查看
開始前，先用一個簡單的死鎖範例，重現死鎖場景，
最小重現MySQL死鎖範例
先建立兩張表，分別是table1和table2,並且分別對這兩張表新增一筆資料。
CREATE TABLE table1 (
id INT PRIMARY KEY,
value VARCHAR(100)
);
CREATE TABLE table2 (
id INT PRIMARY KEY,
value VARCHAR(100)
);
INSERT INTO table1 (id, value) VALUES (1, 'A');
INSERT INTO table2 (id, value) VALUES (1, 'B');
Session 1和Session 2
切換到 Session 2
切回到 Session 1
Log閱讀
當發生死鎖後，可以透過SHOW ENGINE INNODB STATUS;指令查看LATEST DETECTED DEADLOCK查看最後一筆死鎖的原因，透過上面的範例，可以看到類似下面的資訊。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-01-11 16:20:59 0x2b11fbf89700
*** (1) TRANSACTION:
TRANSACTION 1290791, ACTIVE 31 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 47356242081536, query id 70 localhost ::1 root updating
UPDATE table1 SET value = 'A2' WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11541 page no 3 n bits 72 index PRIMARY of table `fb_db`.`table1` trx id 1290791 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000013af9b; asc ;;
2: len 7; hex 3b000002c70110; asc ; ;;
3: len 2; hex 4131; asc A1;;
*** (2) TRANSACTION:
TRANSACTION 1290139, ACTIVE 217 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 47356241811200, query id 71 localhost ::1 root updating
UPDATE table2 SET value = 'B2' WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11541 page no 3 n bits 72 index PRIMARY of table `fb_db`.`table1` trx id 1290139 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000013af9b; asc ;;
2: len 7; hex 3b000002c70110; asc ; ;;
3: len 2; hex 4131; asc A1;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11542 page no 3 n bits 72 index PRIMARY of table `fb_db`.`table2` trx id 1290139 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000013b227; asc ';;
2: len 7; hex 27000003721740; asc ' r @;;
3: len 2; hex 4231; asc B1;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 1290794
Purge done for trx's n:o < 1290794 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 328831001832160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328831001833984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328831001833072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328831001831248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
Transaction 1嘗試更新table1中id = 1的記錄，但它需要等待一個鎖的釋放，這個鎖當前被Transaction 2持有。Transaction 2同時嘗試更新table2中id = 1的記錄，但它也需要等待一個鎖的釋放，這個鎖當前被Transaction 1持有。*** WE ROLL BACK TRANSACTION (2)，MySQL 自動選擇RollBack Transaction 2
補充
另外，由於SHOW ENGINE INNODB STATUS;只能看到最後一次的死鎖資訊以及需要手動執行才行，如果要以Log的方式進行保存，可以開啟這個參數innodb_print_all_deadlocks，將錯誤訊息打印到Mysql的錯誤日誌中，可通過修改my.ini文件或者使用執行下面指令
最後
該怎麼說呢，雖然可以透過Log去查每個發生死鎖的SQL敘述，由於Log只會打印發生死鎖的SQL描述，而不是打印整個事務裡包含的所有SQL，所以可能或多或少還是去自己的寫的程式中去排查可能的問題。
另外，回到一開始的問題，公司的線上POS系統的Log裡出現死鎖的錯誤，主要是某個UPDATE語句上的WHERE條件有個條件式的索引沒有建立，當建立完後，就沒有發生死鎖的問題了。