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

Improve this question
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. Commented 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? Commented Jun 3, 2014 at 13:24

1 Answer 1

Reset to default
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;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.