Mysql 資料庫死結過程分析(select for update)_Mysql

近期有一個業務需求，多台機器需要同時從Mysql一個表裡查詢資料並做後續商務邏輯，為了防止多台機器同時拿到一樣的資料，每台機器需要在擷取時鎖住擷取資料的資料區段，保證多台機器不拿到相同的資料。

我們Mysql的儲存引擎是innodb，支援行鎖。解決同時拿資料的方法有很多，為了更加簡單，不增加其他表和服務的情況下，我們考慮採用select... for update的方式，這樣X鎖鎖住查詢的資料區段，表裡其他資料沒有鎖，其他商務邏輯還是可以操作。

這樣一台伺服器比如select .. for update limit 0,30時，其他伺服器執行同樣sql語句會自動等待釋放鎖，等待前一台伺服器鎖釋放後，該台伺服器就能查詢下一個30條資料。如果要求更智能，oracle支援for update skip locked跳過鎖地區，這樣能不等待馬上查詢沒有被鎖住的下一個30條記錄。

下面說下mysql for update導致的死結。

經過分析，mysql的innodb儲存引擎實務鎖雖然是鎖行，但它內部是鎖索引的，根據where條件和select的值是否只有主鍵或非主鍵索引來判斷怎麼鎖，比如只有主鍵，則鎖主鍵索引，如果只有非主鍵，則鎖非主鍵索引，如果主鍵非主鍵都有，則內部會按照順序鎖。但同樣的select .. for update語句怎麼就死結了呢？同樣的sql語句查詢條件和結果順序都一致，按理不會導致一個鎖了主鍵索引，等待鎖非主鍵索引，另外一個鎖了非主鍵索引，等待主鍵索引導致的死結。

最後經過分析，我們項目裡發現是for update的sql語句，和另外一個update非select資料的sql語句導致的死結。

比如有60條資料，select .. for update查詢第31-60條資料，update在更新1-10條資料，按照innodb儲存引擎的行鎖原理，應該不會導致不同行的鎖導致的互相等待。開始以為是行鎖在資料量較大情況下，會鎖資料區塊。導致一個段的資料被鎖住，但經過大量資料測試，發現感覺把整個表都鎖住了，但實際不是。

 下面舉幾個例子說明：

資料從id =400000的資料開始，IsSuccess和GetTime欄位都為0，現在如果400000資料的IsSuccess為1了。執行下面兩條sql.

-- 1:set autocommit=0;begin;select * from table1 where getTime < 1 and IsSuccess=0 order by id asc limit 0,30 for update;commit;-- 2:update table1 a set IsSuccess=0 where id =400000; 

　　第一條sql語句先不commit，則第二條sql語句將只能等待，因此第二條sql語句把IsSuccess修改為0，IsSuccess非主鍵索引鎖了值為0的索引資料，第二條sql語句將無法把資料更新到被鎖的行裡。

再執行下面的sql語句

-- 1:set autocommit=0;begin;select * from table1 where getTime < 1 and IsSuccess=0 order by id asc limit 0,30 for update;commit;-- 2:update table1 a set IsSuccess=2 where id =400000; 

　　這樣第二條sql語句將可以執行。因為IsSuccess=2的索引段沒有被鎖。

上面的例子知道了鎖索引段後還比較容易看懂，下面就奇葩一點：

先把id =400000資料的GetTime修改為1，IsSuccess=0，然後一次執行sql：

-- 1:set autocommit=0;begin;update ctripticketchangeresultdata a set issuccess=1 where id =400000;commit;-- 2:select * from table1 where getTime < 1 and IsSuccess=0 order by id asc limit 0,30 for update; 

第1個sql先不commit，按照道理只會鎖40000這行記錄，第二個sql執行，按照道理只能查詢從400001記錄的30條記錄，但第二個sql語句會阻塞等待。

原因是第一個sql語句還沒有commit也沒有rollback，因此它先鎖主鍵索引，再鎖IsSuccess的非主鍵索引，第二個sql語句由於where裡要判斷IsSuccess欄位的值，由於400000這條資料以前的IsSuccess是0，現在更新為1還不確定，可能會復原，因此sql2需要等待確定400000這條資料的IsSuccess是否被修改。sql2的sql語句因為判斷了GetTime<1，實際400000這條記錄已經不滿足了，但按照鎖索引的原理，所以sql2語句會被阻塞。

因此如果根據業務情境，可以把sql2語句的IsSuccess條件取消掉，並且這裡GetTime查詢條件由GetTime<1修改為GetTime=0，這樣即可不阻塞直接查詢出來。

GetTime用範圍查詢導致的鎖影響經過分析，還不是間隙鎖的問題，感覺應該是用範圍作為條件，所有從第0行開始的所有尋找範圍都會被鎖住。 比如這裡更新400000會被阻塞，但更新400031不會被阻塞。

我們項目出現死結，就是這個原理，一條sql語句先鎖主鍵索引，再鎖非主鍵索引；另外一條sql語句先鎖非主鍵索引，再鎖主鍵索引。雖然兩個sql語句期望鎖的資料行不一樣，但兩個sql語句查詢或更新的條件或結果欄位如果有相同列，則可能會導致互相等待對方鎖，2個sql語句即引起了死結。

個人總結一下innodb儲存引擎下的鎖的分析，可能會有問題：

1、更新或查詢for update的時候，會在where條件中開始為每個欄位判斷是否有鎖，如果有鎖就會等待，因為如果有鎖，那這個欄位的值不確定，只能等待鎖commit或rollback後資料確定後再查詢。

2、另外還和order by有關係，因為可能前面資料有鎖，但從後面查詢一個範圍就可以查詢。

3、另外limit也有關係，比如limit 20,30從第20條記錄取30行資料，但第一行資料如果被鎖，因為不確定復原還是提交，也會鎖等待。

 ps:mysql使用kill命令解決死結問題,殺死某條正在執行的sql語句

 使用mysql運行某些語句時，會因資料量太大而導致死結，沒有反映。這個時候，就需要kill掉某個正在消耗資源的query語句即可，　KILL命令的文法格式如下：

複製代碼 代碼如下:

KILL [CONNECTION | QUERY] thread_id

每個與mysqld的串連都在一個獨立的線程裡運行，您可以使用SHOW PROCESSLIST語句查看哪些線程正在運行，並使用KILL thread_id語句終止一個線程。

KILL允許自選的CONNECTION或QUERY修改符：KILL CONNECTION與不含修改符的KILL一樣：它會終止與給定的thread_id有關的串連。KILL QUERY會終止串連當前正在執行的語句，但是會保持串連的原狀。

如果您擁有PROCESS許可權，則您可以查看所有線程。如果您擁有超級管理員權限，您可以終止所有線程和語句。否則，您只能查看和終止您自己的線程和語句。您也可以使用mysqladmin processlist和mysqladmin kill命令來檢查和終止線程。

首先登入mysql，然後使用： show processlist; 查看當前mysql中各個線程狀態。

mysql> show processlist;+------+------+----------------------+----------------+---------+-------+-----------+--------------------- | Id  | User | Host         | db       | Command | Time | State   | Info+------+------+----------------------+----------------+---------+-------+-----------+--------------------- | 7028 | root | ucap-devgroup:53396 | platform    | Sleep  | 19553 |      | NULL| 8352 | root | ucap-devgroup:54794 | platform    | Sleep  | 4245 |      | NULL| 8353 | root | ucap-devgroup:54795 | platform    | Sleep  |   3 |      | NULL| 8358 | root | ucap-devgroup:62605 | platform    | query  | 4156 | updating | update t_shop set |

以上顯示出當前正在執行的sql語句列表，找到消耗資源最大的那條語句對應的id.

然後運行kill命令，命令格式如下：

kill id;
-- 樣本:
 kill 8358

殺掉即可。