V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
ieesk
V2EX  ›  PostgreSQL

postgres 咨询锁性能问题

  •  
  •   ieesk · 143 天前 · 1269 次点击
    这是一个创建于 143 天前的主题,其中的信息可能已经有所发展或是发生改变。
    
    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 的安全执行,会出现脏写,有没有什么办法用
    • 在保证安全并行并且高性能的方案?
    9 条回复    2024-01-24 08:41:43 +08:00
    neoblackcap
        1
    neoblackcap  
       143 天前
    我们以前的做法是尽量让锁前置,不用数据库。比如特定的竞争操作只有 master 进程有写入的权限。这样就去掉了锁竞争
    frank000
        2
    frank000  
       143 天前
    这是必须要使用数据库触发器来做这件事么?还是有什么特别的考虑因素?
    ieesk
        3
    ieesk  
    OP
       143 天前
    @frank000 是的,不然就用 sql 更新了。
    ieesk
        4
    ieesk  
    OP
       143 天前
    @neoblackcap 这样改,我这业务牺牲有多大
    binbin0915jjpp
        5
    binbin0915jjpp  
       143 天前
    放到 AP 端吧 比如 mybatis 的拦截器里
    MoYi123
        6
    MoYi123  
       143 天前
    为什么会有脏写?
    是用 set coin = xxx 的写法吗?
    为什么不用 set coin = coin + xxx?
    ZZ74
        7
    ZZ74  
       143 天前
    你用锁也没比其他的高性能啊,只是把压力放到了数据库层而已。性能就和数据库服务器性能强相关。
    +钱操作简单 直接插入+更新即可。
    -钱就是 where coin - xxx >0 更新成功就插入。或者代码层面分布式锁。

    你要是想改的少,用存储过程或者 function 啊,也比触发器合适多了
    neoblackcap
        8
    neoblackcap  
       143 天前
    @ieesk 其实完全可以很少改动,你把写入的操作放在一个独立的服务,那个服务只有一个进程,开放一个接口。现有的服务在写入的时候就调用这个接口。
    上锁,释放锁的速度并不慢。慢是因为锁竞争。单线程写入的话,性能上限应该可以逼近你数据的写入效率极限。

    不过这样改的话,运维会多了很多工作。毕竟无缘故就多了一个服务需要运维,还让系统引入了一个单点问题。如果要解决单点问题的话,又要引入分布式锁。
    iseki
        9
    iseki  
       81 天前 via Android
    悲观锁定能不用就不用,看上去你不需要锁定啊,你只是需要确保记录更新的事务性而已,那为什么不调高事物隔离级别?此外看了下您这个触发器,似乎默认的 RC 级别已经够用了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   827 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 20:20 · PVG 04:20 · LAX 13:20 · JFK 16:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.