MySQL：深入总结锁机制

写在前面

在 MySQL 数据库中，锁机制是保障并发控制和数据一致性的关键。合理运用锁机制，能有效避免数据竞争，提升数据库性能。接下来，我们就深入了解 MySQL 中的各类锁。

博主总结（注：针对总结的详解补充在后面）

• 锁机制
  • mysql里有哪些锁？
    • 全局锁：通过flush tables with read lock 语句会将整个数据库就处于只读状态了，这时其他线程执行以下操作，增删改或者表结构修改都会阻塞。全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
    • 表级锁

      

      • read、write实现读写锁也就是共享锁、排他锁。
      • ①表锁：通过lock tables 语句可以对表加表锁，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。对于 InnoDB 引擎，无索引的 UPDATE/DELETE 可能会导致锁升级为表锁。
      • ②元数据锁：当我们对数据库表进行操作时，会自动给这个表加上 MDL锁，对一张表进行 CRUD 操作时，加的是 MDL 读锁；对一张表做结构变更操作的时候，加的是 MDL 写锁；MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
      • ③意向锁：意向锁是一种表级锁，表示事务打算对表中的某些行数据加锁，但不会直接锁定数据行本身。当执行 SELECT ... LOCK IN SHARE MODE 时，会自动加意向共享锁；当执行 SELECT ... FOR UPDATE 时，会自动加意向排他锁。意向锁之间互相兼容，也不会与行锁冲突。
        • 作用：意向锁的目的是为了快速判断表里是否有记录被加锁。在没有意向锁的情况下，当事务 A 持有某表的行锁时，如果事务 B 想添加表锁，InnoDB 必须检查表中每一行数据是否被加锁，这种全表扫描的方式效率极低。有了意向锁之后，事务在加行锁前，先在表上加对应的意向锁；其他事务加表锁时，只需检查表上的意向锁，无需逐行检查。

          

    • 行级锁：InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。行锁是 InnoDB 存储引擎中最细粒度的锁，它锁定表中的一行记录，允许其他事务访问表中的其他行。底层是通过给索引加锁实现的，这就意味着只有通过索引条件检索数据时，InnoDB 才能使用行级锁，否则会退化为表锁。

      

      • 通过 SELECT ... FOR UPDATE 可以加排他锁。通过 SELECT ...LOCK IN SHARE MODE 可以加共享锁。
      • ①记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的，满足读写互斥，写写互斥
      • ②间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
      • ③Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
  • MySQL的乐观锁和悲观锁了解吗？
    • MySQL 中的行锁和表锁都是悲观锁。
    • 悲观锁是一种"先上锁再操作"的保守策略，它假设数据被外界访问时必然会产生冲突，因此在数据处理过程中全程加锁，保证同一时间只有一个线程可以访问数据。

      

    • 乐观锁会假设并发操作不会总发生冲突，属于小概率事件，因此不会在读取数据时加锁，而是在提交更新时才检查数据是否被其他事务修改过。乐观锁并不是 MySQL 内置的锁机制，而是通过程序逻辑实现的，常见的实现方式有版本号机制和时间戳机制。通过在表中增加 version 字段或者 timestamp 字段来实现。

      

  • 遇到过MySQL死锁问题吗，你是如何解决的？
    • 遇到过。MySQL 的死锁是由于多个事务持有资源并相互等待引起的。
    • 我通过 SHOW ENGINE INNODB STATUS 查看死锁信息，定位到是加锁顺序不一致导致的，最后通过调整加锁顺序解决了这个问题。
  • MySQL两个线程的update语句同时处理一条数据，会不会有阻塞？
    • 会，行级锁锁住了。
    • 具体例子来说，当事务A对id = 1这行记录执行更新操作时，会在主键id为1的记录上添加X类型（排他锁）的记录锁。此时，如果事务B也尝试对id = 1的记录进行更新，由于发现该记录已经被加锁，事务B就会进入阻塞状态，直到事务A提交或回滚，释放锁之后，事务B才能继续执行。
  • 两条update语句处理一张表的不同的主键范围的记录，一个<10，一个>15，会不会遇到阻塞？底层是为什么的？
    • 不会，临键锁没有冲突。因为这两个范围没有重叠部分，所以不存在锁冲突。
    • 具体例子来说，假设存在两条UPDATE语句，分别处理主键范围不同的记录：
      • 第一条UPDATE语句的条件是id < 10，它锁定的范围是(-∞, 10)，即小于10的所有记录及其间隙。
      • 第二条UPDATE语句的条件是id > 15，它锁定的范围是(15, +∞)，即大于15的所有记录及其间隙。
  • 如果2个范围不是主键或索引？还会阻塞吗？
    • 会，因为第一个查询触发了全表查询，相当于加了表锁。这时第二个查询就会阻塞。
    • 具体解释下，因为如果 update 没有用到索引，在扫描过程中会对索引加锁，所以全表扫描的场景下，所有记录都会被加锁，也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁，相当于锁住了全表。

———————————————————————————————————————————

———————————————————————————————————————————

实战：解决 MySQL 死锁问题

遇到死锁时，可通过SHOW ENGINE INNODB STATUS查看详细信息。曾遇到因加锁顺序不一致导致的死锁，通过调整加锁顺序成功解决。

常见问题解答

• 两个线程update同一数据：会阻塞，因行级排他锁锁定该行记录，需等前一事务提交或回滚释放锁。

• 两条update语句处理不同主键范围记录：若无重叠，临键锁无冲突，不会阻塞。

• 操作范围无索引：会触发全表扫描，相当于添加表锁，导致后续操作阻塞。

掌握 MySQL 锁机制，有助于在开发和运维中合理使用锁，提升数据库性能与稳定性。后续可进一步探索不同业务场景下的锁优化策略，欢迎大家在评论区分享经验！

比如在项目中，两个事务分别更新两张表，但是更新顺序不一致。

-- 创建表/插入数据
CREATE TABLE account (
    id INT AUTO_INCREMENT PRIMARY KEY,
    balance INT NOT NULL
);

INSERT INTO account (balance) VALUES (100), (200);

-- 事务 1
START TRANSACTION;
-- 锁住 id=1 的行
UPDATE account SET balance = balance - 10 WHERE id = 1;

-- 等待锁住 id=2 的行（事务 2 已锁住）
UPDATE account SET balance = balance + 10 WHERE id = 2;

-- 事务 2
START TRANSACTION;
-- 锁住 id=2 的行
UPDATE account SET balance = balance - 10 WHERE id = 2;

-- 等待锁住 id=1 的行（事务 1 已锁住）
UPDATE account SET balance = balance + 10 WHERE id = 1;

访问相同的资源，但顺序不同，就会导致死锁。

解决办法也很简单，先使用 SHOW ENGINE INNODB STATUS\G; 确认死锁的具体信息，然后调整资源的访问顺序。