How to explain the reason for this deadlock?

Question

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;

2. second step

start transaction;
update test set uni_id=1,total=total+1 where uni_id=1;

3. third step Transaction 1：

update test set current_status=1 where id=1 and 
current_status=0;

then the dealock happened.

1. first step : transaction 1 holds S lock.
2. second step: transaction 2 waits, and from the results of the source code debug,the obtained lock failed.
3. third step: deadlock

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)

Answer 1

I don't believe that your analysis of what actually happened is completely correct. This is the likely version of events:

1. First transaction gets an S lock on the record
2. Second transaction wants to get an exclusive lock on the same record, but can't, because the first transaction holds the S lock. This transaction therefore waits, trying to obtain the lock.
3. The third transaction also goes into a wait state on the same record, but now a deadlock happens.

From the MySQL documentation:

Here, FOR SHARE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.

As that documentation suggests, a better approach might be to do a SELECT ... FOR UPDATE:

SELECT * FROM test WHERE id = 1 FOR UPDATE;
UPDATE test SET uni_id = 1, total = total+1 WHERE uni_id = 1;

Answer 2

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