[Fwd: Re: deadlock on the same relation]
От | Francesco Formenti - TVBLOB S.r.l. |
---|---|
Тема | [Fwd: Re: deadlock on the same relation] |
Дата | |
Msg-id | 4390893D.4070109@tvblob.com обсуждение исходный текст |
Ответы |
Re: [Fwd: Re: deadlock on the same relation]
|
Список | pgsql-general |
Tom Lane wrote: >"Francesco Formenti - TVBLOB S.r.l." <francesco.formenti@tvblob.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.formenti@tvblob.com
В списке pgsql-general по дате отправления: