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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
