PostgreSQL 死锁

PostgreSQL 死锁分析与解决方法

sql
-- 查询当前锁信息
SELECT * FROM pg_locks;
-- 查询当前活动的事务
SELECT * FROM pg_stat_activity;

sql
-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

sql
-- 设置事务超时
SET statement_timeout = '5s';

sql
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance INT
);
INSERT INTO accounts (balance) VALUES (1000), (2000);
CREATE OR REPLACE FUNCTION transfer_money(from_account INT, to_account INT, amount INT)
RETURNS VOID AS $$
BEGIN
    -- 开始事务
    BEGIN;
    
    -- 锁定转出账户
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    
    -- 锁定转入账户
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    
    -- 提交事务
    COMMIT;
END;
$$ LANGUAGE plpgsql;