关于 MySQL 中 InnoDB 事务锁的一些理解

2017/6/8 posted in  理论概念 comments

InnoDB 行锁

参考 MySQL · 引擎特性 · InnoDB 事务锁系统简介
Locks Set by Different SQL Statements in InnoDB

区间锁(LOCK_GAP)

锁住一段范围,不锁记录本身,通常表示两个索引记录之间,或者索引上的第一条记录之前,或者最后一条记录之后的锁。一般在 RR 隔离级别下会使用。

Next-Key 锁(LOCK_ORDINARY)

其实就是锁住 待插入记录本身 + 记录之前的 GAP,Next-Key 锁用于解决 RR 隔离级别下的幻读问题。

通常对于 UPDATE 或 DELETE 或 SELECT … FOR UPDATE 或 SELECT … IN SHARE MODE 操作:

  • 查询条件为唯一索引且是唯一等值查询时,只对记录本身加锁
  • 非唯一条件查询,或者查询会扫描到多条记录,加 Next-Key 锁

共享锁 (LOCK_S)

允许多个事务读取,阻止其他事务获取相同数据集的排他锁。

通常 INSERT 操作是不加锁的,但如果在插入或更新记录时,检查到 duplicate key(或者有一个被标记删除的 duplicate key),对于普通的 INSERT/UPDATE,会加 LOCK_S 锁,而对于类似 REPLACE INTO 或者 INSERT … ON DUPLICATE 这样的 SQL 加的是 X 锁。

排它锁 (LOCK_X)

允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

通常对于 UPDATE 或 DELETE 或类似 SELECT … FOR UPDATE 操作,都会对记录加排他锁

插入意向锁(LOCK_INSERT_INTENTION)

插入意向锁是 GAP 锁的一种,如果有多个 Session 插入同一个 GAP 时,他们无需互相等待,例如当前索引上有记录 4 和 8,两个并发 Session 同时插入记录 6,7。他们会分别为 (4,8) 加上 GAP 锁,但相互之间并不冲突(因为插入的记录不冲突)。

会去检查当前插入位置的下一条记录上是否存在锁对象,这里的下一条记录不是指的物理连续,而是按照逻辑顺序的下一条记录。如果下一条记录上存在锁对象,就需要判断该锁对象是否锁住了 GAP。如果 GAP 被锁住了,并判定和插入意向 GAP 锁冲突,当前操作就需要等待,加的锁类型为 LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION。

对于 GAP 类型且申请的不是插入意向锁时,无需等待任何锁,这是因为不同 Session 对于相同 GAP 可能申请不同类型的锁,而 GAP 锁本身设计为不互相冲突;
任何锁请求都无需等待插入意向锁。

死锁场景

  1. 并发插入导致的死锁

    create table t1 (a int primary key);
    三个会话执行 insert into t1(a) values (2);

    Session1 Session2 Session3
    insert,获取 X 锁
    insert,等待获取 S 锁
    insert,等待获取 S 锁
    Rollback,释放 X 锁
    获取 S 锁 获得 S 锁
    申请插入意向 X 锁,等待 Session 3
    申请插入意向 X 锁,等待 Session 2

    检测到有 duplicate key 所以加 S 锁。回滚后,同时持有 S 锁,但又都无法获得 插入意向 X 锁。

  2. GAP 锁导致的死锁

    create table t1 (a int primary key ,b int);
    insert into t1 values (2,2),(6,6),(10,10);
    
    Session1 Session2
    delete from t where id = 3; 获得 2-5 的 GAP 锁
    delete from t where id = 4; 获得 2-5 的 GAP 锁
    insert into t values (3, 0); 意向插入锁等待 Session2 释放 GAP 锁
    insert into t values (4, 0); 意向插入锁等待 Session2 释放 GAP 锁

相关查询

  1. 查看innodb状态(包含最近的死锁日志)

    show engine innodb status;
    
  2. 查看事务锁等待状态情况

    select * from information_schema.innodb_locks;
    select * from information_schema.innodb_lock_waits;
    select * from information_schema.innodb_trx;