[Fwd: Re: deadlock on the same relation]

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".
>
> regards, tom lane
>
>
>
>

Unfortunately, the first operation I do after the "BEGIN" declaration is
the LOCK TABLE in access exclusive mode, and is the only explicit lock I
perform in all the stored procedures. I'm wondering: if other functions
access to the same table, via SELECT or UPDATE, not specifying an
explicit lock, could this generate a deadlock? The fact that I don't
understand is the common resource on which the two processes are locked
into.
I can imagine a flow like this:

Transaction 1: ---lock table A (for an UPDATE, for instance)
Transaction 2: ---lock access exclusive on table B (at the beginning of
the stored procedure)
Transaction 1: ---try to lock table B (for an UPDATE, for instance)
Transaction 2: ---try to lock table A (for an UPDATE, for instance)

But I think it doesn't generate a deadlock error message on the same
resource (in this case, table B), like the one I've got.

Thanks
Regards,
Francesco

--

Francesco Formenti - TVBLOB S.r.l.
Software Engineer

Via G. Paisiello, 9 20131 Milano, Italia
-----------------------------------------
Phone +39 02 36562440
Fax +39 02 20408347
Web Site http://www.tvblob.com
E-mail francesco(dot)formenti(at)tvblob(dot)com