MySQL 技术内幕 - InnoDB - 锁

基本

锁类型

锁兼容: T1获得了行r的共享锁, T2可以立即获取行r的共享锁, 因为读取没有改变行r的数据, 这种情况称为锁兼容. 如果是X锁, 则必须等待之前的 X锁或者S锁释放, 这种情况称为锁不兼容.

  • X 排它锁, 与 其他锁都不兼容
  • S 共享锁, 与 共享锁兼容

锁相关表

information_schema.INNODB_TRX

  • trx_id
  • trx_mysql_thread_id MySQL线程ID, 会在 SHOW PROCESS LIST 中显示
  • trx_query 执行的 SQL
  • trx_state
  • trx_started 已经开始时间
  • trx_wait_started
  • trx_requested_lock_id 仅当 trx_state = LOCK_WAIT 时有值, 表示在等待锁的 ID

information_schema.INNODB_LOCKS

  • lock_mode
  • lock_type 锁类型, 行锁 / 表锁

information_schema.INNODB_LOCK_WAITS

  • requesting_trx_id / requesting_lock_id 正在等待的事务ID / 锁ID
  • blocking_trx_id / blocking_lock_id 阻塞其他事务的事务ID / 锁ID

一致性非锁定读

指MySQL 使用 MVCC(multi version concurrency control) 来读取当前数据库中行的数据. 如果该行上有 UPDATE 或者 DELETE 操作, 这时读取操作不会去等待行上锁的释放, 而是去读取一个快照数据.

在事务隔离级别 READ COMMITTED / REPEATABLE READ 下, InnoDB 会使用 MVCC.

  • READ COMMITTED: 总是读取最新一份快照数据.
  • REPEATABLE READ: 总是读取事务开始时的快照版本. 这是 InnoDB 默认的事务隔离级别.

设置隔离级别

1
2
3
4
5
6
7
SET [global | session] TRANSACTION LEVEL
{
READ UNCOMMITTED,
READ COMMITTED,
REPEATABLE READ,
SERIALIZABLE
}

获取隔离级别

1
SELECT @@tx_isolation;

一致性锁定读

  • SELECT ... FOR UPDATE 加上 X 锁
  • SELECT ... LOCK IN SHARE MODE 加上 S 锁

之前的非锁定读, 不加锁, 也不等待锁.

锁算法

行锁算法

  • record lock, 锁住记录本身
  • gap lock, 锁住一个范围
  • next-key lock 锁住记录本身以及一个范围

Note

在查询的索引包含唯一属性时, next-key lock 会降级为 record lock, 只锁住记录本身

例如

  1. 表 t (a int primary key, b int key)

    数据 1,1 3,1 5,3 7,6 10,8

  2. T1 SELECT * from t WHERE b = 3 FOR UPDATE

    1. 对于主键, 包含 unique 属性, 锁定 primary key a = 5, 降级为 record lock
    2. 对于辅助索引b, 加的是 next-key lock, 会锁住 (1, 3] 这个范围, 以及下一个键值之间的 gap lock (3,6)
    3. 于是另一个事务要插入 b > 1 & b < 6 之间的值都会被阻塞

关闭 Gap Lock

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

幻读(Phantom Problem)

指在同一事务下, 连续执行同样的 SQL 语句可能导致不同的结果, 第二次的 SQL 可能返回之前不存在的行.

  • 在默认的书屋隔离级别 REPEATABLE READ 下, MySQL 使用 Next-Key Locking 来避免 Phantom Problem
  • 在 READ COMMITTED 隔离级别下, 对于一个 SELECT FOR UPDATE WHERE key > value 锁住的仍然是 record 本身, 而不是一个范围

锁问题

脏读

是指在不同事务下, 当前事务可以读到另外事务未提交的数据, 简单来说就是可以读到脏数据.

  • 脏读需要事务隔离级别是 READ UNCOMMITTED
  • 某些情况下可以使用 READ UNCOMMITTED, 例如 replication 环境中的 slave 节点, 并且查询不要求返回精确的值

不可重复读

是指在一个事务内多次读取同一数据集合, 在这个事务还没有结束时, 另外的事务也对该数据集合做了一些操作, 因此T1 多次读的结果可能不一致.

也就是 Phantom Problem

特点

  • 脏读: 读到了未提交的数据
  • 不可重复读: 读到了已提交的数据. 但是违反了事务的一致性:

解决

  • 隔离级别 READ COMMITTED, 允许读到已提交的数据
  • 隔离级别 REPEATABLE READ, 使用 Gap Lock / Next-Key lock 锁定范围来确定不会出现 Phantom Problem / 不可重复读问题.
  • 使用最高隔离级别 SERIALIZABLE, 读也加共享锁, 但会导致并发性能下降

丢失更新

是指逻辑上的丢失更新. 并不是数据库层的问题.

例如两个事务 T1 / T2, 分别将 row 更新为 v1 / v2, 这样后续的的 v2 就将前面的 v1 更新覆盖了.

例如同账户并发取款, 原有 10 * 1000

  • 机器1, 取款 9000, UPDATE amount = 1000 where uid = foobar
  • 机器2, 取款 2000, UPDATE amount = 8000 where uid = foobar

这样第一次取款记录就丢失了, 这通常是与用户界面相关的问题, 金额没有刷新, 解决办法

  • 使用 UPDATE table set val = val - xxx 解决
  • 使用一致性锁定读, 读取也加上锁. SELECT FOR UPDATE / SELECT LOCK IN SHARE MODE

阻塞

  • 使用 innodb_lock_wait_timeout 确定等待超时时间, 可动态修改
  • 使用 innodb_rollback_on_timeout 确定是否在超时时自动回滚, 默认 OFF, 不可动态修改

死锁

死锁是指在两个或两个以上的事务在执行过程中, 因争夺资源而造成的一种互相等待的现象.

  • MySQL 不会回滚大部分错误, 除了死锁, 如果应用层 catch 了1213 这个错误, 不需要回滚