Re: deadlock on the same relation

"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".

regards, tom lane