MySQL HA_ERR_LOCK_WAIT_TIMEOUT 忽略 innodb_lock_wait_timeout （刪除大部分錶）

我正在向某些數據庫發出一些長時間執行的刪除查詢，並且收到以下錯誤：

Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.005698, end_log_pos 91495479

我已將“innodb_lock_wait_timeout”設置為高值，例如查詢永遠不會超時。

SELECT @@GLOBAL.innodb_lock_wait_timeout;
+-----------------------------------+
| @@GLOBAL.innodb_lock_wait_timeout |
+-----------------------------------+
|                           1000000 |
+-----------------------------------+

有關如何避免此錯誤的任何建議？我需要刪除大量數據，所以我想在沒有監督的情況下循環發出查詢。感覺有點像mysql的bug。MySQL 版本是 5.6。

編輯：這是確切的查詢。數據根據顯示的函式在客戶端分片 ( MOD(MOD(user_id,65536), shard_count) != shard_id)。數據庫中的大部分數據不應該存在，因此刪除量很大，並且它會向下級聯到大於 300GB 的表（每個使用者可能有例如，有 200 件物品的庫存）

DELETE FROM User
WHERE account_type = "type1"
AND
user_id IN (
   SELECT b.user_id
   FROM (
       SELECT a.account_type, a.user_id
       FROM (
           SELECT account_type, user_id
           FROM User
           WHERE account_type = "type1"
           AND
           MOD(MOD(user_id,65536), 20) != 9
           LIMIT 1000000
       ) a
   ) b
)

編輯：根據要求，輸出SHOW CREATE TABLE

mysql> SHOW CREATE TABLE User \G;
*************************** 1. row ***************************
      Table: User
Create Table: CREATE TABLE `User` (
 `account_type` varchar(20) NOT NULL DEFAULT '',
 `user_id` bigint(20) unsigned NOT NULL,
 `mod_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `team_id` int(10) unsigned DEFAULT NULL,
 `shard_key` int(10) unsigned DEFAULT '0',
 PRIMARY KEY (`account_type`,`user_id`),
 KEY `PLAY_SHKEY_I` (`shard_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

編輯：我相信此時這是一個程式錯誤，因為我沒有commit()從 python 客戶端呼叫，直到一個非常大的循環完成，所以我認為有一個巨大的正在進行的事務從未送出過（因為它從來沒有一直通過循環）

循環執行時不要使用；它可能會變得越來越慢，甚至可能會影響您增加的超時時間。OFFSET``DELETEs

盡量不要將所有刪除操作放在一個事務中。相反，如果一個刪除操作死亡，重新執行刪除操作仍將“正確”工作。

一次刪除的行數不要超過 1000 行。

更多關於“大刪除”的討論： MySQL Big DELETEs

刪除表的 95%

現在您已經解釋了更多發生的事情，我建議通過創建一個新表並複制超過 5% 來重建表。（此技術適用於任何百分比，但如果複製超過 50%，它的好處是值得懷疑的。）

請注意，innodb_file_per_table在建構臃腫的表之前需要已經打開，否則您將無法收回磁碟空間。

如果這會使表格太長，那麼遍歷PRIMARY KEY1K 行的塊，刪除每個塊的大約 95%。（正如該連結中其他地方所討論的那樣。）您將無法將磁碟空間歸還給作業系統（沒有鎖定OPTIMIZE TABLE），但“釋放的”空間最終將被 new 使用INSERTs。

您可能會遇到這個問題，因為您同時執行多個刪除事務，並且其中一些刪除查詢試圖鎖定相同的行並且它們正在等待對方。

盡量不要同時刪除不同事務中的相同行。

如果您共享架構和查詢，我可以給您更多建議。

還有一個名為pt-archiver的工具，它可以毫無問題地從表中歸檔/刪除大量數據。

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