┌─────────────────────────────────────────────────────────────┐
│                      ACID 特性                              │
├─────────────────────────────────────────────────────────────┤
│  Atomicity (原子性)   │  事务是不可分割的工作单元            │
│  Consistency (一致性) │  事务执行后数据库状态保持一致         │
│  Isolation (隔离性)   │  并发事务相互隔离，互不干扰          │
│  Durability (持久性)   │  事务提交后永久保存                   │
└─────────────────────────────────────────────────────────────┘

-- 显式事务
BEGIN;
-- 或
START TRANSACTION;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT savepoint_name;

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

-- 释放保存点
RELEASE SAVEPOINT savepoint_name;

-- 转账示例
BEGIN;

-- 扣除转出账户
UPDATE accounts SET balance = balance - 1000 
WHERE user_id = 1;

-- 检查余额
SELECT balance FROM accounts WHERE user_id = 1;
-- 如果余额不足，回滚
-- IF balance < 0 THEN ROLLBACK;

-- 转入账户
UPDATE accounts SET balance = balance + 1000 
WHERE user_id = 2;

-- 记录交易日志
INSERT INTO transactions (from_user, to_user, amount)
VALUES (1, 2, 1000);

COMMIT;

-- psql 中默认为自动提交
-- 开启手动提交
\echo :AUTOCOMMIT
\set AUTOCOMMIT off

-- 关闭自动提交（在 psql 中执行）
\encoding UTF8

-- 每个语句自动在事务中执行
-- 仍需显式 COMMIT 或 ROLLBACK

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

-- 在 BEGIN 后设置
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- 会话 1
BEGIN;
UPDATE users SET email = 'new1@example.com' WHERE id = 1;
-- 未提交

-- 会话 2
SELECT email FROM users WHERE id = 1;
-- 结果: 旧 email（未提交）

-- 会话 1
COMMIT;

-- 会话 2
SELECT email FROM users WHERE id = 1;
-- 结果: new1@example.com（读取已提交数据）

-- 会话 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT email FROM users WHERE id = 1;
-- 结果: old@example.com

-- 会话 2（在 REPEATABLE READ 事务外）
UPDATE users SET email = 'new@example.com' WHERE id = 1;
COMMIT;

-- 会话 1
SELECT email FROM users WHERE id = 1;
-- 结果: 仍是 old@example.com（同一事务内一致）

COMMIT;
SELECT email FROM users WHERE id = 1;
-- 结果: new@example.com（新事务可见）

-- 会话 1
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- 尝试插入重复记录
INSERT INTO unique_values (value)
SELECT 100 WHERE NOT EXISTS (
    SELECT 1 FROM unique_values WHERE value = 100
);

-- 会话 2 也同时执行相同操作
-- 只有一个会成功，另一个会报错
COMMIT;

-- ERROR: could not serialize access

-- 事务可见性由以下条件决定：
-- 1. 事务开始时 xmin（创建版本）是否已提交
-- 2. 事务结束时 xmax（删除版本）是否已提交

-- 查看行版本信息
SELECT xmin, xmax, * FROM users WHERE id = 1;

-- 获取当前事务ID
SELECT txid_current();

-- 获取当前后端的事务ID
SELECT txid_current_if_assigned();

-- 查看活动事务
SELECT 
    pid,
    txid_current_if_assigned() AS txid,
    state,
    query_start
FROM pg_stat_activity
WHERE state != 'idle';

-- 获取当前快照
SELECT txid_current_snapshot();

-- 解析快照
SELECT * FROM pg_stat_get_snapshot_timestamp(txid_current());

-- 表级锁
BEGIN;
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;
-- 执行操作
COMMIT;

-- 行级锁
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 锁定行直到事务结束
UPDATE users SET email = 'new@example.com' WHERE id = 1;
COMMIT;

-- 多种行锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;      -- 排他锁
SELECT * FROM users WHERE id = 1 FOR SHARE;       -- 共享锁
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;  -- 不等待
SELECT * FROM users WHERE id = 1 FOR UPDATE SKIP LOCKED; -- 跳过已锁定的行

-- 会话 1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 锁定账户1
-- 等待...

-- 会话 2
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- 锁定账户2
-- 等待...

-- 会话 1
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- 死锁！
-- ERROR: deadlock detected

-- 会话 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 成功（之前已释放）

-- 会话 1
BEGIN;
UPDATE users SET balance = balance - 1000 WHERE id = 1;
-- 未提交

-- 会话 2（READ COMMITTED）
SELECT balance FROM users WHERE id = 1;
-- 结果: 仍然是旧值（PostgreSQL 防止脏读）

-- 会话 1
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM users WHERE id = 1;  -- 第一次读取: 1000

-- 会话 2
UPDATE users SET balance = 2000 WHERE id = 1;
COMMIT;

-- 会话 1
SELECT balance FROM users WHERE id = 1;  -- 第二次读取: 2000
-- READ COMMITTED 会出现不可重复读

-- 会话 1
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 10行

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

-- 会话 1
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 11行
-- 出现幻读

-- 添加版本号字段
ALTER TABLE users ADD COLUMN version INTEGER DEFAULT 1;

-- 乐观锁更新
UPDATE users 
SET email = 'new@example.com', 
    version = version + 1
WHERE id = 1 AND version = 1;

-- 检查更新结果
IF NOT FOUND THEN
    -- 记录已被其他事务修改
    RAISE NOTICE '更新失败，数据已被修改';
END IF;

-- 应用层逻辑
-- 1. 读取记录及其版本号
-- 2. 执行更新，检查版本号
-- 3. 如果版本号不匹配，回滚重试

-- 查看当前锁
SELECT 
    l.locktype,
    l.relation::regclass,
    l.mode,
    l.granted,
    l.pid,
    a.usename,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.pid != pg_backend_pid();

-- 查看等待中的查询
SELECT 
    pid,
    usename,
    query,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
    AND state != 'idle';

-- 查看长事务
SELECT 
    pid,
    usename,
    query,
    state,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
    AND now() - query_start > interval '1 minute';

-- 终止阻塞查询
SELECT pg_cancel_backend(pid);   -- 优雅终止
SELECT pg_terminate_backend(pid); -- 强制终止