数据库原理与应用
1 / 7
0

准备

情景　并发要两个人同时操作。我们打开两个窗口：窗口 A 扮演事务 T1，窗口 B 扮演事务 T2，用一张银行账户表来做实验。

  1. 在 Workbench 首页双击 root 的连接两次，打开两个标签页。把第一个当作 窗口 A，第二个当作 窗口 B。

    注意要从首页再双击一次连接。在同一个标签页里按 Ctrl+T 新建的查询页，用的还是同一个连接，做不了实验。

    应看到顶部有两个 root 标签页。

  2. 窗口 A 复制下面整段，粘贴进编辑区，按 Ctrl+Shift+Enter 全部执行。

    窗口 A 中整段执行下载 .sql
    -- 第 9 章上机练习：准备数据（在窗口 A 中整段执行，可以反复执行）
-- 重新执行前，先在两个窗口都执行 ROLLBACK; 和 UNLOCK TABLES;

DROP DATABASE IF EXISTS bankaccount;
CREATE DATABASE bankaccount DEFAULT CHARACTER SET utf8mb4;
USE bankaccount;

-- 银行账户表：id 账户，R 余额
CREATE TABLE account (
  id VARCHAR(20) PRIMARY KEY,
  R  DECIMAL(10,2) NOT NULL
);

INSERT INTO account VALUES
('account1', 500),
('account2', 900),
('account3', 900);

SELECT * FROM account;

    应看到3 行：account1 = 500.00，account2 = 900.00，account3 = 900.00。

  3. 窗口 A 窗口 B 两个窗口都执行下面两句：进入 bankaccount 库，再看看自己的连接编号。

    窗口 A 和 窗口 B 都执行
    USE bankaccount;
SELECT CONNECTION_ID();

    技巧以后每一步，先清空编辑区再粘贴；或者选中要执行的几行，再按 Ctrl+Shift+Enter。这样不会把前面的语句再执行一遍。

    应看到两个窗口的编号不一样——它们是两个独立的连接，可以各自开事务。

1

提交与回滚

情景　account1 给 account2 转 100 元。先转一次再「反悔」，再转一次真的提交。同时让窗口 B 在旁边看着：没提交的修改，别人看得到吗？

  1. 窗口 A 开始事务，转账，查看结果。

    窗口 A 中执行
    START TRANSACTION;
UPDATE account SET R = R - 100 WHERE id = 'account1';
UPDATE account SET R = R + 100 WHERE id = 'account2';
SELECT * FROM account;

    应看到account1 = 400.00，account2 = 1000.00。这是窗口 A 自己看到的，还没提交。

  2. 窗口 B 查一下账户表。

    窗口 B 中执行
    SELECT * FROM account;

    应看到还是 500.00 和 900.00。A 没提交，B 看不到 A 的修改——这就是隔离性。

  3. 窗口 A 「反悔」：回滚，再查。

    窗口 A 中执行
    ROLLBACK;
SELECT * FROM account;

    应看到回到 500.00 和 900.00。两条 UPDATE 一起被撤销了。

  4. 窗口 A 再转一次，这次提交；然后到 窗口 B 再查。

    窗口 A 中执行
    START TRANSACTION;
UPDATE account SET R = R - 100 WHERE id = 'account1';
UPDATE account SET R = R + 100 WHERE id = 'account2';
COMMIT;
    窗口 B 中执行
    SELECT * FROM account;

    应看到B 看到 400.00 和 1000.00。提交以后，修改才对别人可见。

  5. 窗口 A 收尾：把余额改回去，给后面的实验用。

    窗口 A 中执行
    UPDATE account SET R = 500 WHERE id = 'account1';
UPDATE account SET R = 900 WHERE id = 'account2';

    应看到两句都显示 1 row(s) affected。

2

看见脏读

情景　把窗口 A 调到最低的隔离级别「读未提交」，看它会不会读到窗口 B 还没提交的数据。

  1. 窗口 A 设为 READ UNCOMMITTED，开始事务，查 account1。

    窗口 A 中执行
    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM account WHERE id = 'account1';

    应看到account1 = 500.00。

  2. 窗口 B 开始事务，给 account1 加 100，不要提交。

    窗口 B 中执行
    START TRANSACTION;
UPDATE account SET R = R + 100 WHERE id = 'account1';

    应看到1 row(s) affected。

  3. 窗口 A 再查一次。

    窗口 A 中执行
    SELECT * FROM account WHERE id = 'account1';

    应看到变成了 600.00，可 B 还没提交——这就是脏读。

  4. 窗口 B 回滚；再回 窗口 A 查一次，然后结束事务。

    窗口 B 中执行
    ROLLBACK;
    窗口 A 中执行
    SELECT * FROM account WHERE id = 'account1';
COMMIT;

    应看到又是 500.00。A 刚才读到的 600 从来没有真正生效过。

3

读已提交

情景　窗口 A 升一级，改为「读已提交」。脏读还会发生吗？B 提交以后，A 前后两次读到的一样吗？

  1. 窗口 A 设为 READ COMMITTED，开始事务，查 account1。

    窗口 A 中执行
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM account WHERE id = 'account1';

    应看到account1 = 500.00。

  2. 窗口 B 开始事务，给 account1 加 100，不要提交。

    窗口 B 中执行
    START TRANSACTION;
UPDATE account SET R = R + 100 WHERE id = 'account1';

    应看到1 row(s) affected。

  3. 窗口 A 再查一次。

    窗口 A 中执行
    SELECT * FROM account WHERE id = 'account1';

    应看到还是 500.00。没提交的修改读不到，脏读被挡住了。

  4. 窗口 B 提交。

    窗口 B 中执行
    COMMIT;

    应看到执行成功。account1 现在真的是 600 了。

  5. 窗口 A 还在同一个事务里，第三次查。

    窗口 A 中执行
    SELECT * FROM account WHERE id = 'account1';

    应看到变成 600.00。同一个事务里三次读到 500、500、600——这就是不可重复读。

  6. 窗口 A 收尾：结束事务，把 account1 改回 500。

    窗口 A 中执行
    COMMIT;
UPDATE account SET R = 500 WHERE id = 'account1';

    应看到1 row(s) affected。

4

可重复读

情景　再升一级，改回 MySQL 的默认级别「可重复读」。这次 B 改完直接提交，看 A 在自己的事务里还会不会读到变化。

  1. 窗口 A 设为 REPEATABLE READ，开始事务，查 account1。

    窗口 A 中执行
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM account WHERE id = 'account1';

    应看到account1 = 500.00。

  2. 窗口 B 给 account1 加 100，直接提交。

    窗口 B 中执行
    START TRANSACTION;
UPDATE account SET R = R + 100 WHERE id = 'account1';
COMMIT;

    应看到三句都执行成功。

  3. 窗口 A 再查一次。

    窗口 A 中执行
    SELECT * FROM account WHERE id = 'account1';

    应看到还是 500.00。B 已经提交了，A 在自己的事务里读到的仍和第一次一样——不可重复读被挡住了。

  4. 窗口 A 结束事务，再查。

    窗口 A 中执行
    COMMIT;
SELECT * FROM account WHERE id = 'account1';

    应看到600.00。事务结束后，才看到 B 的修改。

  5. 窗口 A 收尾：把 account1 改回 500。

    窗口 A 中执行
    UPDATE account SET R = 500 WHERE id = 'account1';

    应看到1 row(s) affected。

5

X 锁

情景　窗口 A 给账户表加上 X 锁（写锁），就像把书包放在座位上。这时窗口 B 连「看一眼」都要等。

  1. 窗口 A 加 X 锁，再查。

    窗口 A 中执行
    LOCK TABLES account WRITE;
SELECT * FROM account;

    应看到正常查出 3 行。持有 X 锁的 A 自己能读能写。

  2. 窗口 B 试着查账户表。

    窗口 B 中执行
    SELECT * FROM account;

    应看到一直在转圈，没有结果——被 A 的 X 锁挡住了。

    要快Workbench 等大约 30 秒还没结果，会报 2013 并断开。请在 30 秒内做下一步。

  3. 窗口 A 释放锁，然后马上看 窗口 B。

    窗口 A 中执行
    UNLOCK TABLES;

    应看到A 一放锁，B 立刻出结果。X 锁是独占的：别人读、写都要等。

6

S 锁

情景　这次加 S 锁（读锁），就像大家围着看墙上的课表：两个人可以一起看，但谁都不能改。

  1. 窗口 A 加 S 锁，再查。

    窗口 A 中执行
    LOCK TABLES account READ;
SELECT * FROM account;

    应看到正常查出 3 行。

  2. 窗口 B 也加 S 锁，再查。

    窗口 B 中执行
    LOCK TABLES account READ;
SELECT * FROM account;

    应看到立刻出结果，不用等。S 锁和 S 锁可以同时加。

  3. 窗口 A 拿着 S 锁，试着改数据。

    窗口 A 中执行
    UPDATE account SET R = 100 WHERE id = 'account1';

    应看到报错 1099：表加的是 READ 锁，不能更新。持有 S 锁的事务自己也不能写。

  4. 窗口 B 先放掉自己的锁，再试着改数据。

    窗口 B 中执行
    UNLOCK TABLES;
UPDATE account SET R = 500 WHERE id = 'account1';

    应看到UPDATE 一直在转圈：A 的 S 锁还在，谁都不能写。

    要快同样请在 30 秒内做下一步。这里把余额改成原来的 500，值不变，只是为了看看「写」要不要等。

  5. 窗口 A 释放锁，然后看 窗口 B。

    窗口 A 中执行
    UNLOCK TABLES;

    应看到B 的 UPDATE 马上执行完（值本来就是 500，所以显示 0 row(s) affected）。

练习结束

回头对一对：你亲眼看到的现象，分别对应讲义里的哪一条？

任务看到了什么对应
1没提交的修改，B 看不到；回滚后全部撤销原子性、隔离性
2读未提交：读到 B 没提交的 600脏读
3读已提交：读不到没提交的，但三次结果 500、500、600挡住脏读，挡不住不可重复读
4可重复读：B 提交了，A 读到的仍不变挡住不可重复读
5A 加 X 锁，B 连读都要等X 锁独占
6两边都能加 S 锁；但谁都不能写只有 S + S 能同时加
回看理论讲义返回第 9 章