1、環境說明

MySQL5.6.33，隔離級別是RR。表結構及資料：

Create table t1(id int not null primary key auto_increment,c1 int,c2 int,c3 int, unique key(c1),unique key(c2));

insert into t1(c1,c2,c3) values(1,3,4),(6,6,10),(9,9,14);

2、測試用例

session1	session2	session3
begin;	begin;	begin;
insert into t1 (c1,c2,c3) values(4,4,1);
	insert into t1 (c1,c2,c3) values(4,4,2);
		insert into t1 (c1,c2,c3) values(4,4,3);
commit;
	Update t1 set c3=5 where c1=4;
		Update t1 set c3=5 where c1=4;
		deadlock;

3、死鎖日誌


	
	
		
		
			
			
				
			

		

		
			
			
				------------------------
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				LATEST DETECTED DEADLOCK
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				------------------------
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				2018-07-07 06:27:15 a347bb90
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				*** (1) TRANSACTION:
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				TRANSACTION 7973, ACTIVE 43 sec starting index read
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				mysql tables in use 1, locked 1
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s)
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				MySQL thread id 4, OS thread handle 0xa34acb90, query id 75 localhost root updating
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				Update t1 set c3=5 where c1=4
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				RECORD LOCKS space id 18 page no 4 n bits 72 index `c1` of table `yzs`.`t1` trx id 7973 lock_mode X locks rec but not gap waiting
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				0: len 4; hex 80000004; asc     ;;
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				1: len 4; hex 80000006; asc     ;;
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				*** (2) TRANSACTION:
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				TRANSACTION 7974, ACTIVE 33 sec starting index read
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				mysql tables in use 1, locked 1
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				3 lock struct(s), heap size 320, 2 row lock(s)
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				MySQL thread id 5, OS thread handle 0xa347bb90, query id 76 localhost root updating
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				Update t1 set c3=5 where c1=4
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				*** (2) HOLDS THE LOCK(S):
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				RECORD LOCKS space id 18 page no 4 n bits 72 index `c1` of table `yzs`.`t1` trx id 7974 lock mode S
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				0: len 4; hex 80000004; asc     ;;
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				1: len 4; hex 80000006; asc     ;;
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				RECORD LOCKS space id 18 page no 4 n bits 72 index `c1` of table `yzs`.`t1` trx id 7974 lock_mode X locks rec but not gap waiting
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				0: len 4; hex 80000004; asc     ;;
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				1: len 4; hex 80000006; asc     ;;
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				
			

		

	

	
		
		
			
			
				
			

		

		
			
			
				*** WE ROLL BACK TRANSACTION (2)

4、分析死鎖日誌

從死鎖日誌上可以看到：

TRANSACTION 7973：

Update t1 set c3=5 where c1=4語句在等待二級索引c1上(4,6)上的X型別的記錄鎖（lock_mode X locks rec but not gap）

TRANSACTION 7974：

擁有二級索引c1上(4,6)上S型別的next key鎖(lock mode S),等待申請(4,6)上的記錄鎖(lock_mode X locks rec but not gap)

但從死鎖日誌上只看到兩個update語句互相等待，不知道業務邏輯場景的話，很難找到原因。

注：這裡留下疑問，為什麼主鍵是6呢，不是4？這個和自增鍵有關，關於自增值這裡不做過多考慮，感興趣的自行測試分析。

5、加鎖原理

1）關於insert唯一鍵加鎖時重複鍵判斷加S型別next-key鎖的加鎖原理見之前部落格：

https://blog.csdn.net/yanzongshuai/article/details/79326637

以及https://blog.csdn.net/yanzongshuai/article/details/79301868

注意，這裡發生重複鍵加S 型別next key鎖時，不論是什麼隔離級別，都會加這樣的鎖。

2）關於隱式鎖轉換顯式鎖流程見之前部落格：

https://blog.csdn.net/yanzongshuai/article/details/79306514

https://blog.csdn.net/yanzongshuai/article/details/79254031

https://blog.csdn.net/yanzongshuai/article/details/79252679

3）關於update加鎖原理見之前部落格：

https://blog.csdn.net/yanzongshuai/article/details/80870949

https://blog.csdn.net/yanzongshuai/article/details/80870957

https://blog.csdn.net/yanzongshuai/article/details/80872095

6、解析

1）session1執行insert into t1 (c1,c2,c3) values(4,4,1);實際上是沒有加任何鎖的。

2）session2執行insert into t1 (c1,c2,c3) values(4,4,2);二級索引(4)和session1的發生衝突，使session1的隱式鎖轉換成顯式鎖；發生唯一衝突，則對(4)加S型別的next key鎖，此時session1已經加了X鎖，發生等待；

3）session3執行insert into t1 (c1,c2,c3) values(4,4,3);同理，等待session1釋放二級索引c1(4)上的X鎖，申請S型別的next key鎖。

4）session1執行commit後，session2和session3報錯：ERROR 1062 (23000): Duplicate entry '4' for key 'c1'，同時會申請到S型別的next key鎖。

5）session2執行Update t1 set c3=5 where c1=4;從之前部落格

https://blog.csdn.net/yanzongshuai/article/details/80872095

可知在search階段會對二級索引記錄(4)申請X型別的記錄鎖。session3已擁有S型別next key鎖，所以發生等待；

6）session3再執行Update t1 set c3=5 where c1=4;同理，會申請X型別的記錄鎖，等待session2釋放其S型別next key鎖。此時發生死鎖。

7、解決方法

楊奇龍老師解釋可以使用使用insert on duplicate key語句來代替原來的insert語句。這2個語句的加鎖不一樣，感興趣的可以研究下。

8、參考

https://mp.weixin.qq.com/s/96CDhpgu5uUQ7qKYhKgt3w

死鎖案例二