Thread: deadlock on the same relation
Hi all,
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
I don't understand what's happening; is it possible that a deadlock is
detected on the same relation (that is, the stored procedure using the
ACCESS EXCLUSIVE LOCK mentioned before) ?
I'm using Postgres 7.4.7 on a Debian Sarge.
Thanks!
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
"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
On Fri, Dec 02, 2005 at 10:15:04AM -0500, 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". 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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Mon, Jan 16, 2006 at 11:57:35AM +0100, Francesco Formenti - TVBLOB S.r.l. wrote: > Jim C. Nasby wrote: > > >On Thu, Jan 12, 2006 at 06:11:32PM +0100, Francesco Formenti - TVBLOB > >S.r.l. wrote: > > > > > >>I put an ACCESS EXCLUSIVE LOCK on the table of objects at the beginning > >>of the stored procedure, just to ensure that only one process at a time > >>can check and set the status of the object, so the status is always > >>consistent. Is there another way to do that? > >> > >> > > > >Take a look at SELECT ... FOR UPDATE. It will allow you to block access > >to a single object without locking the entire table. > > > > > > > OK!, thank you. Based on your precious suggestions and reading some more > documentation, I think I've found the proper way to resolve the problem > without affecting the performance. > > Given an object that can be in status A, B or C, and given the map of > allowed transitions, such as: > A--->B > A--->C > B--->C > I can resolve the concurrency problem using a technique based on both > optimistic lock and Postgres'MVCC strategy. > Setting the new status, the function set_status(.) will check for both > starting and ending status, so it will look like: > > UPDATE set status=newstatus WHERE (obj_id= $1 AND curr_status= $2) > > The MVCC model checks if the WHERE clause is valid before applying the > changes; since the check is made for object id and for the current > status, if the status has changed due to the commit of a concurrent > transaction, the update will fail, and the object status will remain > consistent. > I think this should work, I didn't tried it iet. Of course, the proper > WHERE clause could be placed in the stored procedure, maybe reproducing > the finite state machine of the object (e.g., if I want to put the > object in status C, I will check WHERE obj_id= $1 AND curr_status <> > new_status, rather than curr_status = $2). Yes, simply checking for a valid current status will allow this to work. You could also put the state transition information into a check constraint to enforce the state machine. At a former job I actually had the states named (in an external table), and did the check with a trigger that read the valid transition information from another table. > So, I will not use explicit locks here. However, I'm still curious about > the model of locking, because I don't understand the reason of the > deadlock on the same relation. How does it works under the hood? I > assumed a model like non-recursive mutex locking: I lock the mutex > accessing to a protected area, and other processes call acquire(.) and > wait for the mutex to be freed; so, there's a queue. As a test, I tried > to put an access exclusive lock at the beginning of all my stored > procedures, and to increase the deadlock_timeout, trying to enlarge the > waiting buffer of the queue, but the result has been awful! There were > deadlock on ALL of the stored procedures! :-( > Is the queue model valid for locks? Or something like try_acquire(.) > rather than acquire(.) happens, so, if the mutex is locked, an error is > raised and no queue is made? I'm not a C coder, so I can't help you there. What I can tell you is that anytime you're updating multiple things in the same transaction you're at risk for a deadlock. In your case it was probably due to either updating multiple rows in the same table in a different order (ie: transaction 1 updates ID 1, 3, 5 while transaction 2 updates 5, 7, 1 - risk of deadlock), or there were triggers involved that could operate against other objects (remember that RI is done as triggers). -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net