Re: deadlock on the same relation - Mailing list pgsql-general

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