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 默认的事务隔离级别.

设置隔离级别

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, 只锁住记录本身

例如

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

数据 1,1 3,1 5,3 7,6 10,8
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 这个错误, 不需要回滚