Re: Avoiding deadlocks when performing bulk update and delete operations - Mailing list pgsql-general
From | Gavin Flower |
---|---|
Subject | Re: Avoiding deadlocks when performing bulk update and delete operations |
Date | |
Msg-id | 5472B86D.6020502@archidevsys.co.nz Whole thread Raw |
In response to | Avoiding deadlocks when performing bulk update and delete operations (Sanjaya Vithanagama <svithanagama@gmail.com>) |
List | pgsql-general |
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
pgsql-general by date: