Avoiding deadlocks on mass delete / update
I've got a simple 'spool' table, one process 'worker' reads and updates
this table, other 'stat' performs 'delete ... where ... returning *'.
Sometimes I've got dedlocks on delete operation in 'stat', seems like at
the moment of expiration of data by timeout some state changes arrived
from worker. So the question, is it possible to somehow set order of row
deletion in such bulk delete operation, to avoid deadlocks?
Thank you beforehand
--
MRJ
Роман Маширов wrote:
I've got a simple 'spool' table, one process 'worker' reads and updates
this table, other 'stat' performs 'delete ... where ... returning *'.
Sometimes I've got dedlocks on delete operation in 'stat', seems like at
the moment of expiration of data by timeout some state changes arrived
from worker. So the question, is it possible to somehow set order of row
deletion in such bulk delete operation, to avoid deadlocks?
OK, so for the sake of example, WORKER is UPDATEing rows that stat is
trying to DELETE at the same time, such that worker holds a lock on row
A and wants a lock on row B, but stat holds B and wants A?
In other words, the deadlock is an _interaction_ between 'stat' and
'worker'?
Can you post the queries?
One option is to SELECT ... FOR UPDATE NOWAIT before your UPDATE or DELETE.
http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-FOR-UPDATE-SHARE
http://www.postgresql.org/docs/8.4/static/explicit-locking.html#LOCKING-ROWS
Some kind of discussion of row level lock ordering might be in order for
the manual, actually. If there is one, it needs a link from the above
sections. The section on deadlocks:
http://www.postgresql.org/docs/8.4/static/explicit-locking.html#LOCKING-DEADLOCKS
doesn't mention how to avoid deadlock when multi-row updates/deletes are
being used concurrently.
--
Craig Ringer
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Craig Ringer wrote:
<blockquote cite="mid:%3C4BA99567.6070502@postnewspapers.com.au%3E"
type="cite">
<pre wrap="">Роман Маширов wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I've got a simple 'spool' table, one process 'worker' reads and updates
this table, other 'stat' performs 'delete ... where ... returning *'.
Sometimes I've got dedlocks on delete operation in 'stat', seems like at
the moment of expiration of data by timeout some state changes arrived
from worker. So the question, is it possible to somehow set order of row
deletion in such bulk delete operation, to avoid deadlocks?
</pre>
</blockquote>
<pre wrap=""><!---->
OK, so for the sake of example, WORKER is UPDATEing rows that stat is
trying to DELETE at the same time, such that worker holds a lock on row
A and wants a lock on row B, but stat holds B and wants A?
In other words, the deadlock is an _interaction_ between 'stat' and
'worker'?
</pre>
</blockquote>
yes<br>
<blockquote cite="mid:%3C4BA99567.6070502@postnewspapers.com.au%3E"
type="cite">
<pre wrap="">
Can you post the queries?
</pre>
</blockquote>
as dumb as possible :) <br>
<br>
worker parses several thousand events and do<br>
update queue set state=$1 where queue_id in (<id list>) and state
in (<previous state list>)<br>
for each target state, so it performs 1-4 update queries.<br>
<br>
stat do<br>
delete from queue where queue_stamp < now()-'1day'::interval or
state in (<terminal state list>)<br>
returning *<br>
<br>
The main reason for such thing is to reduce amount of queries to
perform, since this queue could work at about 50 objects per second
with 4 state changes.<br>
<blockquote cite="mid:%3C4BA99567.6070502@postnewspapers.com.au%3E"
type="cite">
<pre wrap="">One option is to SELECT ... FOR UPDATE NOWAIT before your UPDATE or DELETE.
</pre>
</blockquote>
Yep, thank you very much!<br>
<br>
But, it would be good feature to somehow allow to explicitly set order
of multi-row update / delete, <br>
or to 'delete but locked', meaning delete all rows by given query
parameters, except locked ones. <br>
<br>
--<br>
MRJ<br>
</body>
</html>