1. InnoDB 引擎錶行鎖等待和等待超時發生的場景

2.InnoDB 引擎行鎖等待情況的處理

2.1 InnoDB 行鎖等待超時引數 innodb_lock_wait_timeout

2.2 大量行鎖等待和行鎖等待超時的處理

---

1. InnoDB 引擎錶行鎖等待和等待超時發生的場景

當一個 RDS for MySQL 連線會話等待另外一個會話持有的互斥行鎖時，會發生 InnoDB 引擎錶行鎖等待情況。

通常情況下，持有該互斥行鎖的會話（連線）會迅速的執行完相關操作並釋放掉持有的互斥鎖（事務提交或者回滾），進而等待的會話在行鎖等待超時時間到來前獲得該互斥行鎖，進行下一步操作。

但在某些情況下，比如一個例項未感知到的來自客戶端應用的資料庫會話中斷，持有該互斥行鎖的會話長時間不釋放該互斥行鎖，此時如果有其他會話申請該互斥行鎖，則會導致大量的行鎖等待與行鎖等待超時。

2. InnoDB 引擎行鎖等待情況的處理 

本文提供的檢查和處理方法，僅當正在發生 InnoDB 行鎖等待的情況下才成立；因為 InnoDB 行鎖等待預設超時時間為 50 秒，因此通常情況下不容易觀察到行鎖等待現場，可以通過將 innodb_lock_wait_timeout 引數設定為較大值來複現問題（生產環境不推薦使用過大的 innodb_lock_wait_timeout 引數值）。

2.1. InnoDB 行鎖等待超時引數 innodb_lock_wait_timeout

#	引數	預設值	最小值	最大值	說明
1	innodb_lock_wait_timeout	50	1	1073741824	獲取Innodb 行鎖的等待時間，單位秒。可在會話級別設定

該引數控制 Innodb 行鎖等待的超時時間，單位為秒，RDS 例項該引數的預設值為 50（秒）。

等待互斥鎖的會話在等待 50 秒後會退出鎖等待狀態並返回下面的錯誤，這個行為稱之為 InnoDB 引擎錶行鎖等待超時。

 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 

 

可以通過下面的命令檢視當前會話和全域性的引數設定。

show variables like `innodb_lock_wait_timeout`; -- 檢視當前會話

show global variables like `innodb_lock_w%`; -- 檢視全域性設定

該引數支援在會話級別修改，方便應用在會話級別單獨設定某些特殊操作的行鎖等待超時時間，如下：

set innodb_lock_wait_timeout=1000; --設定當前會話 Innodb 行鎖等待超時時間，單位秒

 

2.2. 大量行鎖等待和行鎖等待超時的處理

如果行鎖等待和行鎖等待超時持續發生，並且導致當前應用執行異常，那麼需要獲取到一直持有行鎖的會話，並且終止該會話來釋放持有的鎖（會話對應的事務會回滾）。

2.2.1 檢查導致鎖等待和鎖超時的會話

• 注：
  下面的方法必須在行鎖等待正在發生的時候進行檢查。

方法 1： 通過 DMS  例項資訊   Innodb 鎖等待檢視，如下圖：

方法 2：通過 DMS  例項資訊  例項會話檢視，如下圖

方法 3： 在 DMS 無法登入的情況下，可以通過執行下面的查詢，獲得導致行鎖等待和行鎖等待超時的會話。

select l.* from ( select
 `Blocker` role,
    p.id,
    p.user,
    left(p.host, locate(`:`, p.host) - 1) host,
    tx.trx_id,
    tx.trx_state,
    tx.trx_started,
 timestampdiff(second, tx.trx_started, now()) duration,
 lo.lock_mode,
 lo.lock_type,
 lo.lock_table,
 lo.lock_index,
    tx.trx_query,
	lw.requesting_thd_id Blockee_id,
	lw.requesting_trx_id Blockee_trx
from
    information_schema.innodb_trx tx,
    information_schema.innodb_lock_waits lw,
 information_schema.innodb_locks lo,
    information_schema.processlist p
where
    lw.blocking_trx_id = tx.trx_id
 and p.id = tx.trx_mysql_thread_id
 and lo.lock_id = lw.blocking_lock_id
union
select
    `Blockee` role,
    p.id,
    p.user,
    left(p.host, locate(`:`, p.host) - 1) host,
    tx.trx_id,
    tx.trx_state,
    tx.trx_started,
 timestampdiff(second, tx.trx_started, now()) duration,
 lo.lock_mode,
 lo.lock_type,
 lo.lock_table,
 lo.lock_index,
    tx.trx_query,
	null,
	null
from
    information_schema.innodb_trx tx,
    information_schema.innodb_lock_waits lw,
 information_schema.innodb_locks lo,
    information_schema.processlist p
where
    lw.requesting_trx_id = tx.trx_id
 and p.id = tx.trx_mysql_thread_id
 and lo.lock_id = lw.requested_lock_id) l
 order by role desc, trx_state desc;

比如：

對於複雜的多個會話相互行鎖等待情況，建議先終止 Role 為 Blocker 且 trx_state 為 RUNNING 的會話；終止後再次檢查，如果仍舊有行鎖等待，再終止新結果中的 Role 為 Blocker 且 trx_state 為 RUNNING 的會話。 

2.2.2 處理導致行鎖等待和行鎖等待超時的會話

對於標識為 Blocker 的會話（持有鎖阻塞其他會話的 DML 操作，導致行鎖等待和行鎖等待超時），確認業務可以接受其對應的事務回滾的情況下，可以將其終止。

比如，可以通過 Kill 命令來今後會話終止。