Роман Маширов 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
