MySQL锁学习笔记

在数据库中，除了传统的计算资源（e.g. cpu、I/O等）的争用之外，数据也是许多用户共享的资源。

为了保证数据库事务的隔离性特性、解决并发场景下 多个事务可能出现的问题（脏读、不可重复读、幻读），就需要锁机制。 MySQL锁机制是确保并发操作下数据一致性和完整性的重要手段。

从锁的粒度上，MySQL锁可以分为全局锁、表级锁和行级锁。

全局锁

全局锁锁的是数据库实例，加锁后，整个数据库实例处于只读状态，后续的DML写、DDL、已更新操作的事务commit操作都会被阻塞。

应用场景有对进行全库的逻辑备份。

语法：

• 加全局锁 flush tables with read lock
• 释放全局锁 unlock tables

表级锁

表级锁锁的是整个数据表，阻塞其他客户端对该表的写操作、结构更改操作等。是针对非索引字段加的锁。

表锁

• 表级共享锁 Read Lock

  允许所有会话的读操作，阻塞所有会话的写操作。

  lock tables table_name read;
  unlock tables;
  

• 表级排他锁 Write Lock

  阻塞其他会话的所有读写操作。

  lock tables table_name write;
  unlock tables;
  

元数据锁

元数据锁 Metadata Lock，MDL。

元数据锁是隐式锁，加锁过程由系统自动控制，DML的目标是保证表结构操作和数据操作的一致性，防止对表查询修改过程中 其他线程对表结构修改。

对数据库表操作时，会自动给表加MDL：

• 查询操作自动给表加MDL共享读锁。
• 表更新操作 - MDL共享写锁。
• 显式加表锁 - shared_read_write。
• 变更表结构 - 排他锁。

意向锁

意向锁可以快速判断表里是否有行记录加锁及类型，作用是协调行级锁和表级锁的共存关系，解决表级锁和行级锁之间的冲突检测问题。

• 如果不使用表级锁，为了避免表锁和行锁的冲突，需要一行行检查行数据是否有加锁、加锁和表级锁是否兼容。
• 使用意向锁：意向锁会声明事务在表中某些行持有的锁类型，避免其他事务对表加不兼容的表级锁。

意向锁的类型：

• 意向共享锁（Intention Shared Lock, IS）：
  • 表示事务 准备在表中的某些行上加共享锁（S锁）（如 SELECT ... LOCK IN SHARE MODE）。
  • 允许其他事务对同一表加 IS 或 IX 锁，但会阻塞表级排他锁（X锁）的请求。
• 意向排他锁（Intention Exclusive Lock, IX）：
  • 表示事务 准备在表中的某些行上加排他锁（X锁）（如 SELECT ... FOR UPDATE 或写操作）。
  • 允许其他事务对同一表加 IS 锁，但会阻塞表级共享锁（S锁）和排他锁（X锁）的请求。

行级锁

行锁

单个行记录上的锁（S锁、X锁），行记录更新或者显式加锁时触发。

e.g. update tb set name='mark' where id=1, 会锁住id=1的索引，这时不能更新id=1的记录。

间隙锁

锁定一个范围，不包括记录本身。只存在于可重复读隔离级别，目的是解决可重复读隔离级别下幻读的现象。

e.g. select * from tb where id between 1 and 10 for update，锁住的是(1, 10)开区间内行记录，这时不能插入id = 5的新记录。

临键锁

临键锁是行锁和间隙锁的结合，锁住索引记录和索引记录之前的间隙。

临键锁的加锁规则：

• 唯一索引的等值查询：

  • 如果记录存在，退化为记录锁（例如 WHERE id=1，id是主键）。
  • 如果记录不存在，退化为间隙锁（例如 WHERE id=5，但id=5不存在，锁住相邻间隙）。

• 非唯一索引或范围查询：

  • 默认加临键锁，锁住记录和前面的间隙。

e.g. select * from tb where id between 10 and 20 for update，潜在的临键锁可能有(前一个id值, 10]、(10, id]、(id, 20]、(20, id)（这是InnoDB为了防止幻读加的间隙锁）。