MySQL锁机制

本文最后更新于：3 年前

引言

在数据库高并发应用中，对数据进行安全并行读写的关键技术之一便是锁。MySQL 在不同存储引擎与不同层面（全局、表、行、元数据等）都提供了多种锁机制来保障数据一致性与并发性能。其中，InnoDB 通过行级锁与多版本并发控制（MVCC）实现了高并发与事务隔离的平衡，而 MyISAM 则依赖简单的表级锁适用于读多写少场景。本文将从全局锁、表级锁、行级锁等多方面系统介绍 MySQL 的锁体系，探讨从锁的分类、适用场景到死锁处理、锁优化等关键话题，帮助开发者在设计与运维中能更好地掌控数据库的并发行为。

分类

全局锁（Global Lock）

定义与作用

全局锁是作用于整个 MySQL 实例的锁，通常用于对所有数据库或整个实例进行操作时，防止数据发生变化。最典型的使用场景包括数据备份和主从复制的一致性维护。

特点

  • 锁定范围大，覆盖整个实例所有的数据库和表。
  • 使用时需要特别注意锁的释放，否则会导致数据库不可写，影响正常业务。

适用场景

  • 数据备份：在进行物理备份或冷备份时，通过全局锁保证数据一致性。
  • 主从复制配置：在配置复制时，确保主库数据在复制过程中不会发生写入操作，便于建立稳定的复制关系。

使用示例

1
FLUSH TABLES WITH READ LOCK;

该命令会锁住所有打开的表，期间所有写操作（INSERT、UPDATE、DELETE 等）都会被阻塞，直至锁释放。

注意事项

  • 锁定范围广：全局锁会影响整个实例，因此在生产环境中使用时必须非常谨慎，避免长时间持锁导致业务不可写。
  • 释放锁：全局锁一般在当前连接断开或显式执行解锁操作时释放。开发者应确保在备份或配置工作完成后及时释放锁，否则可能影响系统性能。
  • 业务中断风险：由于全局锁会阻塞所有写请求，建议在业务低峰期或维护窗口内使用。

表级锁（Table Lock）

定义与作用

表级锁是对整个数据表进行加锁，常见于 MyISAM 和 Memory 存储引擎，也可能出现在 InnoDB 的某些场景中。

表级锁可以明确分为两种模式：读锁、写锁。

适用场景

  • 批量操作：当需要对表进行一系列操作并保证数据在整个操作期间不被其他连接修改时，可以使用表级锁。
  • MyISAM 存储引擎：由于 MyISAM 只支持表级锁，因此在该引擎下所有的并发控制都依赖于这种锁机制。

使用示例

  1. 使用 LOCK TABLES 语句进行锁定，注意需要指明锁的种类

    1
    2
    3
    4
    5
    # 对指定表加读锁，允许其他会话读取，但禁止写入。
    LOCK TABLES table_name READ;

    # 对指定表加写锁，其他会话既不能读取也不能写入。
    LOCK TABLES table_name WRITE;

  2. 使用完毕释放锁

    1
    UNLOCK TABLES;

注意事项

  • 锁的粒度：由于表级锁锁定的是整张表，可能会导致不必要的阻塞，特别是在高并发场景下。使用时应权衡并发性与一致性要求。
  • 事务与锁无关：表级锁通常不在事务中自动释放（除非使用事务性引擎），开发者需要手动调用 UNLOCK TABLES 进行释放。
  • 避免死锁：如果多个连接需要锁定多个表，必须严格按照相同的顺序加锁，以避免死锁风险。

元数据锁（Metadata Lock, MDL）

定义与作用

元数据锁并非由用户直接控制，而是由 MySQL 内部在执行 DDL（如 ALTER、DROP、RENAME 等）时自动加上的锁。其主要目的是保护表的结构和定义，防止在执行 DDL 操作时，DML 操作对表结构造成冲突或不一致。

特点

  • MDL 是在每次访问表时都会隐式加上的锁，既存在于读操作，也存在于写操作中。
  • 与显式的表级锁不同，MDL 的管理更透明，通常不需要开发人员手动干预。

注意

在高并发环境下执行 DDL 可能产生长时间阻塞，或使读写操作被阻塞，就是 MDL 的影响，所以在业务繁忙时期一律禁止执行 DDL 操作。

行级锁（Row Lock）

行级锁主要由 InnoDB 存储引擎实现，其粒度较细，能够提高并发处理能力。行级锁可以进一步细分为多种类型，根据不同的场景选择合适的锁定方式。

共享锁（Shared Lock）

定义与作用

  • 允许其他事务同时加共享锁读取相同的数据行，但不允许其他事务对这些数据行加排他锁进行修改。
  • 保证在当前事务期间，数据不会被其他事务修改，从而保持读取数据的一致性。

适用场景

  • 当需要读取数据并确保在当前事务内数据不被更新时使用共享锁。

  • 适用于需要多事务并发读取但不希望数据发生变动的场景，例如统计、报表等只读操作。

使用示例

使用 LOCK IN SHARE MODE 表示对查询结果集中的每一行加共享锁。

1
2
3
4
START TRANSACTION;
SELECT * FROM your_table WHERE condition LOCK IN SHARE MODE;
-- 后续可以进行其他操作
COMMIT;

注意事项

  • 锁持有时间：共享锁会一直持有到事务结束（提交或回滚），因此应尽快完成相关操作，避免长时间持锁导致并发性能下降。
  • 并发影响：多个事务可以同时获取共享锁，但如果有事务试图对相同数据加排他锁，则会被阻塞，直到所有共享锁释放。
  • 与事务隔离级别的关系：在 REPEATABLE READ 或 SERIALIZABLE 隔离级别下，MySQL 可能会自动加锁来防止幻读，显式加共享锁时应注意与自动加锁行为的交互。
  • 使用索引：建议在查询条件中使用索引，以避免锁定过多无关数据行，减少锁竞争和可能的性能问题。

排他锁（Exclusive Lock）

定义与作用

  • 排他锁允许事务对数据进行修改，同时禁止其他事务对同一数据进行读取或修改。

适用场景

  • 数据修改操作：排他锁主要用于更新、删除或插入操作前，确保当前事务对数据拥有完全的控制权，防止其他事务并发修改相同数据，造成数据不一致。
  • 预防并发问题：在某些需要强制序列化操作的业务场景下，显式加排他锁可以避免因并发修改引起的竞争和死锁问题（前提是锁粒度和锁顺序设计合理）。

使用示例

使用 FOR UPDATE 表示对查询结果中的每一行加排他锁。

1
2
3
4
START TRANSACTION;
SELECT * FROM your_table WHERE condition FOR UPDATE;
-- 进行更新或其他修改操作
COMMIT;

注意事项

  • 锁的范围与精确性：使用 FOR UPDATE 时，尽可能通过精确的查询条件或利用索引，避免锁定不必要的数据行，减少潜在的锁冲突和死锁风险。如果查询条件不够精准，可能会锁定超出预期范围的数据，从而影响其他事务的执行。
  • 锁持有时间：同共享锁一样，排他锁会一直持有到事务结束。因此，建议在获取锁后尽快完成修改操作，并及时提交或回滚事务，避免长时间持锁阻塞其他事务。
  • 死锁风险：排他锁的使用不当容易导致死锁。设计事务时应确保所有事务遵循相同的加锁顺序，减少交叉锁定的情况。在开发过程中，可通过 SHOW ENGINE INNODB STATUS 来监控和诊断死锁问题，并根据日志信息进行相应优化。
  • 与自动加锁机制的关系：在某些情况下，InnoDB 会自动为数据修改操作加排他锁。开发者在显式使用 FOR UPDATE 时，应了解数据库自动加锁的行为，避免重复锁定或逻辑混淆。

意向锁（Intention Lock）

定义

  • 意向锁是 InnoDB 为了兼顾表级锁和行级锁之间的协调而引入的锁机制。
  • 它并不直接锁定数据行，而是表示事务打算在这些行上加何种类型的锁。

作用

  • 通过意向锁，InnoDB 可以在对表进行锁定时快速判断是否存在行级锁，从而避免不必要的锁冲突。
  • 意向锁和表级锁兼容，帮助实现锁粒度的逐级检查。

分类

  • 意向共享锁（IS）： 表示事务准备在行级上加共享锁。
  • 意向排他锁（IX）： 表示事务准备在行级上加排他锁。

间隙锁（Gap Lock）

定义与作用

  • 间隙锁锁定的是索引值之间的 “间隙” 区域，而不是具体的记录。
  • 主要用于防止其他事务在已锁定的范围内插入新的数据，从而避免幻读现象。

使用场景

  • 在 REPEATABLE READ 隔离级别下，InnoDB 会自动使用间隙锁来防止幻读。
  • 常见于范围查询操作，例如 SELECT ... FOR UPDATE WHERE id BETWEEN ...。

临键锁（Next-Key Lock）

定义与作用

  • 临键锁是行锁与间隙锁的组合，既锁定了索引记录本身，也锁定了该记录前面的间隙。
  • 这种锁机制主要用于防止幻读，在范围查询中非常常见。

使用场景

  • 当查询条件涉及索引的连续范围时，InnoDB 会采用临键锁来确保整个范围内的数据状态稳定。

记录锁

定义与作用

  • 记录锁是对数据行本身的锁定，不包括记录间的间隙。
  • 在某些场景下（如精确索引查找），InnoDB 只对命中的行加记录锁，而不必对间隙加锁，从而提高并发性能。

使用场景

  • 对于基于主键或唯一索引的精确查找，InnoDB 通常只加记录锁，而避免额外的间隙锁。

自增锁（AUTO-INC Lock）

定义与作用

  • 自增锁是 InnoDB 针对 AUTO_INCREMENT 列设计的特殊锁。
  • 它保证在并发插入数据时，生成的自增值不会冲突，并且保持有序性。

使用场景

  • 当一个表中存在 AUTO_INCREMENT 字段时，在执行插入操作时，InnoDB 会临时获取一个轻量级的自增锁，确保当前事务生成的自增值不会被其他事务干扰。

存储引擎与锁

MySQL 支持多种存储引擎，不同引擎的锁机制有较大差异，主要体现在锁粒度、锁类型和并发控制方式上。

InnoDB

主要特点

InnoDB 是 MySQL 默认的事务型存储引擎，支持 ACID 事务，采用行级锁和多版本并发控制（MVCC）来提高并发性能和数据一致性。

锁粒度与锁类型

  • 行级锁：

    • 共享锁（S Lock） 和 排他锁（X Lock）：用于控制单行数据的读取和修改。
    • 记录锁：锁定实际存在的数据行。
    • 间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止幻读。
    • 临键锁（Next-Key Lock）：行锁与间隙锁的组合，既锁定记录也锁定其前的间隙。

  • 意向锁（Intention Lock）：

    用于表级的标记，表明事务打算在行级上加共享锁或排他锁，便于存储引擎在处理表级操作时迅速判断行锁情况。

  • 自增锁：

    针对 AUTO_INCREMENT 列设计的轻量级锁，保证并发插入时生成的自增值顺序性和唯一性。

并发控制机制

  • 采用 MVCC，允许非锁定读取，通过保存数据的历史版本（Undo Log）来实现一致性读。
  • 依靠锁与版本控制共同维护事务隔离，同时在高并发场景下降低锁竞争。

MyISAM

主要特点

MyISAM 为非事务型存储引擎，主要特点是查询速度较快，但不支持事务和行级锁。

锁粒度与锁类型

  • 表级锁：
    • 对整个表加锁，所有的读写操作都必须等待当前操作释放锁。
    • 表级锁简单且开销小，但在并发写入场景下容易形成瓶颈。

并发控制机制

  • MyISAM 采用简单的表级锁机制来实现并发控制，在高并发写操作时容易导致阻塞或锁等待，因此适合读多写少的场景。

Memory（HEAP）

主要特点

Memory 存储引擎的数据全部保存在内存中，访问速度极快，但数据在服务器重启后会丢失。

锁粒度与锁类型

  • 表级锁：
    • 和 MyISAM 类似，Memory 引擎也采用表级锁机制，因此在并发操作时同样可能出现锁竞争问题。

适用场景

  • 主要适用于临时表、缓存或对数据一致性要求不高但需要极快响应的场景。

NDB Cluster

主要特点

NDB Cluster 是 MySQL 的分布式存储引擎，适用于高可用和大规模数据的分布式环境。

锁机制

  • NDB Cluster 实现了一套分布式锁机制，在数据节点上采用行级锁来保证数据一致性。
  • 由于数据分布在多个节点上，锁的协调涉及网络通信和全局一致性协议，因此在高并发和分布式环境中会有特殊的延迟和同步考虑。

其他存储引擎

Archive、CSV 等引擎：这些引擎通常不支持事务，锁机制较简单，或者依赖于操作系统层面的文件锁，不适合高并发场景。

事务与锁

隐式加锁

  • 在 InnoDB 中，DML 操作（如 SELECT ... FOR UPDATE、UPDATE、DELETE 等）会根据操作类型和事务隔离级别自动加锁，通常加行级锁（共享锁或排他锁），以确保数据在事务期间的一致性。
  • 在 REPEATABLE READ 隔离级别下，InnoDB 会对符合条件的数据行加上临键锁（next-key lock），既锁定了数据记录也锁定了相邻间隙，从而防止幻读现象。

锁的持有与事务边界

  • 在事务内加的锁会一直保持到事务提交或回滚时释放。因此，长事务可能导致锁长时间持有，从而影响其他事务的并发性能。
  • 自动释放：事务结束后，InnoDB 会自动释放事务持有的所有行锁、意向锁和自增锁，而全局锁和显式的表锁（如 LOCK TABLES）则需要开发者主动释放。

隔离级别对锁行为的影响

  • READ UNCOMMITTED：不加锁读取，但容易产生脏读，不常用于生产。
  • READ COMMITTED：不加锁读取，因此可能出现不可重复读。
  • REPEATABLE READ：默认隔离级别，保证同一事务内多次读取同一数据时结果一致，为此会加临键锁和间隙锁，防止幻读，但也容易引发死锁。
  • SERIALIZABLE：最严格的隔离级别，所有读取操作都被当作 SELECT ... FOR UPDATE 执行，会加全表锁或行级锁，性能开销较大。

事务并发与锁竞争

  • 在多事务并发执行过程中，不同事务对相同数据记录加锁会产生竞争。如果锁粒度较小（如行级锁），并发性能更高，但也更容易出现死锁；如果使用表级锁，则锁范围大，简单但可能造成大量阻塞。
  • 开发者需要设计好事务的执行顺序和 SQL 语句的查询条件，确保锁尽可能精细，从而减少锁冲突和死锁的风险。

MVCC 与锁的协同

  • InnoDB 的多版本并发控制（MVCC）允许在避免加锁读取，通过读取历史版本实现一致性视图，从而在大部分查询中不需要等待锁释放。
  • 但在执行涉及数据修改或显式锁定的操作时，仍需依赖行锁、意向锁等机制来确保数据的并发安全。

死锁

原因

  • 锁竞争与加锁顺序不一致：当两个或多个事务以不同的顺序获取锁时，可能出现循环等待的情况。例如，事务 A 锁定了数据行 1 后请求数据行 2，而事务 B 锁定了数据行 2 后请求数据行 1，就会形成循环等待。
  • 锁类型与范围：使用行级锁时，如果查询条件不够精确，可能导致锁定更多的数据行（例如使用了间隙锁或临键锁），从而增加死锁概率。
  • 长事务或锁持有时间过长：如果事务执行时间过长，锁长期占用也容易与其他事务产生冲突。

检测

SHOW ENGINE INNODB STATUS

1
SHOW ENGINE INNODB STATUS\G
  • 用途：返回 InnoDB 引擎的详细状态信息，其中包含了最近一次死锁的详细信息，如参与死锁的事务、锁等待情况、锁资源等。
  • 使用场景：当应用中出现异常或事务超时时，可以用此命令查看死锁日志，帮助定位问题所在。

INFORMATION_SCHEMA

MySQL 提供了一些 INFORMATION_SCHEMA 中的表来监控锁的状态：

  • INNODB_LOCKS：显示当前被持有的锁信息。
  • INNODB_LOCK_WAITS：显示锁等待情况，指出哪些事务正在等待其他事务释放锁。
  • INNODB_TRX：显示当前正在执行的事务信息。

示例：

1
2
3
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

处理

  • InnoDB 内置死锁检测机制，当检测到死锁时，会自动选择一个事务作为牺牲者回滚，以打破循环等待。
  • 应用程序在捕获到死锁错误（错误码 1213，通常提示 Deadlock found when trying to get lock; try restarting transaction）后，应实现重试逻辑，即回滚当前事务后重新执行。

避免

  • 统一加锁顺序：确保在多个事务中访问相同的资源时，以相同的顺序加锁，降低循环等待的概率。

  • 尽量减少锁持有时间：尽快完成事务操作，避免在事务中执行不必要的操作或等待用户交互，从而缩短锁的持有时间。

  • 优化 SQL 语句和索引：使用精确的 WHERE 条件和适当的索引，减少扫描和锁定的行数，降低锁冲突。

  • 减少范围锁（如间隙锁）的影响：在设计查询时注意尽可能精确地定位数据，避免因范围过大而锁定不必要的记录或间隙。

  • 调整锁等待超时参数：可以通过设置系统变量 innodb_lock_wait_timeout 来控制事务等待锁的最长时间：

    1
    SET innodb_lock_wait_timeout = 50;

    虽然这不能完全避免死锁，但可以让死锁发生时及时释放等待，避免长时间阻塞。

  • 使用最新版本的 MySQL 特性：部分新版本 MySQL 支持诸如 NOWAIT 和 SKIP LOCKED 等语法（主要在 PostgreSQL 中较为常见），MySQL 在未来版本中也可能会进一步优化并发控制和锁机制。

索引失效导致锁表

原因

在 InnoDB 中，行锁是基于索引实现的，而如果查询条件无法正确利用索引，则会导致 InnoDB 进行全表扫描，从而锁住所有符合查询条件的行，这可能会导致：

  • 所有符合查询条件的行都被加锁
  • 新插入或更新的行可能受影响
  • 多个事务因等待锁释放而阻塞，类似表锁的效果

示例分析

在 users 表中 phone_number 字段是没有索引的，因此以下 SQL 会导致 InnoDB 执行全表扫描，从而对所有扫描到的行加锁：

1
2
BEGIN;
SELECT * FROM users WHERE phone_number = '123456789' FOR UPDATE;
  • phone_number 没有索引，MySQL 只能执行全表扫描。
  • FOR UPDATE 要求对所有符合条件的行加 排他锁，而由于是全表扫描，所有行都被锁住。
  • 这会导致其他事务对 users 表的任何写入（UPDATE、DELETE、INSERT）都可能被阻塞，行锁表现得像一个表锁。

避免

  • 确保查询使用索引：必要时使用 FORCE INDEX 强制使用特定索引，让 InnoDB 仅对匹配的行加锁，而不会锁住整个表，如：

    1
    SELECT * FROM users FORCE INDEX(idx_phone_number) WHERE phone_number = '123456789' FOR UPDATE;

  • 限制锁的范围：如果无法避免全表扫描，考虑减少锁定范围，如在 FOR UPDATE 后面加上 LIMIT 关键字只锁定特定行数，如：

    1
    SELECT * FROM users WHERE phone_number = '123456789' FOR UPDATE LIMIT 1;

  • 修改锁竞争策略：在 FOR UPDATE 后面使用 NOWAIT 立即返回、不等待锁释放，或使用 SKIP LOCKED 跳过已锁定的行，如

    1
    2
    3
    SELECT * FROM users WHERE phone_number = '123456789' FOR UPDATE NOWAIT;

    SELECT * FROM users WHERE phone_number = '123456789' FOR UPDATE SKIP LOCKED;

总结

MySQL 锁机制为数据库在并发环境下提供了多层次的保护，从全局锁到表级锁、行级锁及元数据锁，兼顾了数据安全与性能需求。在应用中，开发者应只有深入了解各种锁类型的特性与兼容关系、合理掌控锁粒度、理解 MySQL 事务与锁的联系、再配合完善的监控及调优方案，才能充分发挥 MySQL 在并发处理方面的高效与安全性，为业务的持续增长打下稳固的数据库基础。

MySQL
MySQL MySQL锁机制

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！