数据库

MySQL 锁机制深度解析

一、概述：锁的作用与重要性

锁是数据库系统中用于管理并发访问的关键机制，确保数据的一致性和完整性。当多个事务同时访问相同数据时，如果没有锁机制，可能导致数据不一致、脏读、不可重复读、幻读等问题。MySQL 提供了多种锁机制应对不同的并发场景。

二、锁的分类

2.1 按锁的粒度

  • 表级锁 (Table-Level Locks): 锁定整个表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。MyISAM 引擎使用表级锁。

  • 行级锁 (Row-Level Locks): 锁定当前操作的行。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB 引擎默认使用行级锁。

  • 页面锁 (Page-Level Locks): 锁定数据库中的一页 (通常包含多个行)。开销和并发性介于表锁和行锁之间；会发生死锁。

2.2 按锁的类别 (基于 InnoDB)

  • 共享锁 (Shared Locks, S 锁): 又称读锁。允许事务读取一行数据。多个事务可同时持有共享锁，互不阻塞。

    • 加锁方式：SELECT ... LOCK IN SHARE MODE;

  • 排他锁 (Exclusive Locks, X 锁): 又称写锁。允许事务删除或更新一行数据。一个事务持有排他锁后，其他事务不能再获取任何类型的锁 (包括共享锁和排他锁)。

    • 加锁方式：SELECT ... FOR UPDATE; UPDATE, DELETE, INSERT 语句会自动加 X 锁。

  • 意向锁 (Intention Locks): 表级锁，协调行锁和表锁的关系，支持多粒度锁并存。

    • 意向共享锁 (IS 锁): 事务打算给数据行加共享锁，必须先取得该表的 IS 锁。

    • 意向排他锁 (IX 锁): 事务打算给数据行加排他锁，必须先取得该表的 IX 锁。

  • 记录锁 (Record Locks): 单个行记录上的锁。总是锁住索引记录 (即使没有定义索引，InnoDB 也会创建隐式聚簇索引)。

  • 间隙锁 (Gap Locks): 锁定一个范围，但不包含记录本身。仅在 REPEATABLE READ 隔离级别下生效，防止幻读。

    • 例如：SELECT * FROM table WHERE id BETWEEN 10 AND 20 FOR UPDATE;

  • 临键锁 (Next-Key Locks): Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身。也在 REPEATABLE READ 下生效，解决幻读。InnoDB 默认的行锁算法。

    • 例如：索引包含值 10, 11, 13, 20。SELECT * FROM table WHERE id > 11 FOR UPDATE; 产生的 Next-Key Locks 会覆盖区间：(11,13], (13, 20], (20, +∞]

意向锁的兼容性：

  • IS 和 IX 锁之间是兼容的：多个事务可以同时对同一表持有 IS 或 IX 锁，不会互相阻塞。

  • IS/IX 与表级 S/X 锁不兼容：当有事务要加表级 S/X 锁（例如 LOCK TABLES ... READ 或 DDL 操作）时，才会与其他事务的 IS/IX 锁冲突

加锁场景和触发方式

1. 共享锁（S 锁）

  • 场景： 用于事务读取数据时保证一致性，允许多个事务并发读取同一数据，但禁止其他事务修改该数据（即阻止排他锁）。 示例：事务 A 和事务 B 同时通过 LOCK IN SHARE MODE 读取同一行数据，均可获取 S 锁，但无法修改数据。

  • 触发方式：

    • 手动触发：通过 SELECT ... LOCK IN SHARE MODE 显式加锁。

    • 自动触发：普通 SELECT 语句默认不加锁（通过 MVCC 实现快照读），仅在显式指定时生效。

2. 排他锁（X 锁）

  • 场景： 用于事务修改或删除数据时，确保数据独占性。一旦事务持有 X 锁，其他事务无法对该数据加任何锁（包括 S 锁和 X 锁）。 示例：事务 A 执行 UPDATE 操作时自动加 X 锁，事务 B 若尝试读取或修改同一数据会被阻塞。

  • 触发方式：

    • 自动触发：UPDATE、DELETE、INSERT 语句自动对涉及的行加 X 锁。

    • 手动触发：通过 SELECT ... FOR UPDATE 显式加锁。

3. 意向锁（IS/IX 锁）

  • 场景： 协调行锁与表锁的共存。事务在加行级 S/X 锁前，需先获取表的 IS/IX 锁，以快速判断表级操作（如 DDL）是否可行。 示例：事务 A 对某行加 S 锁前，自动获取表的 IS 锁；事务 B 若尝试加表级 X 锁，需等待 IS 锁释放。

  • 触发方式： 自动触发：InnoDB 自动处理，无需用户干预。例如，加行级 S/X 锁时自动附加表级 IS/IX 锁。

4. 记录锁（Record Lock）

  • 场景： 锁定单个行记录（通过索引实现），防止其他事务修改或删除该行。 示例：UPDATE table SET name='abc' WHERE id=1 会对 id=1 的索引项加记录锁。

  • 触发方式： 自动触发：所有行级锁（S/X 锁）均通过记录锁实现，由 InnoDB 自动加锁。

5. 间隙锁（Gap Lock）

  • 场景： 锁定索引记录之间的“间隙”，防止其他事务插入数据导致幻读。仅生效于 REPEATABLE READ 隔离级别。 示例：SELECT * FROM table WHERE id BETWEEN 10 AND 20 FOR UPDATE 会锁定 (10,20) 的区间，禁止插入 id=15 的新数据。

  • 触发方式： 自动触发：在 REPEATABLE READ 级别下，InnoDB 对范围查询自动加间隙锁。

6. 临键锁（Next-Key Lock）

  • 场景： 结合记录锁和间隙锁，锁定索引记录及其相邻区间（左开右闭），解决幻读问题。InnoDB 默认的行锁算法。 示例：若索引包含值 10, 11, 13，执行 SELECT * FROM table WHERE id > 11 FOR UPDATE 会锁定 (11,13], (13,+∞] 区间。

  • 触发方式： 自动触发：在 REPEATABLE READ 级别下，InnoDB 默认使用临键锁。

2.3 按加锁方式

  • 自动锁: 数据库系统自动管理。如 InnoDB 在 DML 操作时自动加排他锁。

  • 显式锁: 用户通过 SQL 语句显式请求。如 LOCK IN SHARE MODE, FOR UPDATE。

2.4 按锁的实现思想

  • 乐观锁 (Optimistic Locking): 假设数据一般不会冲突，只在提交更新时检查是否违反数据完整性。通常通过版本号或时间戳实现。

    • 实现： 表中增加版本号字段 (version) 或时间戳字段，更新时检查版本号是否与读取时一致。

    • 优点： 高并发、读多写少场景下性能好，无需加锁。

    • 缺点： 修改表结构，写多读少场景下更新失败概率高。

  • 悲观锁 (Pessimistic Locking): 假设数据经常冲突，每次读取都加锁。

    • 实现： 通过数据库提供的锁机制 (共享锁、排他锁) 实现。

    • 优点： 保证数据一致性，适用写多读少场景。

    • 缺点： 高并发下性能较低，频繁加锁和释放锁。

三、InnoDB 锁机制详解

InnoDB 使用行级锁，支持事务，提供更高并发性能和数据完整性。

3.1 锁与索引的关系

  • InnoDB 行锁基于索引实现。无索引，InnoDB 使用隐式聚簇索引加锁，导致锁范围扩大，降低并发。

  • 使用合适索引可减少锁范围，只锁定需要修改的行，减少锁冲突，提高并发。

  • 即使使用索引，如果查询无法使用索引 (如函数、类型转换)，仍可能锁表。

3.2 不同隔离级别下的锁行为

隔离级别锁行为
READ UNCOMMITTED (读未提交)不使用任何锁。
READ COMMITTED (读已提交)只使用记录锁 (Record Locks)。
REPEATABLE READ (可重复读)使用记录锁 (Record Locks) 和间隙锁 (Gap Locks) / 临键锁 (Next-Key Locks)。
SERIALIZABLE (可串行化)强制事务串行执行，所有 select 都会隐式加上 LOCK IN SHARE MODE。

3.3 锁兼容矩阵

共享锁(S)排他锁(X)意向共享(IS)意向排他(IX)
共享锁✓✗✓✗
排他锁✗✗✗✗
意向共享✓✗✓✓
意向排他✗✗✓✓
  • 意向锁之间是互相兼容的

3.4 元数据锁 (Metadata Locks, MDL)

  • 作用: 保护数据库元数据 (表结构等) 一致性。DDL 操作 (如 ALTER TABLE) 获取 MDL 写锁，阻塞其他事务对该表的操作。DML 操作持有 MDL 读锁，允许其他事务读，阻塞写 (DDL)。

  • 目的: 防止事务执行期间表结构被修改。

  • 注意: 长时间持有 MDL 锁可能阻塞其他操作，长事务和 DDL 操作要小心。

3.5 死锁检测与处理

  • 死锁检测: InnoDB 自动检测死锁。维护等待图 (wait-for graph)，记录事务等待关系。检测到循环等待，认为发生死锁。

  • 死锁处理: InnoDB 选择持有最少行级排他锁的事务回滚，打破死锁。

  • 死锁避免:

    • 尽量相同顺序访问表或行。

    • 减少事务持有锁的时间。

    • 使用较低隔离级别 (如果业务允许)。

    • 给经常加锁的字段加索引。

    • 避免大事务，拆分成小事务。

3.6 死锁日志分析

LATEST DETECTED DEADLOCK
------------------------
2023-08-08 10:20:21 0x7f1e2c168700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 3 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 4, OS thread handle 123145556811776, query id 2345 localhost root updating
UPDATE account SET balance=balance-100 WHERE user_id=1

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 89 page no 3 n bits 72 index PRIMARY of table `test`.`account`
 trx id 123456 lock_mode X locks rec but not gap

*** (3) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index PRIMARY of table `test`.`account`
 trx id 123456 lock_mode X locks rec but not gap waiting

3.7 锁升级机制

  1. 隐式锁转换

    1. DML 操作自动加行锁。

    2. DDL 操作自动升级为表锁。

  2. 显式锁控制

sql LOCK TABLES users WRITE; -- 显式表锁 UNLOCK TABLES;

四、电商场景锁优化案例

4.1 秒杀库存扣减

-- 优化前（存在超卖风险）
START TRANSACTION;
SELECT stock FROM products WHERE id=1001;
UPDATE products SET stock=stock-1 WHERE id=1001;
COMMIT;

-- 优化后（行锁+版本控制）
START TRANSACTION;
UPDATE products
SET stock=stock-1, version=version+1
WHERE id=1001 AND stock>0;  -- 自动加X锁
COMMIT;

4.2 订单号生成

-- 使用自增锁优化（innodb_autoinc_lock_mode=2）
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(32) NOT NULL
) ENGINE=InnoDB;

-- 批量插入时的锁优化
INSERT INTO orders(order_no) VALUES
('20230808001'),('20230808002'),('20230808003');

五、锁监控与诊断

5.1 实时监控

-- 查看锁等待关系
SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx_id,
  b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 查看当前持有锁
SELECT * FROM performance_schema.data_locks;

-- 查看死锁检测和超时
SHOW VARIABLES LIKE 'innodb_deadlock_detect';  -- ON（默认开启）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 50秒（默认）

5.2 性能优化参数

参数名默认值推荐值作用域优化场景
innodb_lock_wait_timeout501-30Global/Session高并发 OLTP 系统
innodb_autoinc_lock_mode12Global大批量插入场景
transaction_isolationREPEATABLE-READREAD-COMMITTEDGlobal/Session读多写少场景

六、锁模型扩展

6.1 分布式锁实现

  • 基于 Redis 的 Redlock 算法 (伪代码):
def acquire_lock(lock_name, timeout):
    start = time.time()
    while time.time() - start < timeout:
        if redis.set(lock_name, 'locked', nx=True, ex=5):  # nx=True: 仅当 key 不存在时设置；ex=5: 5 秒过期
            return True
        time.sleep(0.1)
    return False
  • 基于 MySQL 的 **GET_LOCK()**:
SELECT GET_LOCK('order_lock', 10);  -- 获取命名锁, 10秒超时
SELECT RELEASE_LOCK('order_lock');  -- 释放命名锁

6.2 锁粒度升级策略

并发压力推荐锁策略适用场景
低行级锁 + MVCC常规 OLTP
中乐观锁 (版本控制)库存扣减
高分区锁 (按用户 ID 分片)海量用户系统

七、总结

MySQL 的锁机制是保证数据一致性和并发控制的重要手段。理解不同类型的锁、锁的粒度、锁与索引的关系、以及不同隔离级别下的锁行为，对于开发高性能、高并发的数据库应用至关重要。 在实际应用中，需要根据具体的业务场景选择合适的锁策略（悲观锁、乐观锁、分布式锁等），监控锁的状态，并注意避免死锁。 建议在金融交易系统中采用行级锁+间隙锁保证数据强一致，在电商秒杀场景结合乐观锁与队列削峰。 定期使用 SHOW ENGINE INNODB STATUS 分析锁等待情况，及时优化慢查询和索引设计。