MySQL--REPLACE INTO加锁测试01

测试需求

在MySQL官方文档中有如下描述：

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

REPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row to be replaced.

测试场景

• 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 3,3;
INSERT INTO TB1001(C1,C2)SELECT 5,5;
INSERT INTO TB1001(C1,C2)SELECT 7,7;

测试操作

会话1先执行:

BEGIN;
REPLACE INTO tb1001(c1,c2)VALUES(3,33);

测试01

会话2执行(被阻塞):

SELECT C1 FROM TB1001 WHERE C1=3 FOR UPDATE;

查看阻塞信息：

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 |
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 15464:1896:4:3 | 15464       | X         | RECORD    | `test`.`tb1001` | UNI_C1     |       1896 |         4 |        3 | 3         |
| 15459:1896:4:3 | 15459       | X         | RECORD    | `test`.`tb1001` | UNI_C1     |       1896 |         4 |        3 | 3         |
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)


SHOW ENGINE INNODB STATUS \G

------------
TRANSACTIONS
------------
Trx id counter 15465
Purge done for trx‘s n:o < 15464 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421744043505488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15464, ACTIVE 85 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 59, OS thread handle 140265625663232, query id 517 127.0.0.1 mysql_admin statistics
SELECT C1 FROM TB1001 WHERE C1=3 FOR UPDATE
------- TRX HAS BEEN WAITING 35 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1896 page no 4 n bits 72 index UNI_C1 of table `test`.`tb1001` trx id 15464 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000002; asc     ;;

------------------
---TRANSACTION 15459, ACTIVE 45 sec
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 58, OS thread handle 140265625122560, query id 519 127.0.0.1 mysql_admin starting
SHOW ENGINE INNODB STATUS
--------

测试02

会话2执行(被阻塞):

SELECT C1 FROM TB1001 WHERE C1=5;

查看锁信息：

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 |
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 15464:1896:4:4 | 15464       | X         | RECORD    | `test`.`tb1001` | UNI_C1     |       1896 |         4 |        4 | 5         |
| 15459:1896:4:4 | 15459       | X         | RECORD    | `test`.`tb1001` | UNI_C1     |       1896 |         4 |        4 | 5         |
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)



SHOW ENGINE INNODB STATUS \G

------------
TRANSACTIONS
------------
Trx id counter 15465
Purge done for trx‘s n:o < 15464 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421744043505488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15464, ACTIVE 179 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 59, OS thread handle 140265625663232, query id 523 127.0.0.1 mysql_admin statistics
SELECT C1 FROM TB1001 WHERE C1=5 FOR UPDATE
------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1896 page no 4 n bits 72 index UNI_C1 of table `test`.`tb1001` trx id 15464 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

------------------
---TRANSACTION 15459, ACTIVE 139 sec
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 58, OS thread handle 140265625122560, query id 524 127.0.0.1 mysql_admin starting
SHOW ENGINE INNODB STATUS
--------

测试03

会话2执行(被阻塞):

INSERT INTO TB1001(C1,C2)SELECT 4,4;

查看锁信息：

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 |
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 15464:1896:4:4 | 15464       | X,GAP     | RECORD    | `test`.`tb1001` | UNI_C1     |       1896 |         4 |        4 | 5         |
| 15459:1896:4:4 | 15459       | X         | RECORD    | `test`.`tb1001` | UNI_C1     |       1896 |         4 |        4 | 5         |
+----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)



SHOW ENGINE INNODB STATUS \G

------------
TRANSACTIONS
------------
Trx id counter 15465
Purge done for trx‘s n:o < 15464 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421744043505488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15464, ACTIVE 484 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 59, OS thread handle 140265625663232, query id 529 127.0.0.1 mysql_admin executing
INSERT INTO TB1001(C1,C2)SELECT 4,4
------- TRX HAS BEEN WAITING 48 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1896 page no 4 n bits 72 index UNI_C1 of table `test`.`tb1001` trx id 15464 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

------------------
---TRANSACTION 15459, ACTIVE 444 sec
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 58, OS thread handle 140265625122560, query id 531 127.0.0.1 mysql_admin starting
SHOW ENGINE INNODB STATUS
--------

测试04

会话2执行(执行成功):

INSERT INTO TB1001(C1,C2)SELECT 6,7;

测试结论

当REPLACE INTO插入数据(c1=3,c2=33)和现有数据(c1=3,c2=3)在唯一索引idx_c1上冲突时，由于目前idx_c1上数据为(1,3,5,7),因此会：

• 对c1=3的索引记录加行锁
• 对c1=3的索引记录的下一条记录(c1=5)加行锁
• 对c1=3的索引记录到c1=5索引记录之间加间隙锁

MySQL--REPLACE INTO加锁测试01

原文：https://www.cnblogs.com/gaogao67/p/14635013.html