-- 显式事务
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 回滚
BEGIN;
    DELETE FROM important_data WHERE id = 42;
ROLLBACK;  -- 数据恢复

-- SAVEPOINT（保存点）
BEGIN;
    INSERT INTO orders (customer_id, total) VALUES (1, 100);
    SAVEPOINT sp1;
        INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 10, 5);
    ROLLBACK TO sp1;  -- 只回滚到保存点，订单仍在
    INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 20, 3);
COMMIT;

-- 自动提交
-- psql 默认每条语句自动提交
-- 关闭自动提交：\set AUTOCOMMIT off

-- READ COMMITTED（默认）
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SELECT balance FROM accounts WHERE id = 1;  -- 假设读到 1000
    -- 此时另一个事务把 balance 改为 800 并提交
    SELECT balance FROM accounts WHERE id = 1;  -- 读到 800（不可重复读）
COMMIT;

-- REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT balance FROM accounts WHERE id = 1;  -- 读到 1000
    -- 此时另一个事务把 balance 改为 800 并提交
    SELECT balance FROM accounts WHERE id = 1;  -- 仍然读到 1000（快照）
    -- 但如果尝试修改这行，会报错：could not serialize access due to concurrent update
COMMIT;

-- SERIALIZABLE
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    -- 最强隔离，任何序列化异常都会回滚
    -- 适合银行转账等场景
COMMIT;

-- 查看行的事务信息
SELECT xmin, xmax, ctid, id, name FROM employees;

-- xmin  | xmax | ctid  | id | name
-- 1000  |    0 | (0,1) |  1 | Alice     ← 活跃（xmax=0）
-- 1000  | 1001 | (0,2) |  2 | Bob       ← 被事务 1001 删除
-- 1002  |    0 | (0,3) |  3 | Charlie   ← 活跃

-- 查看事务 ID 信息
SELECT txid_current();          -- 当前事务 ID
SELECT txid_current_snapshot(); -- 当前快照

-- 如果更新的列不涉及索引列，且新行在同一页面，PG 使用 HOT Update
-- HOT Update 不需要更新索引，性能更好

-- 设计建议：频繁更新的列和索引列分开
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,       -- 索引列（不频繁更新）
    status SMALLINT DEFAULT 0,      -- 频繁更新的列
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- 更新 status 和 updated_at 不影响 customer_id 索引 → HOT Update

-- 锁定行，防止并发修改
BEGIN;
    SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
    -- 其他会话尝试修改这行会阻塞
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- NOWAIT：如果无法获取锁，立即报错
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED：跳过已锁定的行（队列场景）
SELECT * FROM tasks WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

-- 查看当前锁
SELECT
    l.locktype, l.mode, l.granted,
    a.pid, a.usename, a.query, a.state
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
ORDER BY l.relation, l.mode;

-- 查看锁等待关系
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 策略 1：按固定顺序访问表/行
-- 总是按 id 升序处理
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 策略 2：使用 NOWAIT 或超时
SET lock_timeout = '5s';
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- 策略 3：减少事务持有时间
-- 不要在事务中做外部 API 调用

-- 会话级咨询锁
SELECT pg_advisory_lock(12345);      -- 获取锁（阻塞式）
SELECT pg_try_advisory_lock(12345);  -- 尝试获取（非阻塞）
SELECT pg_advisory_unlock(12345);    -- 释放锁

-- 事务级咨询锁（事务结束自动释放）
SELECT pg_advisory_xact_lock(12345);
SELECT pg_try_advisory_xact_lock(12345);

-- 适用场景：
-- 1. 分布式锁
-- 2. 防止并发任务重复执行
-- 3. 速率限制

-- 事务 ID 是 32 位无符号整数，约 20 亿个
-- 超过后会回卷（wraparound），导致旧数据丢失

-- 查看事务 ID 消耗
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    2147483647 - age(datfrozenxid) AS remaining
FROM pg_database;

-- VACUUM FREEZE 防止回卷
VACUUM FREEZE mytable;

-- autovacuum 会自动执行防回卷 VACUUM
-- 确保 autovacuum 正常运行！