测试场景
- 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)