欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

一文詳解Mysql?insert也會發(fā)生死鎖嗎

 更新時間：2024年02月28日 08:29:07   作者：peachesTao  
死鎖的本質(zhì)是資源競爭,批量插入如果順序不一致很容易導(dǎo)致死鎖,這篇文章主要給大家介紹了關(guān)于Mysql?insert是否也會發(fā)生死鎖的相關(guān)資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

前言

今天給大家分享我們前幾天線上遇到的一個Mysql死鎖的案列，希望在大家碰到類似的問題時有所幫助。

9月28號下午我們線上釘釘報警群報了一個“Error 1213: Deadlock found when trying to get lock”的錯誤，第一次線上發(fā)生數(shù)據(jù)庫死鎖，當(dāng)時感覺事態(tài)嚴重。

來不急多想，馬上通過錯誤日志堆棧找到了發(fā)生死鎖的sql語句，竟然是一條insert語句：“insert into ... on duplicate key update ...”，這直接戳中了我的盲區(qū)：insert也會導(dǎo)致死鎖？

在正式介紹案例前我們先來看一下前置知識，這有助于后面的理解。

前置知識

  • 記錄鎖
    包含共享鎖和獨占鎖

    共享鎖：簡稱S鎖，當(dāng)事務(wù)讀取一條記錄時需要先獲取改記錄的S鎖，如果一條記錄持有S鎖，其他事務(wù)可以繼續(xù)獲取該記錄的S鎖，但不能獲取X鎖。

    獨占鎖：也成排他鎖，簡稱X鎖，如果一條記錄持有X 鎖，其他事務(wù)既不可以獲取該記錄的S鎖，也不能獲取該記錄的X鎖。
     

  • 間隙鎖，簡稱gap鎖
    一種在記錄前面添加的鎖，該鎖阻止新記錄插入到當(dāng)前記錄的前面，直到當(dāng)前記錄的間隙鎖釋放后新記錄才能正常插入。

  • next-key鎖
    本質(zhì)是記錄鎖+間隙鎖的組合

  • 插入意向鎖
    新記錄在被間隙鎖阻塞時會生成插入意向鎖，間隙鎖釋放后插入意向鎖也會釋放

  • 隱式鎖
    Mysql 為了節(jié)省鎖的開銷，insert語句執(zhí)行時記錄是不會生成鎖的，只有在滿足下面條件時insert語句執(zhí)行后的記錄才會生成鎖：

    當(dāng)其他事務(wù)想獲取該記錄的S鎖或X鎖時且該記錄所在的聚簇索引中的事務(wù)屬于活躍狀態(tài)時（1、每條記錄的聚簇索引中會有一個隱藏字段存儲該記錄被最后修改時所在的事務(wù)id 2、已開始但未commit的事務(wù)稱為活躍的事務(wù)），在其他事務(wù)中會為該事務(wù)（指的是記錄所在的聚簇索引中存儲的事務(wù)）生成X鎖，并將其置為not waitting（持有）狀態(tài)，而將自己的鎖狀態(tài)標記為waitting（阻塞）狀態(tài)。（這段比較難理解，不要著急，后面會通過案例詳細說明）

    而其他情況則可以正常讀取，不需要生成鎖。

    我們將insert時不生成鎖，等到滿足條件時才生成的鎖稱為隱式鎖，從這里可以看出隱式鎖實際上不是一種新鎖，而是一種特殊的記錄鎖。
     

  • 對于insert語句當(dāng)遇到唯一二級索引重復(fù)時無論事務(wù)處于什么隔離級別都會為記錄添加S型鎖和next-key鎖，而對于insert...on duplicate key...這樣的語句當(dāng)遇到唯一二級索引重復(fù)時無論事務(wù)處于什么隔離級別都會為記錄添加X型鎖和next-key鎖

    （主鍵重復(fù)的場景這里不做介紹，在后面的推薦資料中大家可以自行了解）

所有的鎖在內(nèi)存中都表現(xiàn)為一個鎖結(jié)構(gòu)，鎖結(jié)構(gòu)中有一個等待的屬性，如果為true,表示當(dāng)前事務(wù)獲取到鎖成功，如果為false,表示當(dāng)前事務(wù)尚未獲取到鎖，處于等待狀態(tài)。

死鎖分析

接著排查問題，通過SHOW ENGINE INNODB STATUS語句查事務(wù)加鎖的日志，里面就有最近一次的死鎖記錄。（因為數(shù)據(jù)的敏感性和便于分析，我將數(shù)據(jù)做了替換、刪除了對分析無關(guān)的字段）

SHOW ENGINE INNODB STATUS只會顯示最后一次死鎖日志，如果要顯示所有發(fā)生的死鎖日志則需要將系統(tǒng)變量：innodb_print_all_deadlocks設(shè)置為ON

下面為事務(wù)的死鎖日志，其中標注的①②③④⑤⑥為6個關(guān)鍵點

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-09-28 14:56:20 0x7fb14a2bd700
*** (1) TRANSACTION:
TRANSACTION 1374635254, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 2045802, OS thread handle 140399504230144, query id 12689481084 192.168.0.1 account_001 update
①發(fā)生死鎖時此事務(wù)正在執(zhí)行的語句
insert into course_member_statics(course_id,uid) values('20230928145601000001',222222) on duplicate key update member_delete_flag=0
②此事務(wù)正在等待其他事務(wù)對記錄course_id:20230928145601000001、uid:222222釋放X型記錄鎖
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1753 page no 659149 n bits 360 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374635254 lock_mode X waiting
Record lock, heap no 58 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;; # 3230323330393238313435363031303030303031是20230928145601000001的utf8編碼,這里是course_id字段的值
 1: len 4; hex 0003640E; asc GD ;;# 0003640E是222222十六進制編碼,這里是uid字段的值【下同】
 2: len 8; hex 8000000000a66c9d; asc l ;; # 8000000000a66c9d是10906781十六進制編碼,這里是主鍵id字段的值（存儲的是有符號數(shù)，前面的8要改成0）【下同】

*** (2) TRANSACTION:
TRANSACTION 1374634984, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1136, 160 row lock(s), undo log entries 669
MySQL thread id 2045822, OS thread handle 140399430326016, query id 12689481315 192.168.0.2 account_001 update
③發(fā)生死鎖時此事務(wù)正在執(zhí)行的語句
insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',222222),
('20230928145601000001',111111)

*** (2) HOLDS THE LOCK(S):
④此事務(wù)對記錄course_id:20230928145601000001、uid:222222持有X型記錄鎖
RECORD LOCKS space id 1753 page no 659149 n bits 312 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374634984 lock_mode X locks rec but not gap
Record lock, heap no 38 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640E; asc ;;
 2: len 8; hex 8000000000a66c9d; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
⑤此事務(wù)對記錄course_id:20230928145601000001、uid:222222持有插入意向鎖，正在等待其他事務(wù)對該記錄釋放間隙鎖
RECORD LOCKS space id 1753 page no 659149 n bits 472 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374634984 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 58 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640E; asc  GD ;;
 2: len 8; hex 8000000000a66c9d; asc ;;
⑥最后決定回滾事務(wù)1 
*** WE ROLL BACK TRANSACTION (1)

我們從上述日志中摘取下面幾個關(guān)鍵信息進行說明：

  • LATEST DETECTED DEADLOCK：表示最新檢測到的死鎖，下方為死鎖的事務(wù)日志

  • (x) TRANSACTION：表示第幾個事務(wù)，(1) TRANSACTION為第一個，(2) TRANSACTION為第二個
     

  • WAITING FOR THIS LOCK TO BE GRANTED：表示當(dāng)前事務(wù)正在對某條記錄加某種類型的鎖，但由于其他事務(wù)已經(jīng)對該記錄持有某種類型的鎖而導(dǎo)致阻塞，自己處于等待狀態(tài)中，一旦其他事務(wù)釋放鎖，該事務(wù)就可以加鎖成功。
     

    • RECORD LOCKS...：表示要添加的、處于阻塞中的鎖，其中l(wèi)ock_mode X waiting表示正在等待加X型next-key鎖，lock_mode X locks gap before rec insert intention waiting表示想在某條記錄前面插入記錄，由于該記錄持有間隙鎖，正在等待間隙鎖釋放，此時持有插入意向鎖

    • Record lock：表示要加的、處于等待中的鎖作用在哪些記錄上，可能會有多條。其下方的hex中數(shù)據(jù)為編碼后的數(shù)據(jù)，如果真實數(shù)據(jù)為字符串則編碼格式為十六進制uft8，如果真實數(shù)據(jù)為整形則編碼格式為十六進制，我們可以將其解碼得到真實的數(shù)據(jù)。（下同）

      通過解碼后的數(shù)據(jù)我們就能知道鎖作用于哪些記錄了，這對我們分析死鎖是非常有用的。

  • HOLDS THE LOCK(S)：表示當(dāng)前事務(wù)持有哪些鎖

    • RECORD LOCKS...：表示已經(jīng)持有的鎖，其中l(wèi)ock_mode X locks rec but not gap表示持有記錄的X型記錄鎖，不持有間隙鎖

    • Record lock：表示持有的鎖作用在哪些記錄上，可能會有多條。
       

  • 在(x) TRANSACTION下方和WAITING FOR THIS LOCK TO BE GRANTED或HOLDS THE LOCK(S)上方之間出現(xiàn)的sql語句為導(dǎo)致出現(xiàn)死鎖的sql語句，像日志中標出的①和③就是導(dǎo)致死鎖的sql語句
     

  • WE ROLL BACK TRANSACTION (1)：表示死鎖發(fā)生時回滾哪個事務(wù)，這里回滾的是第一個事務(wù)，Mysql會將受影響的數(shù)據(jù)最少的事務(wù)回滾

下面我們對這次死鎖做一次完整的分析：

通過日志可以知道，
事務(wù)1執(zhí)行的語句為：

insert into course_member_statics(course_id,uid) values('20230928145601000001',222222) on duplicate key update member_delete_flag=0;

事務(wù)2執(zhí)行的語句為：

insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',222222),
('20230928145601000001',111111);

其中course_id和uid為唯一索引。

1、事務(wù)2執(zhí)行插入222222這條數(shù)據(jù)

insert ignore into course_member_statics(course_id,uid) values ('20230928145601000001',222222);

（這里怎么是單條insert，不是批量insert嗎？雖然sql語法是批量insert但實際到存儲引擎執(zhí)行的時候是一條條進行的），因為是普通的insert語句所以不會加鎖

2、事務(wù)1執(zhí)行

insert into course_member_statics(course_id,uid) values('20230928145601000001',222222) on duplicate key update member_delete_flag=0;

發(fā)現(xiàn)事務(wù)2已經(jīng)插入了一個相同的記錄，于是事務(wù)1要對該記錄添加X型next-key鎖。

3、根據(jù)前面的知識我們知道，對一條insert的數(shù)據(jù)，如果其他事務(wù)要對其加S型或X型鎖，且該記錄對應(yīng)的聚簇索引中存儲的事務(wù)id處于活躍狀態(tài)時，就會觸發(fā)這條記錄上的隱式鎖升級為顯示鎖。

在這里就是事務(wù)1給事務(wù)2在222222記錄增加X型記錄鎖，并將其狀態(tài)置于持有狀態(tài)，同時將自己置于阻塞狀態(tài)

4、事務(wù)2執(zhí)行插入111111這條數(shù)據(jù)

insert ignore into course_member_statics(course_id,uid) values ('20230928145601000001',111111);

按照二級索引存儲的特點，記錄111111要插在記錄222222的前面，這時出現(xiàn)了插入意向鎖阻塞，按照我們前面的說的，在某條記錄前面插入數(shù)據(jù)只有在該記錄持有間隙鎖時才會阻塞，問題是事務(wù)1對記錄222222并沒有持有間隙鎖，怎么會阻塞呢？

Mysql規(guī)定，只要別的事務(wù)對記錄生成了一個顯式的間隙鎖的鎖結(jié)構(gòu)，不論那個事務(wù)是已經(jīng)獲取到了該鎖(granted)，還是正在等待獲取(waiting)，當(dāng)前事務(wù)要在該記錄前面插入新記錄都會被阻塞。

回到該例，因為事務(wù)1已經(jīng)為記錄222222生成了一個X型的next-key鎖結(jié)構(gòu)（next-key鎖包含間隙鎖），雖然該鎖的狀態(tài)是在阻塞等待中，但事務(wù)2在該記錄前插入記錄仍然會被阻塞。

這時事務(wù)1在等待事務(wù)2釋放記錄222222上的X型記錄鎖，同時事務(wù)2也在等待事務(wù)1在記錄222222上的間隙鎖釋放，出現(xiàn)了互相等待的現(xiàn)象，導(dǎo)致了死鎖發(fā)生。

最后由于死鎖導(dǎo)致事務(wù)1被回滾了，事務(wù)2執(zhí)行成功，因為事務(wù)2包含事務(wù)1的數(shù)據(jù)，所有沒有對線上的數(shù)據(jù)造成影響，就算最后回滾的是事務(wù)2也沒問題，因為insert ignore into語句代碼做了錯誤重試處理。

下面我們通過例子還原上述死鎖，并對每條sql語句的執(zhí)行進行加鎖分析

還原死鎖

建表sql語句

DROP TABLE IF EXISTS `course_member_statics`;
CREATE TABLE `course_member_statics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `course_id` varchar(40) NOT NULL DEFAULT '' COMMENT '課程ID',
  `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用戶UID',
  `delete_flag` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否被刪除 狀態(tài) 0：未刪除  1：已刪除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_courseid_uid` (`course_id`,`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='課程成員表';

事務(wù)1要執(zhí)行的語句：

START TRANSACTION;
a、insert into course_member_statics(course_id,uid) values ('20230928145601000001',222222) on duplicate key update delete_flag=0;
COMMIT;

事務(wù)2要執(zhí)行的語句：（為了每次都出現(xiàn)死鎖，這里將批量插入改成了單獨的兩條insert）

START TRANSACTION;
b、insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',222222);
c、insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',111111);
COMMIT;

我們按照b，a，c的順序逐步在終端執(zhí)行（事務(wù)開始前最好要先執(zhí)行START TRANSACTION語句，如果不執(zhí)行同時系統(tǒng)變量autocommit=ON時每執(zhí)行一條sql都會認為是一個單獨的事務(wù)，無法看到死鎖效果），

并通過SHOW ENGINE INNODB STATUS來查看加鎖情況（注意：開始執(zhí)行sql語句前還需要將系統(tǒng)變量innodb_status_output_locks打開（set GLOBAL innodb_status_output_locks = 1），否則日志中不會出現(xiàn)任何加鎖信息）

1、先執(zhí)行事務(wù)2的b語句，執(zhí)行SHOW ENGINE INNODB STATUS看日志

------------
TRANSACTIONS
------------
---TRANSACTION 1864, ACTIVE 5 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 22, OS thread handle 6129594368, query id 125 localhost 127.0.0.1 root
TABLE LOCK table `test`.`course_member_statics` trx id 1864 lock mode IX

看TRANSACTIONS段落，可以看出語句執(zhí)行完后事務(wù)2只持有表的意向X型鎖，沒有持有記錄的任何鎖

2、再執(zhí)行事務(wù)1的a語句，執(zhí)行SHOW ENGINE INNODB STATUS看日志

------------
TRANSACTIONS
------------
---TRANSACTION 1865, ACTIVE 20 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 6131822592, query id 127 localhost 127.0.0.1 root update
insert into course_member_statics(course_id,uid) values ('20230928145601000001',222222) on duplicate key update delete_flag=0
------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1865 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

------------------
TABLE LOCK table `test`.`course_member_statics` trx id 1865 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1865 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

---TRANSACTION 1864, ACTIVE 58 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 22, OS thread handle 6129594368, query id 125 localhost 127.0.0.1 root
TABLE LOCK table `test`.`course_member_statics` trx id 1864 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

看TRANSACTIONS段落，可以看到事務(wù)2本來是沒有持有記錄222222的X型記錄鎖的，在執(zhí)行這條語句后就有了，并且事務(wù)1自己對該記錄的X型next-key鎖置于等待中。這正是隱式鎖升級為顯示鎖的效果

3、最后執(zhí)行事務(wù)2的c語句，執(zhí)行SHOW ENGINE INNODB STATUS看日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-06 15:35:02 0x16c617000
*** (1) TRANSACTION:
TRANSACTION 1865, ACTIVE 36 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 6131822592, query id 127 localhost 127.0.0.1 root update
insert into course_member_statics(course_id,uid) values ('20230928145601000001',222222) on duplicate key update delete_flag=0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1865 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 1864, ACTIVE 74 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 22, OS thread handle 6129594368, query id 129 localhost 127.0.0.1 root update
insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',111111)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
---TRANSACTION 1864, ACTIVE 92 sec
3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 22, OS thread handle 6129594368, query id 129 localhost 127.0.0.1 root
TABLE LOCK table `test`.`course_member_statics` trx id 1864 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks gap before rec insert intention
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

當(dāng)執(zhí)行這條語句后事務(wù)1的終端出現(xiàn)了死鎖的錯誤提示：“Deadlock found when trying to get lock; try restarting transaction”

Deadlock found when trying to get lock; try restarting transaction

先看TRANSACTIONS段落，可以看出事務(wù)2分別對記錄222222持有X型記錄鎖和插入意向鎖，持有插入意向鎖是因為在記錄222222插入插入111111時被間隙鎖阻塞了。

再看LATEST DETECTED DEADLOCK段落，可以看到事務(wù)1在等待事務(wù)2釋放記錄222222上的X型記錄鎖，同時事務(wù)2也在等待事務(wù)1在記錄222222上的間隙鎖釋放，出現(xiàn)了互相等待的現(xiàn)象，導(dǎo)致了死鎖發(fā)生。因為事務(wù)1只影響1條記錄，而事務(wù)2影響兩條記錄，所以將事務(wù)1回滾。

如果將執(zhí)行順序改成a,b,c也會出現(xiàn)死鎖，死鎖原因跟上面的類似，至于其他組合：a,c,b、b,c,a、c,a,b、c,b,a都不會出現(xiàn)死鎖，至于原因大家可以自己分析一下。

上面所有的分析都是基于REPEATABLE READ隔離級別分析的，如果換成READ UNCOMMITTED，READ COMMITTED，SERIALIZABLE隔離級別還會出現(xiàn)死鎖嗎？

答案是會的，因為無論是哪種事務(wù)隔離級別，insert遇到唯一二級索引重復(fù)時都會給記錄添加next-key鎖（包含間隙鎖），且會觸發(fā)隱式鎖升級為顯示鎖，而這兩者正是導(dǎo)致出現(xiàn)死鎖的條件。

如何避免死鎖

既然存在死鎖的問題，那么死鎖能避免嗎？ 避免死鎖的方法：

  • 改變事務(wù)執(zhí)行語句的順序
    在確保業(yè)務(wù)功能正確的情況下，可以通過改變語句的執(zhí)行順序避免死鎖。當(dāng)然前提是得知道是什么原因?qū)е碌乃梨i

    但很多時候語句的執(zhí)行順序會隨著數(shù)據(jù)的變化而變化的，無法人為控制，像上面死鎖的問題insert ignore實際上有上千條數(shù)據(jù)批量插入，無法知道存儲引擎到底先執(zhí)行哪條后執(zhí)行哪條
     

  • 給記錄添加合適的索引
    建立合適的索引，縮小鎖作用的范圍和減少事務(wù)的執(zhí)行的時間，這樣能減少事務(wù)之間爭搶鎖的概率

雖然死鎖可以一定程度的減少，但無法完全避免，當(dāng)出現(xiàn)死鎖時也不必過于擔(dān)心，Mysql會以最小的代價回滾事務(wù)，只要我們做了合理的重試機制（要注意重試的頻率，過快可能會導(dǎo)致進一步死鎖），比如對異步的操作要做重試處理，因為發(fā)生錯誤無法直接反饋給操作人，同步操作還好，發(fā)生死鎖會收到報錯信息，重新執(zhí)行即可。

總結(jié)

Mysql insert 語句在特定的并發(fā)場景下也是會出現(xiàn)死鎖的，當(dāng)我們能分析出死鎖的原因，就能做到有的放矢。以下為本篇文章主要內(nèi)容

  • 記錄鎖、間隙鎖、插入意向鎖、next-key鎖、隱式鎖的定義以及作用

  • 隱式鎖在特定的條件下會升級為顯示鎖

  • insert語句在遇到唯一二級索引重復(fù)時會為記錄添加S型的next-key鎖，而insert... on duplicate key...則會添加X型的next-key鎖
     

  • 只要別的事務(wù)對記錄生成了一個顯式的間隙鎖的鎖結(jié)構(gòu)，不論那個事務(wù)是已經(jīng)獲取到了該鎖，還是正在等待獲取，當(dāng)前事務(wù)要在該記錄前面插入新記錄都會被阻塞。
     

  • 通過SHOW ENGINE INNODB STATUS查看、分析死鎖和加鎖過程

  • 在能確定語句的執(zhí)行順序且保證業(yè)務(wù)功能正確的情況下可以通過改變語句的執(zhí)行順序避免死鎖。死鎖不能完全避免，要有合理的重試機制

到此這篇關(guān)于Mysql insert是否也會發(fā)生死鎖的文章就介紹到這了,更多相關(guān)Mysql insert死鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論