标签：记录 lock 数据库行锁临键索引 InnoDB id select

数据库系列：MySQL慢查询分析和性能优化
 数据库系列：MySQL索引优化总结（综合版）
数据库系列：高并发下的数据字段变更
 数据库系列：覆盖索引和规避回表
 数据库系列：数据库高可用及无损扩容
 数据库系列：使用高区分度索引列提升性能
 数据库系列：前缀索引和索引长度的取舍
 数据库系列：MySQL引擎MyISAM和InnoDB的比较
 数据库系列：InnoDB下实现高并发控制
 数据库系列：事务的4种隔离级别
 数据库系列：RR和RC下，快照读的区别
 数据库系列：MySQL InnoDB锁机制介绍
 数据库系列：MySQL不同操作分别用什么锁？
数据库系列：业内主流MySQL数据中间件梳理
 数据库系列：大厂使用数据库中间件解决什么问题？
数据库系列：索引失效场景总结

1 背景

我们在之前的一篇文章《数据库系列：MySQL InnoDB锁机制介绍》中介绍过InnodB引擎下几种常见锁的机制和原理。而在实际的select...for update操作中，锁影响的范围还是有区别的，下面就详细讨论下select 操作中的加锁规则。

2 回顾常见的锁类型

★InnoDB默认的事务隔离级别为可重复读(Repeated Read, RR)，我们当下的所有介绍都是基于这个隔离级别为前提的。

记录锁(Record Locks)：锁定单一行记录，InnoDB 使用记录锁来实现行级锁，这样允许多个事务并发访问不同的行。
间隙锁(Gap Locks)：InnoDB 的特性，用于锁定一个范围，但不包括实际的记录。这主要用于防止幻读（Phantom Reads）。
临键锁(Next-Key Locks)：InnoDB 存储引擎的一种锁定机制，在执行查询语句时，根据查询条件所锁定的一个范围。这个范围中包含有间隙锁和记录锁。它的设计目的是为了解决幻读（Phantom Reads）。

2.1 记录锁(Record Locks)

记录锁一般在使用主键或者唯一索引进行查找时体现

记录锁，它封锁索引记录，例如：

select * from table where id=5 for update;

它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

需要说明的是：

select * from table where id=5;

则是快照读(SnapShot Read)，它并不加锁，快照读可以参考作者这篇文章：数据库系列：RR和RC下，快照读的区别

2.2 间隙锁(Gap Locks)

间隙锁通常在不使用唯一索引进行范围查找时出现

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
延续上面的那个例子继续演示：

# 表结构
users (Id PK, Name , Company);

# 表中包含四条记录
5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Elison, Oracle

执行SQL语句如下：

select * from users
    where id between 7 and 13 
    for update;

-- 假设我们要删除id在7到13之间的所有用户记录（不包括id=7和id=13）  
DELETE FROM users WHERE id BETWEEN 7 AND 13;

这样的话，会封锁数据的区间，以防止其他事务插入id=8的记录。
假设没有间隙锁，则可能够插入成功，而之前的select事务，会发现检索的结果集莫名多了一条记录，即幻影数据。
所以间隙锁主要目的用于防止幻读（Phantom Reads），避免其他事务在间隔中插入数据，导致『不可重复读』。

如果把事务的隔离级别降级为读提交(Read Committed, RC)，对，就是互联网最常用的隔离级别，间隙锁则会自动失效。

2.3 临键锁(Next-Key Locks)

临键锁（Next-Key Locks）是数据库管理系统InnoDB中的一种重要锁定机制。这种锁是查询时根据查询条件锁定的一个范围，这个范围包括间隙锁和记录锁，左开右闭，即不锁住左边界，但会锁住右边界。临键锁的主要设计目的是为了解决所谓的“幻读”问题。

# 左开右闭 示例
(-infinity, 1]
(1, 7]
(7, +infinity)

依然沿用上面的例子，InnoDB引擎，RR隔离级别：

-- 创建一个示例表  
CREATE TABLE users (  
    Id INT PRIMARY KEY,  
    Name VARCHAR(255) NOT NULL,  
    Company VARCHAR(255) NOT NULL,  
);  
  
-- 插入一些示例数据  
INSERT INTO users (id, name, company) VALUES (1, 'Alice', 'ali');
INSERT INTO users (id, name, company) VALUES (2, 'Brand', 'tencent');
INSERT INTO users (id, name, company) VALUES (3, 'Charlie', 'baidu');
  
-- 开始一个事务，并使用临键锁查询数据  
START TRANSACTION;  
SELECT * FROM users WHERE id > 1 FOR UPDATE;  
  
-- 在另一个事务中尝试插入新数据，将会被阻塞直到第一个事务释放锁 
START TRANSACTION;  
INSERT INTO users (id, name, age) VALUES (4, 'David', 30);  
COMMIT;  
  
-- 第一个事务提交后，第二个事务可以继续执行插入操作  
COMMIT;

临键锁的主要目的，也是为了避免幻读(Phantom Read)，在事务隔离级别为可重复读的情况下，InnoDB存储引擎默认使用临键锁。这种锁提供了一种有效的机制来保证在并发环境中数据的完整性和一致性。
如果把事务的隔离级别降级为RC，临键锁则也会失效。

3 不同select操作的加锁规则

3.0 前置条件

# 表结构(姓名、公司、工号)
userinfo (Id PK, username, company, usercode);

# 表中包含四条记录
5, Gates, Microsoft, 24
7, Bezos, Amazon,35
11, Jobs, Apple,37
14, Elison, Oracle,38

3.1 主键检索

1. 记录存在的情况

# 5是存在的记录，行锁
mysql> select * from userinfo where id=5 for update;

mysql> update userinfo set username = "Brand" where id = 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# X 排他锁
# RECORD 记录锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X             | RECORD   |
+---------------+-------------+

2. 记录不存在的情况

# 6是不存在的记录，间隙锁，锁住的区间为（5,7）,对应上面的前置条件
mysql> select * from userinfo where id = 6 for update;

mysql>  insert into user values(6, 'Brand', 'Ali',100);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactio

# X 排他锁 + Gap 间隙锁
# RECORD 记录锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

3.2 唯一索引检索

与主键检索结果一致，因为这两种都是可以唯一确定索引值和区间范围的。

3.3 普通索引检索

1. 记录存在的情况

# 24是存在的记录，更新行锁，插入间隙锁。24要算在内，锁住的区间为 usercode的[24,35）,对应上面的前置条件
mysql> select * from userinfo where usercode = 24 for update;

mysql> insert into user values(6, 'Brand', 'Ali',25);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# X 排他锁
# RECORD 记录锁  + Gap 间隙锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

2. 记录不存在的情况

# 25是不存在的记录，间隙锁，锁住的区间为 usercode的(24,35）,对应上面的前置条件
mysql> select * from userinfo where id = 25 for update;

mysql>  insert into user values(6, 'Brand', 'Ali',26);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactio

# X 排他锁 + Gap 间隙锁
# RECORD 记录锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

3.4 索引的范围检索

索引包括主键(默认)、唯一索引和其他普通索引

mysql> select * from userinfo where id > 4 for update;

mysql> insert into user values(66, 'Brand', 'Ali',25);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# X 排他锁 + Gap 间隙锁
# RECORD 记录锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

可以对 id <= 4 的数据进行更新(如果有的话)，而且他的数据都会被锁住，锁住的Id字段的范围是为：

(5, 7], (7, 11], (11,14], (14, +infinity)

3.5 普通检索（无索引）

表锁，因为需要扫描整张表。扫描期间所有的操作都不能被获取或变更。

4 总结

事务隔离级别为可重复读(Repeated Read, RR)
以主键或唯一索引作为查询条件，有存在值（记录）时是行锁，不存在值时触发间隙锁。
普通索引作为查询条件，恒定间隙锁。
索引作为查询条件，并以范围取值时，产生间隙锁。
无索引时的普通检索，产生表锁。

标签：记录,lock,数据库,行锁,临键,索引,InnoDB,id,select
From： https://www.cnblogs.com/wzh2010/p/18030866

InnoDB常用锁总结（行锁、间隙锁、临键锁、表锁）

相关文章