锁定资料 Lock

MySQL 交易 Transaction: 锁定资料 Lock

Tags:

锁定资料有 shared lock (sharedLock) 与 lock for update (lockForUpdate)，两者都可以避面同一行资料被其他的 transaction update

不同的地方

定义

sharedLock locks only for write, lockForUpdate also prevents them from being selected

shared lock (sharedLock) 与 lock for update (lockForUpdate) 两者都是锁定类型的 transaction，但这两个锁定类型的 transaction 不会避免非锁定的 transaction 去读取到资料。

sharedLock

其他的 Session 可以 Select，但无法 Update 资料

如果 Session_1 使用 sharedLock 去读取资料并对资料进行修改，而 Session_2 也使用 sharedLock 去读取资料并对资料进行修改，则 Session_2 会等到 Session_1 commit 之后，才能对资料进行 update，若 Session_1 与 Session_2 都有修改到同样栏位资料，则会变成最后修改的 Session_2 的资料。

使用情境

有 parent 及 child 两个资料表，而两者资料是有关联性的，当要新增资料至 child 资料表时，必须确保 parent 资料表有资料，才不会让 child 找不到他的 parent，此时可以使用 sharedLock 去避免资料被异动（删除、更新）

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

lockForUpdate

可以避免被其他锁定型 transaction 进行 Select

当需要对同一栏位确保资料 update 是正确的，如计数器，则必须使用 lockForUpdate 来锁定资料

当 parent 内有一个计数器栏位纪录总共有多少 child，则为了数字正确更新，必须避免其他锁定型的 transaction 进行资料异动

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

NOWAIT & SKIP LOCKED

NOWAIT

若有资料被锁定，不会等待其他 transaction commit 后再去读取资料，直接抛出错误

SKIP LOCKED

若有资料被锁定，不会等待其他 transaction commit 后再去读取资料，直接忽略该资料

范例

Session 1 锁定资料

--
# Session 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;
-- 锁定资料 2 的资料，避免被读取
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

Session 2 捞取资料，使用 NOWAIT


# Session 2:

mysql> START TRANSACTION;
-- NOWAIT 遇到资料 2 被 Lock 抛出错误
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

Session 3 捞取资料，使用 SKIP LOCKED

# Session 3:

mysql> START TRANSACTION;

-- SKIP LOCKED 遇到资料 2 被 Lock，忽略资料 2
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+