锁表 Table Lock

定义

在要更新资料表的资料时，MySQL会将表资料锁定无法读取，直到资料异动完毕，MyISAM 预设支援 Table-level lock，而 InnoDB 预设支援 Row-level lock

Table-level lock

资料表资料异动时，将「整个资料表（Table）」都锁定住无法读取

Row-level lock

资料表资料异动时，将「要更新的资料列（row）」都锁定住无法读取

注意事项

在使用 Row-level lock 时必需要 明确指定要异动资料的主键（Primary Key），否则将会改用 Table-level lock 去做资料表的异动

范例

假设有 user 资料表，裡面有 id 与 name 的栏位，id 是主键

备註

FOR UPDATE 仅适用于 InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

锁定方式

Exclusive Lock (Write Lock, X-Lock)

• 拿到 Exclusive Lock 的 Transaction 可以写入
• 其他 Transaction 无法读取、无法写入
• 资料比需不处于 Exclusive Lock 及 Shared Lock 状态，才可以被 Exclusive Lock

SELECT * FROM `users`
WHERE `id` = '3' LOCK IN SHARE MODE

Shared Lock (Read Lock, S-Lock)

• 拿到 Exclusive Lock 的 Transaction 可以读取
• 其他 Transaction 可以 Share Lock 读取
• 无法被其他 Transaction Exclusive Lock
• 没有任何 Transaction 可以写入
• 当资料上 只有 1 个 Shared Lock，而且这个 Shared Lock 的拥有者 是 Transaction 自己本身 时，Transaction 就可以直接将这个 Shared Lock 升级变成 Exclusive Lock，直接对资料做写入。

SELECT * FROM `users`
WHERE `id` = '3' FOR UPDATE

Range Lock 范围锁

对一个 range 内的资料做 Lock，主要用来避免 Phantom 幻影现象。例如如果执行下面的 MySQL 指令

SELECT * FROM `user`
WHERE height >= 170 FOR UPDATE;

• 在 height 从 170 到无限大的 range 也会被加上 Exclusive Lock
• 任何 height 介于这个 Range Lock 范围内的资料都 不能读取 也 不能写入。

Range Lock 在 InnoDB Isolation Level 差异

Serializable

• 其他 Transaction 不能读取也不能写入 height 介于 Range Lock 内的资料

Repeatable Read (RR)

• InnoDB 在 Repeatable Read Isolation 是採用 Snapshot Isolation，读取时都是读取 Snapshot 内的资料，所以读取 Range Lock 范围内的资料是可以成功的 (读 Snapshot 内的资料)
• 仍然 不能写入 任何 height 介于 Range Lock 范围内的资料。

幻影现象 Phenomena (Race Conditions)

当 Transaction 没有获取 范围锁 (Range lock) 的情况下执行 SELECT … WHERE 操作可能会发生「幻影读 phantom read」。

Dirty Read 读取到错误的资料

如果一个 Transaction 还没有 commit，但是你却读得到已经更新的结果，这个情形叫做 Dirty Read。

• Transaction B 读到 Transaction A 才刚刚更新，但是还没有 Commit 的资料
• Transaction A 可能 再次更新资料 或是 Rollback
• 所以 Transaction B 就拿到髒掉的资料

Read Committed 或是更高的 Isolation 可以避免这个现象。

Non-repeatable Read (Read Skew) 同一交易不可重複的读取

如果将资料库 Isolation Level 设定为 Read Committed

如果你在同一个 transaction 裡面连续使用相同的 Query 读取了多次资料，但是相同的 Query 却回传了不同的结果，这个现象称为 Non-repeatable reads。

• Transaction B 第一次读取时只能拿到已经被 Commit 的资料
• Transaction A 更新，但是还没 Commit 的资料，所以拿不到最新的 Commit 资料
• Transaction B 第二次读取时，拿到的就是 Transaction A Commit 之后的资料。

在同一个 Transaction 中，重複读取时会拿到不一致的资料就叫做 Non-repeatable Read。

Repeatable Read 或是更高的 Isolation 可以避免这个现象。

Lost Update 失去更新的资料

两个 Transaction 同时进行卖出 Item A 的操作

• Transaction A 直接把 Transaction A 的更新复盖掉了，造成 Transaction A 的更新遗失，这就是 Lost Update。

Serializable Isolation 可以避免这个现象

Repeatable Read Isolation 依照每个资料库实作的不同而有不同的行为，有的无法避免 Lost Update (ex: MySQL InnoDB)，有的则可以 (ex: PostgreSQL)。

Phantom Read 幻影读

同一个 transaction 连续两次读取时，读取出来的 笔数跟上次不同，这个情况称为 Phantom Read 幻影读。

• Transaction A 一开始读取到是 Transaction B 尚未更新的资料
• Transaction A 在自己的交易阶段后读取到的资料是 Transaction B Commit 后的资料，前后资料发生不一致现象

Serializable Isolation 可以避免这个现象。

Repeatable Read Isolation 依照每个资料库实作的不同而有不同的行为。例如 PostgreSQL 可以完全避免 Phantom，但是 MySQL InnoDB 只能避免 Phantom Read

Write Skew

现有商品库存是 3，两个 Transaction 分别 同时卖出 2 个 Item A，为了确保有库存，在卖出前会先读取现在库存的数字，确认数字大于 2

• 两个 Transaction 都满足大于 2 这个『前提』，所以继续进行更新库存的操作
• 最后却导致库存数量等于 -1，违反库存不能小于 0 的前提，这就是 Write Skew。

所以 Write Skew 现象只有在 Serializable Isolation

Isolation Levels 资料隔离等级

MySQL

PostgreSQL

1. Read Uncommitted Isolation

• 允许读取还没有被 Commit 的资料

2. Read Committed Isolation

• 只允许读取已经被 Commit 的资料

3. Repeatable Read Isolation

• 每次 Transaction 要读取特定栏位的资料时，只要 query 条件相同，读取到的资料就会相同
• 只要能够避免 Dirty Read 和 Non-repeatable Read 现象就可以被称为 Repeatable Read Isolation。

有的 Repeatable Read Isolation 还可以避免 Lost Update 或 Phantom 现象。

实作方式

对读取过的资料都加上 Shared Lock，一直到 Transaction 结束

期间都不允许其他 Transaction 做写入更新。

但是因为没有做 Range Lock，通常都无法避免 Phantom 现象。

Snapshot Isolation

• 在每个 Transaction 第一次读取资料时，对资料库做一个概念上像是 Snapshot 的纪录。
• Transaction 之后就都 只能看到这个 Snapshot 的内容
• 无法读取到其他 Transaction 所做的更动

4. Serializable Isolation

• 会跟一次只让一个 Transaction 照顺序 (serially) 进行读写所得到的结果完全一致。
• Serializable 通常被认为是最严格的 Isolation Level，可以避免上述全部五种现象，但是因为 必须牺牲一些 Concurrency，效能较差。

MySQL Repeatable Read Isolation 情境讨论

Repeatable Read Isolation 的 Phantom Read 幻影读

任务是在每週的最后一天，当下分数最高的前三名玩家增加 credit，前三名玩家的 credit 都各增加 1 分

目前玩家分数状况

Transaction A 负责处理计算前三名玩家，Transaction B 在过程中新增了一笔玩家分数资料

在步骤 6 的时候 Transaction A 还是没有读取到最新的玩家 Frank 的分数，因为 Repeatable Read Isolation 在 START TRANSACTION 后读取的是 Snapshot 的资料

所以 Transaction A 会找出 Snapshot 中前三名的玩家

然后在这些玩家中，找到最低分的是 740， Transaction A 会对分书高于 740 的玩家 credit 都个加 1

最后有 4 位玩家的 credit 都被 + 1，比原本系统预计送出的 credit 还多。这种现象属于 Write Skew，在这个例子中是因为 Phamtom 而导致的 Write Skew。

Repeatable Read Isolation 的实作都是採用 Snapshot Isolation，所以 MySQL Repeatable Read Isolation 无法避免 Phantom

如果瞭解 Snapshot Isolation 的机制，就可以知道 Snapshot Isolation 在 read-only Transaction 中才可以避免 Phantom

Snapshot Isolation 执行方式

• 会在每个 Transaction 第一次 SELECT 资料的时候，记录下一个概念上像是时间标记的资料
• 每个 Transaction 在执行完第一次 SELECT 之后，Transaction 就只允许读取:
  • 在这个 时间标记之前 就已经 commit 的资料
  • Transaction 自己本身 对资料库所做的更动

在 InnoDB 的实作中，这个规则只限于 SELECT (DQL: Data Query Language) 指令，其他像是 INSERT、UPDATE 和 DELETE 等 DML (Data Manipulation Language) 指令，看到的就 不是 Snapshot

这也就是为什麽 Transaction A 在执行 UPDATE 时可以看到玩家 Frank，并且帮他增加 credit。而且在执行完 UPDATE 后，重新 SELECT 一次时，玩家 Frank 也出现在列表中 (Transaction 可以看到自己所做的更新)。

同样是採用 Snapshot Isolation 实作 RR Isolation 的 PostgreSQL

它的 Snapshot 就不只在 SELECT 指令有效，其他像 INSERT、UPDATE 和 DELETE 等 DML 指令上也都有效。所以上面例子中的 Phantom 现象并不会在 PostgreSQL 发生。

但如果情境规则实际上要更新 Frank 分数 999 的使用者，则就无法更新到

避免 Phantom 跟 Write Skew

1. 更新指令限定更新范围

UPDATE gamer SET credit = credit + 1
WHERE name IN ("Alice", "Bob", "Carol");

2. 使用 MySQL 的 Share Lock 或是 Exclusive Lock

Block 住其它想更改资料的 Transaction

SELECT * FROM gamer LOCK IN SHARE MODE;

UPDATE gamer SET credit = credit + 1
WHERE score >= 740;

COMMIT;

3. 将 MySQL 设定为 Serialzable Isolation

MySQL 就会自动为所有的 SELECT 都加上 LOCK IN SHARE MODE。

但这会影响效能很大，如果没有为栏位做好 Index ，就有可能会造成 Full-Table-Lock，应该尽量避免使用。

Repeatable Read Isolation 的 Lost Update 失去更新的资料

总共有 10 个商品，Transaction A 卖出 4 个，Transaction B 卖出 1 个

商品交易过程会像这样

最后总共卖出 5 个商品，库存应该剩 5 个，但库存却因为 Transaction B 的更新变成库存 9 个，这就是 Lost Update 现象。

Lost Update 现象通常都发生在像这种对资料库做 read-modify-write 的操作。

有的资料库会实作 Lost Update 的自动侦测机制来避免这种错误，像是 PostgreSQL 的 RR Isolation。

但是 MySQL 则没有，所以 Lost Update 现象是有可能在 MySQL 的 RR Isolation 发生的。

避免 Lost Update

1. 使用 Atomic Operations

对原本的栏位数字做增减，不要直接设定程式计算的数字

UPDATE inventory SET quantity = quantity - 4
WHERE item = A;

2. 使用 SHARE LOCK / EXCLUSIVE LOCK

对要更新的资料做锁定，但不建议使用，这会影响效能很大，如果没有为栏位做好 Index ，就有可能会造成 Full-Table-Lock，应该尽量避免使用。

SELECT * FROM inventory FOR UPDATE;
UPDATE inventory SET quantity = 6 WHERE item = A;
COMMIT;

MySQL Range Locks on Indexed and Non-indexed Column

MySQL 的文件中介绍它的 Range Lock 採用的是 Next-Key Lock，Next-Key Lock 只 对有 Index 的栏位有作用

没有做 Index 的栏位则没有作用，且可能造成 Full Table Lock。

锁定指定范围的 索引（indexed） 栏位资料

有一学生资料表，裡面有学生的 姓名 (name)、身高 (height)、体重 (weight)，然后 身高 (height) 栏位有建立索引

限制锁定 身高大于等于 170 的资料

SELECT * FROM student
WHERE height >= 170 FOR UPDATE;

上面语法会对所有 SELECT 出来的资料做 Exclusive Lock。MySQL 还会另外做 Range Lock

Lock 的范围是 height 从 170 到无限大，不允许其他 Transaction 新增任何 height 的值介于这个范围内的资料

Transaction B 在步骤 5 要新增身高超过 170 的资料会被 Block 住，要等 Transaction A 执行完 Commit 后才可以继续执行

Range Lock 这个机制的好处是可以只 Lock 所有跟 Transaction A 有关的『资料 Range』，而不是 Lock 整张 Table，提高存取效能

MySQL RR Isolation，如果没有额外下 Shared Lock 或是 Exclusive Lock 指令，Range Lock 就不会生效。

锁定指定范围的 未索引（none indexed） 栏位资料

限制锁定 体重大于等于 58 的资料，但因为 体重 (weight) 栏位没有做过索引，所以会对整张资料表做 Full Table Lock。

SELECT * FROM student
WHERE weight >= 58 FOR UPDATE;

所以即便新增的栏位资料 体重小于 58，也没办法顺利新增资料

设定资料库的 Transaction Isolation

MySQL

SET SESSION transaction_isolation='SERIALIZABLE';

参考资料

• 複习资料库的 Isolation Level 与图解五个常见的 Race Conditions | by Chester Chu | Medium
• 事务隔离 - 维基百科，自由的百科全书
• MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels
• 对于 MySQL Repeatable Read Isolation 常见的三个误解 | by Chester Chu | Medium
• 资料库交易的 Isolation. 最近在读 High Performance MySQL 时读到了… | by Yuren Ju | getamis

• KeJyun学习日誌: MySQL锁表(Lock Table)Table-level与Row-level比较