Mysql 基础知识 锁

数据库►MySQL , 评论

1. 悲观锁和乐观锁

确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

  • 悲观锁 - 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
    • 在查询完数据的时候就把事务锁起来，直到提交事务（COMMIT）
    • 实现方式：使用数据库中的锁机制。
  • 乐观锁 - 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
    • 在修改数据的时候把事务锁起来，通过 version 的方式来进行锁定
    • 实现方式：使用 version 版本或者时间戳。

【示例】乐观锁示例

商品 goods 表中有一个字段 status，status 为 1 代表商品未被下单，status 为 2 代表商品已经被下单，那么我们对某个商品下单时必须确保该商品 status 为 1。假设商品的 id 为 1。

1
2
3
4
5
select (status,status,version) from t_goods where id=#{id}

update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};

2. 表级锁和行级锁

从数据库的锁粒度来看，MySQL 中提供了两种封锁粒度：行级锁和表级锁。

  • 表级锁（table lock） - 锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。
  • 行级锁（row lock） - 锁定指定的行记录。这样其它进程还是可以对同一个表中的其它记录进行操作。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，锁竞争的发生频率就越小，系统的并发程度就越高。但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此锁粒度越小，系统开销就越大。

在选择锁粒度时，需要在锁开销和并发程度之间做一个权衡。

在 InnoDB 中，行锁是通过给索引上的索引项加锁来实现的。如果没有索引，InnoDB 将会通过隐藏的聚簇索引来对记录加锁。

在 InnoDB 中，行锁是在需要的时候加上去的，但不是不需要了就立即释放，而是等到事务结束之后才释放，这就是两阶段锁协议。

3. 读写锁

  • 独享锁（Exclusive），简写为 X 锁，又称写锁。使用方式：SELECT ... FOR UPDATE;
  • 共享锁（Shared），简写为 S 锁，又称读锁。使用方式：SELECT ... LOCK IN SHARE MODE;

写锁和读锁的关系，简言之：独享锁存在，其他事务就不能做任何操作。

InnoDB 下的行锁、间隙锁、next-key 锁统统属于独享锁。

4. 意向锁

当存在表级锁和行级锁的情况下，必须先申请意向锁（表级锁，但不是真的加锁），再获取行级锁。使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁规定：

  • IX/IS 是表锁；
  • X/S 是行锁。
  • 一个事务在获得某个数据行的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
  • 一个事务在获得某个数据行的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

各种锁的兼容关系如下：

- X IX S IS
X ❌ ❌ ❌ ❌
IX ❌ ✔️ ❌ ✔️
S ❌ ❌ ✔️ ✔️
IS ❌ ✔️ ✔️ ✔️

解释如下：

  • 任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
  • 这里兼容关系针对的是表级锁，而表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T1 想要对数据行 R1 加 X 锁，事务 T2 想要对同一个表的数据行 R2 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

5. MVCC

多版本并发控制（Multi-Version Concurrency Control, MVCC）可以视为行级锁的一个变种。它在很多情况下都避免了加锁操作，因此开销更低。不仅是 Mysql，包括 Oracle、PostgreSQL 等其他数据库都实现了各自的 MVCC，实现机制没有统一标准。

MVCC 是 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

5.1. MVCC 思想

加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的。

MVCC 的思想是：

  • 保存数据在某个时间点的快照，写操作（DELETE、INSERT、UPDATE）更新最新的版本快照；而读操作去读旧版本快照，没有互斥关系。这一点和 CopyOnWrite 类似。
  • 脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。

5.2. 版本号

InnoDB 的 MVCC 实现是：在每行记录后面保存两个隐藏列，一个列保存行的创建时间，另一个列保存行的过期时间（这里的时间是指系统版本号）。每开始一个新事务，系统版本号会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

  • 系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
  • 事务版本号 TRX_ID ：事务开始时的系统版本号。

5.3. Undo 日志

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

例如在 MySQL 创建一个表 t，包含主键 id 和一个字段 x。我们先插入一个数据行，然后对该数据行执行两次更新操作。

1
2
3
INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用 START TRANSACTION 将上面的操作当成一个事务来执行，根据 MySQL 的 AUTOCOMMIT 机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID 和操作之外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除。

INSERT、UPDATE、DELETE 操作会创建一个日志，并将事务版本号 TRX_ID 写入。DELETE 可以看成是一个特殊的 UPDATE，还会额外将 DEL 字段设置为 1。

5.4. ReadView

MVCC 维护了一个一致性读视图 consistent read view ，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, ...}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。

img

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

  1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
  2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
  3. 如果落在黄色部分，那就包括两种情况
    a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
    b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

  • TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
  • TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
  • TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
    • 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
    • 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

5.5. 快照读与当前读

快照读

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

1
SELECT * FROM table ...;

当前读

MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

1
2
3
INSERT;
UPDATE;
DELETE;

在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

1
2
SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;

6. 行锁

行锁的具体实现算法有三种：record lock、gap lock 以及 next-key lock。

  • Record Lock - 行锁对索引项加锁，若没有索引则使用表锁。
  • Gap Lock - 对索引项之间的间隙加锁。锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15：SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;。在 MySQL 中，gap lock 默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是 disable 的，且 MySQL 中默认的是 RR 事务隔离级别。
  • Next-key lock -它是 Record Lock 和 Gap Lock 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间。

只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock。在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 gap lock 或 next-key lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用 gap lock 或 next-key lock。

MVCC 不能解决幻读问题，Next-Key 锁就是为了解决幻读问题。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key 锁 可以解决幻读问题。

索引分为主键索引和非主键索引两种，如果一条 SQL 语句操作了主键索引，MySQL 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。在 UPDATE、DELETE 操作时，MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的 next-key lock。

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。发生死锁后，InnoDB 一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

7. 死锁和死锁检查

查看死锁

1
show engine innodb status \G

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务；但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 `innodb_lock_wait_timeout`` 来解决

1
2
# 死锁记录只记录最近一个死锁信息，若要将每个死锁信息都保存到错误日志，启用以下参数：
show variables like 'innodb_print_all_deadlocks';

相关配置

1
2
3
4
5
[mysqld]
log-error =/var/log/mysqld3306.log
innodb_lock_wait_timeout=60     #锁请求超时时间(秒)
innodb_rollback_on_timeout = 1  #事务中某个语句锁请求超时将回滚真个事务
innodb_print_all_deadlocks = 1  #死锁都保存到错误日志

iFan在路上啊

个人简介。