sysbench花式踩坑之三：自增值導致的鎖等待

上篇文章 《sysbench花式採坑之二：自增值導致的主鍵衝突》 遺留了一個問題，為什麼在RR級別下delete一條id不存在的列，再在另一個事務用同樣的id插入一條資料會發生鎖等待，在RC級別下就不會出現鎖等待？

| 自增值導致的鎖等待

看到RR級別和RC級別下不同的鎖等待表現，第一個應該想到的原因就是gap鎖。

我們知道gap鎖會阻塞insert，那麼delete會產生gap鎖嗎？借用mysql 8.0新加入的performance_schema.data_locks觀察一下。

首先準備測試所用的表結構和表資料。  

id為主鍵列，age為索引列，首先我們刪除id為12的這條資料，觀察一下鎖資訊。

可以看到delete產生了一個意向排它表鎖和一個排它行鎖。

回滾這個事務，然後我們刪除一條id不存在的資料，觀察一下鎖資訊。

發現行鎖變成了gap鎖，這時候我們往鎖住的範圍裡面做insert操作都是會等待的。 

這時透過performance_schema.data_locks檢視鎖資訊

• 事務1 

• 事務2  

• 鎖資訊 

發現表中顯示的insert的事務有一條GAP鎖正在等待delete的GAP鎖。

這裡竟然是GAP鎖等待GAP鎖，前面也驗證了，兩條對同一個不存在的id進行delete的語句相互之間不會衝突，也就是說delete產生的GAP鎖不會阻塞delete產生的GAP鎖。

• 事務1 

• 事務2 

• 鎖資訊

可以看到，上述兩個鎖資訊的圖中除了第一張的lock status為waiting外，其他內容完全相同，這麼看來performance_schema.data_locks裡面記錄的資訊不是太全面，那我們看一下insert鎖等待的時候具體點的鎖資訊吧。

• 事務1 

• 事務2 

• show engine innodb status\G 

發現insert持有的鎖為insert intention lock，那麼這個insert intention lock為什麼在performance_schema.data_locks顯示為GAP呢，看一下官方文件insert intention lock的解釋。 

第一句就表明了insert intention lock是GAP鎖的一種，因此performance_schema.data_locks顯示為GAP好像也沒什麼毛病，只是和兩個delete的鎖資訊對比起來就比較容易讓人困擾了，再上圖仔細感受一下：  

| 總結

• 對不存在的行以id為where條件進行delete或者update的時候會產生gap lock；

• gap lock和gap lock之間互相相容；

• insert intention lock是一種特殊的gap lock，當先持有gap lock時，會阻塞後面的insert intention lock；

• sysbench壓測的時候自增值要設定為1，否則對資料的間隙進行dml的時候存在很多問題。

| 作者簡介

李文航·沃趣科技資料庫技術專家

熟悉MySQL體系結構和工作原理、SQL調優、資料庫故障診斷、資料遷移、備份恢復