处理SQL语句时, Locking Read, UPDATE, 和DELETE会在扫描过的索引记录上加record lock. InnoDB不会记住WHERE条件, 只知道扫描了哪一部分索引. Lock通常指代next-key lock, 其会锁住一个索引记录和其之前的间隙.
若使用secondary index(二级索引)搜索, 且索引记录的锁为exclusive, InnoDB会锁住clustered index上对应的索引记录.
若未能找到匹配SQL语句的索引, MySQL必须扫描整个表, 并锁住表中的每一行, 导致其他用户无法写入任何数据, 因此最好为query创建合适的索引.
InnoDB的锁类型如下:

  • SELECT ... FROM
    该语句为consistent read, 从数据库快照中读取, 且只要不为SERIALIZABLE隔离级别, 就不会加任何锁. 若事务隔离级别为SERIALIZABLE, 则在索引记录上加shared next-key lock; 若使用unique index(唯一索引)搜索某一行, 则在对应的索引记录上加record lock.

  • SELECT ... FOR UPDATE, SELECT ... FOR SHARE
    上述语句使用唯一索引时, 会锁住扫描过的行, 并释放不匹配WHERE条件的行上的锁. 然而在某些情况下, 由于结果行与原始数据源之间的关系丢失, 不会立即释放锁, 例如在UNION中, 在判断被扫描的行是否符合条件之前, 这些行被插入到一个临时表. 此时, 临时表中的行与原始表中的行不存在任何关系, 原始表中的行直到查询结束之前都不会释放锁.

  • Locking Read(SELECT FOR UPDATE, SELECT FOR SHARE, UPDATE, DELETE)
    上述语句的加锁规则分为两种情况:

    • 具有唯一搜索条件的唯一索引: InnoDB使用record lock锁住目标行对应的索引记录
    • 范围搜索, 或非唯一索引: InnoDB使用gap lock或next-key lock锁住扫描的索引记录, 以防止其他事务插入到间隙中

  • 对于扫描到的索引记录, SELECT ... FOR UPDATE会阻止其他事务执行SELECT ... FOR SHARE, 并阻止其他事务在特定事务隔离级别下读取.

  • UPDATE ... WHERE ...
    该语句在扫描过的每个索引记录上添加exclusive next-key lock; 若使用唯一索引搜索到唯一一行, 则降级为record lock.

  • 当UPDATE修改clustered index上的索引记录时, 会锁住受影响的secondary index(二级索引)上的记录. UPDATE还会在两种情况下在secondary index的记录上加shared lock:

    • 插入新的secondary index记录
    • 插入新的secondary index记录前, 执行duplicate check scan(重复检查扫描)

  • DELETE FROM ... WHERE ...
    该语句在每个扫描过的索引记录上添加exclusive next-key lock; 若使用唯一索引搜索到唯一一行, 则降级为record lock

  • INSERT在被插入的行上添加exclusive lock. 该锁为record lock, 而不是next-key lock, 不会阻止其他事务插入到目标行的前后间隙

在插入行之前, 需先获得一种gap lock, 称为insert intention gap lock. 该锁表示当前事务有意插入, 若其他事务插入到同一间隙的不同位置, 则无需等待当前事务: 假设InnoDB中有两个索引记录, 值分别为4和7, 两个事务分别插入5和6, 在获得插入行的exclusive lock前, 两个事务会使用insert intention lock锁住(4,7)间隙, 且不会互相阻塞, 因为插入值不同.

若发生duplicate-key error(冲突键错误), 则会在重复的索引记录上添加shared lock. 若多个事务尝试插入同一行, 而该行已存在, 且其他事务持有该行的exclusive lock, 则shared lock会导致死锁; 若其他事务删除该行, 也会发生这种情况. 假设InnoDB中table t1的结构如下:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

假设三个事务按从上向下的顺序执行:

  1. 事务1: 
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);
  2. 事务2: 
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);
  3. 事务3: 
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);
  4. 事务1: 
    ROLLBACK;

事务1获得该行的exclusive lock, 事务2和3的插入操作导致重复键错误, 并获得该行的shared key; 当事务1回滚时会释放exclusive lock, 此时事务2和3都试图将自己的shared lock升级为exclusive lock, 并导致死锁: 事务2和事务3都不愿放弃自己的shared lock, 因此两者都无法获得exclusive lock.
若表中包含键值为1的行, 且三个事务按照以下顺序执行, 也会出现类似情况:

  1. Session 1: 
    START TRANSACTION;
    DELETE FROM t1 WHERE i = 1;
  2. Session 2: 
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);
  3. Session 3: 
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);
  4. Session 1: 
    COMMIT;

第一个事务获得目标行的exclusive lock, 事务2和3的操作导致重复键错误, 并获得目标行的shared lock. 当事务1提交时, 其释放拥有的exclusive lock, 并导致死锁: 由于事务2和3都不愿释放自己的shared lock, 因此无法获得exclusive lock.

  • INSERT ... ON DUPLICATE KEY UPDATE
    该语句与INSERT不同, 当发生重复键错误时, 该语句会获得目标行的exclusive lock, 而不是shared lock. 不同键值类型会导致不同的锁类型:

    • 主键重复: exclusive record lock
    • 唯一键重复: exclusive next-key lock

  • REPLACE
    当没有唯一键冲突时, 该语句与INSERT上锁行为相同; 当发生冲突时, 会在目标行上放置exclusive next-key lock

  • INSERT INTO T SELECT ... FROM S WHERE ...
    该语句会在所有插入行上设置exclusive record lock, 若事务隔离级别为READ COMMITTED, 则InnoDB执行consistent read(不设置任何锁); 否则, InnoDB会在扫描过的行上设置shared next-key lock. 使用基于statement的二进制日志进行回滚恢复时, 每条SQL语句都必须按照与最初完全相同的方式执行, 因此必须设置锁.

  • CREATE TABLE ... SELECT ...
    分为两种上锁情况:

    • REPEATABLE READ隔离级别: SELECT会给扫描过的索引记录设置shared next-key lock
    • READ COMMITTED隔离级别: 与consistent read相同, 不会设置任何锁

  • REPLACE INTO t SELECT ... FROM s WHERE ..., UPDATE t ... WHERE col IN (SELECT ... FROM s ...)
    SELECT子句会在扫描过的索引记录上设置shared next-key lock

  • 若table中有FOREIGN KEY约束, 任何需要检查约束条件的insert, update, delete都需要设置shared record lock.

  • LOCK TABLES会设置table lock. 但这些锁由MySQL设置, 而不是InnoDB. 若innodb_table_locks = 1且autocommit = 0, 则InnoDB可知道table-level lock, MySQL也可以知道row-level lock; 否则InnoDB无法检测到table lock相关的死锁. 并且, 由于MySQL不知道row-level lock, 有可能在其他事务已设置row-level lock的情况下, 为当前事务设置table-level lock

  • LOCK TABLES会在innodb_table_locks = 1时为每个table设置两个锁(IS和IX). 若不想设置table lock, 可改为innodb_table_locks = 0. MySQL 8.0后, 若执行LOCK TABLES ... WRITE, 即使innodb_table_locks = 0, 仍会为目标表上锁, LOCK TABLES ... READ同理.

  • 所有InnoDB lock都会在事务提交或中止时释放, 因此, 若autocommit = 1(已开启自动提交), 则无需任何LOCK TABLES, 因此每条语句都是一个单独事务, 语句执行完毕后立即释放锁.

  • 若autocommit = 1, 则不应在事务中使用LOCK TABLES, 因为LOCK TABLES会自动提交当前事务, 并开启新的事务