El Psy Congroo

MySQL锁机制分析

Posted on

两种读取方式

  • 快照读（snapshot read）

    MVCC实现，无锁，高性能但数据不实时，事务中首次select时确定快照版本

    select ...

  • 当前读（current read）

    加锁实现：next-key

    1
    2
    3
    4
    5
    select ... lock in share mode
    select ... for update
    insert
    update
    delete

锁机制

2PL：Two-Phase Locking

锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交

锁模式
  • Shared(S) and Exclusive(X) Locks
    • select … lock in share mode / lock table … read 加S锁
    • select … for update / insert update delete / lock table … write 加X锁
  • Intention Locks
    • 表级锁，IS/IX锁表明事务有意图获取表中某一行的S或X锁
    • 规则：事务在获取行锁之前必须获取到相同或更高级别的表级意向锁
    • 用途：加表级锁时无需遍历全表查找是否存在行级锁
    • 意向锁兼容性（用于判断是否允许加锁，以下锁类型均为表级锁）
X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible
锁类型
  • Record Locks
    • 锁住索引（主键或二级索引）中的一条记录
    • 日志：lock_mode X locks rec but not gap
  • Gap Locks
    • 锁住索引记录之间的gap，以及首条记录之前，末尾记录之后
    • 仅在RR级别下，未完整命中唯一索引（多字段唯一索引只使用了部分字段也视为未完整命中）的情况下使用
    • gap lock用于防止gap中插入新数据，因此不区分S和X，效果是一样的，并且相互不冲突
    • 日志：lock_mode X locks gap before rec
  • Next-Key Locks
    • record lock+gap lock，锁住索引记录，及记录之前的gap，左开右闭
    • 在RR级别下防止幻读
    • 日志：lock_mode X
  • Insert Intention Locks
    • 特殊的gap锁，在插入之前获取（在获取插入记录的X锁之前）
    • 与gap lock冲突（gap lock本身就是用于防止插入）
    • insert intention lock之间不冲突（允许在同一gap中不同位置并发插入）
    • 日志：lock_mode X locks gap before rec insert intention waiting
  • AUTO-INC Locks

加锁场景

  • 查询命中主键索引
    • 精确查询
      • 命中的索引项加record lock，X(update)/S(select..lock in share mode)
    • 范围查询
      • RC
        • 命中的索引项加record lock
      • RR
        • 命中的索引项加next-key lock
    • 查询结果为空
      • RR
        • 目标所在间隙加gap lock
  • 查询命中唯一索引（非主键）
    • 精确查询
      • 对命中唯一索引及主键索引加record lock
    • 范围查询
      • RC 同精确查询一致
      • RR
        • 命中的索引项加next-key lock，主键索引加record lock
  • 查询命中非唯一索引
    • RC
      • 同命中唯一索引
    • RR
      • 命中的索引项加next-key lock，索引项之后加gap lock，对应主键索引加record lock
  • 查询未命中索引
    • RC
      • 对于update和delete，先锁住所有记录，保留需要执行写操作的记录上的锁，立即释放其他记录上的锁。如果出现锁冲突，update语句不等待锁，执行semi-consistent read（快照读），由MySQL Server判断是否冲突，认为冲突时才等待锁。delete语句无此逻辑，冲突时直接等待锁
      • 存在死锁可能，虽然最终只锁操作记录，但是之前要获取到所有记录上的锁
    • RR
      • 全表主键索引加next-key lock
  • 修改索引键值
    • 若被set的字段不在where条件中，就只需要对修改前后的记录加record lock，set操作不存在幻读问题，无需加gap lock
  • 插入数据
    1. 唯一性约束检查，对索引项加S锁进行当前读（快照读无法读到最新数据）
    2. 加插入意向锁
    3. 对插入记录的所有索引项加X锁

其他

  • 查看事务加锁情况

    1
    2
    3
    4
    5
    6
    7
    //查询隔离级别
    select @@global.tx_isolation;
    select @@tx_isolation;
    // innodb_locks记录了所有innodb正在等待的锁，和被等待的锁
      select * from information_schema.innodb_locks;
      // innodb_lock_waits记录了所有innodb锁的持有和等待关系
      select * from information_schema.innodb_lock_waits;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
	mysql> set global innodb_status_output=ON; // 可选。将监控输出到log_error输出中，15秒刷新一次
	mysql> set global innodb_status_output_locks=ON; // 输出的内容包含锁的详细信息
	mysql> show engine innodb status;
	
	// 在两个事务中执行：insert into city(city_name) values('wz');
+-------+------------+---------------+--------------+-------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name |
+-------+------------+---------------+--------------+-------------+
| city  |          0 | PRIMARY       |            1 | id          |
| city  |          0 | uni_city_name |            1 | city_name   |
+-------+------------+---------------+--------------+-------------+
	
	 ---TRANSACTION 78390, ACTIVE 31 sec inserting
	// 事务78390对`city`表持有IX
	TABLE LOCK table `test`.`city` trx id 78390 lock mode IX
	// `space id` 唯一确定一张表 `page no` 锁所在页
	// 事务等待`city`表`uni_city_name`索引记录的S锁`lock mode S waiting`
	RECORD LOCKS space id 238 page no 4 n bits 72 index uni_city_name of table `test`.`city` trx id 78390 lock mode S waiting
	Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
	// 索引记录`777a`，对应的asc字符为`wz`
	 0: len 2; hex 777a; asc wz;;
	// 主键索引记录`00000003`，对应id为3
	 1: len 4; hex 00000003; asc     ;;
	
	---TRANSACTION 78385, ACTIVE 53 sec
	TABLE LOCK table `test`.`city` trx id 78385 lock mode IX
	// 事务持有`city`表`uni_city_name`索引记录的X锁`lock_mode X locks rec but not gap`
	RECORD LOCKS space id 238 page no 4 n bits 72 index uni_city_name of table `test`.`city` trx id 78385 lock_mode X locks rec but not gap
	Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
	 0: len 2; hex 777a; asc wz;;
	 1: len 4; hex 00000003; asc     ;;

常见死锁场景

并发insert同一条数据且存在唯一性索引