MYSQL的InnoDB引擎中的锁以及在sql语句中实现对锁的控制

最新推荐文章于 2026-06-20 16:48:12 发布

原创最新推荐文章于 2026-06-20 16:48:12 发布 · 696 阅读

18 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #sql #数据库

程序员工作日常专栏收录该内容

26 篇文章

订阅专栏

在MySQL的InnoDB存储引擎中，锁机制是实现事务隔离性和数据一致性的核心部分。以下是InnoDB的锁类型及其在SQL语句中的控制方法的详细说明：

一、InnoDB的锁类型

1. 按锁的粒度划分

表级锁（Table Locks）：
- 共享锁（Shared Locks, S）：允许事务读取表，阻止其他事务获取排他锁。
- 排他锁（Exclusive Locks, X）：允许事务修改表，阻止其他事务获取共享锁或排他锁。
- 意向锁（Intention Locks）：
  - 意向共享锁（IS）：事务打算对表中的某些行加共享锁。
  - 意向排他锁（IX）：事务打算对表中的某些行加排他锁。
  - 意向锁是表级锁，用于协调行锁和表锁的关系，避免冲突。
行级锁（Row Locks）：
- 记录锁（Record Locks）：锁定索引记录，仅锁住具体的数据行。
- 间隙锁（Gap Locks）：锁定索引记录之间的“间隙”，防止其他事务插入新记录（用于防止幻读）。
- 临键锁（Next-Key Locks）：记录锁 + 间隙锁的组合，锁定一个左开右闭区间（如 (10, 20]），是InnoDB默认的锁类型。
- 插入意图锁（Insert Intention Locks）：插入新记录时使用的锁，表示插入意向，与其他事务的间隙锁兼容。

2. 按锁的属性划分

共享锁（S锁）：允许事务读取数据，阻止其他事务修改数据。
排他锁（X锁）：允许事务修改数据，阻止其他事务读取或修改数据。

二、SQL语句中的锁控制

1. 行级锁的显式控制

InnoDB的行级锁通常由事务自动管理，但可以通过以下方式显式控制：

共享锁（SELECT … LOCK IN SHARE MODE）：

START TRANSACTION;
SELECT * FROM table_name WHERE id = 10 LOCK IN SHARE MODE;
-- 其他事务可以读取，但不能修改该行
COMMIT;

排他锁（SELECT … FOR UPDATE）：

START TRANSACTION;
SELECT * FROM table_name WHERE id = 10 FOR UPDATE;
-- 其他事务既不能读取也不能修改该行
COMMIT;

2. DML语句的隐式锁

UPDATE/DELETE：

START TRANSACTION;
UPDATE table_name SET column = value WHERE condition;
DELETE FROM table_name WHERE condition;
-- 自动对匹配的行加排他锁
COMMIT;

INSERT：

START TRANSACTION;
INSERT INTO table_name (columns) VALUES (values);
-- 自动对插入的行加排他锁，并可能触发间隙锁
COMMIT;

3. 用户级锁（GET_LOCK/RELEASE_LOCK）

用户级锁是会话级别的锁，适用于跨表或跨数据库的资源控制：

获取锁：

SELECT GET_LOCK('lock_name', timeout_seconds);
-- 成功返回1，超时返回0

释放锁：

SELECT RELEASE_LOCK('lock_name');
-- 成功返回1，未持有锁返回0

示例：

START TRANSACTION;
-- 获取名为'my_lock'的锁，最多等待10秒
SELECT GET_LOCK('my_lock', 10);
-- 执行操作
UPDATE my_table SET status = 'processing' WHERE id = 123;
-- 释放锁
SELECT RELEASE_LOCK('my_lock');
COMMIT;

4. 表级锁的显式控制

加锁：

LOCK TABLES table_name READ;  -- 加共享锁
LOCK TABLES table_name WRITE; -- 加排他锁

解锁：
```
UNLOCK TABLES;
```

注意：LOCK TABLES 是MySQL的表级锁，与InnoDB的行级锁不同，通常用于需要跨多个表的原子操作。

三、锁行为的影响因素

1. 隔离级别

读已提交（RC）：
- 只对当前读的行加锁（记录锁），不加间隙锁。
- 可能出现幻读。
可重复读（RR）：
- 默认使用临键锁（Next-Key Locks），防止幻读。
- 对范围查询加间隙锁。

2. 索引的存在

主键/唯一索引：
- 使用记录锁（Record Locks），不加间隙锁。
非唯一索引：
- 使用临键锁（Next-Key Locks），锁住索引范围。

3. 查询条件

精准匹配（=）：
- 仅对匹配的行加锁。
范围条件（>、<、LIKE）：
- 可能加间隙锁或临键锁，锁住范围内的所有行。

四、锁的监控与优化

1. 锁监控

使用 INFORMATION_SCHEMA 中的系统表查看锁状态：

INNODB_TRX：当前运行的事务。
INNODB_LOCKS：当前持有的锁。
INNODB_LOCK_WAITS：锁等待关系。

示例查询：

-- 查看当前事务和锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

2. 优化建议

减少锁冲突：
- 保持事务短小，避免长事务。
- 使用合适的索引，减少锁定的行数。
- 按固定顺序访问资源（避免循环等待）。
避免死锁：
- 使用 SHOW ENGINE INNODB STATUS 分析死锁。
- 合理设置 innodb_lock_wait_timeout（默认50秒）。
合理选择隔离级别：
- 在RR下使用临键锁防止幻读，但可能降低并发性。
- 在RC下减少锁开销，但需自行处理幻读问题。

五、实际场景示例

1. 组合一：主键 + RC

DELETE FROM t1 WHERE id = 10;

加锁：仅对主键 id=10 的记录加排他锁（X锁）。

2. 组合二：唯一索引 + RC

DELETE FROM t1 WHERE unique_col = 10;

加锁：
1. 对唯一索引 unique_col=10 的记录加X锁。
2. 回主键索引，对对应的聚簇索引记录加X锁。

3. 组合三：非唯一索引 + RC

DELETE FROM t1 WHERE non_unique_col = 10;

加锁：对非唯一索引 non_unique_col=10 的所有匹配记录加临键锁（Next-Key Locks），包括间隙锁。

六、总结

InnoDB的锁机制通过行级锁（记录锁、间隙锁、临键锁）和表级锁（共享锁、排他锁）实现高并发下的数据一致性。在SQL中，可以通过 SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、GET_LOCK 等语句显式控制锁，同时需结合索引设计、隔离级别和事务优化策略，避免锁冲突和死锁问题。理解锁的行为和场景是优化数据库性能的关键。