亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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

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

前言

今天給大家分享我們前幾天線(xiàn)上遇到的一個(gè)Mysql死鎖的案列，希望在大家碰到類(lèi)似的問(wèn)題時(shí)有所幫助。

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

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

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

前置知識(shí)

  • 記錄鎖
    包含共享鎖和獨(dú)占鎖

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

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

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

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

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

  • 隱式鎖
    Mysql 為了節(jié)省鎖的開(kāi)銷(xiāo)，insert語(yǔ)句執(zhí)行時(shí)記錄是不會(huì)生成鎖的，只有在滿(mǎn)足下面條件時(shí)insert語(yǔ)句執(zhí)行后的記錄才會(huì)生成鎖：

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

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

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

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

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

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

死鎖分析

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

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

下面為事務(wù)的死鎖日志，其中標(biāo)注的①②③④⑤⑥為6個(gè)關(guān)鍵點(diǎ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ā)生死鎖時(shí)此事務(wù)正在執(zhí)行的語(yǔ)句
insert into course_member_statics(course_id,uid) values('20230928145601000001',222222) on duplicate key update member_delete_flag=0
②此事務(wù)正在等待其他事務(wù)對(duì)記錄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十六進(jìn)制編碼,這里是uid字段的值【下同】
 2: len 8; hex 8000000000a66c9d; asc l ;; # 8000000000a66c9d是10906781十六進(jìn)制編碼,這里是主鍵id字段的值（存儲(chǔ)的是有符號(hào)數(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ā)生死鎖時(shí)此事務(wù)正在執(zhí)行的語(yǔ)句
insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',222222),
('20230928145601000001',111111)

*** (2) HOLDS THE LOCK(S):
④此事務(wù)對(duì)記錄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ù)對(duì)記錄course_id:20230928145601000001、uid:222222持有插入意向鎖，正在等待其他事務(wù)對(duì)該記錄釋放間隙鎖
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)

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

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

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

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

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

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

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

  • 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：表示持有的鎖作用在哪些記錄上，可能會(huì)有多條。
       

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

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

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

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

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

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

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語(yǔ)法是批量insert但實(shí)際到存儲(chǔ)引擎執(zhí)行的時(shí)候是一條條進(jìn)行的），因?yàn)槭瞧胀ǖ膇nsert語(yǔ)句所以不會(huì)加鎖

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)插入了一個(gè)相同的記錄，于是事務(wù)1要對(duì)該記錄添加X(jué)型next-key鎖。

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

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

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

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

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

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

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

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

最后由于死鎖導(dǎo)致事務(wù)1被回滾了，事務(wù)2執(zhí)行成功，因?yàn)槭聞?wù)2包含事務(wù)1的數(shù)據(jù)，所有沒(méi)有對(duì)線(xiàn)上的數(shù)據(jù)造成影響，就算最后回滾的是事務(wù)2也沒(méi)問(wèn)題，因?yàn)閕nsert ignore into語(yǔ)句代碼做了錯(cuò)誤重試處理。

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

還原死鎖

建表sql語(yǔ)句

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 '用戶(hù)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í)行的語(yǔ)句：

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í)行的語(yǔ)句：（為了每次都出現(xiàn)死鎖，這里將批量插入改成了單獨(dú)的兩條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ù)開(kāi)始前最好要先執(zhí)行START TRANSACTION語(yǔ)句，如果不執(zhí)行同時(shí)系統(tǒng)變量autocommit=ON時(shí)每執(zhí)行一條sql都會(huì)認(rèn)為是一個(gè)單獨(dú)的事務(wù)，無(wú)法看到死鎖效果），

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

1、先執(zhí)行事務(wù)2的b語(yǔ)句，執(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段落，可以看出語(yǔ)句執(zhí)行完后事務(wù)2只持有表的意向X型鎖，沒(méi)有持有記錄的任何鎖

2、再執(zhí)行事務(wù)1的a語(yǔ)句，執(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本來(lái)是沒(méi)有持有記錄222222的X型記錄鎖的，在執(zhí)行這條語(yǔ)句后就有了，并且事務(wù)1自己對(duì)該記錄的X型next-key鎖置于等待中。這正是隱式鎖升級(jí)為顯示鎖的效果

3、最后執(zhí)行事務(wù)2的c語(yǔ)句，執(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í)行這條語(yǔ)句后事務(wù)1的終端出現(xiàn)了死鎖的錯(cuò)誤提示：“Deadlock found when trying to get lock; try restarting transaction”

Deadlock found when trying to get lock; try restarting transaction

先看TRANSACTIONS段落，可以看出事務(wù)2分別對(duì)記錄222222持有X型記錄鎖和插入意向鎖，持有插入意向鎖是因?yàn)樵谟涗?22222插入插入111111時(shí)被間隙鎖阻塞了。

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

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

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

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

如何避免死鎖

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

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

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

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

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

總結(jié)

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

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

  • 隱式鎖在特定的條件下會(huì)升級(jí)為顯示鎖

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

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

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

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

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

相關(guān)文章

最新評(píng)論