mysql死鎖問題實例分析

這篇文章主要介紹“mysql死鎖問題實例分析”，在日常操作中，相信很多人在mysql死鎖問題實例分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”mysql死鎖問題實例分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

一、問題描述：

        一張表的非索引字段更新，并發環境下出現鎖表，導致死鎖！

二、簡要分析：

        兩個事務，一個事務，在執行一個索引字段更新sql，另外一個事務正好進行主鍵條件更新，也進行非索引更新。導致兩個事務交叉等待釋放鎖，所以出現死鎖問題。

三、數據模擬：

創建表：

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))

);

插入兩條數據

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');

四、問題復現

step 1：開啟一個事務1，首先執行主鍵更新

  begin;

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

step 2：再開啟一個事務2，執行另一個主鍵更新語句，

   begin;

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

step 3：事務1執行一條非索引字段更新，不要提交事務;

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

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

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

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

四、分析結果：

1.兩個事務，交叉等待對方釋放鎖，就會出現死鎖。

2.如上操作，事務1執行sql2的時候等待事務2釋放行鎖，事務2這時候可能也進行非索引字段更新，需要等待事務1釋放表鎖，這就出現了死鎖。

3.主鍵更新，只會鎖住該索引，這就是行級鎖的概念;索引字段更新或者刪除，會先根據索引字段，找到相應的主鍵記錄，再進行加鎖;非主鍵，非索引字段的更新和刪除默認會鎖住整張表，這就是一個表級鎖的概念;

五、優化與建議：

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

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

3.對相應的字段加索引，但是一張表中索引不宜太多，會影響數據庫性能，最好四個以內，對于超過100ms的查詢，需要優化；但是加索引也不是最好的解決方案，索引更新，也會先找到索引對應的主鍵，再進行加鎖，也有一點點性能問題。但是滿足大部分的業務需求。

4.系統需要異常情況要有重試機制，對于數據庫這樣的，死鎖問題，一般的在并發下出現，也是會有系統空缺的時候，這時候有重試，就稍微好一點。但是對于時效性比較高的業務，一定不能出現這種問題。

到此，關于“mysql死鎖問題實例分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！