Tom Lane wrote:
"Francesco Formenti - TVBLOB S.r.l." <francesco.formenti@xxxxxxxxxx> 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.formenti@xxxxxxxxxx
