TRUNCATE in transaction: dead lock

Question

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

Answer 1

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;