Lock wait timeout exceeded; try restarting transaction

時間 2021-01-06

問題出現的原因：假設有兩個事務A和事務B,他們兩個都存在update 同一條記錄，A 先修改，但是沒有提交事務，B也想修改但是一直等，直到等到了超過了innodb_lock_wait_timeout所設置的時間，就會爆出此異常

模仿異常的出現：

創建一個表：

CREATE TABLE `emp` (
`id` int(4) NOT NULL,
`deptno` int(4) DEFAULT NULL,
`col3` int(4) DEFAULT NULL,
`col4` int(4) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

並在表中隨便插入幾條數據。

用navicate 連接上mysql服務，並設置客戶端set autocommit=0 ，用mysql-client 也連接上服務並使用相應的數據庫，並設置set autocommit=0 。

在第一個客戶端執行

START TRANSACTION;
update emp SET deptno=2 WHERE id=2; 此時不要commit；

在第二個客戶端執行對同一條記錄的修改，如

START TRANSACTION;

update emp SET deptno=2 WHERE id=2;

此時執行結果如下圖：

第一個圖中的update 語句執行完成後並未commit；接着第二個執行語句執行後會發發現一直在運行，並沒有停止，直到出現

[SQL]update emp set deptno=2,col3=6 WHERE id=1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

那麼如果出現這種情況該怎麼處理呢？

方案一：

show prosseslist; 然後kill 掉對應的進程就可以了，查看sql服務中的所有的進程可暫時解決問題，但是如果進程偏多的話就難以查看了

方案二：

在5.5中，information_schema庫中增加了三個關於鎖的表（MEMORY引擎）；
innodb_trx ## 當前運行的所有事務
innodb_locks ## 當前出現的鎖
innodb_lock_waits ## 鎖等待的對應關係

下面看下各個表的簡介：

desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |#鎖ID
| lock_trx_id | varchar(18) | NO | | | |#擁有鎖的事務ID
| lock_mode | varchar(32) | NO | | | |#鎖模式
| lock_type | varchar(32) | NO | | | |#鎖類型
| lock_table | varchar(1024) | NO | | | |#被鎖的表
| lock_index | varchar(1024) | YES | | NULL | |#被鎖的索引
| lock_space | bigint(21) unsigned | YES | | NULL ||#被鎖的表空間號
| lock_page | bigint(21) unsigned | YES | | NULL ||#被鎖的頁號
| lock_rec | bigint(21) unsigned | YES | | NULL ||#被鎖的記錄號
| lock_data | varchar(8192) | YES | | NULL | |#被鎖的數據

desc innodb_trx ;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |#事務ID
| trx_state | varchar(13) | NO | | | |#事務狀態：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 ||#事務開始時間；
| trx_requested_lock_id | varchar(81) | YES | | NULL ||#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL ||#事務開始等待的時間
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 ||#事務線程ID
| trx_query | varchar(1024) | YES | | NULL | |#具體SQL語句
| trx_operation_state | varchar(64) | YES | | NULL ||#事務當前操作狀態
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 ||#事務中有多少個表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 ||#事務擁有多少個鎖
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 ||#事務鎖住的內存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 ||#事務鎖住的行數
| trx_rows_modified | bigint(21) unsigned | NO | | 0 ||#事務更改的行數
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 ||#事務併發票數
| trx_isolation_level | varchar(16) | NO | | | |#事務隔離級別
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性檢查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外鍵檢查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL ||#最後的外鍵錯誤
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 ||#

有了這三個表就可以在事故發生時查看錶中的記錄

根據當前所有運行的時候可以清楚的找到等待的事務即沒有獲取鎖的事務，這樣的就可能是發生timeout 的原因所在

根據鎖的等待關係，可以看出正在等待的事務id。

事務等待鎖的超時的異常一般是事務未提交導致的，比如一個系統正在運行還未提交事務但是此時這個系統突然間宕掉了，那麼如果此時別的系統的事務也在請求同一條記錄的鎖，那麼就會出現事務等待鎖，這種可以從系統代碼優化方面來考慮如何避免這種情況。

感謝觀看。