mysql一個死鎖分析

版本5.6，隔離級别為rc

表結構：

CREATE TABLE `uk_test` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`a` int(11) NOT NULL,

`b` int(11) NOT NULL,

`c` int(11) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uk_a_b` (`a`,`b`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4

表中資料：

+----+---+---+---+

| id | a | b | c |

| 1 | 1 | 1 | 2 |

| 6 | 1 | 2 | 1 |

sql:執行順序

session1:begin;

session2:begin;

session1:select * from uk_test where a = 1 and b = 1 for update;

session2:select * from uk_test where a = 1 and b = 1 for update;

session1:insert into uk_test (a,b,c) values(1,1,2) on duplicate key update c = 2;

session2:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖資訊：

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

LATEST DETECTED DEADLOCK

2019-02-23 13:33:12 2b5795081700

*** (1) TRANSACTION:

TRANSACTION 2088212, ACTIVE 17.150 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

LOCK BLOCKING MySQL thread id: 453394049 block 436450404

MySQL thread id 436450404, OS thread handle 0x2b5794040700, query id 16524108 10.151.203.57 hkadmin statistics

select * from uk_test where a = 1 and b = 1 for update

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

RECORD LOCKS space id 149 page no 4 n bits 72 index `uk_a_b` of table `aaaaa`.`uk_test` trx id 2088212 lock_mode X locks rec but not gap waiting

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

0: len 4; hex 80000001; asc ;;

1: len 4; hex 80000001; asc ;;

2: len 8; hex 8000000000000007; asc ;;

*** (2) TRANSACTION:

TRANSACTION 2088208, ACTIVE 21.411 sec inserting

4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1

MySQL thread id 453394049, OS thread handle 0x2b5795081700, query id 16524256 10.151.203.57 hkadmin update

insert into uk_test (a,b,c) values(1,1,2) on duplicate key update c = 2

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

RECORD LOCKS space id 149 page no 4 n bits 72 index `uk_a_b` of table `aaaaa`.`uk_test` trx id 2088208 lock_mode X locks rec but not gap

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

RECORD LOCKS space id 149 page no 4 n bits 72 index `uk_a_b` of table `aaaaa`.`uk_test` trx id 2088208 lock_mode X waiting

*** WE ROLL BACK TRANSACTION (1)

 分析：

如果a+b是主鍵，那麼update的時候要擷取a+b上的X鎖，那麼事務B就會繼續等待，事務A已經持有了a+b的X鎖，會繼續執行，執行後釋放掉，然後事務B繼續運作

如果a+b是唯一索引，那麼

其實要先擷取a+b的S鎖，但是此時事務B在等待a+b的X鎖，是以事務A拿不到S鎖，事務A和事務B都等待a+b的鎖，并且這兩個鎖是互斥的，是以B被選為犧牲品

unique key 對 重複值比較的時候，需要先拿到 uk 上的 S 鎖的