事务与并发控制：当多人同时操作数据库

事务与并发控制当多人同时操作数据库一句话总结事务通过 ACID 特性保证操作的完整性并发控制通过锁机制和多版本并发控制MVCC协调多个事务同时执行防止丢失修改、读脏数据、不可重复读和幻读四大问题。一、从一个生活场景说起想象你和同事同时操作公司的财务数据库你从 A 账户转 1000 元到 B 账户同事同时查询 A 账户的余额用于报表如果没有事务和并发控制会发生什么你的转账做了一半A 已扣款B 还没加款系统崩溃了——钱去哪了同事读到了你扣款后、加款前的中间状态——报表数据是错的两人同时修改同一条记录——一个人的修改被另一个人覆盖这些问题正是事务与并发控制要解决的核心。二、事务要么全做要么全不做2.1 什么是事务事务Transaction是用户定义的一个数据库操作序列这些操作要么全部执行要么全部不执行是一个不可分割的工作单位。2.2 事务的 ACID 特性特性英文含义类比原子性Atomicity事务中的操作要么全部成功要么全部回滚转账扣款和加款必须同时完成一致性Consistency事务执行前后数据库从一个一致状态变到另一个一致状态转账前后总金额不变隔离性Isolation多个事务并发执行时互不干扰你转账同事查账互不干扰持久性Durability事务一旦提交结果永久保存转账成功后即使断电钱也不会变回来2.3 SQL 中的事务控制-- 开始事务BEGINTRANSACTION;-- 或 START TRANSACTION;-- 执行操作UPDATE账户SET余额余额-1000WHERE账户号A;UPDATE账户SET余额余额1000WHERE账户号B;-- 提交事务永久保存COMMIT;-- 或者回滚事务撤销所有操作-- ROLLBACK;2.4 事务的状态BEGIN │ ▼ ┌───────────────┐ │ 活动状态 │ ← 正在执行操作 └───────┬───────┘ │ ┌───────┴───────┐ ▼ ▼ ┌─────────┐ ┌─────────┐ │ 部分提交 │ │ 失败状态 │ │ (COMMIT)│ │ (ROLLBACK) └────┬────┘ └────┬────┘ │ │ ▼ ▼ ┌─────────┐ ┌─────────┐ │ 提交状态 │ │ 中止状态 │ │ (持久化)│ │ (回滚完成) └─────────┘ └─────────┘三、并发操作带来的四大问题当多个事务同时操作数据库时如果不加控制会出现以下问题3.1 丢失修改Lost Update两个事务同时读取同一数据然后都修改后提交的事务覆盖了先提交的事务。时间事务 A事务 BT1读取余额 1000T2读取余额 1000T3余额 1000 100 1100T4余额 1000 200 1200T5提交T6提交覆盖了 A 的修改结果A 的 100 操作丢失了最终余额是 1200 而非 1300。3.2 读脏数据Dirty Read一个事务读取了另一个事务尚未提交的数据而那个事务后来回滚了。时间事务 A事务 BT1修改余额为 1200未提交T2读取余额 1200脏数据T3回滚余额恢复为 1000T4基于 1200 做决策错误3.3 不可重复读Non-repeatable Read同一事务内两次读取同一数据结果不一样因为另一个事务在两次读取之间修改并提交了。时间事务 A事务 BT1读取余额 1000T2修改余额 800提交T3再次读取余额 800变了不可重复读强调同一事务内两次读取结果不一致。3.4 幻读Phantom Read同一事务内两次查询条件相同的数据集合但第二次出现了新的行另一个事务插入并提交。时间事务 A事务 BT1SELECT COUNT(*) FROM 学生 WHERE 系‘计算机’ → 100T2INSERT 一名计算机系学生提交T3再次执行相同查询 → 101幻行出现幻读和不可重复读的区别不可重复读是某行的值变了幻读是行数变了。四、事务隔离级别在并发与性能之间权衡SQL 标准定义了四种隔离级别级别越高并发问题越少但性能越低。隔离级别丢失修改脏读不可重复读幻读实现复杂度READ UNCOMMITTED❌✅✅✅低READ COMMITTED❌❌✅✅中REPEATABLE READ❌❌❌✅高SERIALIZABLE❌❌❌❌最高❌ 不会发生✅ 可能发生4.1 设置隔离级别-- MySQLSETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;SETSESSIONTRANSACTIONISOLATIONLEVELSERIALIZABLE;-- PostgreSQLSETTRANSACTIONISOLATIONLEVELSERIALIZABLE;-- SQL ServerSETTRANSACTIONISOLATIONLEVELREADCOMMITTED;4.2 各数据库默认隔离级别数据库默认隔离级别说明MySQL (InnoDB)REPEATABLE READ默认解决不可重复读但可能有幻读PostgreSQLREAD COMMITTED默认解决脏读OracleREAD COMMITTEDSQL ServerREAD COMMITTED五、并发控制锁机制5.1 锁的基本类型锁类型英文含义兼容共享锁S Lock / Shared Lock读锁可以多人同时加S 与 S 兼容S 与 X 不兼容排他锁X Lock / Exclusive Lock写锁只能一人持有X 与任何锁都不兼容5.2 锁的粒度粒度范围并发度开销行锁一行高大页锁一页通常 8KB中中表锁整张表低小数据库锁整个库最低最小InnoDB 默认使用行锁并发度高MyISAM 使用表锁并发度低。5.3 封锁协议一级封锁协议事务修改数据前加X 锁直到事务结束才释放解决丢失修改二级封锁协议一级协议 读取数据前加S 锁读完后立即释放解决丢失修改 读脏数据三级封锁协议一级协议 读取数据前加S 锁直到事务结束才释放解决丢失修改 读脏数据 不可重复读┌────────────────────────────────────────┐ │ 隔离级别 vs 封锁协议对应关系 │ ├────────────────────────────────────────┤ │ READ UNCOMMITTED → 无锁或最低级别 │ │ READ COMMITTED → 二级封锁协议 │ │ REPEATABLE READ → 三级封锁协议 │ │ SERIALIZABLE → 三级封锁协议 锁全表│ └────────────────────────────────────────┘5.4 活锁与死锁活锁Livelock事务 A 申请锁事务 B 也申请同一锁但 B 优先级更高A 一直等待永远得不到执行。解决采用先来先服务FIFO策略避免优先级导致的饥饿。死锁Deadlock两个事务互相等待对方持有的锁形成循环等待。时间事务 A事务 BT1加 X 锁 on 记录 1T2加 X 锁 on 记录 2T3请求 X 锁 on 记录 2等待 BT4请求 X 锁 on 记录 1等待 AT5死锁死锁死锁的解决策略预防一次加锁法事务开始时一次性加所有需要的锁、顺序加锁法按固定顺序加锁检测与解除设置超时时间超时后自动回滚其中一个事务牺牲者等待图DBMS 维护等待图发现环就选择一个事务回滚六、MVCC多版本并发控制6.1 什么是 MVCCMVCCMulti-Version Concurrency Control是不用锁也能实现并发控制的技术。核心思想每个数据项都有多个版本读操作读取的是历史版本写操作创建新版本读和写互不阻塞。6.2 MVCC 如何工作以 InnoDB 为例InnoDB 的每行数据都有两个隐藏列DB_TRX_ID创建该版本的事务 IDDB_ROLL_PTR回滚指针指向该版本的上一个版本形成版本链读操作根据事务的 Read View可见性判断规则找到对该事务可见的最新版本。写操作不直接修改原数据而是创建新版本的行旧版本保留在 Undo 日志中。6.3 MVCC 的优势读不阻塞写写不阻塞读读操作读取历史版本不会等待写锁高并发性能避免了大量锁等待实现快照读REPEATABLE READ 隔离级别下事务内两次读取结果一致七、动手练习练习 1事务基础在 MySQL 中执行以下操作观察事务的行为STARTTRANSACTION;SELECT余额FROM账户WHERE账户号A;-- 记录结果UPDATE账户SET余额余额-100WHERE账户号A;SELECT余额FROM账户WHERE账户号A;-- 查看变化ROLLBACK;-- 撤销SELECT余额FROM账户WHERE账户号A;-- 验证是否恢复练习 2隔离级别实验开启两个 MySQL 客户端窗口分别模拟事务 A 和事务 B测试不同隔离级别下的行为在 READ UNCOMMITTED 下A 修改未提交B 能否读到在 READ COMMITTED 下A 修改未提交B 能否读到A 提交后呢在 REPEATABLE READ 下A 修改并提交B 再次读结果是否变化练习 3死锁模拟在两个会话中执行-- 会话 ABEGIN;UPDATE账户SET余额余额-100WHERE账户号A;-- 暂停UPDATE账户SET余额余额-100WHERE账户号B;-- 等待 B 释放锁COMMIT;-- 会话 BBEGIN;UPDATE账户SET余额余额-100WHERE账户号B;-- 暂停UPDATE账户SET余额余额-100WHERE账户号A;-- 等待 A 释放锁COMMIT;观察哪个事务被回滚以及错误信息。八、常见误区与避坑指南误区正确理解“事务越多越好”长事务占用锁时间长降低并发性能尽量缩小事务范围“SERIALIZABLE 最安全 always 用它”隔离级别越高性能越差大多数场景 READ COMMITTED 或 REPEATABLE READ 足够“InnoDB 的默认隔离级别不会幻读”REPEATABLE READ 下 InnoDB 通过 MVCC 间隙锁解决了大部分幻读但某些场景仍可能“锁越粗越好”粗粒度锁并发度低细粒度锁并发度高但管理复杂“ROLLBACK 很慢”ROLLBACK 利用 Undo 日志恢复通常很快不要因担心性能而不用九、下篇预告下一篇我们将学习数据库恢复技术——当数据库遭遇故障断电、崩溃、误操作时如何通过日志、检查点和备份把数据救回来。