1. 概述

在本教程中，我們將討論MySQL中的“Lock wait timeout exceeded(鎖等待超時(shí))”錯(cuò)誤。我們將討論導(dǎo)致這個(gè)錯(cuò)誤的原因以及MySQL鎖的一些細(xì)微差別。

為了簡單起見，我們將關(guān)注MySQL的InnoDB引擎，因?yàn)樗亲钍軞g迎的引擎之一。但是，我們可以使用這里使用的相同測試來檢查其他引擎的行為。

2. 在MySQL中的鎖

lock是一個(gè)特殊的對象，用于控制對資源的訪問。在MySQL中，這些資源可以是表、行或內(nèi)部數(shù)據(jù)結(jié)構(gòu)。

另一個(gè)需要習(xí)慣的概念是鎖模式。鎖模式S(共享)允許事務(wù)讀取一行。多個(gè)事務(wù)可以同時(shí)獲得某一行的鎖。

X(排他)鎖允許單個(gè)事務(wù)獲取它。一個(gè)事務(wù)可以更新或刪除行，而其他事務(wù)必須等待鎖被釋放，以便獲取它。

MySQL 也有意向鎖。 這些與表相關(guān)，并指示事務(wù)打算在表中的行上獲取的鎖類型。

鎖定對于保證高并發(fā)環(huán)境中的一致性和可靠性至關(guān)重要。 但是，在優(yōu)化性能時(shí)，必須進(jìn)行一些權(quán)衡，在這些情況下，選擇正確的隔離級(jí)別至關(guān)重要。

3. 隔離級(jí)別

MySQL InnoDB 提供4個(gè)事務(wù) 隔離級(jí)別。 它們在性能、一致性、可靠性和可重復(fù)性之間提供不同級(jí)別的平衡。 它們分別從最不嚴(yán)格到最嚴(yán)格：

• READ UNCOMMITTED: 顧名思義，就是讀未提交，也就是說事務(wù)所作的修改在未提交前，其他并發(fā)事務(wù)是可以讀到的。
  存在"臟讀"問題。
• READ COMMITTED: 顧名思義，就是讀已提交，一個(gè)事務(wù)只能看到其他并發(fā)的已提交事務(wù)所作的修改。很顯然，該級(jí)別可以解決Read Uncommitted中出現(xiàn)的“臟讀“問題。除了Mysql，很多數(shù)據(jù)庫都以Read Committed作為默認(rèn)的事務(wù)隔離級(jí)別。
  存在"不可重復(fù)讀"問題。雖然解決了“臟讀”問題，但是Read Committed不能保證在一個(gè)事務(wù)中每次讀都能讀到相同的數(shù)據(jù)
• REPEATABLE READ: 顧名思義，可重復(fù)讀，也即在一個(gè)事務(wù)范圍內(nèi)相同的查詢會(huì)返回相同的數(shù)據(jù)。
  存在"幻讀"問題。也即在一次事務(wù)范圍內(nèi)多次進(jìn)行查詢，如果其他并發(fā)事務(wù)中途插入了新的記錄，那么之后的查詢會(huì)讀取到這些“幻影”行。
• SERIALIZABLE: 顧名思義，可串行化的，也即并發(fā)事務(wù)串行執(zhí)行。很顯然，該級(jí)別可以避免前面講到的所有問題：“臟讀”、“不可重復(fù)讀”和“幻讀”。
  代價(jià)是處理事務(wù)的吞吐量低，嚴(yán)重浪費(fèi)數(shù)據(jù)庫的性能，因此要慎用此事務(wù)隔離級(jí)別。

??注意: *"不可重復(fù)讀"對應(yīng)的是修改即Update，“幻讀”*對應(yīng)的是插入即Insert。

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

4. 監(jiān)控

我們將在這里看到的工具不一定適用于生產(chǎn)用途。 相反，它們會(huì)讓我們了解幕后發(fā)生的事情。

這些命令將描述 MySQL 如何處理事務(wù)以及哪些鎖與哪些事務(wù)相關(guān)或如何從此類事務(wù)中獲取更多數(shù)據(jù)。 再說一遍，這些工具將在我們的測試期間幫助我們，但可能不適用于生產(chǎn)環(huán)境，或者至少在錯(cuò)誤已經(jīng)發(fā)生時(shí)不適用.

開啟收集數(shù)據(jù)庫服務(wù)器性能參數(shù)(5.7以上是自動(dòng)開啟的),在MySql的配置文件中的[mysqld]段里加入一下語句:

# 收集數(shù)據(jù)庫服務(wù)器性能參數(shù)
performance_schema=ON
performance_schema_instrument='%lock%=on'

檢查性能數(shù)據(jù)庫是否啟動(dòng)的命令:

SHOW VARIABLES LIKE 'performance_schema';

?警告: 如果打開performance_schema選項(xiàng)來收集執(zhí)行過的語句和事務(wù)會(huì)有性能損失，一般建議需要的時(shí)候開啟，然后在線關(guān)閉掉。

4.1. InnoDB 狀態(tài)

命令 SHOW ENGINE INNODB STATUS 向我們展示了有關(guān)內(nèi)部結(jié)構(gòu)、對象、 和指標(biāo)。 根據(jù)可用和活動(dòng)連接的數(shù)量，輸出可能會(huì)被截?cái)唷?但是，我們只需要查看我們用例的事務(wù)部分。

在事務(wù)部分，我們會(huì)發(fā)現(xiàn)如下內(nèi)容:

• 活動(dòng)事務(wù)數(shù)
• 每個(gè)事務(wù)的狀態(tài)
• 每個(gè)事務(wù)中涉及的表數(shù)
• 事務(wù)獲取的鎖數(shù)
• 執(zhí)行的語句可能持有的事務(wù)
• 鎖等待信息

那里有很多值得看的東西，但現(xiàn)在對我們來說已經(jīng)足夠了。

4.2. 進(jìn)程列表

命令 SHOW PROCESSLIST 顯示一個(gè)當(dāng)前會(huì)話打開的表，該表顯示如下信息:

• 會(huì)話id
• 用戶名
• 主機(jī)連接
• 數(shù)據(jù)庫
• 命令/當(dāng)前活動(dòng)語句類型
• 運(yùn)行時(shí)間
• 連接狀態(tài)
• 會(huì)話描述

這個(gè)命令讓我們了解不同的活動(dòng)會(huì)話、它們的狀態(tài)和它們的活動(dòng)。

4.3. Select語句

MySQL通過一些表公開了一些有用的信息，我們可以使用它們來理解給定場景中應(yīng)用的鎖策略的類型。它們還保存諸如當(dāng)前事務(wù)id之類的東西。

在本文中，我們將使用表 information_schema.innodb_trx 和 performance_schema.data_locks。

5. 測試設(shè)置

為了運(yùn)行我們的測試，我們將使用 MySQL 的 docker 映像來創(chuàng)建我們的數(shù)據(jù)庫并填充我們的測試模式，以便我們可以練習(xí)一些事務(wù)場景 ：

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

一旦我們有了數(shù)據(jù)庫服務(wù)器，我們就可以通過連接到它并執(zhí)行腳本來創(chuàng)建模式：

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

然后，輸入密碼后，讓我們創(chuàng)建數(shù)據(jù)庫并插入一些數(shù)據(jù)：

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. 測試場景

要記住的最重要的事情是，當(dāng)一個(gè)事務(wù)正在等待另一個(gè)事務(wù)獲得的鎖時(shí)，會(huì)發(fā)生“Lock wait timeout exceeded(超過鎖定等待超時(shí))”錯(cuò)誤。

事務(wù)將等待的時(shí)間取決于全局或會(huì)話級(jí)別的屬性 innodb_lock_wait_timeout 中定義的值。

面臨此錯(cuò)誤的可能性取決于復(fù)雜性和每秒事務(wù)的數(shù)量。 但是，我們將嘗試重現(xiàn)一些常見的場景。

??提示: 還有一點(diǎn)可能值得一提的是，一個(gè)簡單的重試策略就可以解決這個(gè)錯(cuò)誤導(dǎo)致的問題。

為了在測試過程中提供幫助，我們將對打開的所有會(huì)話運(yùn)行以下命令:

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

這將鎖等待超時(shí)定義為10秒，防止我們等待太久才看到錯(cuò)誤。

6.1. 行鎖

由于行鎖是在不同的情況下獲得的，讓我們試著重現(xiàn)一個(gè)示例。

首先，我們將使用前面看到的登錄MySQL腳本從兩個(gè)不同的會(huì)話連接到服務(wù)器。之后，讓我們在兩個(gè)會(huì)話中運(yùn)行下面的語句:

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

10秒后，第二個(gè)會(huì)話將失敗:

mysql>  UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
> 1205 - Lock wait timeout exceeded; try restarting transaction
> Time: 11.095s

發(fā)生錯(cuò)誤的原因是由于禁用了自動(dòng)提交，第一個(gè)會(huì)話啟動(dòng)了一個(gè)事務(wù)。接下來，一旦UPDATE語句在事務(wù)中運(yùn)行，就會(huì)獲得該行的獨(dú)占鎖。但是，沒有執(zhí)行提交，使事務(wù)處于打開狀態(tài)，并導(dǎo)致其他事務(wù)一直等待。由于提交沒有發(fā)生，鎖等待的超時(shí)達(dá)到了限制。這也適用于DELETE語句。

6.2. 檢查數(shù)據(jù)鎖表中的行鎖

現(xiàn)在，讓我們在兩個(gè)會(huì)話中回滾，并像在第一個(gè)會(huì)話中一樣運(yùn)行腳本，但這次，在第二個(gè)會(huì)話中，讓我們運(yùn)行以下語句:

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

我們可以觀察到，這兩個(gè)語句都能成功執(zhí)行，因?yàn)樗鼈儾辉傩枰恍械逆i。

為了確認(rèn)這一點(diǎn)，我們將在任何一個(gè)會(huì)話或新的會(huì)話中運(yùn)行以下語句:

SELECT * FROM performance_schema.data_locks;
//8.0以下用這個(gè): SELECT * FROM sys.innodb_lock_waits;

上面的語句返回四行，其中兩行是表意向鎖，指定事務(wù)可能打算鎖表中的一行，另外兩行是記錄鎖. 查看列LOCK_TYPE、LOCK_MODE和LOCK_DATA，我們可以確認(rèn)剛才描述的鎖:

在5.7里是這樣:

在兩個(gè)會(huì)話中運(yùn)行回滾并再次查詢，結(jié)果是一個(gè)空數(shù)據(jù)集。

6.3. 行鎖和索引

這次讓我們在 WHERE 子句中使用不同的列。 對于第一個(gè)會(huì)話，我們將運(yùn)行：

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

在第二個(gè)會(huì)話中，讓我們運(yùn)行這些語句：

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

剛剛發(fā)生了意想不到的事情。 即使這些語句針對兩個(gè)不同的行，我們也會(huì)遇到鎖定超時(shí)錯(cuò)誤。 好的，如果我們在對表 performance_schema.data_locks 運(yùn)行 SELECT 語句后立即重復(fù)同樣的測試，我們會(huì)看到實(shí)際上，第一個(gè)會(huì)話鎖定了所有行，而第二個(gè)會(huì)話正在等待。

問題與 MySQL 執(zhí)行查詢 如何查找更新的候選者有關(guān)，因?yàn)?WHERE 子句中使用的列沒有索引。 MySQL 必須掃描所有行以找到與 WHERE 條件匹配的行，這也會(huì)導(dǎo)致這些行被鎖定。

?重要: 確保我們的SQL語句是最優(yōu)的是很重要的.

6.4. 行鎖 和 涉及多個(gè)表的更新/刪除

鎖定超時(shí)錯(cuò)誤的其他常見情況是涉及多個(gè)表的 DELETE 和 UPDATE 語句。 鎖定的行數(shù)取決于語句執(zhí)行計(jì)劃，但我們應(yīng)該記住，所有涉及的表都可能有一些行被鎖定。

例如，讓我們回滾所有其他事務(wù)并執(zhí)行以下語句:

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

在這里，我們創(chuàng)建了一個(gè)表，并啟動(dòng)了一個(gè)事務(wù)，該事務(wù)在單個(gè)語句中讀取zipcode表，并寫入zipcode_backup表。

下一步是在第二個(gè)會(huì)話中運(yùn)行以下語句:

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

再次，事務(wù) 2 超時(shí)，因?yàn)榈谝粋€(gè)事務(wù)獲得了表中行的鎖定。 讓我們在 data_lock 表中運(yùn)行 SELECT 語句來演示發(fā)生了什么。 然后，讓我們回滾兩個(gè)會(huì)話。

6.5. 填充臨時(shí)表時(shí)的行鎖定

在這個(gè)例子中，讓我們在新腳本的第一個(gè)會(huì)話中混合執(zhí)行DDL和DML語句:

CREATE TEMPORARY TABLE temp_zipcode SELECT * FROM zipcode;

然后，如果我們在第二個(gè)會(huì)話中重復(fù)之前使用的語句，我們將能夠再次看到鎖錯(cuò)誤。

6.6. 共享和獨(dú)占鎖

我們不要忘記在每次測試結(jié)束時(shí)回滾兩個(gè)會(huì)話事務(wù)。

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

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

現(xiàn)在，我們將運(yùn)行與之前相同的更新，結(jié)果又是超時(shí)。 除此之外，我們應(yīng)該記住這里允許讀取。

與 LOCK IN SHARE MODE 不同，FOR UPDATE 不允許讀鎖，如下所示，當(dāng)我們在第一個(gè)會(huì)話中運(yùn)行語句時(shí)：

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

然后，我們運(yùn)行相同的SELECT語句，并在第一個(gè)會(huì)話中使用LOCK IN SHARE MODE選項(xiàng)，但現(xiàn)在在第二個(gè)會(huì)話中，我們將再次觀察到超時(shí)錯(cuò)誤。總結(jié)一下，可以為多個(gè)會(huì)話獲取LOCK IN SHARE MODE鎖，并且它鎖定 寫 操作。獨(dú)占鎖或FOR UPDATE選項(xiàng)允許讀，但不允許讀鎖或?qū)戞i。

6.7. 表鎖

表鎖沒有超時(shí)，不推薦用于InnoDB：

LOCK TABLE zipcode WRITE;

一旦我們運(yùn)行它，我們可以打開另一個(gè)會(huì)話，嘗試選擇或更新，并檢查它是否會(huì)被鎖定，但這一次，沒有超時(shí)發(fā)生。 更進(jìn)一步，我們可以打開第三個(gè)會(huì)話并運(yùn)行：

SHOW PROCESSLIST;

它顯示活動(dòng)會(huì)話及其狀態(tài)，我們將看到第一個(gè)會(huì)話處于睡眠狀態(tài)，第二個(gè)會(huì)話正在等待表的元數(shù)據(jù)鎖定。 在這種情況下，解決方案將是運(yùn)行下一個(gè)命令：

UNLOCK TABLES;

我們可能會(huì)發(fā)現(xiàn)會(huì)話等待獲取某些元數(shù)據(jù)鎖的其他場景是在 DDL 執(zhí)行期間，例如 ALTER TABLEs。

6.8. 間隙鎖

Gap locks發(fā)生在索引記錄被鎖定的特定時(shí)間間隔內(nèi)，而另一個(gè)會(huì)話試圖在這個(gè)時(shí)間間隔內(nèi)執(zhí)行某些操作。在這種情況下，甚至插入也會(huì)受到影響。

讓我們考慮在第一個(gè)會(huì)話中執(zhí)行的以下語句：

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;

在第二個(gè)會(huì)話中，我們將運(yùn)行以下語句：

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

運(yùn)行數(shù)據(jù)鎖后，我們在第三個(gè)會(huì)話中選擇語句，以便檢查新的 LOCK MODE 值 GAP。 這也適用于 UPDATE 和 DELETE 語句。

6.9. Deadlocks

默認(rèn)情況下，MySQL 會(huì)嘗試識(shí)別死鎖，如果它設(shè)法解決事務(wù)之間的依賴關(guān)系圖，它會(huì)自動(dòng)終止其中一個(gè)任務(wù)以允許其他任務(wù)通過。 否則，我們會(huì)得到一個(gè)鎖定超時(shí)錯(cuò)誤，就像我們之前看到的那樣。

讓我們模擬一個(gè)簡單的死鎖場景。 對于第一個(gè)會(huì)話，我們執(zhí)行：

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();

最后一個(gè) SELECT 語句將給我們當(dāng)前的事務(wù) ID。 稍后我們將需要它來檢查日志。 然后，對于第二個(gè)會(huì)話，讓我們運(yùn)行：

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;

在這個(gè)序列中，我們回到會(huì)話一并運(yùn)行:

SELECT * FROM address_type WHERE id = 2 FOR UPDATE;

馬上，我們會(huì)得到一個(gè)錯(cuò)誤：

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

最后，我們進(jìn)入第三個(gè)會(huì)話，我們運(yùn)行：

SHOW ENGINE INNODB STATUS;

該命令的輸出應(yīng)與此類似：

------------------------
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 ...

使用我們之前得到的事務(wù)id，我們可以找到很多有用的信息，比如錯(cuò)誤時(shí)刻的連接狀態(tài)、行鎖的數(shù)量、最后執(zhí)行的命令、持有鎖的描述、 事務(wù)正在等待的鎖的描述。 之后，它對死鎖中涉及的其他事務(wù)重復(fù)相同的操作。 此外，最后，我們找到了有關(guān)哪些事務(wù)被回滾的信息。

7. 結(jié)尾

在本文中，我們研究了 MySQL 中的鎖，它們是如何工作的，以及它們何時(shí)導(dǎo)致“超出鎖定等待超時(shí)”錯(cuò)誤。

我們定義了測試場景，允許我們重現(xiàn)這個(gè)錯(cuò)誤，并在處理事務(wù)時(shí)檢查數(shù)據(jù)庫服務(wù)器的內(nèi)部細(xì)微差別。

到此這篇關(guān)于MySQL出現(xiàn)"Lock wait timeout exceeded"錯(cuò)誤的原因是什么的文章就介紹到這了,更多相關(guān)MySQL Lock wait timeout exceeded錯(cuò)誤內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！