是什麼導致 MySQL 中出現“超過鎖定等待超時”錯誤？

一、概述

在本教程中，我們將討論 MySQL 中的“Lock wait timeout exceeded”錯誤。我們將討論導致此錯誤的原因以及有關 MySQL 鎖的一些細微差別。

為了簡單起見，我們將專注於 MySQL 的 InnoDB 引擎，因為它是最受歡迎的引擎之一。但是，我們可以使用此處使用的相同測試來檢查其他引擎的行為。

2. 鎖定 MySQL

鎖是控制對資源的訪問的特殊對象。對於 MySQL，這些資源可以是表、行或內部數據結構。

另一個習慣的概念是鎖定模式。鎖定模式“S”（共享）允許事務讀取一行。多個事務可以同時獲取特定行的鎖。

“X”（排他）鎖允許單個事務獲取它。事務可以更新或刪除行，而另一個必須等到鎖被釋放才能獲取它。

MySQL 也有意向鎖。這些與表相關，並指示事務打算在表中的行上獲取的鎖類型。

鎖定對於保證高並發環境中的一致性和可靠性至關重要。但是，在優化性能時，必須進行一些權衡，在這些情況下，選擇正確的隔離級別至關重要。

3. 隔離級別

MySQL InnoDB 提供四種事務隔離級別。它們在性能、一致性、可靠性和可重複性之間提供不同級別的平衡。它們分別從最不嚴格到最嚴格：

READ UNCOMMITTED ：簡而言之，所有事務都可以讀取其他人所做的所有更改，即使他們沒有提交
READ COMMITTED：只有已提交的更改對其他事務可見
可重複讀取：第一個查詢定義了一個快照，它成為該行的基線。即使另一個事務在讀取後立即更改了行，如果在第一次查詢後沒有更改，基線也將始終返回
SERIALIZABLE：行為與前一個完全一樣，除瞭如果禁用自動提交，它會在任何更新或刪除期間鎖定行，並且僅在提交後才允許讀取

現在我們了解了不同隔離級別的工作原理，讓我們運行一些測試來檢查鎖定場景。首先，為了簡短起見，我們將在默認隔離級別 REPEATABLE READ 中運行所有測試。但是，稍後我們可以運行所有其他級別的測試。

4. 監控

我們將在這裡看到的工具不一定適用於生產用途。相反，它們會讓我們了解幕後發生的事情。

這些命令將描述 MySQL 如何處理事務以及哪些鎖與哪些事務相關或如何從此類事務中獲取更多數據。同樣，這些工具將在我們的測試期間為我們提供幫助，但可能不適用於生產環境，或者至少在錯誤已經發生時不適用。

4.1。 InnoDB 狀態

命令[SHOW ENGINE INNODB STATUS](https://dev.mysql.com/doc/refman/8.0/en/show-engine.html)向我們展示了有關內部結構、對象和指標的大量信息。根據可用和活動連接的數量，輸出可能會被截斷。但是，我們只需要查看我們用例的事務部分。

在交易部分，我們會發現如下內容：

活躍交易數量
每筆交易的狀態
每個事務涉及的表數
事務獲取的鎖數
可能執行的語句可能持有事務
關於鎖等待的信息

那裡還有更多可看的東西，但現在對我們來說已經足夠了。

4.2.進程列表

命令[SHOW PROCESSLIST](https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html#:~:text=The%20SHOW%20PROCESSLIST%20statement%20is,those%20belonging%20to%20other%20users.)顯示一個表，其中包含當前打開的會話，該表顯示以下信息：

會話 ID
用戶名
主機連接
數據庫
命令/當前活動語句類型
運行時間
連接狀態
會話描述

這個命令讓我們可以了解不同的活動會話、它們的狀態和它們的活動。

4.3.選擇語句

MySQL 通過一些表公開了一些有用的信息，我們可以使用它們來了解在給定場景中應用的鎖策略的種類。它們還保存著當前交易的 id 之類的東西。

出於本文的目的，我們將使用表information_schema.innodb_trx和performance_schema.data_locks 。

5. 測試設置

為了運行我們的測試，我們將使用 MySQL 的 docker 鏡像來創建我們的數據庫並填充我們的測試模式，以便我們可以練習一些事務場景：

# Create MySQL container
 docker run --network host --name example_db -e MYSQL_ROOT_PASSWORD=root -d mysql

一旦我們有了數據庫服務器，我們就可以通過連接到它並執行腳本來創建模式：

# Logging in MySQL
 docker exec -it example_db mysql -uroot -p

然後，輸入密碼後，讓我們創建數據庫並插入一些數據：

CREATE DATABASE example_db;
 USE example_db;
 CREATE TABLE zipcode (
 code varchar(100) not null,
 city varchar(100) not null,
 country varchar(3) not null,
 PRIMARY KEY (code)
 );
 INSERT INTO zipcode(code, city, country)
 VALUES ('08025', 'Barcelona', 'ESP'),
 ('10583', 'New York', 'USA'),
 ('11075-430', 'Santos', 'BRA'),
 ('SW6', 'London', 'GBR');

6. 測試場景

要記住的最重要的事情是，當一個事務正在等待另一個事務獲得的鎖時，會發生“超出鎖定等待超時”錯誤。

事務等待的時間取決於在全局或會話級別定義的屬性[innodb_lock_wait_timeout](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout)中的值。

面臨此錯誤的可能性取決於復雜性和每秒事務的數量。但是，我們將嘗試重現一些常見的場景。

還有一點可能值得一提的是，一個簡單的重試策略就可以解決這個錯誤導致的問題。

為了在測試期間為我們提供幫助，我們將為我們打開的所有會話運行以下命令：

USE example_db;
 -- Set our timeout to 10 seconds
 SET @@SESSION.innodb_lock_wait_timeout = 10;

這將鎖定等待超時定義為 10 秒，防止我們等待太久才能看到錯誤。

6.1。行鎖

由於在不同情況下獲取行鎖，讓我們嘗試重現一個示例。

首先，我們將使用我們之前看到的登錄 MySQL 腳本從兩個不同的會話連接到服務器。之後，讓我們在兩個會話中運行以下語句：

SET autocommit=0;
 UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';

10 秒後，第二個會話將失敗：

mysql> UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

發生錯誤是因為第一個會話由於禁用自動提交而啟動了事務。接下來，一旦UPDATE語句在事務中運行，就會獲取該行的排他鎖。但是，不執行任何提交，使事務保持打開狀態並導致另一個事務繼續等待。由於提交永遠不會發生，鎖定等待的超時達到限制。這也適用於DELETE語句。

6.2.檢查數據鎖表中的行鎖

現在，讓我們在兩個會話中回滾並像以前一樣在第一個會話中運行腳本，但這一次，在第二個會話中，讓我們運行以下語句：

SET autocommit=0;
 UPDATE zipcode SET code = 'Test' WHERE code = '08025';

正如我們所看到的，兩條語句都成功執行，因為它們不再需要同一行的鎖。

為了確認這一點，我們將在任何會話或新會話中運行以下語句：

SELECT * FROM performance_schema.data_locks;

上面的語句返回四行，其中兩行是表意向鎖，指定事務可能打算鎖定表中的一行，另外兩行是記錄鎖。查看LOCK_TYPE 、 LOCK_MODE和LOCK_DATA列，我們可以確認我們剛剛描述的鎖：

是什麼導致

在兩個會話中運行回滾並再次查詢，結果是一個空數據集。

6.3.行鎖和索引

這次讓我們在WHERE子句中使用不同的列。對於第一個會話，我們將運行：

SET autocommit=0;
 UPDATE zipcode SET city = 'SW6 1AA' WHERE country = 'USA';

在第二個中，讓我們運行這些語句：

SET autocommit=0;
 UPDATE zipcode SET city = '11025-030' WHERE country = 'BRA';

剛剛發生了意想不到的事情。即使這些語句針對兩個不同的行，我們也會遇到鎖定超時錯誤。好的，如果我們在對錶performance_schema.data_locks運行SELECT語句後立即重複相同的測試，我們將看到實際上，第一個會話鎖定了所有行，而第二個會話正在等待。

問題與 MySQL 如何執行查詢以查找更新的候選者有關，因為WHERE子句中使用的列沒有索引。 MySQL 必須掃描所有行以找到與WHERE條件匹配的行，這也會導致這些行被鎖定。

確保我們的陳述是最優的很重要。

6.4.行鎖定和更新/刪除多個表

鎖定超時錯誤的其他常見情況是涉及多個表的DELETE和UPDATE語句。鎖定的行數取決於語句執行計劃，但我們應該記住，所有涉及的表都可能有一些行被鎖定。

例如，讓我們回滾所有其他事務並執行這些語句：

CREATE TABLE zipcode_backup SELECT * FROM zipcode;
 SET autocommit=0;
 DELETE FROM zipcode_backup WHERE code IN (SELECT code FROM zipcode);

在這裡，我們創建了一個表並啟動了一個從zipcode表讀取並在單個語句中寫入zipcode_backup表的事務。

下一步是在第二個會話中運行以下語句：

SET autocommit=0;
 UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';

再次，事務 2 超時，因為第一個事務獲得了表中行的鎖定。讓我們在data_lock表中運行SELECT語句來演示發生了什麼。然後，讓我們回滾兩個會話。

6.5。填充臨時表時的行鎖定

在此示例中，讓我們混合在新腳本的第一個會話中執行的 DDL 和 DML：

CREATE TEMPORARY TABLE temp_zipcode SELECT * FROM zipcode;

然後如果我們重複我們之前在第二個會話中使用的語句，我們將能夠再次看到鎖定錯誤。

6.6.共享鎖和排他鎖

我們不要忘記在每次測試結束時回滾兩個會話事務。

我們已經討論過共享鎖和排它鎖。但是，我們沒有看到如何使用LOCK IN SHARE MODE和FOR UPDATE選項顯式定義它們。首先，讓我們使用共享模式：

SET autocommit=0;
 SELECT * FROM zipcode WHERE code = 'SW6' LOCK IN SHARE MODE;

現在，我們將運行與之前相同的更新，結果又是超時。除此之外，我們應該記住這裡允許讀取。

與SHARE MODE不同， FOR UPDATE不允許讀鎖，如下所示，當我們在第一個會話中運行語句時：

SET autocommit=0;
 SELECT * FROM zipcode WHERE code = 'SW6' FOR UPDATE;

然後，我們使用之前在第一個會話中使用的SHARE MODE選項運行相同的SELECT語句，但現在在第二個會話中，我們將再次觀察到超時錯誤。回顧一下，可以為多個會話獲取SHARE MODE鎖，並且它會鎖定寫入。排他鎖或FOR UPDATE選項允許讀取但不允許鎖定讀取或寫入。

6.7.表鎖

表鎖沒有超時，不推薦用於 InnoDB：

LOCK TABLE zipcode WRITE;

一旦我們運行它，我們可以打開另一個會話，嘗試選擇或更新，並檢查它是否會被鎖定，但這一次，沒有超時發生。更進一步，我們可以打開第三個會話並運行：

SHOW PROCESSLIST;

它顯示活動會話及其狀態，我們將看到第一個會話處於睡眠狀態，第二個會話正在等待表的元數據鎖定。在這種情況下，解決方案將運行下一個命令：

UNLOCK TABLES;

我們可能會發現會話等待獲取一些元數據鎖的其他場景是在 DDL 執行期間，例如ALTER TABLE 。

6.8.間隙鎖

當索引記錄的特定間隔被鎖定時，會發生間隙鎖，並且另一個會話嘗試在此間隔內執行某些操作。在這種情況下，甚至插入也會受到影響。

讓我們考慮在第一個會話中執行的以下語句：

CREATE TABLE address_type ( id bigint(20) not null, name varchar(255) not null, PRIMARY KEY (id) );
 SET autocommit=0;
 INSERT INTO address_type(id, name) VALUES (1, 'Street'), (2, 'Avenue'), (5, 'Square');
 COMMIT;
 SET autocommit=0;
 SELECT * FROM address_type WHERE id BETWEEN 1 and 5 LOCK IN SHARE MODE;

在第二個會話中，我們將運行以下語句：

SET autocommit=0;
 INSERT INTO address_type(id, name) VALUES (3, 'Road'), (4, 'Park');

運行數據鎖後，我們在第三個會話中選擇語句，以便檢查新的LOCK MODE值GAP 。這也可以應用於UPDATE和DELETE語句。

6.9.死鎖

默認情況下，MySQL 會嘗試識別死鎖，如果它設法解決事務之間的依賴關係圖，它會自動終止其中一個任務以允許其他任務通過。否則，我們會得到一個鎖定超時錯誤，就像我們之前看到的那樣。

讓我們模擬一個簡單的死鎖場景。對於第一個會話，我們執行：

SET autocommit=0;
 SELECT * FROM address_type WHERE id = 1 FOR UPDATE;
 SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();

最後一個SELECT語句將給我們當前的事務 ID。稍後我們將需要它來檢查日誌。然後，對於第二個會話，讓我們運行：

SET autocommit=0;
 SELECT * FROM address_type WHERE id = 2 FOR UPDATE;
 SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();
 SELECT * FROM address_type WHERE id = 1 FOR UPDATE;

在序列中，我們回到會話一併運行：

SELECT * FROM address_type WHERE id = 2 FOR UPDATE;

馬上，我們會得到一個錯誤：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

最後，我們進入第三個會話，我們運行：

SHOW ENGINE INNODB STATUS;

該命令的輸出應與此類似：

------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 *** (1) TRANSACTION:
 TRANSACTION 4036, ACTIVE 11 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
 MySQL thread id 9, OS thread handle 139794615064320, query id 252...
 SELECT * FROM address_type WHERE id = 1 FOR UPDATE
 *** (1) HOLDS THE LOCK(S):
 RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4036 lock_mode X locks rec but not gap
 Record lock
 ...

 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4036 lock_mode X locks rec but not gap waiting
 Record lock
 ...
 *** (2) TRANSACTION:
 TRANSACTION 4035, ACTIVE 59 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 3 lock struct(s), ... , 2 row lock(s)
 MySQL thread id 11, .. query id 253 ...
 SELECT * FROM address_type WHERE id = 2 FOR UPDATE
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4035 lock_mode X locks rec but not gap
 Record lock
 ...
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4035 lock_mode X locks rec but not gap waiting
 Record lock
 ...
 *** WE ROLL BACK TRANSACTION (2)
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 4037
 ...
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ...
 ---TRANSACTION 4036, ACTIVE 18 sec
 3 lock struct(s), heap size 1128, 2 row lock(s)
 MySQL thread id 9, ... , query id 252 ...

使用我們之前得到的事務id，我們可以找到很多有用的信息，比如錯誤時刻的連接狀態、行鎖的數量、最後執行的命令、持有鎖的描述、事務正在等待的鎖的描述。之後，它對死鎖中涉及的其他事務重複相同的操作。此外，最後，我們找到了有關哪些事務被回滾的信息。

7. 結論

在本文中，我們研究了 MySQL 中的鎖，它們是如何工作的，以及它們何時導致“超出鎖定等待超時”錯誤。

我們定義了允許我們重現此錯誤並在處理事務時檢查數據庫服務器的內部細微差別的測試場景。

本作品係原創或者翻譯，採用《署名-非商業性使用-禁止演繹4.0國際》許可協議