On Fri, Dec 02, 2005 at 10:15:04AM -0500, 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". 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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
