Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
@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. –  Craig Ringer Jun 3 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? –  Colin 't Hart Jun 3 at 13:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.