数据库系统课往后的实验一次比一次像真正的工程。前几次实验还在跟建表、增删改查、视图这些“能跑就行”的内容较劲到了实验四直接把事务、并发控制、隔离级别、锁、触发器、索引优化全部塞进来纯靠背概念根本写不出实验报告。我这里记录的是某高校软件学院数据库系统实验四的完整实现过程包括我自己踩过的坑、实测过的现象以及每一步背后的设计逻辑。如果你正在发愁隔离级别到底怎么复现、死锁怎么触发、触发器为什么没生效这篇文章应该能一次性讲透。实验四的核心我总结下来就一句话模拟一个高并发业务场景用事务保证数据一致性通过调整并发控制手段观察异常现象再用约束、触发器、索引把系统的健壮性补上。它不是让你证明“我会写SQL”而是逼着你去理解数据库在真正面对多用户访问时如何保证不丢数据、不错账、不崩库。1. 实验四到底想让你学会什么1.1 从建表查询到并发控制难度跳过了一个台阶数据库系统这门课一般前面三次实验都是“打地基”实验一熟悉环境建库建表实验二练 SQL 查询多表连接、子查询、聚合函数实验三开始接触视图、存储过程、触发器的基本用法。这些内容本质上还是在教你“如何把数据按需求拿出来”。但实验四不一样它已经进入数据库内核层面的并发控制了。很多同学在这里第一次意识到数据库不只是存数据的文件它需要同时处理几十上百个会话的读写而这些会话之间会互相打架。比如经典的“丢失更新”问题两个人同时给同一账户扣钱结果只扣了一次或者一个事务读到另一事务未提交的数据整个账目直接乱掉。这些现象在单机单用户环境下根本不会出现只有真正模拟出并发访问才会露馅。所以实验四给我的感觉是它是从“会用数据库”到“懂数据库”的分水岭。你光会写正确SQL不够你还得搞清楚为什么在某些并发场景下明明每条SQL都没写错结果却是错的。1.2 我这次选定的实验场景与表结构实验题目通常不会限定具体场景只要求用事务和并发控制完成一个业务。我选的场景是“在线票务系统 账户余额扣减”因为它足够直观用户购票要更新票务库存同时从账户扣钱任意一步失败都必须整体回滚否则就会出现“钱扣了票没买到”或者“票出了钱没扣”的脏数据。按这个需求我只建了四张表account用户账户保存余额余额字段是关键必须能承受并发修改。seat_stock票务库存表每个场次的剩余票数。orders订单表购票成功后写入一条订单。audit_log操作审计日志表用来配合触发器记录资金变动。这个场景的好处在哪它把“事务一致性”和“并发异常”都具象化了扣钱和扣库存不放在同一事务里系统就会出大问题两个事务不加锁地并发扣同一行余额就会凭空变少。后面复现脏读、不可重复读、死锁全都可以在这个小系统里完成不用额外造复杂数据。1.3 动手前需要提前啃下来的几个概念写实验四之前我建议先把下面这些概念过一遍不然做实验就是照抄网上代码做完不懂ACID原子性、一致性、隔离性、持久性这四个特性的实现原理。隔离级别读未提交、读已提交、可重复读、可串行化分别能阻止哪些异常。锁机制共享锁、排他锁、行锁、表锁、间隙锁以及锁的兼容关系。死锁发生条件、检测机制、如何避免。触发器DML 操作触发额外逻辑跟约束的区别。索引与执行计划用 EXPLAIN 分析 SQL观察索引有没有生效。概念可以用生活化方式理解。隔离级别就像朋友圈的可见范围读未提交相当于任何人可见你的“草稿”读已提交相当于只有发布后才能看到可重复读则是你看过一次的内容在你整个查看过程中保持一致。锁就像公厕里的门锁一个人进去把门锁上其他人只能等共享锁是“可多人同时查看但不能有人修改”排他锁是“只有我能改别人看都不行”。2. 实验背后的核心机制事务、锁与隔离级别2.1 ACID 到底靠什么落地而不只是一句口号数据库系统里的事务四大特性的实现并不神秘。原子性靠的是日志里的 undo 信息事务执行中如果中途失败数据库会通过 undo log 把已经改过的数据回滚到事务开始时的状态。持久性靠 redo log事务提交前先写日志就算数据库某时刻崩溃重启后也能根据 redo log 恢复已提交的数据。隔离性更复杂一点MySQL 默认用悲观锁结合多版本并发控制MVCC来实现不同隔离级别对应不同的可见性规则。一致性则是前面三个特性配合出来的结果——如果原子性、隔离性、持久性都得到保障审计业务规则和完整性约束正确那数据理论上就能一直保持合法状态。这里值得展开说的是我之前一直以为只要写了BEGIN然后一堆 SQL 最后COMMIT事务就“自动正确”了。实际上隔离性和原子性需要靠数据库机制更需要靠你的事务边界设计。比如你扣钱和扣库存不在同一个事务里哪怕每条SQL都写成事务也照样会出现两边不一致。实验四真正要练的就是设计边界和选择隔离级别而不是单纯会敲 B E G I N / C O M M I T。2.2 隔离级别与三类并发异常的关系在并发控制里最容易考也最容易实际操作的就是隔离级别。MySQL 支持四种级别读未提交READ UNCOMMITTED、读已提交READ COMMITTED、可重复读REPEATABLE READ、可串行化SERIALIZABLE。默认是可重复读PostgreSQL 默认是读已提交。实验里我建议分别设置每种级别再故意制造并发冲突然后把看到的现象记录到实验报告里。先说结论下面用表格表示隔离级别防止脏读防止不可重复读防止幻读读未提交否否否读已提交是否否可重复读是是否MySQL InnoDB 通过间隙锁基本避免可串行化是是是实测中最容易复现的是“脏读”和“不可重复读”。脏读就是事务A读到了事务B还没提交的数据如果B最后回滚A刚才读到的是永远不存在的值。不可重复读是在同一个事务里第一次查余额是 1000第二次查变成 900因为中间有其他事务提交了修改。MySQL 在可重复读隔离级别下通过快照读让同一个事务的普通 SELECT 永远看到同一版本数据所以这个问题被解决了但在读已提交下仍然存在。至于幻读是指在同一事务中执行两次范围查询第二次多出了新行。MySQL InnoDB 在可重复读下默认使用间隙锁来锁定范围正常情况下可以避免但如果你在上网找实验答案时只用了普通 SELECT发现两次查询结果一样就以为“幻读不存在”这是不严谨的。真要复现幻读最好把隔离级别调成读已提交然后一个事务里做两次范围查询期间另一个事务插入满足条件的新行并提交。2.3 锁的类型、死锁原理与乐观锁兜底在 InnoDB 里锁有共享锁S和排他锁X之分S 锁和 S 锁兼容S 锁和 X 锁互斥X 锁和 X 锁也互斥。普通 SELECT 是不加锁的“快照读”真正能锁住行的是SELECT ... FOR UPDATE、UPDATE、DELETE这些操作。实验里我为了让并发扣款不超扣就在扣余额前先用SELECT balance FROM account WHERE id 1 FOR UPDATE把这行锁住后续事务只能排队等这属于悲观锁策略。死锁的出现很有意思它是两个事务互相占着对方需要的资源谁也不让。实验里我故意写了这么一段操作事务A先锁account表中id1的行事务B先锁account表中id2的行然后A继续申请锁id2B继续申请锁id1两个事务僵持不下。InnoDB 默认打开死锁检测会立刻侦测到并回滚其中一个事务然后另一个继续执行。MySQL 的命令行里会看到类似这样的报错ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction这个报错实际上帮了你大忙因为它直接终止了死锁循环。真实系统里更要靠良好的SQL执行顺序来避免死锁比如所有事务都按相同顺序锁定资源。还有一种方式是用版本号实现乐观锁表里加一个 version 字段更新时先读版本号提交时把修改后的数据和版本号一起更新数据库根据条件判断版本号是否被改变如果变了就拒绝提交。这种方式适合冲突较少的场景并发度高但锁代价小。3. 完整实操分步骤完成实验四3.1 建表与环境准备我的实验环境是 MySQL 8.0Windows 下用命令行操作连接工具用的是 DBeaver。如果你们是小组合作建议统一数据库版本避免因为版本差异导致隔离级别语法不一样影响实验结果。建库建表要特别注意以下几点存储引擎必须用 InnoDB它才支持事务、行锁、外键。MyISAM 不支持事务实验里很容易踩坑。字符集统一使用 utf8mb4避免中文乱码问题。余额、库存这些关键数字字段用 DECIMAL不要用 FLOAT/DOUBLE否则会产生浮点误差。外键约束可以加但要注意后续批量插入数据时不要导出顺序错误。下面是建表的核心代码我写的是一个最简版本方便复现CREATE DATABASE IF NOT EXISTS db_experiment4 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE db_experiment4; CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50) NOT NULL UNIQUE, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00, version INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB; CREATE TABLE seat_stock ( id INT PRIMARY KEY AUTO_INCREMENT, show_name VARCHAR(100) NOT NULL, remain_count INT NOT NULL DEFAULT 0, ticket_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, version INT NOT NULL DEFAULT 0 ) ENGINEInnoDB; CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL UNIQUE, user_id INT NOT NULL, stock_id INT NOT NULL, ticket_num INT NOT NULL, total_amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES account(id), CONSTRAINT fk_orders_stock FOREIGN KEY (stock_id) REFERENCES seat_stock(id) ) ENGINEInnoDB; CREATE TABLE audit_log ( id INT PRIMARY KEY AUTO_INCREMENT, account_id INT NOT NULL, before_balance DECIMAL(10,2) NOT NULL, after_balance DECIMAL(10,2) NOT NULL, change_amount DECIMAL(10,2) NOT NULL, change_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB;初始化几条测试数据INSERT INTO account (user_name, balance) VALUES (alice, 1000.00), (bob, 2000.00); INSERT INTO seat_stock (show_name, remain_count, ticket_price) VALUES (Symphony Concert, 50, 188.00);这里我在 account 表里提前加了一个 version 字段是为了后面索引优化和乐观锁演示都方便当然如果用悲观锁演示这个字段可以先不填。总之表结构设计要尽量把实验所有需要的东西都包含进去不然来回 ALTER TABLE 很没必要。3.2 用事务完成一次“转账购票”的正确流程事务的基本写法大家都知道但实验要求不是“写对”而是“演示对”。我比较推荐在命令行里开两个终端会话一个模拟用户的购买行为另一个在旁边查询数据这样能直观看到事务影响范围。一个正确的购票事务应该是START TRANSACTION; SELECT balance FROM account WHERE id 1 FOR UPDATE; SELECT remain_count FROM seat_stock WHERE id 1 FOR UPDATE; UPDATE account SET balance balance - 188.00 WHERE id 1; UPDATE seat_stock SET remain_count remain_count - 1 WHERE id 1; INSERT INTO orders (order_no, user_id, stock_id, ticket_num, total_amount) VALUES (ORD20250001, 1, 1, 1, 188.00); COMMIT;如果事务执行到一半出现异常比如库存不足就执行ROLLBACKSTART TRANSACTION; UPDATE seat_stock SET remain_count remain_count - 1 WHERE id 1 AND remain_count 0; -- MySQL 返回 Rows matched: 0说明没有库存可扣 -- 此时应回滚不能继续生成订单 ROLLBACK;这里有一个关键细节更新库存的 SQL 里加上AND remain_count 0条件是一个低成本防超卖的方案。就算没有事务这个条件也能避免余票变成负数。但加上事务才能真正防止“余票扣减成功但订单失败”这类跨表不一致问题。我在实测中发现新手最容易犯的错是在事务里做了一步 UPDATE然后发现下面的 SQL 写错了就只把最后一条SQL撤回而不是整个事务回滚。这等于放弃了事务的原子性。实验前可以把“事务回滚后所有中间操作都消失”这一个现象单独截图放进报告非常能说明问题。3.3 复现“丢失更新”并验证行锁修复方案“丢失更新”是并发异常里最容易理解也最容易演示的。假设两个事务同时读取同一账户余额都读到 1000 元然后各自打算扣掉 100 元最后把结果写回。如果不加锁这两个更新会先后覆盖最终余额可能停留在一个只扣了100的状态而不是正确的 800。复现过程需要两个并发的终端会话。会话A执行START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 读到 1000 DO SLEEP(5); UPDATE account SET balance balance - 100 WHERE id 1; COMMIT;会话B在A开始后、A写回前执行START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 也读到 1000 UPDATE account SET balance balance - 100 WHERE id 1; COMMIT;两个事务都成功提交了但余额最终是 900 而不是 800。原因就是第二个事务的更新基于的是过期读到的 1000而不是第一个事务写回的 900。用数据库的话说这就是丢失更新后一个覆盖了前一个已经提交的结果。修复方法有很多种最直接的例子就是用SELECT ... FOR UPDATE把读操作变成加锁读START TRANSACTION; SELECT balance FROM account WHERE id 1 FOR UPDATE; -- 加排他行锁 UPDATE account SET balance balance - 100 WHERE id 1; COMMIT;会话B也执行同样的 SQL 时因为行锁被A持有它的FOR UPDATE会一直阻塞住直到A提交才继续。此时B重新读到的是 900再扣100就变成 800数据正确。这个过程非常适合写进实验报告截图要截两件事一是未加锁时两个终端都读到 1000 的瞬间二是加锁后B的 SELECT 阻塞等待的画面。前者证明异常存在后者证明锁的解决办法有效。3.4 验证不同隔离级别下的脏读与不可重复读复现脏读第一步把会话A的隔离级别设为读未提交SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; SELECT balance FROM account WHERE id 1;然后切换到会话B开启事务把余额改成 500但先不提交START TRANSACTION; UPDATE account SET balance 500.00 WHERE id 1; -- 暂时不COMMIT回到会话A再查一次会发现已经能读到 balance 500.00而且这笔钱还没提交。这就是脏读。接着让会话B执行 ROLLBACK你再查一次会发现余额又变回原来的值相当于刚才读到了现实世界从没存在过的数据。这个现象强烈推荐截图。验证不可重复读需要把隔离级别改成读已提交。会话A开启事务先查余额为 1000会话B提交一个把余额改成 900 的事务会话A再次查询发现结果是 900。同一个事务里两次查询结果不一样这就是不可重复读。值得注意的是MySQL 在可重复读隔离级别下普通 SELECT 走的是快照读所以你在会话A第二次查询时还是会读到第一次查询时的旧快照。很多实验报告为了展示“不可重复读”直接用默认级别却查不出现象就是这个原因。必须先把级别改成 READ COMMITTED才能复现出来。这一点是最容易栽的坑。3.5 通过触发器自动维护操作日志实验四的完整性约束部分触发器算是一个大重点。我利用前面建的 audit_log 表写了一个简单的 AFTER UPDATE 触发器当账户余额被修改时自动记录变化前后的金额和差额。MySQL 触发器语法如下DELIMITER // CREATE TRIGGER trg_account_update_audit AFTER UPDATE ON account FOR EACH ROW BEGIN INSERT INTO audit_log ( account_id, before_balance, after_balance, change_amount ) VALUES ( NEW.id, OLD.balance, NEW.balance, NEW.balance - OLD.balance ); END// DELIMITER ;触发器的核心逻辑是在 UPDATE 操作影响每一行后立刻执行一段额外的 SQL。OLD 代表修改前的行NEW 代表修改后的行。如果触发器本身执行失败会导致原 UPDATE 也失败因此写插入语句时要注意字段类型和约束避免触发器裹乱。验证触发器是否生效很简单UPDATE account SET balance balance - 100 WHERE id 1; SELECT * FROM audit_log;实测发现一条 UPDATE 就会自动插入一条日志不需要应用层代码再去手动写日志。这种设计在真实系统里非常常见比如资金变动审计、价格变动留痕、订单状态流转记录都可以交给触发器完成。还要提醒一下别把所有业务逻辑都往触发器里塞。触发器执行在事务内部如果触发器内部出现了慢查询或者死锁会拖垮主事务。在实验报告里可以额外补充一句“触发器适合轻量级审计不适合复杂业务计算”老师会认为你真的理解它的边界。3.6 索引优化与执行计划对比索引在实验四里有点“画龙点睛”的意思。前面事务和锁解决的是并发安全索引解决的是查询性能。我用EXPLAIN分析了一个按用户查订单的 SQL在没有索引和有索引时执行计划的差异非常大。先看无索引时的执行计划EXPLAIN SELECT * FROM orders WHERE user_id 1;通常 rows 列会显示扫描全表type 是 ALLExtra 里也没有 Using index。数据量大的时候这种扫描会拖慢所有并发操作而且全表扫描时 MySQL 可能锁更多行进一步增加锁冲突概率。创建索引后再看CREATE INDEX idx_orders_user_id ON orders(user_id); EXPLAIN SELECT * FROM orders WHERE user_id 1;type 变成 refrows 大幅减少Extra 可能显示 Using index condition。这就是索引命中后减少扫描行的直接证据。这里要补充一个容易被忽略的细节在并发写入场景下索引不是越多越好。每次 INSERT/UPDATE 都要同步维护索引索引越多写放大越严重。实验里如果只是为了演示“索引能优化查询”建议只对高频查询字段加索引不要太贪心。4. 实验中容易踩的坑与排查方案4.1 事务里混入 DDL 导致之前的操作被“隐式提交”这是我在实验中最先踩中的坑。事务里写了几个 UPDATE接着想加一列顺手执行了ALTER TABLE。结果数据库把 ALTER TABLE 之前的全部操作自动提交了。也就是说我原以为事务还没结束其实事务已经被系统默默切掉了。后续一旦报错再回滚前面那些更新已经生效无法撤销。MySQL 里的 DDL 语句比如 CREATE、ALTER、DROP、TRUNCATE都会触发隐式提交这是很多实验翻车的直接原因。如果你要演示事务注意事务里只放 DML 语句DML 包括 SELECT 超集 INSERT、UPDATE、DELETE。万一真改了表结构就放弃本次演示重新来过别硬着头皮往下写。4.2 死锁现场复现与排查思路死锁一旦发生数据库会立刻回滚一个事务作为牺牲者。我一开始以为死锁是“偶发故障”查了半天不知道哪里触发。后来发现只要控制好两个事务的加锁顺序就能稳定复现。刚才提到过事务A锁住 id1 但想申请 id2事务B锁住 id2 但想申请 id1。这种循环等待就是死锁。MySQL 的报错信息会提示尝试重启事务。我的演示脚本大致长这样-- 会话A START TRANSACTION; SELECT * FROM account WHERE id 1 FOR UPDATE; DO SLEEP(5); SELECT * FROM account WHERE id 2 FOR UPDATE; -- 会话B在A等待时执行 START TRANSACTION; SELECT * FROM account WHERE id 2 FOR UPDATE; DO SLEEP(5); SELECT * FROM account WHERE id 1 FOR UPDATE;执行一段时间后其中一边会报死锁。实验报告里可以把死锁检测开关也分析一遍MySQL 8.0 下可以在配置里控制innodb_deadlock_detect一般保持默认打开即可。如果关闭了死锁检测遇到死锁后事务会一直等待直到锁等待超时系统将“被卡死”所以我不建议在生产环境关闭检测。排查死锁的另一个有用命令是SHOW ENGINE INNODB STATUS里面会列出最近一次死锁的详细信息包括两个事务各持有和等待哪些锁。实验报告里加一段这个输出会显得分析过程非常完整。4.3 隔离级别设置语法在不同数据库里的差异很多同学在自己的电脑上装的是 PostgreSQL或者直接把 MySQL 的语法拿去用结果发现SET SESSION TRANSACTION ISOLATION LEVEL和SET TRANSACTION ISOLATION LEVEL傻傻分不清。MySQL 里设置会话级别的写法是SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;如果只写SET TRANSACTION ISOLATION LEVEL ...它只对下一个事务生效。PostgreSQL 里则一般是BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;这两种写法不能混用我在实验开始时忘了确认数据库类型结果第一次演示跑出的效果完全不对。建议第一步先在命令行执行SELECT VERSION();确认环境再决定用哪一种语法。同时说明一下实验报告里最好把每种隔离级别对应的设置代码和验证结果放在一起形成完整对应关系不要只粘贴一个表格就算完。4.4 约束冲突和数据导入成败的快速判断设计外键、唯一约束和触发器后批量插入数据经常遇到冲突。比如我在插入订单数据时因为外键约束要求 user_id 必须存在于 account 表而我又没有提前插入足够多的用户直接导致插入失败。这个原因很清楚数据完整性约束的确在起作用只是我测试前没准备好完整的数据集。遇到这种问题我的排查思路是先看报错信息末尾的关键字比如foreign key constraint fails、Duplicate entry、Data too long。查询information_schema.TABLE_CONSTRAINTS或information_schema.KEY_COLUMN_USAGE确认约束字段。用SELECT * FROM account WHERE id xxx检查是否存在对应父表记录。检查字段字符集和排序规则是否一致不然也会导致外键无法建立成功。约束冲突本身不是坏事它验证了数据库的完整性机制。实验里如果想让外键生效记得设计测试用例来故意违反约束并截图记录报错这样报告会很有说服力。4.5 常见问题速查表下面对照表是我整理整个实验过程中最常碰到的问题适合直接放在实验报告的“问题分析”部分。现象可能原因解决思路事务执行后没有效果忘记 COMMIT事务未提交检查命令补上 COMMIT 或用SELECT * FROM ...验证Lock wait timeout exceeded行锁被其他事务持有检查并提交/回滚占用锁的事务调大innodb_lock_wait_timeout仅可临时验证Deadlock found事务加锁顺序不一致形成循环等待回滚牺牲事务统一加锁顺序加入重试机制Cannot add foreign key constraint父表数据缺失、字段类型或字符集不一致对比两张表字段定义检查关联数据存在脏读/不可重复读复现失败隔离级别用错或默认级别过高使用SET SESSION TRANSACTION ISOLATION LEVEL调整到目标级别后再重复操作触发器不执行触发器未创建成功或表名/字段名拼写错误SHOW TRIGGERS;查看触发器列表核对 OLD/NEW 字段EXPLAIN 显示全表扫描索引缺失或查询语句不符合最左前缀原则创建合适索引分析 WHERE 条件的字段顺序这张表不是一次性总结出来的很多条目是我在做实验中途反复查终端里的报错才搞明白的。建议你们做实验时也养成“看到报错先记录、再查资料”的习惯能省掉不少重复劳动。5. 做完实验四之后的一些小体悟整个实验四做下来我最想分享的一句话是并发控制不是一个开关而是一整套设计思路。隔离级别、锁、事务边界、索引策略这些东西是放在一起共同起作用的。你用可重复读级别能避免很多读异常但锁冲突和并发性能会变差你用读已提交级别并发能力上去了但业务代码里就得小心不可重复读和幻读的影响。没有绝对正确的配置只有适合业务场景的选择。实验之外我还做了一点扩展用乐观锁的 version 字段在没有显式行锁的情况下实现了同样的防超卖逻辑UPDATE seat_stock SET remain_count remain_count - 1, version version 1 WHERE id 1 AND version 0;这个写法每执行一次都会判断版本号是否变化如果条件不满足就说明已经有其他事务改过了应用层可以提示用户重新读取数据。它和悲观锁各有优劣真实系统里往往两者结合使用。最后再分享一个小技巧做实验不要只盯着 GUI 工具看结果命令行终端的输出信息会准确得多。锁等待、死锁报错、事务阻塞状态在命令行里一览无余。如果能把每个操作的输出截图整理好这份实验报告不仅分数稳你自己对数据库系统的理解也能提高一大截。