postgres 咨询锁性能问题 - V2EX
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
ieesk
V2EX    PostgreSQL

postgres 咨询锁性能问题

  •  
  •   ieesk 2023-11-23 15:17:57 +08:00 2152 次点击
    这是一个创建于 691 天前的主题,其中的信息可能已经有所发展或是发生改变。
     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  
       2023-11-23 15:29:18 +08:00
    我们以前的做法是尽量让锁前置,不用数据库。比如特定的竞争操作只有 master 进程有写入的权限。这样就去掉了锁竞争
    frank000
        2
    frank000  
       2023-11-23 15:34:00 +08:00
    这是必须要使用数据库触发器来做这件事么?还是有什么特别的考虑因素?
    ieesk
        3
    ieesk  
    OP
       2023-11-23 15:36:48 +08:00
    @frank000 是的,不然就用 sql 更新了。
    ieesk
        4
    ieesk  
    OP
       2023-11-23 15:37:55 +08:00
    @neoblackcap 这样改,我这业务牺牲有多大
    binbin0915jjpp
        5
    binbin0915jjpp  
       2023-11-23 16:06:36 +08:00
    放到 AP 端吧 比如 mybatis 的拦截器里
    MoYi123
        6
    MoYi123  
       2023-11-23 18:35:16 +08:00
    为什么会有脏写?
    是用 set coin = xxx 的写法吗?
    为什么不用 set coin = coin + xxx?
    ZZ74
        7
    ZZ74  
       2023-11-23 19:03:35 +08:00
    你用锁也没比其他的高性能啊,只是把压力放到了数据库层而已。性能就和数据库服务器性能强相关。
    +钱操作简单 直接插入+更新即可。
    -钱就是 where coin - xxx >0 更新成功就插入。或者代码层面分布式锁。

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

    不过这样改的话,运维会多了很多工作。毕竟无缘故就多了一个服务需要运维,还让系统引入了一个单点问题。如果要解决单点问题的话,又要引入分布式锁。
    iseki
        9
    iseki  
       2024-01-24 08:41:43 +08:00 via Android
    悲观锁定能不用就不用,看上去你不需要锁定啊,你只是需要确保记录更新的事务性而已,那为什么不调高事物隔离级别?此外看了下您这个触发器,似乎默认的 RC 级别已经够用了
    关于     帮助文档     自助推广系统     博客     API     FAQ     Solana     1073 人在线   最高记录 6679       Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 26ms UTC 23:07 PVG 07:07 LAX 16:07 JFK 19:07
    Do have faith in what you're doing.
    ubao snddm index pchome yahoo rakuten mypaper meadowduck bidyahoo youbao zxmzxm asda bnvcg cvbfg dfscv mmhjk xxddc yybgb zznbn ccubao uaitu acv GXCV ET GDG YH FG BCVB FJFH CBRE CBC GDG ET54 WRWR RWER WREW WRWER RWER SDG EW SF DSFSF fbbs ubao fhd dfg ewr dg df ewwr ewwr et ruyut utut dfg fgd gdfgt etg dfgt dfgd ert4 gd fgg wr 235 wer3 we vsdf sdf gdf ert xcv sdf rwer hfd dfg cvb rwf afb dfh jgh bmn lgh rty gfds cxv xcv xcs vdas fdf fgd cv sdf tert sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf shasha9178 shasha9178 shasha9178 shasha9178 shasha9178 liflif2 liflif2 liflif2 liflif2 liflif2 liblib3 liblib3 liblib3 liblib3 liblib3 zhazha444 zhazha444 zhazha444 zhazha444 zhazha444 dende5 dende denden denden2 denden21 fenfen9 fenf619 fen619 fenfe9 fe619 sdf sdf sdf sdf sdf zhazh90 zhazh0 zhaa50 zha90 zh590 zho zhoz zhozh zhozho zhozho2 lislis lls95 lili95 lils5 liss9 sdf0ty987 sdft876 sdft9876 sdf09876 sd0t9876 sdf0ty98 sdf0976 sdf0ty986 sdf0ty96 sdf0t76 sdf0876 df0ty98 sf0t876 sd0ty76 sdy76 sdf76 sdf0t76 sdf0ty9 sdf0ty98 sdf0ty987 sdf0ty98 sdf6676 sdf876 sd876 sd876 sdf6 sdf6 sdf9876 sdf0t sdf06 sdf0ty9776 sdf0ty9776 sdf0ty76 sdf8876 sdf0t sd6 sdf06 s688876 sd688 sdf86