MySQL锁

MySQL 锁
MySQL 并发更新数据的加锁处理
- SELECT 显式加锁
- 使用乐观锁

# MySQL 锁

# 锁的类型

InnoDB实现了两种标准的行级锁：
1. 共享锁（S Lock）
  - 语法：SELECT * FROM table LOCK IN SHARE MODE。
2. 排他锁（X Lock）
  - 语法：SELECT * FROM table FOR UPDATE。
如果一个事务T1已经获取了行r的共享锁，另一个事务T2可以立即获得行r的共享锁。因为读取不会改变行的数据，所以多个事务可以同时获取共享锁，这称为锁兼容。
但是，如果有其他事务T3想获得行r的排他锁，则必须等待事务T1和T2释放行r上的共享锁，这称为锁不兼容。

	X	S
X	不兼容	不兼容
S	不兼容	兼容

普通的 SELECT 语句默认不加锁，而插入（INSERT）、更新（UPDATE）、删除（DELETE）操作默认加排他锁。

# 锁的粒度

锁级别	说明
表级锁	开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁	开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
页面锁	开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

在以下情况下，表锁优先于页级或行级锁：
- 表的大部分行用于读取。
- 对严格的关键字进行读取和更新：
  - UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
  - DELETE FROM tbl_name WHERE unique_key_col=key_value;
- SELECT 结合并行的 INSERT 语句，并且只有很少的 UPDATE 或 DELETE 语句。
- 在整个表上有许多扫描或 GROUP BY 操作，没有任何写操作。

# 死锁

MySQL 提供了有效的死锁检测策略，当检测到死锁后，InnoDB 会将持有最少行级排他锁的事务回滚，以打破死锁。

# 乐观锁与悲观锁

# 乐观锁

通过数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。
- 为数据增加一个版本标识，通常通过为数据库表增加一个数字类型的 version 字段来实现。
- 当读取数据时，将 version 字段的值一同读出，每次更新数据时，对 version 值加 1。
- 当提交更新时，判断数据库表对应记录的当前版本信息与第一次取出的 version 值是否相等，如果相等，则予以更新，否则认为是过期数据。

-- 读取数据和版本号
SELECT id, value, version FROM TABLE WHERE id = <id>;

-- 按特定版本号更新
UPDATE TABLE
SET value = 2, version = version + 1
WHERE id = <id> AND version = <version>;

1
2
3
4
5
6
7

# 悲观锁

即上面提到的共享锁和排他锁。

# 锁升级

锁升级（Lock Escalation）是指将当前锁的粒度加大，锁粒度：行锁 < 页锁 < 表锁。
InnoDB 的锁升级机制：
- 由单独的 SQL 语句在一个对象上持有的锁的数量超过阈值时触发，默认阈值为 5000。如果是不同对象，则不会发生锁升级。
- 锁资源占用的内存超过了活动内存的 40% 时发生锁升级。
InnoDB 通过每个事务访问的每个页对锁进行管理，采用位图方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

# MySQL 并发更新数据的加锁处理

MySQL 支持给数据行加锁（InnoDB），并且在 UPDATE/DELETE 等操作时会自动加上排他锁。
但并非只要有 UPDATE 关键字就会全程加锁，例如：

UPDATE table1 SET num = num + 1 WHERE id = 1;

这条语句实际上相当于两条 SQL 语句（伪代码）：

a = SELECT * FROM table1 WHERE id = 1;
UPDATE table1 SET num = a.num + 1 WHERE id = 1;

1
2

其中执行 SELECT 语句时没有加锁，只有在执行 UPDATE 时才加锁，这会导致并发操作时的数据更新不一致。
解决方法有两种：
- 通过事务显式对 SELECT 加锁。
- 使用乐观锁机制。

# SELECT 显式加锁

对 SELECT 进行加锁的方式有两种：

SELECT ... LOCK IN SHARE MODE;  -- 共享锁，其它事务可读，不可更新
SELECT ... FOR UPDATE;          -- 排它锁，其它事务不可读写

1
2

对于上述场景，必须使用排它锁。
上述两种语句只有在事务中才能生效，否则不会生效。在 MySQL 命令行中使用事务的方式如下：

SET AUTOCOMMIT = 0;
BEGIN WORK;
    a = SELECT num FROM table1 WHERE id = 2 FOR UPDATE;
    UPDATE table1 SET num = a.num + 1 WHERE id = 2;
COMMIT WORK;

1
2
3
4
5

这样在更新数据时使用事务操作，可以在并发情况下通过锁将并发改为顺序执行。

# 使用乐观锁

乐观锁是锁实现的一种机制，它假定所有需要修改的数据都不会冲突。
在更新之前不加锁，而是查询数据行的版本号（版本号是自定义字段，需在业务表上增加，每次更新时自增或更新）。
在具体更新数据时，更新条件中添加版本号信息。当版本号未变化时，说明数据行未被更新过，满足更新条件，因此更新成功。
当版本号变化时，条件不满足，需要重新查询数据行，再次使用新的版本号进行更新。

原则上，两种方式都可支持，具体使用哪一种取决于实际业务场景，对哪种支持更好，并且对性能影响最小。

上次更新: 4/24/2025

← MySQL8双1设置保障安全 innodb cluster安装→