Re: deadlock on the same relation

On Fri, Dec 02, 2005 at 10:15:04AM -0500, Tom Lane wrote:
> "Francesco Formenti - TVBLOB S.r.l." <francesco(dot)formenti(at)tvblob(dot)com> writes:
> > I have a problem about deadlock. I have several stored procedures; only
> > one of them uses ACCESS EXCLUSIVE LOCK on a table; however, all the
> > stored procedures can access to that table, using SELECT, INSERT or UPDATE.
> > The stored procedures are called by different processes of an external
> > application.
>
> > In a non-predictable way, I obtain error messages like this one:
>
> > 2005-11-29 18:23:06 [12771] ERROR: deadlock detected
> > DETAIL: Process 12771 waits for AccessExclusiveLock on relation 26052
> > of database 17142; blocked by process 12773.
> > Process 12773 waits for AccessExclusiveLock on relation 26052 of
> > database 17142; blocked by process 12771.
> > CONTEXT: PL/pgSQL function "set_session_box_status" line 7 at SQL statement
>
> Probably you have been careless about avoiding "lock upgrade"
> situations. If you are going to take an exclusive lock on a relation,
> it is dangerous to already hold a non-exclusive lock on the same
> relation, because that prevents anyone else from getting an exclusive
> lock; thus if another process is doing the exact same thing you are in
> a deadlock situation.
>
> Since SELECT/INSERT/UPDATE take non-exclusive locks, you can't do one of
> those and later ask for exclusive lock within the same transaction.
> The general rule is "get the strongest lock you will need first".

And better yet, don't grab an exclusive lock...

I'm curious; what are you doing that requires one?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461