1. 概述

在本教程中，我們將討論MySQL中的“Lock wait timeout exceeded(鎖等待超時)”錯誤。我們將討論導致這個錯誤的原因以及MySQL鎖的一些細微差別。

為了簡單起見，我們將關(guān)注MySQL的InnoDB引擎，因為它是最受歡迎的引擎之一。但是，我們可以使用這里使用的相同測試來檢查其他引擎的行為。

2. 在MySQL中的鎖

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

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

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

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

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

3. 隔離級別

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

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

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

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

4. 監(jiān)控

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

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

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

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

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

SHOW VARIABLES LIKE 'performance_schema';

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

4.1. InnoDB 狀態(tài)

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

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

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

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

4.2. 進程列表

命令 SHOW PROCESSLIST 顯示一個當前會話打開的表，該表顯示如下信息:

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

這個命令讓我們了解不同的活動會話、它們的狀態(tài)和它們的活動。

4.3. Select語句

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

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

5. 測試設(shè)置

為了運行我們的測試，我們將使用 MySQL 的 docker 映像來創(chuàng)建我們的數(shù)據(jù)庫并填充我們的測試模式，以便我們可以練習一些事務(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. 測試場景

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

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

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

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

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

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

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

6.1. 行鎖

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

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

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

10秒后，第二個會話將失敗:

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

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

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

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

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

我們可以觀察到，這兩個語句都能成功執(zhí)行，因為它們不再需要同一行的鎖。

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

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

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

在5.7里是這樣:

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

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';

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

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

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

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

鎖定超時錯誤的其他常見情況是涉及多個表的 DELETE 和 UPDATE 語句。 鎖定的行數(shù)取決于語句執(zhí)行計劃，但我們應(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)建了一個表，并啟動了一個事務(wù)，該事務(wù)在單個語句中讀取zipcode表，并寫入zipcode_backup表。

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

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

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

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

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

CREATE TEMPORARY TABLE temp_zipcode SELECT * FROM zipcode;

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

6.6. 共享和獨占鎖

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

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

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

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

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

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

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

6.7. 表鎖

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

LOCK TABLE zipcode WRITE;

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

SHOW PROCESSLIST;

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

UNLOCK TABLES;

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

6.8. 間隙鎖

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

讓我們考慮在第一個會話中執(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;

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

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

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

6.9. Deadlocks

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

讓我們模擬一個簡單的死鎖場景。 對于第一個會話，我們執(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();

最后一個 SELECT 語句將給我們當前的事務(wù) 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;

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

7. 結(jié)尾

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

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

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

最新評論