select ... for update 是否可以锁不存在的记录？
结论
可以加锁，但锁的是supremum pseudo-record记录，导致表级插入数据阻塞。
验证
表
1
2
3
4
5
6
7
| CREATE TABLE `tb_order_usage` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`order_id` bigint unsigned NOT NULL,
`user_id` bigint unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `tb_order_usage_unique` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
当前数据
1
2
3
4
5
6
| mysql> select * from tb_order_usage;
+
| id | order_id | user_id |
+
| 5 | 111 | 111 |
+
|
事务A
1
2
| begin;
select * from tb_order_usage where order_id = 222 for update;
|
查看当前事务
1
2
3
4
| mysql> SELECT * FROM information_schema.INNODB_TRX;
trx_id|trx_state|trx_started |trx_requested_lock_id|trx_wait_started|trx_weight|trx_mysql_thread_id|trx_query|trx_operation_state|trx_tables_in_use|trx_tables_locked|trx_lock_structs|trx_lock_memory_bytes|trx_rows_locked|trx_rows_modified|trx_concurrency_tickets|trx_isolation_level|trx_unique_checks|trx_foreign_key_checks|trx_last_foreign_key_error|trx_adaptive_hash_latched|trx_adaptive_hash_timeout|trx_is_read_only|trx_autocommit_non_locking|trx_schedule_weight|
------+---------+-------------------+---------------------+----------------+----------+-------------------+---------+-------------------+-----------------+-----------------+----------------+---------------------+---------------+-----------------+-----------------------+-------------------+-----------------+----------------------+--------------------------+-------------------------+-------------------------+----------------+--------------------------+-------------------+
3475|RUNNING |2024-10-10 11:15:49| | | 2| 44| | | 0| 1| 2| 1128| 1| 0| 0|REPEATABLE READ | 1| 1| | 0| 0| 0| 0| |
|
事务B
1
2
| begin;
insert into tb_order_usage (order_id, user_id) values (222, 222);
|
此时，事务B会被阻塞。
1
2
3
4
5
| mysql> SELECT * FROM information_schema.INNODB_TRX;
trx_id|trx_state|trx_started |trx_requested_lock_id |trx_wait_started |trx_weight|trx_mysql_thread_id|trx_query |trx_operation_state|trx_tables_in_use|trx_tables_locked|trx_lock_structs|trx_lock_memory_bytes|trx_rows_locked|trx_rows_modified|trx_concurrency_tickets|trx_isolation_level|trx_unique_checks|trx_foreign_key_checks|trx_last_foreign_key_error|trx_adaptive_hash_latched|trx_adaptive_hash_timeout|trx_is_read_only|trx_autocommit_non_locking|trx_schedule_weight|
------+---------+-------------------+---------------------------+-------------------+----------+-------------------+----------------------------------------------------------------+-------------------+-----------------+-----------------+----------------+---------------------+---------------+-----------------+-----------------------+-------------------+-----------------+----------------------+--------------------------+-------------------------+-------------------------+----------------+--------------------------+-------------------+
3479|LOCK WAIT|2024-10-10 13:53:17|5670670232:4:5:1:5595828760|2024-10-10 13:53:17| 3| 46|insert into tb_order_usage (order_id, user_id) values (222, 222)|inserting | 1| 1| 2| 1128| 1| 1| 0|REPEATABLE READ | 1| 1| | 0| 0| 0| 0| 1|
3475|RUNNING |2024-10-10 11:15:49| | | 2| 44| | | 0| 1| 2| 1128| 1| 0| 0|REPEATABLE READ | 1| 1| | 0| 0| 0| 0| |
|
事务B的状态是LOCK WAIT。
查看当前存在的锁等待
1
2
3
4
| mysql> SELECT * FROM sys.innodb_lock_waits;
wait_started |wait_age|wait_age_secs|locked_table |locked_table_schema|locked_table_name|locked_table_partition|locked_table_subpartition|locked_index |locked_type|waiting_trx_id|waiting_trx_started|waiting_trx_age|waiting_trx_rows_locked|waiting_trx_rows_modified|waiting_pid|waiting_query |waiting_lock_id |waiting_lock_mode |blocking_trx_id|blocking_pid|blocking_query|blocking_lock_id |blocking_lock_mode|blocking_trx_started|blocking_trx_age|blocking_trx_rows_locked|blocking_trx_rows_modified|sql_kill_blocking_query|sql_kill_blocking_connection|
-------------------+--------+-------------+-----------------------+-------------------+-----------------+----------------------+-------------------------+---------------------+-----------+--------------+-------------------+---------------+-----------------------+-------------------------+-----------+----------------------------------------------------------------+---------------------------+------------------+---------------+------------+--------------+---------------------------+------------------+--------------------+----------------+------------------------+--------------------------+-----------------------+----------------------------+
2024-10-10 13:53:17|00:00:15| 15|`test`.`tb_order_usage`|test |tb_order_usage | | |tb_order_usage_unique|RECORD | 3479|2024-10-10 13:53:17| 00:00:15| 1| 1| 46|insert into tb_order_usage (order_id, user_id) values (222, 222)|5670670232:4:5:1:5595828760|X,INSERT_INTENTION| 3475| 44| |5670669440:4:5:1:5595824152|X | 2024-10-10 11:15:49| 02:37:43| 1| 0|KILL QUERY 44 |KILL 44 |
|
查看当前的锁
1
2
3
4
5
6
7
| mysql> SELECT * FROM performance_schema.data_locks;
ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME |PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME |OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA |
------+---------------------------+---------------------+---------+--------+-------------+--------------+--------------+-----------------+---------------------+---------------------+---------+---------+-----------+----------------------+
INNODB|5670670232:1066:5593164120 | 3479| 101| 44|test |tb_order_usage| | | | 5593164120|TABLE |IX |GRANTED | |
INNODB|5670670232:4:4:1:5595829448| 3479| 101| 44|test |tb_order_usage| | |PRIMARY | 5595829448|RECORD |X |GRANTED |supremum pseudo-record|
INNODB|5670669440:1066:5593163096 | 3475| 99| 40|test |tb_order_usage| | | | 5593163096|TABLE |IX |GRANTED | |
INNODB|5670669440:4:5:1:5595824152| 3475| 99| 40|test |tb_order_usage| | |tb_order_usage_unique| 5595824152|RECORD |X |GRANTED |supremum pseudo-record|
|
关注下事务 3479 的锁，有两个 表级的是 IX 意向排他锁，在某些行为上是排他的，行级的是排他锁主要关注一下 LOCK_DATA 类型是 supremum pseudo-record ，它是一个虚拟的记录，位于索引的末尾，用于表示比索引中所有实际记录都大的值。
所以，当我们执行 select... where xxx for update 锁无数据的时候，会产生supremum pseudo-record的行级锁，导致其他事务无法插入新的数据。
supremum pseudo-record
定义：
InnoDB 在每个 B+ 树索引的叶子节点末尾插入的一个特殊伪记录，代表索引的最高可能键值，用于简化边界条件处理和锁机制实现。
作用：
- 简化边界条件：统一处理索引的上下界，避免在每个操作中单独处理。
- 支持锁机制：通过定义索引的上界，确保锁定范围的准确性，防止幻读和并发插入冲突。
- 优化查询：提供明确的结束点，提升范围查询的效率。
对比
如果是查询的存在的记录：
1
2
| begin;
select * from tb_order_usage where order_id = 111 for update;
|
查看当前的锁
1
2
3
4
5
6
| mysql> SELECT * FROM performance_schema.data_locks;
ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME |PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME |OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
------+---------------------------+---------------------+---------+--------+-------------+--------------+--------------+-----------------+---------------------+---------------------+---------+-------------+-----------+---------+
INNODB|5670669440:1066:5593163096 | 3480| 99| 44|test |tb_order_usage| | | | 5593163096|TABLE |IX |GRANTED | |
INNODB|5670669440:4:5:2:5595824152| 3480| 99| 44|test |tb_order_usage| | |tb_order_usage_unique| 5595824152|RECORD |X,REC_NOT_GAP|GRANTED |111, 5 |
INNODB|5670669440:4:4:2:5595824496| 3480| 99| 44|test |tb_order_usage| | |PRIMARY | 5595824496|RECORD |X,REC_NOT_GAP|GRANTED |5 |
|
可以看出锁定的记录都是确定的，事务B执行
1
2
| mysql> insert into tb_order_usage (order_id, user_id) values (222, 222);
Query OK, 1 row affected (0.01 sec)
|
参考
innodb-locking