mysql數據庫表鎖等待超時怎么解決

當你開始執行一個 ALTER ，而你遇到了可怕的“元數據鎖定等待”，我敢肯定你一定遇見過。我最近遇到了一個案例，其中被更改的表要執行一個很小范圍的更新（100行）。ALTER 在負載測試期間一直等待了幾個小時。在停止負載測試后，ALTER 按預期在不到一秒的時間內就完成了。那么這里發生了什么？

創新互聯建站是一家專注于網站設計、網站建設與策劃設計,漢中網站建設哪家好?創新互聯建站做網站,專注于網站建設十載,網設計領域的專業建站公司;建站業務涵蓋:漢中等地區。漢中做網站價格咨詢:18982081108

檢查外鍵

每當有奇數次鎖定時，我的第一直覺就是檢查外鍵。當然這張表有一些外鍵引用了一個更繁忙的表。但是這種行為似乎仍然很奇怪。對表運行 ALTER 時，會針對子表請求一個 SHARED_UPGRADEABLE 元數據鎖。還有針對父級的 SHARED_READ_ONLY 元數據鎖。

我們來看看如何根據文檔獲取元數據鎖定[1]：

如果給定鎖定有多個服務器，則首先滿足最高優先級鎖定請求，并且與 max_write_lock_count系統變量有關。寫鎖定請求的優先級高于讀取鎖定請求。

[1]：

請務必注意鎖定順序是序列化的：語句逐個獲取元數據鎖，而不是同時獲取，并在此過程中執行死鎖檢測。

通常在考慮隊列時考慮先進先出。如果我發出以下三個語句（按此順序），它們將按以下順序完成：

1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent

但是當子 ALTER 語句請求對父進行讀取鎖定時，盡管排序，但兩個插入將在 ALTER 之前完成。以下是可以演示此示例的示例場景：

數據初始化：

CREATE TABLE `parent` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`val` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

CREATE TABLE `child` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`parent_id` int(11) DEFAULT NULL,

`val` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_parent` (`parent_id`),

CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION

) ENGINE=InnoDB;

INSERT INTO `parent` VALUES (1, "one"), (2, "two"), (3, "three"), (4, "four");

Session 1:

start transaction;update parent set val = "four-new" where id = 4;

Session 2:

alter table child add index `idx_new` (val);

Session 3:

start transaction;update parent set val = "three-new" where id = 3;

此時，會話 1 具有打開的事務，并且處于休眠狀態，并在父級上授予寫入元數據鎖定。 會話 2 具有在子級上授予的可升級（寫入）鎖定，并且正在等待父級的讀取鎖定。最后會話 3 具有針對父級的授權寫入鎖定：

mysql select * from performance_schema.metadata_locks;+-------------+-------------+-------------------+---------------+-------------+| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE ? ? ? ? | LOCK_DURATION | LOCK_STATUS |+-------------+-------------+-------------------+---------------+-------------+| TABLE ? ? ? | child ? ? ? | SHARED_UPGRADABLE | TRANSACTION ? | GRANTED ? ? | - ALTER (S2)| TABLE ? ? ? | parent ? ? ?| SHARED_WRITE ? ? ?| TRANSACTION ? | GRANTED ? ? | - UPDATE (S1)| TABLE ? ? ? | parent ? ? ?| SHARED_WRITE ? ? ?| TRANSACTION ? | GRANTED ? ? | - UPDATE (S3)| TABLE ? ? ? | parent ? ? ?| SHARED_READ_ONLY ?| STATEMENT ? ? | PENDING ? ? | - ALTER (S2)+-------------+-------------+-------------------+---------------+-------------+

請注意，具有掛起鎖定狀態的唯一會話是會話 2（ALTER）。會話 1 和會話 3 （分別在 ALTER 之前和之后發布）都被授予了寫鎖。排序失敗的地方是在會話 1 上發生提交的時候。在考慮有序隊列時，人們會期望會話 2 獲得鎖定，事情就會繼續進行。但是，由于元數據鎖定系統的優先級性質，會話 3 具有鎖定，會話 2 仍然等待。

如果另一個寫入會話進入并啟動新事務并獲取針對父表的寫鎖定，則即使會話 3 完成，ALTER 仍將被阻止。

只要我保持一個對父表打開元數據鎖定的活動事務，子表上的 ALTER 將永遠不會完成。更糟糕的是，由于子表上的寫鎖定成功（但是完整語句正在等待獲取父讀鎖定），所以針對子表的所有傳入讀取請求都將被阻止！

另外，請考慮一下您通常如何對無法完成的語句進行故障排除。您查看已經打開較長時間的事務（在進程列表和 InnoDB 狀態中）。但由于阻塞線程現在比 ALTER 線程更年輕，因此您將看到的最舊的事務/線程是 ALTER 。

這正是這種情況下發生的情況。在準備發布時，我們的客戶端正在運行 ALTER 語句并結合負載測試（一種非常好的做法！）以確保順利發布。問題是負載測試保持對父表打開一個活動的寫事務。這并不是說它只是一直在寫，而是有多個線程，一個總是活躍的。 這阻止了 ALTER 完成并阻止對相對靜態的子表的隨后的讀請求。

幸運的是，這個問題有一個解決方案（除了從設計模式中驅逐外鍵）。變量?max_write_lock_count[2]?可用于允許在寫入鎖定之后在讀取鎖定之前授予讀取鎖定連續寫鎖。默認情況下，此變量設置為 18446744073709551615，如果你對該表發出 10,000 次寫入/秒，那么你的讀將被鎖定 5800 萬年……

MySQL數據庫表被鎖、解鎖，刪除事務

在程序員的職業生涯中，總會遇到數據庫表被鎖的情況，前些天就又撞見一次。由于業務突發需求，各個部門都在批量操作、導出數據，而數據庫又未做讀寫分離，結果就是：數據庫的某張表被鎖了！

用戶反饋系統部分功能無法使用，緊急排查，定位是數據庫表被鎖，然后進行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點贊收藏，以備不時之需。

用戶反饋某功能頁面報502錯誤，于是第一時間看服務是否正常，數據庫是否正常。在控制臺看到數據庫CPU飆升，堆積大量未提交事務，部分事務已經阻塞了很長時間，基本定位是數據庫層出現問題了。

查看阻塞事務列表，發現其中有鎖表現象，本想利用控制臺直接結束掉阻塞的事務，但控制臺賬號權限有限，于是通過客戶端登錄對應賬號將鎖表事務kill掉，才避免了情況惡化。

下面就聊聊，如果當突然面對類似的情況，我們該如何緊急響應？

想象一個場景，當然也是軟件工程師職業生涯中會遇到的一種場景：原本運行正常的程序，某一天突然數據庫的表被鎖了，業務無法正常運轉，那么我們該如何快速定位是哪個事務鎖了表，如何結束對應的事物？

首先最簡單粗暴的方式就是：重啟MySQL。對的，網管解決問題的神器——“重啟”。至于后果如何，你能不能跑了，要你自己三思而后行了！

重啟是可以解決表被鎖的問題的，但針對線上業務很顯然不太具有可行性。

下面來看看不用跑路的解決方案：

遇到數據庫阻塞問題，首先要查詢一下表是否在使用。

如果查詢結果為空，那么說明表沒在使用，說明不是鎖表的問題。

如果查詢結果不為空，比如出現如下結果：

則說明表（test）正在被使用，此時需要進一步排查。

查看數據庫當前的進程，看看是否有慢SQL或被阻塞的線程。

執行命令：

該命令只顯示當前用戶正在運行的線程，當然，如果是root用戶是能看到所有的。

在上述實踐中，阿里云控制臺之所以能夠查看到所有的線程，猜測應該使用的就是root用戶，而筆者去kill的時候，無法kill掉，是因為登錄的用戶非root的數據庫賬號，無法操作另外一個用戶的線程。

如果情況緊急，此步驟可以跳過，主要用來查看核對：

如果情況緊急，此步驟可以跳過，主要用來查看核對：

看事務表INNODB_TRX中是否有正在鎖定的事務線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個sleep的線程事務一直沒有commit或者rollback，而是卡住了，需要手動kill掉。

搜索的結果中，如果在事務表發現了很多任務，最好都kill掉。

執行kill命令：

對應的線程都執行完kill命令之后，后續事務便可正常處理。

針對緊急情況，通常也會直接操作第一、第二、第六步。

這里再補充一些MySQL鎖相關的知識點：數據庫鎖設計的初衷是處理并發問題，作為多用戶共享的資源，當出現并發訪問的時候，數據庫需要合理地控制資源的訪問規則，而鎖就是用來實現這些訪問規則的重要數據結構。

根據加鎖的范圍，MySQL里面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（metadata lock，MDL)。

表鎖是在Server層實現的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機制。表鎖通過lock tables… read/write來實現，而對于InnoDB來說，一般會采用行級鎖。畢竟鎖住整張表影響范圍太大了。

另外一個表級鎖是MDL（metadata lock），用于并發情況下維護數據的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時會被自動加上。

常見的一種鎖表場景就是有事務操作處于：Waiting for table metadata lock狀態。

MySQL在進行alter table等DDL操作時，有時會出現Waiting for table metadata lock的等待場景。

一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態，后續對該表的任何操作（包括讀）都無法進行，因為它們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果核心表出現了鎖等待隊列，就會造成災難性的后果。

通過show processlist可以看到表上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨占鎖，會進行等待。

通過show processlist看不到表上有任何操作，但實際上存在有未提交的事務，可以在information_schema.innodb_trx中查看到。在事務沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨占鎖。

處理方法：通過 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然后kill掉，讓其回滾。

通過show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務。很可能是因為在一個顯式的事務中，對表進行了一個失敗的操作（比如查詢了一個不存在的字段），這時事務沒有開始，但是失敗語句獲取到的鎖依然有效，沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。

處理方法：通過performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。

總之，alter table的語句是很危險的（核心是未提交事務或者長事務導致的），在操作之前要確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有顯式事務中的報錯語句。

如果有alter table的維護任務，在無人監管的時候運行，最好通過lock_wait_timeout設置好超時時間，避免長時間的metedata鎖等待。

關于MySQL的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發生，當然這需要一定經驗的支撐。但更重要的是，如果發現鎖表我們要能夠快速的響應，快速的解決問題，避免影響正常業務，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。

詳解MySQL（InnoDB）如何處理死鎖

鎖是需要事務結束后才釋放的。

一個是 MVCC，一個是兩階段鎖協議。

為什么要并發控制呢？是因為多個用戶同時操作 MySQL 的時候，為了提高并發性能并且要求如同多個用戶的請求過來之后如同串行執行的一樣（為了解決臟讀、不可重復讀、幻讀）

官方定義：

兩階段鎖協議是指所有事務必須分兩個階段對數據加鎖和解鎖，在對任何數據進行讀、寫操作之前，事務首先要獲得對該數據的封鎖；在釋放一個封鎖之后，事務不再申請和獲得任何其他封鎖。

對應到 MySQL 上分為兩個階段：

但是兩階段鎖協議不要求事務必須一次將所有需要使用的數據加鎖（innodb在需要的索引列數據才鎖行），并且在加鎖階段沒有順序要求，所以這種并發控制方式會形成死鎖。

MySQL有兩種死鎖處理方式：

死鎖檢測 （默認開啟）

死鎖檢測的原理是構建一個以事務為頂點、鎖為邊的有向圖，判斷有向圖是否存在環，存在即有死鎖。

回滾

檢測到死鎖之后，選擇插入更新或者刪除的行數最少的事務回滾，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段來判斷。

收集死鎖信息：

減少死鎖：

死鎖解決：

MySQL 鎖等待超時（Lock wait timeout exceeded）

問題：Lock wait timeout exceeded; try restarting transaction

MySQL版本：5.6.44

官方文檔

意思是：InnoDB在鎖等待超時過期時報告此錯誤。等待時間過長的語句被回滾（而不是整個事務）。如果SQL語句需要等待其他事務完成的時間更長，則可以增加 innodb_lock_wait_timeout 配置選項的值；如果太多長時間運行的事務導致鎖定問題并降低繁忙系統上的并發性，則可以減少該選項的值。

鎖等待超時，可能是出現了死鎖，也可能有事務長時間未提交

庫：information_schema

表：

查看各表信息

innodb_trx 表

innodb_locks 表

innodb_lock_waits 表

processlist 表

模擬出現死鎖

準備一張只有主鍵的表：t_test (id)

Navicat 新建查詢1

Navicat 新建查詢2

檢查是否鎖表

查詢當前正在執行的事務

查詢當前出現的鎖

查詢鎖等待對應的關系

查詢等待鎖的事務所執行的SQL

最后，事務2 等待鎖超時報錯： Lock wait timeout exceeded; try restarting transaction;

通過事務線程ID查找進程信息

win10 查看端口信息

記一次Mysql故障排查(lock wait timeout exceeded;try restarting transaction)

最近接了一個鍋,進入新公司接手了一個進入交付階段的項目.在code?review的時候發現很多問題,然后開始修復bug.

在測試階段突然發現幾乎所有涉及到更新的操作都失敗,下面貼出異常信息.

第一次 出現的時候百度了一下,猜想可能是多服務部署資源沖突,重啟服務故障消失.所以沒有特別重視

第二次 出現的時候只有測試環境部署,不存在多機資源沖突的問題,猜想是多線程資源交叉導致的,于是給可能導致資源競爭的地方加上了分布式鎖.

由于無法重現故障,所以并沒有確認問題得到解決.

第三次 故障依舊,當發現問題依然存在的時候,開始認真反思,發現自己解決問題的思路明顯有問題,過于片面,一直都只在應用層面尋求解決問題的辦法,而且解決問題的方式也只是在嘗試百度出來的方法.并沒有去思考更深層的問題.

在Mysql5.5中，information_schema 庫中增加了三個關于鎖的表（MEMORY引擎）；

INNODB_TRX ## 當前運行的所有事務

INNODB _LOCKS ## 當前出現的鎖

INNODB_LOCK_WAITS ## 鎖等待的對應關系

通過查詢 INNODB_TRX 發現

當前事務中又兩個RUNNING狀態開始時間在一個小時之前

開始一直以為是鎖表了

查看了 INNODB _LOCKS? 事務信息之后發現有4行數據被鎖住了一直沒有釋放

從這里開始發現問題了,應用已經拋了異常,事務理所當然的應該回滾才對,為什么資源依然沒有釋放,導致持續的阻塞呢?

其實最開始的異常信息就已經給出了答案,回到開始的地方,再看異常信息就很清楚了,應用里面的異常類是 MySQLTransactionRollBackException

是一個回滾異常, 這就說明在事務回滾的時候出了問題資源沒有得到釋放

然后開始查詢 MySQLTransactionRollBackException? 相關的信息

這個時候 innodb_rollback_on_timeout =0(默認配置)這個MySQL的配置開始進入我的視線,

舉個栗子

事務在鎖等待超時后是回滾事務內所有的statement還是最后一條語句；

0表示rollback最后一條語句，默認值； 有點坑爹啊( 細思極恐 )

1表示回滾事務內所有的statements；(此參數是只讀參數，需在my.cnf中配置，并且重啟生效；)

吃過一次虧,這次并沒有盲目的相信百度到的信息

于是開始測試

一、驗證innodb_rollback_on_timeout=off的情況

1.session?A

開啟事務,事務未提交,鎖住id=1的數據

2.session B?

開啟事務,執行更新id=2的數據成功(事務未提交,鎖住id=2),然后請求id=1等待鎖超時,id=2的數據更改為222.

3.session C

請求id=2的數據50秒后顯示等待鎖超時

執行 SELECT * FROM information_schema.INNODB_TRX;

可發現有資源一直未釋放,具體到測試數據中就是id=2的資源一直被鎖定,線程一直被掛起.

總結:通過實驗基本可以確定是業務資源交叉導致死鎖之后資源沒釋放造成的持續阻塞，

二.驗證innodb_rollback_on_timeout=on

修改配置后將驗證innodb_rollback_on_timeout=off的步驟再走一遍

發現鎖等待只能在業務層面盡量避免

on/off的區別在于session?C進入時不會持續阻塞,session B異常后全部回滾

mysql連接超時怎么處理

MYSQL_OPT_READ_TIMEOUT 是 MySQL c api 客戶端中用來設置讀取超時時間的參數。在 MySQL 的官方文檔中，該參數的描述是這樣的：

MYSQL_OPT_READ_TIMEOUT (argument type: unsigned int *)The timeout in seconds for each attempt to read from the server. There are retries if necessary, so the total effective timeout value is three times the option value. You can set the value so that a lost connection can be detected earlier than the TCP/IPClose_Wait_Timeout value of 10 minutes.

也就是說在需要的時候，實際的超時時間會是設定值的 3 倍。但是實際測試后發現實際的超時時間和設置的超時時間一致。

而具體什么時候發生三倍超時，在文檔中沒有找到。所以對 MySQL 5.7.20 的源碼進行了一些分析。

使用 GDB 調試代碼找了實際與 mysql server 通信的代碼，如下：

請點擊輸入圖片描述

其中 vio_read() 函數中，使用 recv 和 poll 來讀取報文和做讀取超時。net_should_retry() 函數只有在發生 EINTR 時才會返回 true。從這段代碼來看是符合測試結果的，并沒有對讀取進行三次重試。只有在讀取操作被系統中斷打斷時才會重試，但是這個重試并沒有次數限制。

從上面代碼的分析可以看出，代碼的邏輯和文檔的描述不符。于是在一頓搜索后，找到了一個 MySQL 的 BUG(Bug #31163)。該 BUG 報告了在?MySQL?5.0 中，MySQL c api 讀取的實際超時時間是設置的三倍，與現有文檔描述相符。于是對 MySQL 5.0.96 的代碼又進行分析。

同樣使用 GDB 找到了通信部分的代碼。這次找到了重試三次的代碼，如下：

請點擊輸入圖片描述

這個版本的 MySQL api 的讀寫超時是直接使用的 setsockopt 設置的。第一次循環，在 A 點發生了第一次超時（雖然注釋寫的非阻塞，但是客戶端的連接始終是阻塞模式的）。然后在 B 點將該 socket 設置為阻塞模式，C 點這里重置 retry 次數。由于設置了 alarm 第二次以后的循環會直接進入 D 點的這個分支，并且判斷循環次數。作為客戶端時net-retry_count 始終是 1，所以重試了兩次，共計進行了 3 次 vioread 后從 E 點退出函數。

由上面的分析可知，MySQL 文檔對于該參數的描述已經過時，現在的 MYSQL_OPT_READ_TIMEOUT 并不會出現三倍超時的問題。而 Bug #31163 中的處理結果也是將文檔中該參數的描述更新為實際讀取超時時間是設定時間的三倍。也許是 MySQL 的維護者們在后續版本更新時忘記更新文檔吧。