如何識別MySQL中導致「鎖定等待逾時」錯誤的特定查詢？

當MySQL 發生Lock wait timeout timeout returned 錯誤時，如何辨識持有鎖的特定查詢

介紹：

介紹：

>MySQL中的鎖定等待逾時超出錯誤表示查詢在嘗試時遇到鎖定等待逾時存取鎖定的資源。當多個查詢存取相同的資料並且一個查詢由於鎖定衝突而阻塞另一個查詢的執行時，可能會發生這種情況。確定導致鎖定等待的特定查詢有助於解決問題並防止將來再次發生。

識別罪魁禍首查詢：

在錯誤日誌中，單字「

transaction

」是一個關鍵指標，表明嘗試訪問資料庫的查詢正在修改一個或多個InnoDB 表。知道觸發錯誤的查詢，就可以辨識涉及的表。
• 使用指令 SHOW ENGINE INNODB STATUSG，可以取得有關受影響的表格和鎖定狀態的資訊。此命令顯示詳細信息，包括：
• RW-共享自旋：指定等待共享讀鎖的自旋數量。
• RW -excl spins: 表示等待獨佔的旋轉次數鎖。

互斥體自旋等待： 等待互斥體的自旋數量。

範例輸出：

RW-excl spins: 787046, OS waits: 39353
...
Latest foreign key error (a full list is printed in the InnoDB error log when a foreign key error occurs):
...
Transaction:
TRANSACTION 0 606162814, ACTIVE 0 sec, process no 29956, OS thread id 1223895360 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
...
Foreign key constraint fails for table `backoffice`.`attachment`:

,
  CONSTRAINT `attachment_ibfk_2` FOREIGN KEY (`file_id`) REFERENCES `file` (`file_id`)
Trying to delete or update in parent table, in index `PRIMARY` tuple:
...
But in child table `backoffice`.`attachment`, in index `PRIMARY`, there is a record:

SHOW ENGINE INNODB STATUSG的以下輸出顯示特定查詢正在等待獨佔鎖定附件表：

解決方案：

要解決此問題，您可以增加innodb_lock_wait_timeout 參數的值，此參數指定交易執行的最長時間在報告錯誤之前等待鎖定。透過增加此逾時，您可以為交易完成提供更多的緩衝時間，並減少鎖定逾時錯誤的可能性。

• 設定 innodb_lock_wait_timeout:

  [mysqld]
  innodb_lock_wait_timeout=120

• 要永久設定逾時，請將下列行加入 /etc/my.cnf並重新啟動MySQL:

  SET GLOBAL innodb_lock_wait_timeout = 120;

要設定目前會話的逾時，請執行以下查詢：

以上是如何識別MySQL中導致「鎖定等待逾時」錯誤的特定查詢？的詳細內容。更多資訊請關注PHP中文網其他相關文章！