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 和事务 2 插入同一个主键的记录（事务 1 先执行），则事务 2 会阻塞，等事务 1 提交后再报错。
3. 总结
共享锁（S锁）与排他锁（X锁）的互兼容条件如下。
| 兼容性 | X | S |
|---|---|---|
| X | 不兼容 | 不兼容 |
| X | 不兼容 | 兼容 |