8

In a unittest we run this:

BEGIN;
TRUNCATE table1;
TRUNCATE table2;
...
UPDATE ...;
ROLLBACK;

Unfortunately this results in a dead lock if a cron job starts and this job operates on table1 and table2.

DatabaseError: deadlock detected
DETAIL:  Process 15815 waits for AccessExclusiveLock on relation 291262 of database 290999; blocked by process 16391.
Process 16391 waits for RowShareLock on relation 291431 of database 290999; blocked by process 15815.

Is there a way to isolate the unittest from the cron job (to avoid the dead lock)?

We run PostgreSQL 9.0.10 on x86_64 linux

2
  • @Colin'tHart You're quite right, acquiring the locks as described in my deleted answer isn't sufficient. You need to maintain lock acquisition ordering as well. Jun 3, 2014 at 13:07
  • I would have hoped the truncate table1, table2, ..., tableN syntax would have the semantics "acquire all locks and only then truncate all tables". Wouldn't having that solve the problem? Jun 3, 2014 at 13:24

1 Answer 1

8

TRUNCATE should be used only when it is certain that no other session is using the table at the same time. Otherwise DELETE should be used. It's slower (if the number of rows is significant), but it's designed to support concurrent access within the rules of the current isolation mode.

The doc says:

TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table. When RESTART IDENTITY is specified, any sequences that are to be restarted are likewise locked exclusively. If concurrent access to a table is required, then the DELETE command should be used instead.

If DELETE's slowness is not acceptable, to avoid the deadlock with 100% certainty, you'd need to put the entire sequence into a critical section, both in the unit test and in the cron job. A critical section is entered by requesting an explicit lock before any other operation, such as in:

BEGIN
 SELECT pg_advisory_xact_lock(1); -- (1 is arbitrary: choose any value reserved for this purpose by your apps)
 TRUNCATE ...;
 TRUNCATE ...;
 ...processing...
ROLLBACK;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.