如何解释出现这种僵局的原因？

Question

有两个事务，事务1在一行上持有S锁，事务2想更新行，然后事务2等待，然后事务1也对行执行更新，此时发生死锁，我想知道原因是什么？这里的锁是什么情况？

我在mysql5.6上做了下面的测试，version.There是死锁。

表图：

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数据：

INSERT INTO `test`(`id`, `uni_id`, `current_status`, `total`) VALUES (1, 1, 0, 1);

以下操作按顺序执行: 1.第一步事务1：

 start transaction;
 select * from test where id=1 lock in share mode;

1. 第二步

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

1. 第三步交易1：

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

然后就发生了。

1. 第一步:事务1持有S锁。
2. 第二步:事务2等待，从源代码调试的结果来看，获得的锁失败了。
3. 第三步:死锁

死锁信息：

*** (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

我的一个朋友解释了这种情况。

在MYSQL文档中：

这里会发生死锁，因为客户端A需要一个X锁来删除该行。但是，不能授予该锁请求，因为客户端B已经有一个X锁请求，并且正在等待客户端A释放其S锁。也不能将A持有的S锁升级为X锁，因为B事先请求使用X锁。因此，InnoDB为其中一个客户端生成一个错误并释放其锁。

Answer 2

我不相信你对实际发生的事情的分析是完全正确的。这是事件的可能版本：

1. 第一个事务在记录上获得一个S锁
2. 第二个事务希望获得同一记录上的独占锁，但不能，因为第一个事务持有S锁。因此，此事务等待，试图获得锁。
3. 第三个事务也在同一记录上进入等待状态，但现在会发生死锁。

来自MySQL 文档

在这里，共享不是一个很好的解决方案，因为如果两个用户同时读取计数器，那么在尝试更新计数器时，至少有一个用户会陷入死锁。

如文档所示，更好的方法可能是执行SELECT ... FOR UPDATE

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