第五章练习答案

本页包含第五章所有练习的答案和详细解释。

01-basics

练习 1：基本事务

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

练习 2：回滚测试

BEGIN;
INSERT INTO test_table (data) VALUES ('test');
SELECT * FROM test_table;  -- 可以看到数据
ROLLBACK;
SELECT * FROM test_table;  -- 数据消失

练习 3：SAVEPOINT

BEGIN;
INSERT INTO orders (product) VALUES ('Laptop');
SAVEPOINT sp1;
INSERT INTO orders (product) VALUES ('Mouse');
SAVEPOINT sp2;
INSERT INTO orders (product) VALUES ('Keyboard');
ROLLBACK TO SAVEPOINT sp1;  -- 只保留 Laptop
COMMIT;

练习 4：批量操作

BEGIN;
INSERT INTO orders (user_id, product, amount)
SELECT 1, 'Product' || generate_series(1, 100), 10;
COMMIT;

练习 5：错误处理

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- 检查余额
SELECT balance FROM accounts WHERE id = 1;
-- 如果为负，回滚
ROLLBACK;

---

02-acid

练习 1：原子性验证

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 人为错误
SELECT 1/0;  -- 除零错误
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 自动回滚

练习 2：一致性约束

ALTER TABLE accounts ADD CONSTRAINT balance_check CHECK (balance >= 0);
BEGIN;
UPDATE accounts SET balance = -50 WHERE id = 1;  -- 违反约束
COMMIT;  -- 自动回滚

练习 3：隔离性测试

-- 会话 1
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;

-- 会话 2
SELECT balance FROM accounts WHERE id = 1;  -- 看不到未提交的 200

-- 会话 1
COMMIT;

练习 4：持久性验证

BEGIN;
INSERT INTO orders (product) VALUES ('Test');
COMMIT;
-- 重启数据库
SELECT * FROM orders WHERE product = 'Test';  -- 仍然存在

---

03-isolation

练习 1：测试脏读

-- 会话 1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;

-- 会话 2
BEGIN;
UPDATE accounts SET balance = 999 WHERE id = 1;

-- 会话 1
SELECT balance FROM accounts WHERE id = 1;  -- 999（脏读）

-- 会话 2
ROLLBACK;

练习 2：测试不可重复读

-- 会话 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 100

-- 会话 2
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- 会话 1
SELECT balance FROM accounts WHERE id = 1;  -- 200（不可重复读）
COMMIT;

练习 3：测试幻读

-- 会话 1（MySQL）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1;  -- 5

-- 会话 2
INSERT INTO orders (user_id, product) VALUES (1, 'New');
COMMIT;

-- 会话 1
SELECT COUNT(*) FROM orders WHERE user_id = 1;  -- 仍然 5（防止幻读）
COMMIT;

练习 4：隔离级别对比

-- READ COMMITTED
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT * FROM orders;
-- 可能看到其他事务的新提交

-- REPEATABLE READ
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders;
-- 看到一致的快照

---

04-locks

练习 1：显式加锁

-- 会话 1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 会话 2（阻塞）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 等待

-- 会话 1
COMMIT;  -- 会话 2 解除阻塞

练习 2：死锁复现

-- 会话 1
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- 会话 2
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;

-- 会话 1
UPDATE accounts SET balance = balance + 10 WHERE id = 2;  -- 等待

-- 会话 2
UPDATE accounts SET balance = balance + 10 WHERE id = 1;  -- 死锁检测

练习 3：死锁避免

-- 总是按 id 顺序访问
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;  -- 先 id=1
UPDATE accounts SET balance = balance + 10 WHERE id = 2;  -- 后 id=2
COMMIT;

练习 4：锁超时

SET lock_timeout = '3s';
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 3 秒后超时

练习 5：查看锁（PostgreSQL）

SELECT * FROM pg_locks WHERE granted = false;

练习 6：乐观锁实现

ALTER TABLE products ADD COLUMN version INT DEFAULT 0;

-- 更新
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;

-- 检查 affected_rows，如果为 0 则重试

---

05-mvcc

练习 1：查看版本（PostgreSQL）

SELECT xmin, xmax, * FROM accounts;

练习 2：快照测试

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- 100

-- 其他会话：UPDATE accounts SET balance = 200 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;  -- 仍然 100
COMMIT;

练习 3：VACUUM

VACUUM ANALYZE accounts;
SELECT n_dead_tup FROM pg_stat_user_tables WHERE tablename = 'accounts';

练习 4：并发测试

-- 会话 1：读
BEGIN;
SELECT * FROM orders;

-- 会话 2：写（不阻塞会话 1）
INSERT INTO orders (product) VALUES ('New');
COMMIT;

-- 会话 1
COMMIT;

---

复习要点

事务基础

• BEGIN/COMMIT/ROLLBACK
• SAVEPOINT 部分回滚
• 自动提交模式

ACID

• 原子性：全部成功或失败
• 一致性：满足约束
• 隔离性：事务间隔离
• 持久性：提交后永久保存

隔离级别

• READ UNCOMMITTED：允许脏读
• READ COMMITTED：防止脏读
• REPEATABLE READ：防止不可重复读
• SERIALIZABLE：完全隔离

锁机制

• 共享锁（S锁）：读锁
• 排他锁（X锁）：写锁
• 死锁：循环等待，数据库自动检测

MVCC

• 多版本并发控制
• 读写不阻塞
• 快照隔离

返回第五章概览 · 返回SQL 学习路线