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.
在已提交读事务隔离级别下，官方文档有如下描述：
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.
Gap locking is only used for foreign-key constraint checking and duplicate-key checking.
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
测试场景
- 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索引记录之间加间隙锁
