Re: Avoiding deadlocks when performing bulk update and delete operations

On 24/11/14 16:51, Sanjaya Vithanagama wrote:
> Hi All,
>
> We have a single table which does not have any foreign key references.
>
> 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 or delete:
>
> DELETE FROM table_name t
> USING (
> SELECT id_A, id_B
> FROM table_name
> WHERE id_A = ANY(array_of_id_A)
> AND id_B = ANY(array_of_id_B)
> ORDER BY id_A, id_B
> FOR UPDATE
> ) del
> WHERE t.id_A = del.id_A
> AND t.id_B = del.id_B;
>
>
> UPDATE table_name t
> SET val_1 = 'some value'
> , val_2 = 'some value'
> FROM (
> SELECT id_A, id_B
> FROM table_name
> WHERE id_A = ANY(array_of_id_A)
> AND id_B = ANY(array_of_id_B)
> ORDER BY id_A, id_B
> FOR UPDATE
> ) upd
> WHERE t.id_A = upd.id_A
> AND t.id_B = upd.id_B;
>
> We are experiencing deadlocks and all our attempts to perform
> operations with locks (row level using SELECT FOR UPDATE as used in
> the above queries and table level locks) do not seem to solve these
> deadlock issues. (Note that we cannot in any way use access exclusive
> locking on this table because of the performance impact)
>
> Is there another way that we could try to solve these deadlock
> situations? The reference manual says — "The best defense against
> deadlocks is generally to avoid them by being certain that all
> applications using a database acquire locks on multiple objects in a
> consistent order."
>
> Is there a guaranteed way to do bulk update/delete operations in a
> particular order so that we can ensure deadlocks won't occur? Or are
> there any other tricks to avoid deadlocks in this situation?
>
> Thank you in advance,
> Sanjaya
Unless there is some sort of implied locking, or other nonsense like
different lock types, then always acquiring locks in the same order
should work - as far as I can tell.

For purely locking problems, and assuming that all the relevant tables
are locked:

For if process p1 acquires locks in the order A, B, & C
and process p2 acquires locks in the order A, C, & D,
Then as soon as one process grabs A, then the other process cannot grab
A nor the other locks - so deadlock is avoided.

Similarly:
p1 A, B, C, & D
p2 B & C
However, if p1 grabs A and then p2 grabs B, P1 will have to wait for p2
to finish before p1 continues - but still, neither is deadlocked. Even
if there is p3 which locks B - at worst 2 processes will wait until the
lucky first process releases its locks.

You may have problems if there is some resource that is in contention,
where 2 processes require the resource and grab it in several parts at
different times, and they both grab some, and then there is insufficient
to completely satisfy either - this is guesswork, I'm not sure what
resources (if any) would be a problem here.

Cheers,
Gavin