Mysql數(shù)據(jù)庫(kù)死鎖過(guò)程分析(selectforupdate)

近期有一個(gè)業(yè)務(wù)需求，多臺(tái)機(jī)器需要同時(shí)從Mysql一個(gè)表里查詢數(shù)據(jù)并做后續(xù)業(yè)務(wù)邏輯，為了防止多臺(tái)機(jī)器同時(shí)拿到一樣的數(shù)據(jù)，每臺(tái)機(jī)器需要在獲取時(shí)鎖住獲取數(shù)據(jù)的數(shù)據(jù)段，保證多臺(tái)機(jī)器不拿到相同的數(shù)據(jù)。

我們Mysql的存儲(chǔ)引擎是innodb，支持行鎖。解決同時(shí)拿數(shù)據(jù)的方法有很多，為了更加簡(jiǎn)單，不增加其他表和服務(wù)的情況下，我們考慮采用select... for update的方式，這樣X(jué)鎖鎖住查詢的數(shù)據(jù)段，表里其他數(shù)據(jù)沒(méi)有鎖，其他業(yè)務(wù)邏輯還是可以操作。

這樣一臺(tái)服務(wù)器比如select .. for update limit 0,30時(shí)，其他服務(wù)器執(zhí)行同樣sql語(yǔ)句會(huì)自動(dòng)等待釋放鎖，等待前一臺(tái)服務(wù)器鎖釋放后，該臺(tái)服務(wù)器就能查詢下一個(gè)30條數(shù)據(jù)。如果要求更智能，oracle支持for update skip locked跳過(guò)鎖區(qū)域，這樣能不等待馬上查詢沒(méi)有被鎖住的下一個(gè)30條記錄。

下面說(shuō)下mysql for update導(dǎo)致的死鎖。

經(jīng)過(guò)分析，mysql的innodb存儲(chǔ)引擎實(shí)務(wù)鎖雖然是鎖行，但它內(nèi)部是鎖索引的，根據(jù)where條件和select的值是否只有主鍵或非主鍵索引來(lái)判斷怎么鎖，比如只有主鍵，則鎖主鍵索引，如果只有非主鍵，則鎖非主鍵索引，如果主鍵非主鍵都有，則內(nèi)部會(huì)按照順序鎖。但同樣的select .. for update語(yǔ)句怎么就死鎖了呢？同樣的sql語(yǔ)句查詢條件和結(jié)果順序都一致，按理不會(huì)導(dǎo)致一個(gè)鎖了主鍵索引，等待鎖非主鍵索引，另外一個(gè)鎖了非主鍵索引，等待主鍵索引導(dǎo)致的死鎖。

最后經(jīng)過(guò)分析，我們項(xiàng)目里發(fā)現(xiàn)是for update的sql語(yǔ)句，和另外一個(gè)update非select數(shù)據(jù)的sql語(yǔ)句導(dǎo)致的死鎖。

比如有60條數(shù)據(jù)，select .. for update查詢第31-60條數(shù)據(jù)，update在更新1-10條數(shù)據(jù)，按照innodb存儲(chǔ)引擎的行鎖原理，應(yīng)該不會(huì)導(dǎo)致不同行的鎖導(dǎo)致的互相等待。開(kāi)始以為是行鎖在數(shù)據(jù)量較大情況下，會(huì)鎖數(shù)據(jù)塊。導(dǎo)致一個(gè)段的數(shù)據(jù)被鎖住，但經(jīng)過(guò)大量數(shù)據(jù)測(cè)試，發(fā)現(xiàn)感覺(jué)把整個(gè)表都鎖住了，但實(shí)際不是。

 下面舉幾個(gè)例子說(shuō)明：

數(shù)據(jù)從id =400000的數(shù)據(jù)開(kāi)始，IsSuccess和GetTime字段都為0，現(xiàn)在如果400000數(shù)據(jù)的IsSuccess為1了。執(zhí)行下面兩條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語(yǔ)句先不commit，則第二條sql語(yǔ)句將只能等待，因此第二條sql語(yǔ)句把IsSuccess修改為0，IsSuccess非主鍵索引鎖了值為0的索引數(shù)據(jù)，第二條sql語(yǔ)句將無(wú)法把數(shù)據(jù)更新到被鎖的行里。

再執(zhí)行下面的sql語(yǔ)句

-- 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語(yǔ)句將可以執(zhí)行。因?yàn)镮sSuccess=2的索引段沒(méi)有被鎖。

上面的例子知道了鎖索引段后還比較容易看懂，下面就奇葩一點(diǎn)：

先把id =400000數(shù)據(jù)的GetTime修改為1，IsSuccess=0，然后一次執(zhí)行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個(gè)sql先不commit，按照道理只會(huì)鎖40000這行記錄，第二個(gè)sql執(zhí)行，按照道理只能查詢從400001記錄的30條記錄，但第二個(gè)sql語(yǔ)句會(huì)阻塞等待。

原因是第一個(gè)sql語(yǔ)句還沒(méi)有commit也沒(méi)有rollback，因此它先鎖主鍵索引，再鎖IsSuccess的非主鍵索引，第二個(gè)sql語(yǔ)句由于where里要判斷IsSuccess字段的值，由于400000這條數(shù)據(jù)以前的IsSuccess是0，現(xiàn)在更新為1還不確定，可能會(huì)回滾，因此sql2需要等待確定400000這條數(shù)據(jù)的IsSuccess是否被修改。sql2的sql語(yǔ)句因?yàn)榕袛嗔薌etTime<1，實(shí)際400000這條記錄已經(jīng)不滿足了，但按照鎖索引的原理，所以sql2語(yǔ)句會(huì)被阻塞。

因此如果根據(jù)業(yè)務(wù)場(chǎng)景，可以把sql2語(yǔ)句的IsSuccess條件取消掉，并且這里GetTime查詢條件由GetTime<1修改為GetTime=0，這樣即可不阻塞直接查詢出來(lái)。

GetTime用范圍查詢導(dǎo)致的鎖影響經(jīng)過(guò)分析，還不是間隙鎖的問(wèn)題，感覺(jué)應(yīng)該是用范圍作為條件，所有從第0行開(kāi)始的所有查找范圍都會(huì)被鎖住。 比如這里更新400000會(huì)被阻塞，但更新400031不會(huì)被阻塞。

我們項(xiàng)目出現(xiàn)死鎖，就是這個(gè)原理，一條sql語(yǔ)句先鎖主鍵索引，再鎖非主鍵索引；另外一條sql語(yǔ)句先鎖非主鍵索引，再鎖主鍵索引。雖然兩個(gè)sql語(yǔ)句期望鎖的數(shù)據(jù)行不一樣，但兩個(gè)sql語(yǔ)句查詢或更新的條件或結(jié)果字段如果有相同列，則可能會(huì)導(dǎo)致互相等待對(duì)方鎖，2個(gè)sql語(yǔ)句即引起了死鎖。

個(gè)人總結(jié)一下innodb存儲(chǔ)引擎下的鎖的分析，可能會(huì)有問(wèn)題：

1、更新或查詢for update的時(shí)候，會(huì)在where條件中開(kāi)始為每個(gè)字段判斷是否有鎖，如果有鎖就會(huì)等待，因?yàn)槿绻墟i，那這個(gè)字段的值不確定，只能等待鎖commit或rollback后數(shù)據(jù)確定后再查詢。

2、另外還和order by有關(guān)系，因?yàn)榭赡芮懊鏀?shù)據(jù)有鎖，但從后面查詢一個(gè)范圍就可以查詢。

3、另外limit也有關(guān)系，比如limit 20,30從第20條記錄取30行數(shù)據(jù)，但第一行數(shù)據(jù)如果被鎖，因?yàn)椴淮_定回滾還是提交，也會(huì)鎖等待。

 ps:mysql使用kill命令解決死鎖問(wèn)題,殺死某條正在執(zhí)行的sql語(yǔ)句

 使用mysql運(yùn)行某些語(yǔ)句時(shí)，會(huì)因數(shù)據(jù)量太大而導(dǎo)致死鎖，沒(méi)有反映。這個(gè)時(shí)候，就需要kill掉某個(gè)正在消耗資源的query語(yǔ)句即可，　KILL命令的語(yǔ)法格式如下：

代碼如下:
KILL [CONNECTION | QUERY] thread_id

每個(gè)與mysqld的連接都在一個(gè)獨(dú)立的線程里運(yùn)行，您可以使用SHOW PROCESSLIST語(yǔ)句查看哪些線程正在運(yùn)行，并使用KILL thread_id語(yǔ)句終止一個(gè)線程。

KILL允許自選的CONNECTION或QUERY修改符：KILL CONNECTION與不含修改符的KILL一樣：它會(huì)終止與給定的thread_id有關(guān)的連接。KILL QUERY會(huì)終止連接當(dāng)前正在執(zhí)行的語(yǔ)句，但是會(huì)保持連接的原狀。

如果您擁有PROCESS權(quán)限，則您可以查看所有線程。如果您擁有超級(jí)管理員權(quán)限，您可以終止所有線程和語(yǔ)句。否則，您只能查看和終止您自己的線程和語(yǔ)句。您也可以使用mysqladmin processlist和mysqladmin kill命令來(lái)檢查和終止線程。

首先登錄mysql，然后使用： show processlist; 查看當(dāng)前mysql中各個(gè)線程狀態(tài)。

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 |

以上顯示出當(dāng)前正在執(zhí)行的sql語(yǔ)句列表，找到消耗資源最大的那條語(yǔ)句對(duì)應(yīng)的id.

然后運(yùn)行kill命令，命令格式如下：

kill id;
-- 示例:
 kill 8358

殺掉即可。

您可能感興趣的文章:

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com