Обсуждение: 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.

https://www.postgresql.org/message-id/flat/482CCD42.1060705%40students.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