MySQL 锁实践

1. 锁定读

建立如下数据表 demo（id 主键）

id	val
1	a
2	b
3	c

1.1 共享互斥

事务 1 对记录 1 加共享锁，执行以下 SQL 语句

BEGIN;
SELECT * FROM `demo`
WHERE id = 1
LOCK IN SHARE MODE;

返回 (1,a)

若事务 2 也以共享锁的方式读取数据，可以成功。

BEGIN;
SELECT * FROM `demo`
WHERE id = 1
LOCK IN SHARE MODE;

返回 (1,a)

若事务 2 以独占锁的方式读取数据，会被阻塞直到事务 1 提交。

BEGIN;
SELECT * FROM `demo`
WHERE id = 1
FOR UPDATE;

被阻塞

若事务 2 以独占锁的方式读取其他未被加锁的数据，则会成功。

BEGIN;
SELECT * FROM `demo`
WHERE id = 2
FOR UPDATE;

返回 (2,b)

1.2 独占互斥

事务 1 加独占锁，执行以下语句。

BEGIN;
SELECT * FROM `demo`
WHERE id = 1
FOR UPDATE;

返回 (1,a)

无论事务 2 以共享锁还是独占锁方式读取数据，都会被阻塞直到事务 1 提交。

BEGIN;
SELECT * FROM `demo`
WHERE id = 1
LOCK IN SHARE MODE;

被阻塞

BEGIN;
SELECT * FROM `demo`
WHERE id = 1
FOR UPDATE;

被阻塞

同样，若只要某个记录没有被加独占锁，任何方式都可读取成功。

若以普通查询方式读取数据，不论事务 1 是采用 LOCK IN SHARE MODE 还是 FOR UPDATE 形式查询，事务 2 都不会被阻塞。

BEGIN;
SELECT * FROM demo;
COMMIT;

不被阻塞。

2. 写操作

1.1 DELETE

当不论事务 1 给记录加 S 锁还是 X 锁，事务 2 删除同一条记录都会被阻塞。

# 事务 1 查询记录 1
BEGIN;
SELECT * FROM `demo`
WHERE id = 1
LOCK IN SHARE MODE;

返回 (1,a)

# 事务 2 查询记录 21
BEGIN;
DELETE FROM `demo`
WHERE id = 1;

被阻塞

但若事务 2 删除 其他记录则不会被阻塞

事务 2 删除记录 2
BEGIN;
DELETE FROM `demo`
WHERE id = 2;

成功

2. UPDATE

同样，不论事务 1 使用何种锁，更新同一条记录会被阻塞。

### 事务 2 更新被事务 1 锁定的记录
BEGIN;
UPDATE `demo`
SET val = 'aa'
WHERE id = 1;

阻塞

3. INSERT

事务 1 对当前所有记录加共享锁。

BEGIN;
SELECT * FROM `demo`
LOCK IN SHARE MODE;

返回记录 1、2、3

若事务 2 插入事务 1 锁定记录主键之外的数据，成功。

BEGIN;
INSERT INTO `demo`
VALUES (5, 'aa');

事务 1 对当前所有记录加独占锁。

BEGIN;
SELECT * FROM `demo`
LOCK IN SHARE MODE;

返回记录 1、2、3

若事务 2 插入事务 1 锁定记录主键之外的数据，被阻塞。

BEGIN;
INSERT INTO `demo`
VALUES (5, 'aa');

阻塞

若事务 1 和事务 2 插入同一个主键的记录（事务 1 先执行），则事务 2 会阻塞，等事务 1 提交后再报错。

当事务 1 开使后，当事务 2 执行了一个插入操作并提交（在事务 1 执行查询之前执行），事务 1 使用一致性非锁定读查不到数据（即没有出现幻读），但是使用锁定读（快照读）后会查到新增的数据（即出现了幻读）。

## 比事务 2 开启时间早
BEGIN;
## 读不到新增数据
SELECT * FROM `demo`
WHERE id <= 5;

## 可以读到新增数据
SELECT * FROM `demo`
WHERE id <= 5
FOR UPDATE;
COMMIT;

3. 总结

共享锁（S锁）与排他锁（X锁）的互兼容条件如下。

兼容性	X	S
X	不兼容	不兼容
X	不兼容	兼容