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

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 = '