Deadlock while doing VACUUM and DROP
Hello everyone,
I encountered deadlock during DROP TABLE statement:
2018-10-23 14:49:55 CEST psql > ERROR: deadlock detected
2018-10-23 14:49:55 CEST psql > DETAIL: Process 89171 waits for
AccessExclusiveLock on relation 17582 of database 16390; blocked by
process 50837.
Process 50837 waits for AccessShareLock on relation 20767 of
database 16390; blocked by process 89171.
Process 89171: DROP TABLE IF EXISTS sm.tasks;
Process 50837: autovacuum: ANALYZE sm.tasks
This happenend CentOS 7 with Linux kernel 3.10.0-693 using PostgreSQL 9.6.6.
There was discussion about similar issue without any conclusions 10
years ago, I'm not sure whether it's a bug or some documented behaviour.
/messages/by-id/482CCD42.1060705@students.mimuw.edu.pl
Is there anything I can do make sure that DDL statement won't be
canceled due to collision with autovaccum?
Regards,
Michal Merta
Michal Merta <michal.merta@greycortex.com> writes:
I encountered deadlock during DROP TABLE statement:
2018-10-23 14:49:55 CEST psql > ERROR: deadlock detected
2018-10-23 14:49:55 CEST psql > DETAIL: Process 89171 waits for
AccessExclusiveLock on relation 17582 of database 16390; blocked by
process 50837.
Process 50837 waits for AccessShareLock on relation 20767 of
database 16390; blocked by process 89171.
Process 89171: DROP TABLE IF EXISTS sm.tasks;
Process 50837: autovacuum: ANALYZE sm.tasks
Hm, there's more to that than meets the eye, because the locks were on
two different tables. Have you checked which tables are involved,
eg "select 20767::regclass"?
Is there anything I can do make sure that DDL statement won't be
canceled due to collision with autovaccum?
I suspect your DDL transaction was getting exclusive locks on multiple
tables. That's always hazardous. It might be easier to retry the
DDL than try to guarantee no deadlock. Or break it up into separate
transactions for each table.
regards, tom lane