Re: Autovacuum deadlock - bug or not?

>Hmm, the CLUSTER on vehicle_unit_data_200407 wouldn't have taken any
>lock on vehicle_unit_data_200301.  Were you perhaps issuing a series
>of CLUSTERs inside a transaction block?  That would pile up exclusive
>locks on all the tables involved, which is certainly deadlock-prone.

Ah, that explains it...the clustering is done from a function that's cluste=
ring all (child) tables of a parent, and as I've come to understand, all fu=
nctions have a built-in transaction (the "Clustering idx_foo on bar" notice=
 message also originates from that function)

I'm pretty sure though that 15865 was the autovacuum process, but I'll have=
 to run the function with autovacuum turned on before I can verify that thi=
s is (was) the case.

/Mikael


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: den 17 november 2005 17:42
To: Mikael Carneholm
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Autovacuum deadlock - bug or not?=20


"Mikael Carneholm" <Mikael.Carneholm@WirelessCar.com> writes:
> dfol=3D> select pgc.oid, pgc.relname from pg_class pgc where pgc.oid in (=
68950, 68122);
>   oid  |         relname=20=20=20=20=20=20=20=20=20=20
> -------+--------------------------
>  68950 | vehicle_unit_data_200407
>  68122 | vehicle_unit_data_200301

> NOTICE:  Clustering idx_vehicle_unit_data_200407_person_information__id o=
n vehicle_unit_data_200407
> ERROR:  deadlock detected
> DETAIL:  Process 29022 waits for AccessExclusiveLock on relation 68950 of=
 database 16390; blocked by process 15865.
> Process 15865 waits for AccessShareLock on relation 68122 of database 163=
90; blocked by process 29022.

> So it seems that it was the clustering of idx_vehicle_unit_data_200407_pe=
rson_information__id on vehicle_unit_data_200407 that caused the deadlock.

Hmm, the CLUSTER on vehicle_unit_data_200407 wouldn't have taken any
lock on vehicle_unit_data_200301.  Were you perhaps issuing a series
of CLUSTERs inside a transaction block?  That would pile up exclusive
locks on all the tables involved, which is certainly deadlock-prone.

I'm also wondering where that NOTICE "Clustering ..." came from, because
there is no such message anywhere in the 8.1 PG sources.  You *sure*
this is 8.1?

There's something funny about 15865 too; you said that was an autovacuum
process but I don't think so.  VACUUM doesn't take AccessShareLock;
there's a different lock type that that tries to acquire.  And it
doesn't take any locks at all on more than one user table at a time.

            regards, tom lane