pg多线程更新会发生死锁_在执行批量更新和删除操作时避免PostgreSQL死锁

本文介绍了在并发环境下,由于多线程更新导致PostgreSQL表出现死锁的问题。针对该问题,提出了通过在DELETE和UPDATE语句中使用显式行级锁定有序子查询的方法,确保按照特定顺序锁定行,从而避免死锁。同时,讨论了使用可串行化隔离级别作为另一种解决方案,但可能需要处理序列化失败并重新尝试命令。
摘要由CSDN通过智能技术生成

We have a single table which does not have references to any other tables.

┬────────────┬─────────────┬───────────────┬───────────────╮

│id_A(bigint)│id_B(bigint) │val_1(varchar) │val_2(varchar) │

╪════════════╪═════════════╪═══════════════╪═══════════════╡

The primary key of the table is a composite of id_A and id_B.

Reads and writes of this table are highly concurrent and the table has millions of rows.

We have several stored procedures which do mass updates and deletes. Those stored procedures are being called concurrently mainly by triggers and application code.

The operations usually look like the following where it could match thousands of records to update/delete:

DELETE FROM table_name

WHERE id_A = ANY(array_of_id_A)

AND id_B = ANY(array_of_id_B)

UPDATE table_name

SET val_1 = '

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值