El Psy Congroo

MySQL锁机制分析

两种读取方式

  • 快照读(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中插入新数据,因此不区分SX,效果是一样的,并且相互不冲突
    • 日志: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同一条数据且存在唯一性索引