《Mysql學習Mysql 數據庫死鎖過程分析(select for update)》要點：
本文介紹了Mysql學習Mysql 數據庫死鎖過程分析(select for update)，希望對您有用。如果有疑問，可以聯系我們。

MYSQL必讀近期有一個業務需求,多臺機器需要同時從Mysql一個表里查詢數據并做后續業務邏輯,為了防止多臺機器同時拿到一樣的數據,每臺機器需要在獲取時鎖住獲取數據的數據段,保證多臺機器不拿到相同的數據.

MYSQL必讀我們Mysql的存儲引擎是innodb,支持行鎖.解決同時拿數據的方法有很多,為了更加簡單,不增加其他表和服務的情況下,我們考慮采用select... for update的方式,這樣X鎖鎖住查詢的數據段,表里其他數據沒有鎖,其他業務邏輯還是可以操作.

MYSQL必讀這樣一臺服務器比如select .. for update limit 0,30時,其他服務器執行同樣sql語句會自動等待釋放鎖,等待前一臺服務器鎖釋放后,該臺服務器就能查詢下一個30條數據.如果要求更智能,oracle支持for update skip locked跳過鎖區域,這樣能不等待馬上查詢沒有被鎖住的下一個30條記錄.

MYSQL必讀下面說下mysql for update導致的死鎖.

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

MYSQL必讀最后經過分析,我們項目里發現是for update的sql語句,和另外一個update非select數據的sql語句導致的死鎖.

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

MYSQL必讀?下面舉幾個例子說明：

MYSQL必讀數據從id =400000的數據開始,IsSuccess和GetTime字段都為0,現在如果400000數據的IsSuccess為1了.執行下面兩條sql.

MYSQL必讀
-- 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; 

MYSQL必讀　　第一條sql語句先不commit,則第二條sql語句將只能等待,因此第二條sql語句把IsSuccess修改為0,IsSuccess非主鍵索引鎖了值為0的索引數據,第二條sql語句將無法把數據更新到被鎖的行里.

MYSQL必讀再執行下面的sql語句

MYSQL必讀
-- 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; 

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

MYSQL必讀上面的例子知道了鎖索引段后還比較容易看懂,下面就奇葩一點：

MYSQL必讀先把id =400000數據的GetTime修改為1,IsSuccess=0,然后一次執行sql：

MYSQL必讀
-- 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; 

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

MYSQL必讀原因是第一個sql語句還沒有commit也沒有rollback,因此它先鎖主鍵索引,再鎖IsSuccess的非主鍵索引,第二個sql語句由于where里要判斷IsSuccess字段的值,由于400000這條數據以前的IsSuccess是0,現在更新為1還不確定,可能會回滾,因此sql2需要等待確定400000這條數據的IsSuccess是否被修改.sql2的sql語句因為判斷了GetTime<1,實際400000這條記錄已經不滿足了,但按照鎖索引的原理,所以sql2語句會被阻塞.

MYSQL必讀因此如果根據業務場景,可以把sql2語句的IsSuccess條件取消掉,并且這里GetTime查詢條件由GetTime<1修改為GetTime=0,這樣即可不阻塞直接查詢出來.

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

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

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

MYSQL必讀1、更新或查詢for update的時候,會在where條件中開始為每個字段判斷是否有鎖,如果有鎖就會等待,因為如果有鎖,那這個字段的值不確定,只能等待鎖commit或rollback后數據確定后再查詢.

MYSQL必讀2、另外還和order by有關系,因為可能前面數據有鎖,但從后面查詢一個范圍就可以查詢.

MYSQL必讀3、另外limit也有關系,比如limit 20,30從第20條記錄取30行數據,但第一行數據如果被鎖,因為不確定回滾還是提交,也會鎖等待.

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

MYSQL必讀?使用mysql運行某些語句時,會因數據量太大而導致死鎖,沒有反映.這個時候,就需要kill掉某個正在消耗資源的query語句即可,　KILL命令的語法格式如下：

MYSQL必讀每個與mysqld的連接都在一個獨立的線程里運行,您可以使用SHOW PROCESSLIST語句查看哪些線程正在運行,并使用KILL thread_id語句終止一個線程.

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

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

MYSQL必讀首先登錄mysql,然后使用： show processlist; 查看當前mysql中各個線程狀態.

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 |

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

MYSQL必讀然后運行kill命令,命令格式如下：

MYSQL必讀kill id;
-- 示例:
?kill 8358

MYSQL必讀殺掉即可.

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5144.html