postgresql - PostgreSQL上的内存全行锁定问题

Question

指这个问题Concurrency Postgresql UPDATE

我尝试了锁定行，行锁定有另一个问题。有很多事务（我正在使用数据库主、虚拟和备份的 3 个节点以及应用程序的 6 个节点命中数据库）我尝试使用行锁运行 3 小时，之后数据库内存已满并且许多事务命中数据库失败。你有什么建议吗？

实际流量：

我正在使用 java 来处理这个，hibernate 和 PostgreSQL 用于数据库。我有表关系：

• 帐户
• 租户
• 发票
• 发票项目
• 平衡

数据库关系图

帐户活动余额全部存储在表 invoice_items 中，对于 1 张发票有一些项目并存储在 invoice_items 中。当帐户例如帐户 A 进行充值时，它将生成数据插入到表中：

• 发票
• 发票项目

数据将是：

发票：id、account_id、invoice_date、created_date、created_by

表格发票

                  id                  |               account_id                     |  invoice_date  |       created_date           | created_by
b0a9e664-186b-4b1a-9b43-9edef238765f      e0802b10-70cd-4466-aa53-da1618749809          2019-10-08       2018-10-03 04:10:44.000000      Admin   

invoice_items：id、类型、invoice_id、金额、account_id、created_date、created_by、current_balance、tenant_record_id

表 invoice_items

   id                                |     type     |              invoice_id            |            account_id                  |         created_date        | created_by  | amount | current_balance   |  tenant
b0a9e664-186b-4b1a-9b43-9edef238765f  CBA_ADJ       b0a9e664-186b-4b1a-9b43-9edef238765f    e0802b10-70cd-4466-aa53-da1618749809   2018-10-03 04:10:44.000000      Admin        1000        1000                 1
faef610a-b8bc-4793-8a9e-f9f760e7ee6c  CREDIT_ADJ    b0a9e664-186b-4b1a-9b43-9edef238765f    e0802b10-70cd-4466-aa53-da1618749809   2018-10-03 04:10:44.000000      Admin        -1000       1000                 1
1eaff7c3-226a-42c2-be1c-2939d5b72a60  CBA_ADJ       ab77a359-42ce-4878-bb77-8e66e4227a4d    e0802b10-70cd-4466-aa53-da1618749809   2018-10-03 05:22:43.000000      Admin          1          999                 1
0a73af7f-3310-444c-b852-e5b3931fbbb6    USAGE       ab77a359-42ce-4878-bb77-8e66e4227a4d    e0802b10-70cd-4466-aa53-da1618749809   2018-10-03 05:22:43.000000      Admin         -1          999                 1
7bfc627e-01a3-4a5c-9531-deec9d526c54  CBA_ADJ       a9fe1419-1aa1-4d4b-9af5-a62c4b44ed1c    e0802b10-70cd-4466-aa53-da1618749809   2018-10-03 06:24:43.000000      Admin          1          998                 1
bd3d7241-e18a-443e-a8d0-0dd74af51e27    USAGE       a9fe1419-1aa1-4d4b-9af5-a62c4b44ed1c    e0802b10-70cd-4466-aa53-da1618749809   2018-10-03 06:24:43.000000      Admin         -1          998                 1

因为我无法通过应用程序处理当前余额，所以我尝试使用触发器处理，当插入新记录时执行插入表余额的过程：

CREATE OR REPLACE FUNCTION public.function_insert_currentbalance()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    PERFORM 1 FROM invoice_items
    WHERE type = NEW.type and external_key = new.external_key
    ORDER BY created_date, id DESC LIMIT 1
    FOR UPDATE OF invoice_items;

  IF new.type = 'CBA_ADJ' THEN
    insert into balance (invoice_id, balance, invoice_items_id)
        values (new.invoice_id, (SELECT SUM(amount) FROM invoice_items WHERE  account_id= new.account_id and type=new.type), new.invoice_id);
  END IF;

return null;
END;

$function$
;

对于数据插入具有类型为几行时的类型：

CBA_ADJ 和 USAGE -> 是交易类型

CBA_ADJ 和 CREDIT_ADJ 是 toupup 的类型

应用的类型是贷记和借记，要获得当前余额，只需计算属于 account_id 的金额的总和，以及类型为“CBA_ADJ”的表 invoice_items 中的租户。查询：

 IF new.type = 'CBA_ADJ' THEN
    insert into balance (invoice_id, balance, invoice_items_id)
        values (new.invoice_id, (SELECT SUM(amount) FROM invoice_items WHERE  account_id= new.account_id and type=new.type), new.invoice_id);
  END IF;

标签： postgresql