时间戳

首页 > 解决方案 > 为什么插入时会发生死锁？

mysql - 为什么插入时会发生死锁？

问题描述

对执行插入的死锁感到困惑，而我之前似乎持有相同的锁。

Mysql5.6，Innodb 引擎，读取已提交。

CREATE TABLE `tb_test` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `name` VARCHAR(50) NOT NULL DEFAULT '0',
    `md_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `data` BLOB NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `user_id` (`user_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
insert into tb_test (user_id) values (0);
Here are how deadlock happens:
tx1:start transaction;
tx1:SELECT * FROM tb_test WHERE user_id = 0 FOR UPDATE;
tx2:start transaction;
tx2:SELECT * FROM tb_test WHERE user_id = 0 FOR UPDATE;//blocking
tx1:insert into tb_test (user_id) values (0) on duplicate key update name = 'name';////deadlock occurs

我认为 tx1 在执行第一个 sql 后获得了值为 0 的索引 user_id 上的 X 锁enter code here，而 tx2 正在阻塞，为什么 tx1 在尝试获取已经获得的相同 X 锁时会出现死锁？请帮忙。

死锁日志为：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-03-27 10:01:34 0x560
*** (1) TRANSACTION:
TRANSACTION 176686, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 791, OS thread handle 13064, query id 16596 127.0.0.1 root Statistics
SELECT * FROM tb_test WHERE user_id = 0 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1067 page no 4 n bits 72 index user_id of table `test`.`tb_test` trx id 176686 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000000; asc     ;;
 1: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 176685, ACTIVE 3 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 790, OS thread handle 1376, query id 16597 127.0.0.1 root Update
insert into tb_test (user_id) values (0) ON DUPLICATE KEY UPDATE NAME = '1'
#SELECT * FROM tb_test WHERE user_id = 0 LOCK IN SHARE MODE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1067 page no 4 n bits 72 index user_id of table `test`.`tb_test` trx id 176685 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000000; asc     ;;
 1: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1067 page no 4 n bits 72 index user_id of table `test`.`tb_test` trx id 176685 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000000; asc     ;;
 1: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

tx2 持有的锁和它等待的锁不一样吗？

标签： mysql

解决方案

tx1 失败，因为更新状态确实在发生并且 tx2 有一个意图锁定。

在事务的每个阶段查看SELECT * FROM performance_schema.data_locks。

推荐阅读