数据库原理与应用
1 / 6
引入

同一张卡，同时刷两次

引例　一张卡，两个人用

你和妈妈共用一张银行卡，卡里有 1000 元。同一秒钟，你在便利店刷了 100 元，妈妈在菜市场刷了 200 元。卡里应该剩 700 元——可数据库要是没管好，剩下的可能是 800 元，甚至 900 元。

很多人同时使用同一份数据，怎样保证结果算得对？这就是本章要解决的问题。

重点并发：多个用户同时使用数据库。速度快，但不加控制，数据就会算错。

串行：一个用完下一个再用，不会出错，但大家都在排队。
并发：大家一起用，速度快，但要有规矩，免得互相干扰。数据库选的是并发，再用事务、隔离级别、封锁这三样来定规矩。

动手看两个人同时刷卡
你便利店 −100
卡里余额1000
妈妈菜市场 −200
    点「同时刷卡」，看余额最后是多少。
    1

    事务：要么全做，要么全不做

    引例　转账做到一半，停电了

    张三给李四转 100 元，数据库要做两件事：张三的余额减 100，李四的余额加 100。如果第一件做完就停电了，张三的钱扣了，李四却没收到——100 元凭空消失。

    这两步必须绑在一起：要么都做完，要么都不做。绑在一起的这一组操作，就叫事务。

    重点事务：一组操作，要么全部做完，要么全部不做。
    事务的三条语句
    1. 开始
      START TRANSACTION;
    2. 提交
      COMMIT; -- 全部生效
    3. 回滚
      ROLLBACK; -- 全部撤销
    事务的四个性质：ACID
    A

    原子性　要么全做，要么全不做。转到一半停电，重启后自动撤销已扣的钱。

    C

    一致性　事务前后，数据都是合理的。转账前后，两人的钱加起来不变。

    I

    隔离性　同时运行的事务互不干扰。别人看不到「已经扣了、还没加」的中间状态。

    D

    持久性　提交以后，结果不会丢。COMMIT 之后马上停电，重启后转账仍然有效。

    动手看张三给李四转 100 元
    张三
    1000未提交
    李四
    1000未提交
    mysql>
    按顺序点：START TRANSACTION → ① → ② → COMMIT。也可以中途点 ROLLBACK 或「停电重启」。

    记住后面讲的隔离级别和封锁，都是为了守住 ACID 里的 I（隔离性）。

    2

    四种并发问题

    引例　生活里的四件事

    刷卡算错，只是其中一种。再想三件事：同事说「老板要给大家涨工资」，你转身就告诉了家人，第二天老板说只是随口一提；早上看天气预报是 25℃，中午再看变成了 15℃；数班级群的人数，转头再数，多了两个人。

    这四件事，正好对应事务并发时的四种错误。点任意一种，按时间顺序看它是怎么发生的。

    重点丢失更新＝被覆盖；脏读＝读到没提交的；不可重复读＝同一行的值变了；幻象读＝行数变了。
    丢失更新

    两个事务改同一个数据，后写的把先写的覆盖了。两个人同时刷卡

    脏读

    读到别的事务还没提交的数据；对方一回滚，读到的就是假的。老板「随口一提」的涨工资

    不可重复读

    同一事务里读同一行两次，值不一样：中间别人改了，并且提交了。天气预报中途更新了

    幻象读

    同一事务里按同一条件查两次，行数不一样：中间别人插入或删除了行。群里多了两个人

    易混判断时先看对方提交没有：没提交就被读到，是脏读。已经提交了，再看变的是值（不可重复读）还是行数（幻象读）。

    动手看按时间顺序播放
    时间事务 T1余额 R事务 T2
    3

    隔离级别：挡住哪些问题

    引例　看通知的四种规矩

    学院要发一份通知。最松的规矩：别人还在起草，你就能看到草稿；紧一点：只看盖了章的正式通知；再紧一点：开始办事时拍张照，之后一直看照片，中途改了也不影响你；最紧：大家排队，一个人看完，下一个再看。

    规矩越严，看到的越可靠，大家也等得越久。隔离级别就是这四档规矩。

    重点每升一级，多挡一种问题：脏读 → 不可重复读 → 幻象读。级别越高越安全，也越慢。
    四种隔离级别（SQL 标准）
    隔离级别脏读不可重复读幻象读
    READ UNCOMMITTED读未提交✗✗✗
    READ COMMITTED读已提交✓✗✗
    REPEATABLE READ可重复读MySQL 默认✓✓✗
    SERIALIZABLE串行化✓✓✓

    ✓ 表示能挡住，✗ 表示挡不住。丢失更新四个级别都能挡住，表里不列。

    查看和设置
    在当前连接中执行
    -- 查看当前级别
SELECT @@transaction_isolation;
-- 把当前连接设为「读已提交」
SET SESSION TRANSACTION ISOLATION LEVEL
    READ COMMITTED;

    SESSION 只改当前这个连接；换成 GLOBAL 改的是以后新建的连接。

    动手看选一档隔离级别
    脏读
    不可重复读
    幻象读
    安全
    速度
    4

    封锁：X 锁和 S 锁

    引例　自习室占座

    想用自习室的一个座位，先把书包放上去，别人就不会来坐；走的时候拿走书包，座位才空出来。墙上的课表则不同：大家可以围着一起看，但有人在看的时候，谁也不能把它撕下来改。

    放书包就是加锁，拿走就是释放锁。要改的东西得独占，只看的东西可以共享。

    重点X 锁（写锁）独占，S 锁（读锁）共享。只有 S 锁和 S 锁能同时加。
    X 锁

    排它锁，也叫写锁。加锁的事务能读能写，其他事务读、写都要等。

    S 锁

    共享锁，也叫读锁。大家都能读，谁都不能写，加锁的事务自己也不能写。

    能不能同时加？
    已经加了 ↓　再申请 →S 锁X 锁
    S 锁可以等待
    X 锁等待等待
    MySQL 给整张表加锁
    1. S 锁
      LOCK TABLES account READ;
    2. X 锁
      LOCK TABLES account WRITE;
    3. 释放
      UNLOCK TABLES;
    了解：封锁协议什么时候加锁、什么时候放
    协议规则能防止
    一级写之前加 X 锁，事务结束才放丢失更新
    二级一级 + 读之前加 S 锁，读完就放再加上脏读
    三级一级 + 读之前加 S 锁，事务结束才放再加上不可重复读

    另外，锁的范围可大可小：表级锁锁整张表，行级锁只锁用到的行。MySQL 的 InnoDB 默认用行级锁。

    动手看T2 能不能拿到锁？
    T1 已加 T2 申请
    T1事务 1
    account表
    T2事务 2还没申请
    选好两边，点「申请」。
    5

    死锁：互相等，谁也动不了

    引例　独木桥上碰面

    两个人从独木桥的两头同时走上桥，在中间碰面了。你等他退回去，他等你退回去，谁都不肯让——两个人就一直僵在桥上。

    锁也会带来这种僵局：两个事务各拿着一把锁，又都在等对方手里的那一把。

    重点死锁：两个事务互相等对方释放锁，谁也执行不下去。预防的办法：所有事务按相同的顺序加锁。
    怎样预防
    顺序加锁

    规定加锁顺序，例如都先锁学生表、再锁成绩表。后来的只是排队，不会互相卡住。

    一次加锁

    一开始就把要用的锁全部申请，要么都拿到，要么都不拿。

    真的发生了也不用慌：MySQL 会自动发现死锁，回滚其中一个事务（报错 1213），让另一个接着执行。被回滚的那个，重新执行一次即可。

    动手看T1 和 T2 都要用 s 表和 sc 表
    s学生表空闲
    sc成绩表空闲
    T1 等 T2，T2 等 T1：死锁
    时间事务 T1事务 T2
    点「播放」。