SQLite事务 #

-- 事务是一组SQL操作，作为一个单元执行
-- 要么全部成功，要么全部失败

-- 事务的特点（ACID）：
-- A - Atomicity（原子性）：事务不可分割
-- C - Consistency（一致性）：事务前后数据一致
-- I - Isolation（隔离性）：事务之间互不干扰
-- D - Durability（持久性）：事务提交后永久保存

-- SQLite 事务特点：
-- 1. 默认每个SQL语句是一个事务
-- 2. 支持嵌套事务（通过SAVEPOINT）
-- 3. 支持读事务和写事务
-- 4. 使用锁机制实现隔离

-- 开始事务
BEGIN TRANSACTION;

-- 或简写
BEGIN;

-- 执行SQL操作
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
INSERT INTO orders (user_id, total) VALUES (last_insert_rowid(), 100);

-- 提交事务
COMMIT;

-- 或
COMMIT TRANSACTION;

-- 回滚事务
BEGIN;

INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');

-- 发现错误，回滚
ROLLBACK;

-- 或
ROLLBACK TRANSACTION;

-- SQLite 默认自动提交模式
-- 每个SQL语句自动成为一个事务

-- 单条语句
INSERT INTO users (name) VALUES ('John');
-- 自动提交

-- 显式事务
BEGIN;
INSERT INTO users (name) VALUES ('John');
INSERT INTO users (name) VALUES ('Jane');
COMMIT;
-- 两条语句作为一个事务

-- DEFERRED：延迟获取锁（默认）
-- 开始时不获取锁，第一次读写时获取

BEGIN DEFERRED;

-- 读操作 - 获取共享锁
SELECT * FROM users;

-- 写操作 - 获取排他锁
INSERT INTO users (name) VALUES ('Test');

COMMIT;

-- IMMEDIATE：立即获取保留锁
-- 阻止其他写事务，允许读事务

BEGIN IMMEDIATE;

-- 其他连接可以读，但不能写

INSERT INTO users (name) VALUES ('Test');

COMMIT;

-- EXCLUSIVE：获取排他锁
-- 阻止其他所有事务

BEGIN EXCLUSIVE;

-- 其他连接不能读也不能写

INSERT INTO users (name) VALUES ('Test');

COMMIT;

-- 事务类型对比表
-- | 类型       | 开始时锁 | 允许并发读 | 允许并发写 |
-- |------------|----------|------------|------------|
-- | DEFERRED   | 无锁     | 是         | 是         |
-- | IMMEDIATE  | 保留锁   | 是         | 否         |
-- | EXCLUSIVE  | 排他锁   | 否         | 否         |

-- SAVEPOINT：嵌套事务
BEGIN;

INSERT INTO users (name) VALUES ('User1');

-- 创建保存点
SAVEPOINT sp1;

INSERT INTO users (name) VALUES ('User2');

-- 回滚到保存点
ROLLBACK TO SAVEPOINT sp1;

-- 继续操作
INSERT INTO users (name) VALUES ('User3');

-- 释放保存点
RELEASE SAVEPOINT sp1;

COMMIT;

-- 多级保存点
BEGIN;

INSERT INTO users (name) VALUES ('User1');

SAVEPOINT level1;

INSERT INTO users (name) VALUES ('User2');

SAVEPOINT level2;

INSERT INTO users (name) VALUES ('User3');

-- 回滚到level2
ROLLBACK TO SAVEPOINT level2;
-- User3 被撤销

-- 回滚到level1
ROLLBACK TO SAVEPOINT level1;
-- User2 和 User3 被撤销

COMMIT;
-- 只有 User1 被提交

-- SQLite 默认隔离级别：SERIALIZABLE
-- 支持的隔离级别：
-- 1. SERIALIZABLE（默认）
-- 2. REPEATABLE READ
-- 3. READ COMMITTED

-- SQLite 通过锁机制实现隔离
-- 不支持设置隔离级别

-- SQLite 保证事务内的读一致性

-- 连接1
BEGIN;
SELECT * FROM users;  -- 看到某个状态

-- 连接2
INSERT INTO users (name) VALUES ('New User');
COMMIT;

-- 连接1
SELECT * FROM users;  -- 仍然看到之前的状态，看不到新数据
COMMIT;

SELECT * FROM users;  -- 现在可以看到新数据

SQLite 锁类型（从低到高）：
├── UNLOCKED    - 未锁定
├── SHARED      - 共享锁（读）
├── RESERVED    - 保留锁（准备写）
├── PENDING     - 等待锁（等待其他读完成）
└── EXCLUSIVE   - 排他锁（写）

-- 锁升级过程
-- 1. 读操作：UNLOCKED -> SHARED
-- 2. 写操作：SHARED -> RESERVED -> PENDING -> EXCLUSIVE

-- 查看当前锁状态
PRAGMA locking_mode;

-- 设置锁模式
PRAGMA locking_mode = EXCLUSIVE;

-- WAL（Write-Ahead Logging）模式
-- 提高并发性能

-- 启用WAL
PRAGMA journal_mode = WAL;

-- WAL模式特点：
-- 1. 读写不冲突
-- 2. 更好的并发性能
-- 3. 支持多个读事务
-- 4. 写事务不阻塞读事务

-- 检查WAL状态
PRAGMA journal_mode;

-- 设置等待超时
-- 当遇到锁时，等待指定毫秒

PRAGMA busy_timeout = 5000;  -- 等待5秒

-- 在应用程序中设置
-- Python: conn.execute("PRAGMA busy_timeout = 5000")

-- 锁冲突错误
-- Error: database is locked

-- 解决方案：
-- 1. 增加busy_timeout
-- 2. 使用WAL模式
-- 3. 缩短事务时间
-- 4. 重试逻辑

-- Python示例
-- import sqlite3
-- import time
-- 
-- def execute_with_retry(conn, sql, max_retries=5):
--     for i in range(max_retries):
--         try:
--             return conn.execute(sql)
--         except sqlite3.OperationalError as e:
--             if 'locked' in str(e) and i < max_retries - 1:
--                 time.sleep(0.1)
--                 continue
--             raise

-- 推荐：短事务
BEGIN;
INSERT INTO users (name) VALUES ('Test');
COMMIT;

-- 不推荐：长事务
BEGIN;
-- 大量操作...
-- 可能阻塞其他连接
COMMIT;

-- 批量操作使用事务
-- 不推荐
INSERT INTO users (name) VALUES ('User1');
INSERT INTO users (name) VALUES ('User2');
INSERT INTO users (name) VALUES ('User3');
-- 每条语句都是一个事务

-- 推荐
BEGIN;
INSERT INTO users (name) VALUES ('User1');
INSERT INTO users (name) VALUES ('User2');
INSERT INTO users (name) VALUES ('User3');
COMMIT;
-- 一个事务包含所有操作

-- 使用事务处理错误
-- Python示例
-- try:
--     conn.execute("BEGIN")
--     conn.execute("INSERT INTO users (name) VALUES (?)", ('John',))
--     conn.execute("INSERT INTO orders (user_id) VALUES (?)", (user_id,))
--     conn.commit()
-- except Exception as e:
--     conn.rollback()
--     raise

-- 银行转账事务
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    user_name TEXT,
    balance REAL
);

INSERT INTO accounts (user_name, balance) VALUES ('Alice', 1000);
INSERT INTO accounts (user_name, balance) VALUES ('Bob', 500);

-- 转账事务
BEGIN;

-- 检查余额
SELECT balance FROM accounts WHERE user_name = 'Alice';
-- 假设余额足够

-- 扣款
UPDATE accounts SET balance = balance - 100 WHERE user_name = 'Alice';

-- 入账
UPDATE accounts SET balance = balance + 100 WHERE user_name = 'Bob';

COMMIT;

-- 订单处理事务
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total REAL,
    status TEXT
);

CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    product_name TEXT,
    quantity INTEGER
);

INSERT INTO inventory (product_name, quantity) VALUES ('Product A', 100);

-- 订单事务
BEGIN;

-- 创建订单
INSERT INTO orders (user_id, total, status) VALUES (1, 50, 'pending');

-- 检查库存
SELECT quantity FROM inventory WHERE product_name = 'Product A';
-- 假设库存足够

-- 减少库存
UPDATE inventory SET quantity = quantity - 1 WHERE product_name = 'Product A';

-- 更新订单状态
UPDATE orders SET status = 'completed' WHERE id = last_insert_rowid();

COMMIT;