MySQL--REPLACE INTO导致的死锁案例01

测试场景

• MySQL版本： 5.7.29
• 事务级别： READ-COMMITTED

测试数据

DROP TABLE IF EXISTS tb1001;
CREATE TABLE `tb1001` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `c1` INT(11) NOT NULL,
  `c2` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNI_C1` (`c1`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO TB1001(C1,C2)SELECT 1,1;
INSERT INTO TB1001(C1,C2)SELECT 2,2;
INSERT INTO TB1001(C1,C2)SELECT 3,3;

测试操作

会话1先执行:

BEGIN;
## 执行成功
REPLACE INTO tb1001(c1,c2)VALUES(2,22)

会话2再执行:

BEGIN;
## 执行被阻塞
REPLACE INTO tb1001(c1,c2)VALUES(1,11)

会话3查下锁信息：

mysql> select * from information_schema.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 15317:1890:4:3 | 15317       | X         | RECORD    | `test`.`tb1001` | UNI_C1     |       1890 |         4 |        3 | 2         |
| 15312:1890:4:3 | 15312       | X         | RECORD    | `test`.`tb1001` | UNI_C1     |       1890 |         4 |        3 | 2         |
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+

会话1再执行：

## 执行成功
REPLACE INTO tb1001(c1,c2)VALUES(1,11)

出现死锁，会话2被回滚

死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-04-07 16:39:33 0x7f9222cab700
*** (1) TRANSACTION:
TRANSACTION 15317, ACTIVE 213 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 55, OS thread handle 140265625392896, query id 395 127.0.0.1 mysql_admin update
REPLACE INTO tb1001(c1,c2)VALUES(1,11)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1890 page no 4 n bits 72 index UNI_C1 of table `test`.`tb1001` trx id 15317 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 15312, ACTIVE 224 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3
MySQL thread id 56, OS thread handle 140265625663232, query id 396 127.0.0.1 mysql_admin update
REPLACE INTO tb1001(c1,c2)VALUES(1,11)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1890 page no 4 n bits 72 index UNI_C1 of table `test`.`tb1001` trx id 15312 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000003; asc     ;;

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

*** WE ROLL BACK TRANSACTION (1)