MySQL事務鎖：Lock wait timeout exceeded； try restarting transaction

項目場景：

Python在批量修改MySQL資料庫時報錯

問題描述：

單純的修改語句導緻： Lock wait timeout exceeded; try restarting transaction（鎖等待逾時;試着重新啟動事務）

原因分析：

可能導緻的背景：

1、在同一事務内先後對同一條資料進行插入和更新操作；

2、多台伺服器操作同一資料庫；

3、瞬時出現高并發現象；

問題原因：

1、在高并發的情況下，Spring事物造成資料庫死鎖，後續操作逾時抛出異常。

2、Mysql資料庫采用InnoDB模式，預設參數:innodb_lock_wait_timeout設定鎖等待的時間是50s，一旦資料庫鎖超過這個時間就會報錯

解決方案：

1、檢視資料庫目前程序，檢查是否有執行時間過長的SQL記錄。

show processlist;

2、檢視目前的事務

目前運作的所有事務

字段解釋：

trx_id：事務ID。

trx_state：事務狀态，有以下幾種狀态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。

trx_started：事務開始時間。

trx_requested_lock_id：事務目前正在等待鎖的辨別，可以和 INNODB_LOCKS 表 JOIN 以得到更多詳細資訊。

trx_wait_started：事務開始等待的時間。

trx_weight：事務的權重。

trx_mysql_thread_id：事務線程 ID，可以和 PROCESSLIST 表 JOIN。

trx_query：事務正在執行的 SQL 語句。

trx_operation_state：事務目前操作狀态。

trx_tables_in_use：目前事務執行的 SQL 中使用的表的個數。

trx_tables_locked：目前執行 SQL 的行鎖數量。

trx_lock_structs：事務保留的鎖數量。

trx_lock_memory_bytes：事務鎖住的記憶體大小，機關為 BYTES。

trx_rows_locked：事務鎖住的記錄數。包含标記為 DELETED，并且已經儲存到磁盤但對事務不可見的行。

trx_rows_modified：事務更改的行數。

trx_concurrency_tickets：事務并發票數。

trx_isolation_level：目前事務的隔離級别。

trx_unique_checks：是否打開唯一性檢查的辨別。

trx_foreign_key_checks：是否打開外鍵檢查的辨別。

trx_last_foreign_key_error：最後一次的外鍵錯誤資訊。

trx_adaptive_hash_latched：自适應散列索引是否被目前事務鎖住的辨別。

trx_adaptive_hash_timeout：是否立刻放棄為自适應散列索引搜尋 LATCH 的辨別。

目前出現的鎖

lock_id：鎖 ID。

lock_trx_id：擁有鎖的事務 ID。可以和 INNODB_TRX 表 JOIN 得到事務的詳細資訊。

lock_mode：鎖的模式。有如下鎖類型：行級鎖包括：S、X、IS、IX，分别代表：共享鎖、排它鎖、意向共享鎖、意向排它鎖。表級鎖包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享間隙鎖、排它間隙鎖、意向共享間隙鎖、意向排它間隙鎖和自動遞增鎖。

lock_type：鎖的類型。RECORD 代表行級鎖，TABLE 代表表級鎖。

lock_table：被鎖定的或者包含鎖定記錄的表的名稱。

lock_index：當 LOCK_TYPE=’RECORD’ 時，表示索引的名稱；否則為 NULL。

lock_space：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的表空間 ID；否則為 NULL。

lock_page：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的頁号；否則為 NULL。

lock_rec：當 LOCK_TYPE=’RECORD’ 時，表示一堆頁面中鎖定行的數量，亦即被鎖定的記錄号；否則為 NULL。

lock_data：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的主鍵；否則為NULL。

鎖等待的對應關系

requesting_trx_id：請求事務的 ID。

requested_lock_id：事務所等待的鎖定的 ID。可以和 INNODB_LOCKS 表 JOIN。

blocking_trx_id：阻塞事務的 ID。

blocking_lock_id：某一事務的鎖的 ID，該事務阻塞了另一事務的運作。可以和 INNODB_LOCKS 表 JOIN。

3、殺死程序

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

kill ID
           

總結

工作随筆，希望可以幫助到大家！

參考連結：https://cloud.tencent.com/developer/article/1356959