mysql死鎖問(wèn)題實(shí)例分析

這篇文章主要介紹“mysql死鎖問(wèn)題實(shí)例分析”，在日常操作中，相信很多人在mysql死鎖問(wèn)題實(shí)例分析問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”mysql死鎖問(wèn)題實(shí)例分析”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

一、問(wèn)題描述：

        一張表的非索引字段更新，并發(fā)環(huán)境下出現(xiàn)鎖表，導(dǎo)致死鎖！

二、簡(jiǎn)要分析：

        兩個(gè)事務(wù)，一個(gè)事務(wù)，在執(zhí)行一個(gè)索引字段更新sql，另外一個(gè)事務(wù)正好進(jìn)行主鍵條件更新，也進(jìn)行非索引更新。導(dǎo)致兩個(gè)事務(wù)交叉等待釋放鎖，所以出現(xiàn)死鎖問(wèn)題。

三、數(shù)據(jù)模擬：

創(chuàng)建表：

create table user(

  id int(10) NOT NULL AUTO_INCREMENT,

  user_id varchar(20),

  id_card_no varchar(50),

  name varchar(30),

  password varchar(30),

  sex char(1),

  birthday date,

  PRIMARY KEY (`id`),

  INDEX index_name (user_id(20))

);

插入兩條數(shù)據(jù)

insert into user (user_id,id_card_no,name,password,sex,birthday) values('000001','001','zhangsan','123','t','1993-05-27');

insert into user (user_id,id_card_no,name,password,sex,birthday) values('000002','002','lisi','123','t','1993-05-27');

四、問(wèn)題復(fù)現(xiàn)

step 1：開(kāi)啟一個(gè)事務(wù)1，首先執(zhí)行主鍵更新

  begin;

  update user set name = "test transaction by zz" where id = 1;

step 2：再開(kāi)啟一個(gè)事務(wù)2，執(zhí)行另一個(gè)主鍵更新語(yǔ)句，

   begin;

   update user set name = "test transaction by zz" where id = 2;

step 3：事務(wù)1執(zhí)行一條非索引字段更新，不要提交事務(wù);

  update user set name = "test transaction by zz name" where user_id = '000001';

step 4：事務(wù)2在執(zhí)行一條非索引字段更新，不要提交事務(wù);

  update user set name = "test transaction by zz name" where user_id = '000002';

運(yùn)行結(jié)果：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

四、分析結(jié)果：

1.兩個(gè)事務(wù)，交叉等待對(duì)方釋放鎖，就會(huì)出現(xiàn)死鎖。

2.如上操作，事務(wù)1執(zhí)行sql2的時(shí)候等待事務(wù)2釋放行鎖，事務(wù)2這時(shí)候可能也進(jìn)行非索引字段更新，需要等待事務(wù)1釋放表鎖，這就出現(xiàn)了死鎖。

3.主鍵更新，只會(huì)鎖住該索引，這就是行級(jí)鎖的概念;索引字段更新或者刪除，會(huì)先根據(jù)索引字段，找到相應(yīng)的主鍵記錄，再進(jìn)行加鎖;非主鍵，非索引字段的更新和刪除默認(rèn)會(huì)鎖住整張表，這就是一個(gè)表級(jí)鎖的概念;

五、優(yōu)化與建議：

1.避免非索引字段的更新和刪除操作。

2.如果有這樣的更新，建議事務(wù)外查詢，然后根據(jù)索引字段進(jìn)行更新，就不會(huì)造成鎖表的情況；

3.對(duì)相應(yīng)的字段加索引，但是一張表中索引不宜太多，會(huì)影響數(shù)據(jù)庫(kù)性能，最好四個(gè)以內(nèi)，對(duì)于超過(guò)100ms的查詢，需要優(yōu)化；但是加索引也不是最好的解決方案，索引更新，也會(huì)先找到索引對(duì)應(yīng)的主鍵，再進(jìn)行加鎖，也有一點(diǎn)點(diǎn)性能問(wèn)題。但是滿足大部分的業(yè)務(wù)需求。

4.系統(tǒng)需要異常情況要有重試機(jī)制，對(duì)于數(shù)據(jù)庫(kù)這樣的，死鎖問(wèn)題，一般的在并發(fā)下出現(xiàn)，也是會(huì)有系統(tǒng)空缺的時(shí)候，這時(shí)候有重試，就稍微好一點(diǎn)。但是對(duì)于時(shí)效性比較高的業(yè)務(wù)，一定不能出現(xiàn)這種問(wèn)題。

到此，關(guān)于“mysql死鎖問(wèn)題實(shí)例分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！