锁定 Lock

锁定 Lock
Categories:
Tags:

锁定方式

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 A Transaction B
1 START TRANSACTION
2 UPDATE inventory SET quantity = quantity - 4 WHERE item = 'A'
3 START TRANSACTION
4 SELECT * FROM inventory WHERE item = 'A'
5 ROLLBACK
  • 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 A Transaction B
1 START TRANSACTION
2 START TRANSACTION
3 UPDATE inventory SET quantity = quantity - 4 WHERE item = 'A'
4 SELECT * FROM inventory WHERE item = 'A'
5 COMMIT
6 SELECT * FROM inventory WHERE item = 'A'
  • 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 B
1 START TRANSACTION
2 START TRANSACTION
3 SELECT * FROM inventory WHERE item = 'A'
4 SELECT * FROM inventory WHERE item = 'A'
5 UPDATE inventory SET quantity = 6 WHERE item = 'A'
6 UPDATE inventory SET quantity = 9 WHERE item = 'A'
7 COMMIT
8 COMMIT
  • 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
1 START TRANSACTION
2 SELECT * FROM inventory WHERE item = 'A'
3 START TRANSACTION
4 UPDATE inventory SET quantity = 999 WHERE item = 'A'
5 COMMIT
6 SELECT * FROM inventory WHERE item = 'A'
7 COMMIT
  • 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 A Transaction B
1 START TRANSACTION
2 START TRANSACTION
3 SELECT * FROM inventory WHERE item = 'A'
4 SELECT * FROM inventory WHERE item = 'A'
5 库存数量是否 > 2
6 库存数量是否 > 2
7 UPDATE inventory SET quantity = quantity - 2 WHERE item = 'A'
8 COMMIT
9 UPDATE inventory SET quantity = quantity - 2 WHERE item = 'A'
10 COMMIT
  • 两个 Transaction 都满足大于 2 这个『前提』，所以继续进行更新库存的操作
  • 最后却导致库存数量等于 -1，违反库存不能小于 0 的前提，这就是 Write Skew。

所以 Write Skew 现象只有在 Serializable Isolation

Isolation Levels 资料隔离等级

MySQL

等级 Dirty Reads Non-repeatable Reads Lost Updates Phantoms Write Skews
Read Uncommitted 可能发生 可能发生 可能发生 可能发生 可能发生
Read Committed X 可能发生 可能发生 可能发生 可能发生
Repeatable Read X X 可能发生 可能发生 可能发生
Serializable X X X X X

PostgreSQL

等级 Dirty Reads Non-repeatable Reads Lost Updates Phantoms Write Skews
Read Uncommitted 可能发生 可能发生 可能发生 可能发生 可能发生
Read Committed X 可能发生 可能发生 可能发生 可能发生
Repeatable Read X X X X 可能发生
Serializable X X X X X

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 分

目前玩家分数状况

Name Score Credit
Alice 980 0
Bob 740 0
Carol 880 0
Dave 540 0
Eve 610 0

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

步骤 Transaction A Transaction B
1 START TRANSACTION
2 SELECT * FROM gamer
3 START TRANSACTION
4 INSERT INTO gamer VALUE (Frank, 999, 0)
5 COMMIT
6 SELECT * FROM gamer

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

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

Name Score Credit
Alice 980 0
Bob 740 0
Carol 880 0

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

步骤 Transaction A Transaction B
1 START TRANSACTION
2 SELECT * FROM gamer
3 START TRANSACTION
4 INSERT INTO gamer VALUE (Frank, 999, 0)
5 COMMIT
6 SELECT * FROM gamer (Snapshot)
7 UPDATE gamer SET credit = credit + 1 WHERE score >= 740
8 SELECT * FROM gamer (Snapshot)
9 COMMIT

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

Name Score Credit
Alice 980 1
Bob 740 1
Carol 880 1
Dave 540 0
Eve 610 0
Frank 999 1

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 个

item quantity
A 10

商品交易过程会像这样

步骤 Transaction A Transaction B
1 START TRANSACTION
2 START TRANSACTION
3 SELECT * FROM inventory WHERE item = A
4 SELECT * FROM inventory WHERE item = A
5 卖出 4 个 item
6 卖出 1 个 item
7 UPDATE inventory SET quantity = 6 WHERE item = A
8 COMMIT
9 UPDATE inventory SET quantity = 9 WHERE item = A
10 COMMIT

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

item quantity
A 9

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) 栏位有建立索引

name height (indexed) weight
Alice 173 58
Bob 181 72

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

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

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

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

步骤 Transaction A Transaction B
1 START TRANSACTION
2 SELECT * FROM student WHERE height >= 170 FOR UPDATE
3 START TRANSACTION
4 INSERT INTO student VALUE (Carol, 160, 50) -> success
5 INSERT INTO student VALUE (Dave, 180, 75) -> block
6 COMMIT
7 INSERT INTO student VALUE (Dave, 180, 75) -> success
8 COMMIT

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） 栏位资料

name height (indexed) weight
Alice 173 58
Bob 181 72

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

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

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

步骤 Transaction A Transaction B
1 START TRANSACTION
2 SELECT * FROM student WHERE weight >= 58 FOR UPDATE
3 START TRANSACTION
4 INSERT INTO student VALUE (Carol, 160, 50) -> block
5 COMMIT
6 INSERT INTO student VALUE (Carol, 160, 50) -> success
7 COMMIT

设定资料库的 Transaction Isolation

MySQL

SET SESSION transaction_isolation='SERIALIZABLE';

参考资料