MySql 超時等待表鎖，沒有明顯的死鎖

我開始在寫入同一數據庫和表的兩個不同應用程序上出現以下錯誤：

超過鎖定等待超時；嘗試重啟事務

一開始我認為這是由於一些緩慢的查詢造成了死鎖，但我一直無法找到任何證據。

這是交易部分的輸出show engine innodb status;

------------
TRANSACTIONS
------------
Trx id counter 1031368
Purge done for trx's n:o < 1031258 undo n:o < 0 state: running but idle
History list length 907
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1031348, not started
MySQL thread id 120, OS thread handle 0x7f5e4c148700, query id 1068 localhost db2_prod cleaning up
---TRANSACTION 0, not started
MySQL thread id 53, OS thread handle 0x7f5e3a209700, query id 1136 localhost root init
show engine innodb status
---TRANSACTION 1031286, not started
MySQL thread id 63, OS thread handle 0x7f5e4c0b5700, query id 509 localhost db1_prod cleaning up
---TRANSACTION 1031367, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 24
MySQL thread id 121, OS thread handle 0x7f5e3a26b700, query id 1127 localhost db1_prod update
insert into `my_table` (`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`) values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?,
------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 234 page no 1310 n bits 384 index `PRIMARY` of table `db1`.`my_table` trx id 1031367 lock mode S locks rec but not gap waiting
Record lock, heap no 230 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
0: len 4; hex 0000125b; asc    [;;
1: len 4; hex 00002a4c; asc   *L;;
2: len 4; hex 00000eeb; asc     ;;
3: len 6; hex 0000000fbc8c; asc       ;;
4: len 7; hex 390000014c1434; asc 9   L 4;;
5: len 8; hex 0000000000001c40; asc        @;;
6: len 5; hex 999968ce0c; asc   h  ;;
7: len 4; hex 00000000; asc     ;;
8: SQL NULL;

------------------
---TRANSACTION 1031308, ACTIVE 837 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 60, OS thread handle 0x7f5e3a23a700, query id 1059 localhost 127.0.0.1 db1_prod cleaning up
Trx read view will not see trx with id >= 1031309, sees < 1031309

知道是什麼原因造成的嗎？如果您需要更多資訊，請告訴我。提前致謝。

如果一個事務已經執行了 837 秒，我認為它的設計很糟糕。我不喜歡交易持續 5 秒，更不用說預設的innodb_lock_wait_timeout=50.

不能等待使用者- 事務不應包含任何使用者互動。等待使用者可能意味著等待他/她接聽電話。如果您需要為某種使用者“會話”鎖定某些東西，BEGIN……COMMIT不是這樣做的方法。

autocommit=0 不好- 另一個可能的錯誤是autocommit=0忘記COMMIT. 出於這個原因，我建議永遠不要使用該設置。相反，要明確包含BEGIN並讓它提醒您需要一個COMMIT.

如果您想進一步討論這個問題，讓我們看看您的整個“事務”，無論是在高級別，還是使用特定的 SQL 語句。

我遇到了類似的問題，幾天前，我嘗試了以下方法來解決該問題。

增加 的值innodb_lock_wait_timeout，您可以使用以下兩種方法之一來做到這一點，

在 my.cnf 文件中設置它：

[mysqld]
innodb_lock_wait_timeout=120

此方法需要重新啟動伺服器。

在執行時動態設置它：

SET GLOBAL innodb_lock_wait_timeout = 120; 

殺死導致此鎖定的事務。

您可以通過執行以下命令來查看該鎖定事務：

SHOW FULL PROCESSLIST \G

要終止查詢：

Kill [id] eg: kill 27

注意：有時鎖定查詢狀態顯示為睡眠狀態。

解決方法是您可以使用以下命令查看 innodb 的狀態：

show engine innodb status \G

當兩個執行緒試圖訪問**同一行 (innoDB) 或表 (MyISAM) 時，會發生事務鎖。**在我們的環境中，大多數情況下，事務鎖是由於一個查詢更新一行而另一個查詢嘗試從該行讀取而發生的。

終止事務不是一個好主意，您應該嘗試解決問題。例如，如果某些更新查詢需要更長的時間，您可以檢查表indexes或嘗試以某種方式重寫查詢，它應該在更短的時間內執行。

從您的查詢來看，當一個查詢訪問表my_table的時間較長並且另一個查詢正在等待前一個查詢完成其任務時，就會發生鎖定。

通過設置innodb_lock_time_out和調整特定的查詢，您可以解決這個問題。我希望這個答案能幫助你作為一個起點。

引用自：https://dba.stackexchange.com/questions/139597