MySQL InnoDB事务锁

MySQL InnoDB 事务锁
锁机制用于管理对共享资源的并发访问，而数据库本身作为共享资源的集合，内部需要提供一定的锁机制来保证事务的隔离性。这里探讨的是MySQL（5.7）InnoDB引擎下的锁机制。

锁类型

共享锁

共享锁也称为读锁，允许事务读一行数据。共享锁之间是兼容的，也就是说多个事务可以针对同一行数据加共享锁。

排他锁

共享锁也称为写锁，允许事务删除或更新一行数据。排他锁之间以及排他锁和共享锁是不兼容的。

表级锁

表锁（table lock）对整个表加锁，影响表的所有记录

• 共享锁 LOCK TABLE table_name READ; 用读锁锁表，会阻塞其他事务修改表数据。

• 排他锁 LOCK TABLE table_name WRITE; 用写锁锁表，会阻塞其他事务读和写。

  行级锁

  行锁（row lock）

• 锁定相应记录，但不影响其他记录

• 实际针对记录的索引加锁，而非记录本身
• 如果表中没有索引，则会使用隐式索引进行锁定

共享锁

• 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
  • 可以通过select … lock in share mode显式获取共享锁

排他锁

• 又称写锁。允许获取排他锁的事务更新和删除数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
• 可以通过select …for update显式获取排他锁

  意向锁

  因为表锁覆盖了行锁的数据，所以表锁和行锁也会产生冲突，为了方便检测表级锁和行级锁之间的冲突，就引入了意向锁。

1. 意向锁分为意向读锁（IS）意向写锁（IX）。
2. 意向锁是表级锁，但是却表示事务要锁定或者将要锁定某行记录，而不是整个表。
3. 意向锁之间不会产生冲突，真正的冲突在加行锁时检查。
4. 在给一行记录加锁前，首先要给该表加意向锁，也就是要同时加表意向锁和行锁。
5. 意向锁不会阻塞除全表扫（如LOCK TABLE WRITE）以外的任何请求。

表级锁兼容矩阵如下：
Table-level lock type compatibility is summarized in the following matrix.

Gap Lock

间隙锁（Gap Lock）是锁定索引记录之间的间隙，锁定在第一个之前或最后一个索引记录之后的间隙上。
可以通过以下两种方式显式关闭Gap Lock

• 将事务隔离级别设置为READ COMMITTED

• 将参数innodb_locks_unsafe_for_binlog设置为1 （已弃用）

  Next-Key Lock

  Record Lock + Gap Lock 的结合

  Auto-INC Lock

• 表级锁

• AUTO_INCREMENT字段

• innodb_autoinc_lock_mode

  • 0=传统（执行插入语句时，获取表级锁，语句执行完毕释放）
  • 1=连续（对于能提前估算数量的插入语句，通过互斥量mutex来锁定分配Id操作，对于其他复杂插入则采用表级锁方式）
  • 2=交叉（对所有insert都采用互斥量mutex锁定，性能较好，但是会造成ID不连续；另外binlog的format须为row）

    加锁规则

    在InnoDB的默认的可重复读（REPEATABLE-READ）隔离级别下，存在以下的加锁规则：

• 非锁定读（普通select）：不会产生锁，也不会被锁定

• Insert（主键自增）：存在 Auto-INC Lock、 insert intention gap lock、行级排他锁
• 外键值的插入和更新：采用select lock in share mode方式对父表记录加共享锁
• 锁定读：命中唯一索引产生行锁；普通索引产生Next-Key Lock；没有索引则产生表锁
• Update：同上
• Delete：同上

Online DDL 修改表结构

• 增加、删除字段：不锁表
• 添加、删除索引：不锁表
• 重命名字段：不锁表
• 更改字段类型：锁表

Online DDL andpt-online-schema-changefor some alter operations applied on a table contains 1,078,880 rows

锁问题

丢失更新

丢失更新分为两个层面

• 一个层面数据库层面，两个事务同时更新一条记录，会发生事务更新覆盖的情况，但是在InnoDB中，不会发生这种情况，事务更新时会加排他锁，两个事务顺序执行。
• 另一个层面是应用层面的丢失更新，在读取记录然后更新记录的场景下，在并发场景下，如果不加控制就会出现覆盖的情况。这种情况可以通过乐观锁和悲观锁来解决。

  脏读

  脏读指的是读取到其他事务未提交的数据，在读已提交及以上的隔离级别下就避免该问题。

  不可重复读

  不可重复读指的是同一事务下两次读取同一行的数据不一致。InnoDB使用的MVCC机制来实现的。

  幻读

  幻读指的是同一个事务下两次查询，返回的记录数不一致。InnoDB使用的MVCC机制和Next-Key Lock来实现的。

  锁排查

  当发生锁定时，可以查下 information_schema 库的相关表来查看事务的锁定情况来快速定位问题。
  查看当前锁情况

  SELECT
    w.requesting_trx_id,
    t1.trx_state,
    t1.trx_query,
    t1.trx_started,
    t1.trx_wait_started,
    w.requested_lock_id,
    l1.lock_mode,
    l1.lock_type,
    l1.lock_table,
    l1.lock_index,
    l1.lock_data,
    w.blocking_trx_id,
    t2.trx_state,
    w.blocking_lock_id,
    l2.lock_mode,
    l2.lock_type,
    l2.lock_table,
    l2.lock_index,
    l2.lock_data
  FROM
    INNODB_LOCK_WAITS AS w
  LEFT JOIN INNODB_TRX t1 ON w.requesting_trx_id = t1.trx_id
  LEFT JOIN INNODB_TRX t2 ON w.blocking_trx_id = t2.trx_id
  LEFT JOIN INNODB_LOCKS l1 ON w.requested_lock_id = l1.lock_id
  LEFT JOIN INNODB_LOCKS l2 ON w.blocking_lock_id = l2.lock_id
  WHERE
    t1.trx_state = 'LOCK WAIT'
  LIMIT 1;