Re: Avoiding deadlocks when performing bulk update and delete operations

On Tue, Nov 25, 2014 at 4:42 AM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:

> On Mon, 24 Nov 2014 14:51:42 +1100
> Sanjaya Vithanagama <svithanagama(at)gmail(dot)com> 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?
>
> Lots of stuff to say about this ...
>
> First off, Igor has a valid point that the subselects are not helping any
> and may be making the situation slightly worse. I can't see any reason not
> to simiplify the queries as he suggested.
>

We used sub-selects in the delete queries was with the hope that we could
lock all the rows that will be deleted before the actual deletion happens.
(So that another transaction won't grab the lock for a row which will be
deleted).

>
> Secondly, a lot of your comments are too vague for me to understand what's
> happening, so I'm going to ask a bunch of questions to clarify:
>
> * How many UPDATE/INSERT queries are you running per second?
>

In peak hours it could be anywhere from 100-250. The problem occurs when
two triggers happen to update/delete the same rows at once.

> * How many in parallel on average?
> * What's the typical execution time for an UPDATE/INSERT that might cause
> this problem?
>

The updates are the most problematic with the execution time being in the
rage of 5-50 seconds.

> * How frequently do deadlocks occur?
>

We are seeing deadlocks about 2-3 times per day in the production server.
To reproduce the problem easily we've written a simple Java class with
multiple threads calling to the stored procedures running the above queries
inside a loop. This way we can easily recreate a scenario that happens in
the production.

> * Are there other tables involved in the transactions ... i.e., have you
> confirmed that these are the _only_ tables causing the deadlock?
>

Yes, there are no other tables involved with the quires so we can eliminate
any deadlock issues related to foreign key references.

>
> Since you didn't include any log output, I'm fuzzy on some of those things
> above ... but I'm assuming that you're unable to post specific details of
> the precise problem.
>

The log output looks like the following: (I have abstracted away the
function names are real queries but this represents the actual issue)

ERROR: deadlock detected
DETAIL: Process 54624 waits for ShareLock on transaction 14164828; blocked
by process 54605.
Process 54605 waits for ShareLock on transaction 14164827; blocked by
process 54624.
Process 54624: SELECT 1 FROM proc_delete()
Process 54605: SELECT 1 FROM proc_update()
HINT: See server log for query details.
CONTEXT: SQL statement "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"
PL/pgSQL function proc_delete() line 22 at SQL statement
SQL statement "SELECT proc_delete()"
PL/pgSQL function calling_function() line 6 at PERFORM
STATEMENT: SELECT 1 FROM calling_function()
ERROR: current transaction is aborted, commands ignored until end of
transaction block

>
> I have a lot of suggestions, but instead of bombing you with all of them, I
> think it would be better if you answered those questions, which will tell
> me which suggestions are most likely to help.
>

It'll be really good if we can get some ideas/alternative suggestion on how
to solve this one. It's been affecting our production servers for weeks and
we still haven't come across a concrete solution which fixes them.

Thank You.

>
> --
> Bill Moran
> I need your help to succeed:
> http://gamesbybill.com
>

--
Sanjaya