Re: deadlock while doing VACUUM and DROP

On Fri, May 16, 2008 at 5:24 AM, Jan Urbański
<j(dot)urbanski(at)students(dot)mimuw(dot)edu(dot)pl> wrote:
>
> ERROR: deadlock detected
> DETAIL: Process 25423 waits for AccessExclusiveLock on relation 16386 of
> database 1; blocked by process 25428.
> Process 25428 waits for AccessShareLock on relation 16390 of database
> 1; blocked by process 25423.
> Process 25423: drop table manuale ;
> Process 25428: autovacuum: VACUUM ANALYZE public.manuale

I looked at it briefly. ISTM that the main relation and the toast
relation is getting into a deadlock. VACUUM first vacuums the main
relation, commits the transaction but *keeps* the
ShareUpdateExclusiveLock on the main relation. It then vacuums the
toast relation which requires ShareUpdateExclusiveLock on the toast
relation.

If at the same time, another backend drops the main relation. Because
of dependency, the toast relation is dropped first. So the other
backend takes AccessExclusiveLock on the toast relation. It then tries
to drop the main relation, asks for AccessExclusiveLock on the main
relation and gets into a deadlock with the first process vacumming the
relation.

I think we can fix it by making sure that locks on the to-be-deleted
and all the dependent objects are taken first, in an order that would
prevent the deadlock. Alternatively, we can just acquire
AccessExclusiveLock on the main relation before proceeding with the
recursive deletion. That would solve this case, but may be there are
other similar deadlocks waiting to happen. Also I am not sure if the
issue is big enough to demand the change.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com