PostgreSQL实战指南：多领域数据库应用与实践 - 第3章：事务管理与并发控制

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 场景：用户 Alice 购买了商品 ID 为 1 的 Laptop Pro，数量 1
-- 这需要：1. 创建订单。 2. 创建订单项。 3. 减少商品库存。

BEGIN; -- 开始一个事务

-- 1. 创建订单
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 1200.00, 'processing')
RETURNING order_id; -- 获取新生成的 order_id

-- 假设上面插入的 order_id 是 3
-- DECLARE new_order_id INTEGER;
-- SELECT order_id FROM orders WHERE user_id = 1 AND status = 'processing' ORDER BY order_date DESC LIMIT 1 INTO new_order_id;
-- 实际应用中，RETURNING 是更优雅的方式来获取新生成的ID

-- 2. 创建订单项
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (3, 1, 1, 1200.00);

-- 3. 减少商品库存
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1;

-- 模拟一个错误发生，例如尝试更新不存在的商品
-- UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 999;

-- 假设所有操作都成功，提交事务
COMMIT;

-- 如果上述任何一步出现问题，例如库存不足或商品不存在，我们可以回滚事务
-- ROLLBACK;

1
2
3

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1
2
3
4
5
6
7

-- 准备数据
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance NUMERIC(10, 2) NOT NULL DEFAULT 0
);
INSERT INTO accounts (balance) VALUES (1000.00);

1
2
3
4
5
6
7

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 第一次读取：1000.00

-- (等待)
SELECT balance FROM accounts WHERE id = 1; -- 第二次读取：假设会话2已提交，这里会看到更新后的值 900.00
COMMIT;

1
2
3
4
5
6

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 此时会话1的第一次读取完成。
-- 会话1的第二次读取会发生在此之后。
COMMIT;

1
2
3
4
5
6
7

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 第一次读取：1000.00

-- (等待)
SELECT balance FROM accounts WHERE id = 1; -- 第二次读取：仍旧是 1000.00
COMMIT;

1
2
3
4

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 会话 1 (隔离级别默认为 READ COMMITTED)
BEGIN;
SELECT stock_quantity FROM products WHERE product_id = 1; -- 假设为 50

-- 会话 2
BEGIN;
UPDATE products SET stock_quantity = 49 WHERE product_id = 1;
COMMIT;

-- 会话 1 (在会话 2 提交后)
SELECT stock_quantity FROM products WHERE product_id = 1; -- 此时会看到 49
COMMIT;

-- 如果会话 1 的隔离级别是 REPEATABLE READ:
-- 会话 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT stock_quantity FROM products WHERE product_id = 1; -- 假设为 50

-- 会话 2
BEGIN;
UPDATE products SET stock_quantity = 49 WHERE product_id = 1;
COMMIT;

-- 会话 1 (在会话 2 提交后)
SELECT stock_quantity FROM products WHERE product_id = 1; -- 仍旧看到 50 (因为其快照未改变)
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 场景：用户 A 和用户 B 同时尝试购买同一个商品 ID 为 1 的 Laptop Pro。
-- 假设当前库存为 50。

-- 会话 1：用户 A 购买 1 个 Laptop Pro
BEGIN;
-- 使用 SELECT FOR UPDATE 显式锁定行，防止其他事务同时修改此行
SELECT stock_quantity FROM products WHERE product_id = 1 FOR UPDATE; -- 假设返回 50
-- 检查库存是否足够
-- if stock_quantity >= 1 then
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1; -- 更新为 49
COMMIT;

-- 会话 2：用户 B 购买 1 个 Laptop Pro
BEGIN;
-- 当会话 1 仍在 SELECT FOR UPDATE 时，会话 2 的此语句会被阻塞，直到会话 1 提交或回滚
SELECT stock_quantity FROM products WHERE product_id = 1 FOR UPDATE;
-- 此时会话 2 看到的是会话 1 提交后的 49
-- 检查库存是否足够
-- if stock_quantity >= 1 then
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1; -- 更新为 48
COMMIT;