413_数据库事务与并发控制¶

一句话说明¶

事务就是"要么全做，要么全不做"的操作包，并发控制防止多人同时操作时数据乱套。

核心知识点¶

ACID四大特性¶

特性	英文	含义	类比
原子性	Atomicity	全做或全不做	转账：钱出了必须进账，不能半途而废
一致性	Consistency	事务前后数据合法	账户总额不变
隔离性	Isolation	事务间互不干扰	两人同时操作互不影响
持久性	Durability	提交后永久保存	提交后断电也不丢失

事务隔离级别（从低到高）¶

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	可能	可能	可能	最高
READ COMMITTED	不会	可能	可能	高
REPEATABLE READ	不会	不会	可能	中
SERIALIZABLE	不会	不会	不会	最低

MySQL默认：REPEATABLE READ
PostgreSQL默认：READ COMMITTED

实战SQL示例¶

-- ========== PostgreSQL环境示例 ==========

-- ========== 1. 基本事务操作 ==========
-- 场景：批量插入基因表达数据，失败则全部回滚
BEGIN;  -- 开始事务（或用 START TRANSACTION）

-- 插入样本信息
INSERT INTO samples (sample_id, patient_id, collection_date)
VALUES ('S100', 'P001', '2024-01-15');

-- 插入该样本的基因表达数据
INSERT INTO gene_expression (gene_id, sample_id, expression, condition)
VALUES 
    ('BRCA1', 'S100', 245.7, 'tumor'),
    ('TP53',  'S100', 89.3,  'tumor'),
    ('KRAS',  'S100', 412.1, 'tumor');

-- 更新样本处理状态
UPDATE samples SET status = 'processed' WHERE sample_id = 'S100';

COMMIT;  -- 全部成功则提交（永久生效）
-- 如果中间任何步骤失败，执行 ROLLBACK; 回滚所有操作

-- ========== 2. 保存点（SAVEPOINT）：部分回滚 ==========
BEGIN;

INSERT INTO samples VALUES ('S101', 'P002', '2024-01-16');
SAVEPOINT after_sample;  -- 设置保存点

-- 尝试插入表达数据
INSERT INTO gene_expression VALUES ('INVALID_GENE', 'S101', -999, 'tumor');
-- 假设这步失败（基因ID不合法）

ROLLBACK TO SAVEPOINT after_sample;  -- 回滚到保存点（只撤销表达数据插入）
-- 样本信息插入仍然保留！

-- 插入正确的表达数据
INSERT INTO gene_expression VALUES ('EGFR', 'S101', 156.2, 'tumor');

COMMIT;  -- 提交（样本 + 正确的表达数据都保存）

-- ========== 3. 隔离级别设置 ==========
-- 查看当前隔离级别
SHOW transaction_isolation;  -- PostgreSQL
SELECT @@transaction_isolation;  -- MySQL

-- 设置本次事务的隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- ... 查询操作
COMMIT;

-- ========== 4. 锁机制示例 ==========
-- 行级锁：SELECT FOR UPDATE（悲观锁）
-- 场景：多个分析进程同时要处理样本，防止重复处理
BEGIN;

-- 锁定一个待处理的样本（其他进程遇到这行会等待）
SELECT sample_id, status 
FROM samples 
WHERE status = 'pending'
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- SKIP LOCKED：跳过被锁的行，直接拿下一个未锁的
-- 这样多个工作进程可以并行处理不同样本，不会冲突

-- 更新状态
UPDATE samples SET status = 'processing' WHERE sample_id = 'S100';

COMMIT;  -- 释放锁

-- ========== 5. 乐观锁（用版本号实现）==========
-- 适合读多写少的场景，不加数据库锁
-- 表结构：analysis_jobs(job_id, config, version INT)

-- 第一步：读取数据和版本号
SELECT job_id, config, version FROM analysis_jobs WHERE job_id = 42;
-- 假设读到 version = 5

-- 第二步：更新时检查版本号（如果别人已修改，version就变了，更新失败）
UPDATE analysis_jobs
SET config = '{"threads": 8}',
    version = version + 1   -- 版本号+1
WHERE job_id = 42
  AND version = 5;          -- ★ 关键：只有version还是5才更新

-- 检查影响行数
-- 如果影响0行 → 说明version已被修改（其他人抢先了），需要重试
-- 如果影响1行 → 更新成功

-- ========== 6. 死锁处理 ==========
-- 死锁：进程A锁了表1等表2，进程B锁了表2等表1，互相等待
-- 数据库会自动检测死锁并回滚其中一个事务

-- 避免死锁的原则：所有事务按固定顺序加锁
-- ❌ 容易死锁：
-- 事务A: 锁samples → 锁gene_expression
-- 事务B: 锁gene_expression → 锁samples

-- ✅ 避免死锁：统一顺序
-- 所有事务: 先锁samples → 再锁gene_expression

-- PostgreSQL查看死锁日志
-- log_lock_waits = on （在postgresql.conf中配置）

-- ========== 7. 事务在Python中的使用 ==========
python_example = """
import psycopg2  # PostgreSQL Python驱动

conn = psycopg2.connect("dbname=biodb user=admin")
conn.autocommit = False  # 关闭自动提交（手动控制事务）

try:
    cur = conn.cursor()

    # 插入样本
    cur.execute(
        "INSERT INTO samples VALUES (%s, %s, %s)",
        ('S200', 'P003', '2024-01-17')
    )

    # 插入表达数据
    expression_data = [('BRCA1', 'S200', 312.5), ('TP53', 'S200', 78.9)]
    cur.executemany(
        "INSERT INTO gene_expression (gene_id, sample_id, expression) VALUES (%s, %s, %s)",
        expression_data
    )

    conn.commit()  # 提交事务
    print("数据插入成功！")

except Exception as e:
    conn.rollback()  # 任何错误都回滚
    print(f"错误，已回滚: {e}")

finally:
    conn.close()
"""
print(python_example)

面试常问点¶

Q: 什么是脏读、不可重复读、幻读？ A: 脏读=读到未提交的数据；不可重复读=同一事务两次读同一行得到不同值；幻读=同一事务两次查询返回不同行数（有新行被插入）。
Q: MySQL的REPEATABLE READ怎么避免幻读？ A: 通过MVCC（多版本并发控制）+间隙锁（Gap Lock）。读时用快照，写时用间隙锁防止其他事务在范围内插入新行。
Q: 乐观锁和悲观锁的选择原则？ A: 读多写少用乐观锁（版本号）；写冲突频繁用悲观锁（SELECT FOR UPDATE）；短事务用悲观锁，长事务用乐观锁。
Q: 事务提交后数据是如何持久化的？ A: 通过WAL（预写日志，Write-Ahead Logging）：先写日志到磁盘，再修改数据文件。崩溃恢复时重放日志。

速查表¶

-- 事务控制
BEGIN / START TRANSACTION;  -- 开始
COMMIT;                      -- 提交
ROLLBACK;                    -- 回滚
SAVEPOINT sp_name;           -- 设置保存点
ROLLBACK TO SAVEPOINT sp_name; -- 回滚到保存点

-- 锁
SELECT ... FOR UPDATE;        -- 排他锁（写锁）
SELECT ... FOR SHARE;         -- 共享锁（读锁）
SELECT ... FOR UPDATE SKIP LOCKED; -- 跳过已锁行

-- 隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;