mysql死鎖deadlock相關幾個系統變數innodb_lock_wait_timeout

前言

        隨著對於mysql死鎖知識的進一步瞭解，發現與死鎖相關的幾個系統變數。下面梳理這幾個技術概念，便於更進一步掌握mysql死鎖的概念。這樣在出現一些複雜的死鎖問題時，也有助於更快速解決故障。

相關文章   
mysql 5.6 performance schema分析診斷死鎖deadlock之一
如何使用mysql 5.6 information schema定位事務鎖資訊

innodb_deadlock_detect系統變數

• mysql 5.7.15引入此係統變數，可取值有2個，分別為：off與on。

• 預設值為on,表明開啟互鎖檢測功能。

• 但是有運維經驗的同學或許碰到過，在一些高併發的資料庫，如果開啟了死鎖檢測，因為死鎖檢測程式會去檢測是否發現死鎖，則會導致資料庫效能下降，因為系統必須消耗資源用於定期檢測是否發生死鎖。

• 建議在高併發情況下，可以考慮關閉此功能。當然一般情況，保持預設值即可。

innodb_lock_wait_timeout系統變數

• innodb_lock_wait_timeout只能用於innodb表的事務（不適用於myisam表，因此它只有表鎖，而無行鎖功能)。在發生鎖等

• 待時，到達指定的值，則提示鎖等待超時，自動回滾報 鎖超時 的當前SQL語句（注：不是事務），這樣的話，就會導致所屬事務的產生資料不一致。所以必須保證在讓整個事務回滾，這個由系統變數 innodb_rollback_on_timeout或者命令列選項--innodb-rollback-on-timeout控制。

• innodb_lock_wait_timeout單位為秒，預設值為50秒。如果出現鎖超時，報錯資訊見下：

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

• 對於高併發的OLTP資料庫環境，建議減少innodb_lock_wait_timeout系統變數。你試想一下，如果鎖超時時間檢查一短，就可以提升併發度；而對於OLAP資料庫，可以考慮增加innodb_lock_wait_timeout系統變數，因為OLAP不需要很高的併發度，如果此值過低,鎖超時會話就可以等待其它持鎖會話完成，而不是馬上報錯回滾,畢竟每次更新的資料量極大，一回滾半天白作了。

• 如果啟用了innodb_deadlock_detect=on(預設值)，則innodb_lock_wait_timeout不會起作用。因為啟用前者，一發現死鎖，馬上回滾某個死鎖事務。但如果配置innodb_deadlock_detect=off,則innodb_lock_wait_timeout會起作用，讓後者來控制死鎖的回滾。

mysql> show variables like '%innodb%lock%timeout%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 50    |

+--------------------------+-------+

1 row in set (0.00 sec)

培訓課件

聯絡方式