课程 › 第 9 章 › 实验三
第 9 章 · 实验三 · 在第四节之后

封锁与死锁

用两个窗口观察行级锁和表级锁：同时修改同一行、给表加写锁和读锁、让读锁保持到事务结束，最后制造一次死锁。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 3-1同时修改同一行
  2. 实验 3-2表级写锁
  3. 实验 3-3表级读锁
  4. 实验 3-4读锁保持到事务结束
  5. 实验 3-5死锁
第一步

数据准备（必做）

与实验一相同的脚本，执行后三个账户回到初始余额。

整段复制，全部执行下载 .sql
-- 第 9 章实验数据：银行账户库 bankaccount 和账户表 account
-- 三次实验共用。整段执行一次即可，想从头再做时重新执行。
CREATE DATABASE IF NOT EXISTS bankaccount DEFAULT CHARACTER SET utf8mb4;
USE bankaccount;
DROP TABLE IF EXISTS account;
-- id 账户号，R 余额，datetime 最近一次操作的时间
CREATE TABLE account (
  id VARCHAR(20) NOT NULL,
  R DECIMAL(10,2) NOT NULL,
  datetime DATETIME,
  PRIMARY KEY (id)
);
INSERT INTO account VALUES
('account1', 500.00, '2026-09-01 09:00:00'),
('account2', 900.00, '2026-09-01 09:00:00'),
('account3', 900.00, '2026-09-01 09:00:00');
SELECT * FROM account;
3-1

同时修改同一行

讲义 · 封锁与封锁粒度讲义 · X 锁和 S 锁
目的
两个事务同时修改同一行，观察行级排它锁，验证不会丢失修改。
前提
已执行数据准备，两个窗口都已执行 USE bankaccount。

要用两个窗口：在 Workbench 首页再点一次同一个连接，会打开第二个连接标签页。两个标签页是两个独立的会话，下面称为窗口 A 和窗口 B，两个窗口都先执行 USE bankaccount;。每一步只执行这一步的语句：选中这几行，按 Ctrl+Shift+Enter。标着“等待”的一步，语句会一直处于执行状态。这时要在 20 秒内切到另一个窗口做下一步，否则 Workbench 会报超时。

  1. 窗口 A 开始事务，从 account1 取出 100 元，不提交
  2. 窗口 B 开始事务，从 account1 取出 200 元
  3. 窗口 A 提交，看窗口 B 的情况
  4. 窗口 B 提交并查询 account1

参考答案　按步骤在两个窗口中执行

步骤窗口 B
1
窗口 A
START TRANSACTION;
UPDATE account SET R = R - 100
WHERE id = 'account1';

A 给 account1 这一行加上了 X 锁

2
窗口 B
START TRANSACTION;
UPDATE account SET R = R - 200
WHERE id = 'account1';

等待

3
窗口 A
COMMIT;

A 释放锁，B 的修改完成

4
窗口 B
COMMIT;
SELECT * FROM account WHERE id = 'account1';

account1 是 200

语句解释

UPDATE 加锁修改一行之前，MySQL 自动给这一行加 X 锁，直到事务结束才释放
B 等待X 锁不相容，B 要等 A 的事务结束
R = R - 200B 等到的是 A 提交后的 400，再减 200，两次取款都算数
执行后看：B 的 UPDATE 等待，A 提交后才完成。最后 account1 是 500 - 100 - 200 = 200，没有丢失修改。
3-2

表级写锁

讲义 · LOCK TABLES 的例子
目的
按例 9-5，窗口 A 给 account 表加写锁，窗口 B 读取这张表。
前提
已完成实验 3-1。

标着“等待”的一步，语句会一直处于执行状态。这时要在 20 秒内切到另一个窗口做下一步，否则 Workbench 会报超时。

  1. 窗口 A 给 account 表加写锁
  2. 窗口 B 查询 account 表
  3. 窗口 A 解锁，看窗口 B 的情况

参考答案　按步骤在两个窗口中执行

步骤窗口 B
1
窗口 A
LOCK TABLES account WRITE;
SELECT * FROM account;

A 加上写锁后自己可以读写

2
窗口 B
SELECT * FROM account;

等待

3
窗口 A
UNLOCK TABLES;

A 解锁，B 的查询马上返回结果

语句解释

LOCK TABLES account WRITE给整张表加写锁，也就是 X 锁
B 等待表上有 X 锁时，其他会话连读都要等待
UNLOCK TABLES释放当前会话加的全部表锁
执行后看：B 的查询一直等待，A 执行 UNLOCK TABLES 后才显示结果。
3-3

表级读锁

讲义 · LOCK TABLES 的例子讲义 · X 锁和 S 锁
目的
按例 9-6，窗口 A 给 account 表加读锁，看 A 和 B 各自能做什么。
前提
已完成实验 3-2。

标着“等待”的一步，语句会一直处于执行状态。这时要在 20 秒内切到另一个窗口做下一步，否则 Workbench 会报超时。

  1. 窗口 A 给 account 表加读锁，查询后试着修改
  2. 窗口 B 查询这张表，再加一次读锁，然后解锁
  3. 窗口 B 修改 account2 的余额
  4. 窗口 A 解锁，看窗口 B 的情况

参考答案　按步骤在两个窗口中执行

步骤窗口 B
1
窗口 A
LOCK TABLES account READ;
SELECT * FROM account;
-- 加了读锁，自己也不能修改，这一句会报错 1099
UPDATE account SET R = 800
WHERE id = 'account1';

报错 1099

2
窗口 B
SELECT * FROM account;
LOCK TABLES account READ;
UNLOCK TABLES;

B 能读，也能再加读锁

3
窗口 B
UPDATE account SET R = 600 WHERE id = 'account2';

等待

4
窗口 A
UNLOCK TABLES;

A 解锁后，B 的修改完成

语句解释

LOCK TABLES account READ给整张表加读锁，也就是 S 锁
报错 1099Table was locked with a READ lock，加了读锁的会话只能读
B 再加读锁S 锁和 S 锁相容，几个会话可以同时加读锁
B 修改等待表上有 S 锁时不能加 X 锁，要等读锁释放
执行后看：A 的 UPDATE 报错 1099。B 能查询、能加读锁，但 UPDATE 要等 A 解锁后才完成。
3-4

读锁保持到事务结束

讲义 · 三级封锁协议
目的
用 FOR SHARE 给读到的行加 S 锁并保持到事务结束，验证三级封锁协议能保证可重复读。
前提
已完成实验 3-3。

标着“等待”的一步，语句会一直处于执行状态。这时要在 20 秒内切到另一个窗口做下一步，否则 Workbench 会报超时。

  1. 窗口 A 开始事务，读取 account2 的余额并加 S 锁
  2. 窗口 B 给 account2 存入 100 元
  3. 窗口 A 再读一次，然后提交，看窗口 B 的情况

参考答案　按步骤在两个窗口中执行

步骤窗口 B
1
窗口 A
START TRANSACTION;
SELECT * FROM account WHERE id = 'account2' FOR SHARE;

A 给 account2 加 S 锁

2
窗口 B
UPDATE account SET R = R + 100 WHERE id = 'account2';

等待

3
窗口 A
SELECT * FROM account WHERE id = 'account2' FOR SHARE;
COMMIT;

A 两次读到的相同。提交后 S 锁释放，B 的修改完成

语句解释

FOR SHARE读取时给这些行加 S 锁，保持到事务结束
三级封锁协议读之前加 S 锁，直到事务结束才释放，其他事务不能修改，两次读的结果一定相同
B 等待B 要加 X 锁，与 A 的 S 锁不相容
执行后看：A 两次都读到 600。B 的 UPDATE 等待，A 提交后完成，account2 变为 700。
3-5

死锁

讲义 · 死锁
目的
让两个事务按相反的顺序修改两行，制造一次死锁，看 MySQL 怎样处理。
前提
已完成实验 3-4。

标着“等待”的一步，语句会一直处于执行状态。这时要在 20 秒内切到另一个窗口做下一步，否则 Workbench 会报超时。

  1. 窗口 A 开始事务，修改 account1
  2. 窗口 B 开始事务，修改 account2
  3. 窗口 A 修改 account2
  4. 窗口 B 修改 account1
  5. 窗口 A 提交，查看两个账户

参考答案　按步骤在两个窗口中执行

步骤窗口 B
1
窗口 A
START TRANSACTION;
UPDATE account SET R = R - 100
WHERE id = 'account1';

A 锁住 account1

2
窗口 B
START TRANSACTION;
UPDATE account SET R = R - 100
WHERE id = 'account2';

B 锁住 account2

3
窗口 A
UPDATE account SET R = R + 100 WHERE id = 'account2';

等待 B 释放 account2

4
窗口 B
-- 这一句会报错 1213
UPDATE account SET R = R + 100
WHERE id = 'account1';

报错 1213，B 的事务被回滚

5
窗口 A
COMMIT;
SELECT * FROM account;

A 的两次修改都完成

语句解释

死锁A 等 B 的 account2，B 等 A 的 account1，形成循环等待
报错 1213Deadlock found，MySQL 发现死锁后回滚其中一个事务，另一个继续执行
避免死锁约定所有事务按相同的顺序访问数据，例如都先改 account1 再改 account2
执行后看：B 的第二次修改报错 1213，B 的事务被撤销。A 等待结束，提交后 account1 是 100，account2 是 800。
保山学院·人工智能教研室·曹鼎鼎