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@xxxxxxxxx 512.569.9461 (cell) http://jim.nasby.net
