postgres 咨询锁性能问题


CREATE TABLE IF NOT EXISTS account
(
    id              INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username        TEXT    NOT NULL,
    coin            NUMERIC NOT NULL DEFAULT 0 CHECK ( coin >= (0)::numeric ),
    version         BIGINT  NOT NULL DEFAULT 0,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE
);
comment on table account is '账户表.';
comment on column account.id is '自增唯一 ID 标示.';
comment on column account.username is '账户名.';
comment on column account.coin is '余额.';
comment on column account.version is '账户余额版本标识(乐观锁).';


CREATE TABLE IF NOT EXISTS transaction
(
    id             INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id        INTEGER NOT NULL REFERENCES account (id),
    value          NUMERIC NOT NULL,
    balance        NUMERIC NOT NULL DEFAULT 0 CHECK ( balance <> 'NaN'::numeric AND (balance >= (0)::numeric)),
    type           INTEGER NOT NULL CHECK ( type IN (1, 2, 4, 8, 16) ),
    narration      TEXT    NOT NULL         DEFAULT '',
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP WITH TIME ZONE,
    deleted_at     TIMESTAMP WITH TIME ZONE
);
comment on table transaction is '账户余额变动交易表, 此表记录了每笔交易账户余额变动日志.';
comment on column transaction.id is '自增唯一 ID 标示.';
comment on column transaction.account_id is '账户 ID, 关联账户表.';
comment on column transaction.value is '交易代币数额,收入为正, 支出为负.';
comment on column transaction.balance is '交易完成后剩余的账户余额.';
comment on column transaction.narration is '交易描述.';
comment on column transaction.type is '交易类型.';


-- TRIGGER
CREATE OR REPLACE FUNCTION transaction__sync_balance()
    RETURNS TRIGGER AS
$$
BEGIN
    -- UPDATE
    UPDATE account
    SET coin = coin + NEW.value,version = version + 1
    WHERE id = NEW.user_id
    RETURNING coin INTO NEW.balance;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_balance_trigger
    BEFORE INSERT
    ON transaction
    FOR EACH ROW
EXECUTE FUNCTION transaction__sync_balance();

当插入 transaction 表数据的时候,触发器会更新 account 的余额,我现在遇到的问题是,这一套事物执行的效率太低了,因为我事物最开始时使用了咨询锁:

SELECT pg_advisory_xact_lock(1001, account_id)

但是不使用的话,并发高的情况下不能保证 transaction 的安全执行,会出现脏写,有没有什么办法用
在保证安全并行并且高性能的方案？

neoblackcap

168 天前

@ieesk 其实完全可以很少改动，你把写入的操作放在一个独立的服务，那个服务只有一个进程，开放一个接口。现有的服务在写入的时候就调用这个接口。
上锁，释放锁的速度并不慢。慢是因为锁竞争。单线程写入的话，性能上限应该可以逼近你数据的写入效率极限。

不过这样改的话，运维会多了很多工作。毕竟无缘故就多了一个服务需要运维，还让系统引入了一个单点问题。如果要解决单点问题的话，又要引入分布式锁。