There are tow transaction,transaction 1 holds an S lock on a row,transaction 2 wants to update the row,then transaction 2 waits,then transaction 1 also performs an updates on the row,at this time a deadlock occurs,I think Know what the reason is ? what is the lock situation here?
I did the following test on mysql5.6 version.There is a deadlock.
Table Stracture：
CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增',
`uni_id` bigint(20) DEFAULT NULL,
`current_status` int(11) DEFAULT '0' ,
`total` int(11) NOT NULL DEFAULT '0' ,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_id_unique` (`uni_id`),
KEY `uni_id_idx` (`uni_id`),
KEY `current_status_idx` (`current_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
init data：
INSERT INTO `test`(`id`, `uni_id`, `current_status`, `total`) VALUES (1, 1, 0, 1);
The following operations are performed in order： 1. first step Transaction 1 :
start transaction;
select * from test where id=1 lock in share mode;
start transaction;
update test set uni_id=1,total=total+1 where uni_id=1;
update test set current_status=1 where id=1 and
current_status=0;
then the dealock happened.
the deadlock info :
*** (1) TRANSACTION:
TRANSACTION 4360, ACTIVE 14 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 2, OS thread handle 0x70000a7f4000, query id 145 localhost 127.0.0.1 root updating
update test set uni_id=1,total=total+1 where uni_id=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 3 n bits 72 index `PRIMARY` of table `test`.`test` trx id 4360 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000001106; asc ;;
2: len 7; hex 83000001360110; asc 6 ;;
3: len 8; hex 8000000000000001; asc ;;
4: len 4; hex 80000000; asc ;;
5: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 4359, ACTIVE 24 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1, OS thread handle 0x70000a7b0000, query id 149 localhost 127.0.0.1 root updating
update test set current_status=1 where id=1 and
current_status=0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4 page no 3 n bits 72 index `PRIMARY` of table `test`.`test` trx id 4359 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000001106; asc ;;
2: len 7; hex 83000001360110; asc 6 ;;
3: len 8; hex 8000000000000001; asc ;;
4: len 4; hex 80000000; asc ;;
5: len 4; hex 80000001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 3 n bits 72 index `PRIMARY` of table `test`.`test` trx id 4359 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000001106; asc ;;
2: len 7; hex 83000001360110; asc 6 ;;
3: len 8; hex 8000000000000001; asc ;;
4: len 4; hex 80000000; asc ;;
5: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (1)
A Friend of mine explained this situation.
From the MYSQL documentation：
Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. As a result, InnoDB generates an error for one of the clients and releases its locks