Re: How to update multiple rows atomically

Bob Badour wrote:
> Marshall wrote:
>
> > Marshall wrote:
> >
> >>Here's a possibly-interesting DML question.
> >>I want to update multiple rows in a table, but
> >>I only want to do so if I can do the update on
> >>all of them. A small example:
> >>
> >> CREATE TABLE T(
> >> id int primary key,
> >> owner int default 0
> >> );
> >>
> >> insert into T(id) values (1), (2), (3),(4), (5);
> >>
> >>I have five rows in the table, representing five resources
> >>that must be locked for exclusive use. The "owner" column
> >>is the id of the exclusive holder of the lock for
> >>the resource specified by "id".
> >>
> >>To aquire resources 2 and 3, I want to put my owner id
> >>value, 1, in the owner attribute for rows where id in {2, 3}.
> >>But I should only do so if they are *both* unlocked (owner=0.)
> >>I don't want to lock just one up, since I need both to
> >>proceed.
> >>
> >>Here's the update I came up with:
> >>
> >> UPDATE T set owner = 1 WHERE (id = 2 or id = 3) and
> >> (SELECT count(*) from T
> >> WHERE (id = 2 or id = 3) and owner = 0) = 2;
> >>
> >>
> >>In essence I'm implementing application locking at the
> >>application level. That *is* the right thing to do,
> >>isn't it?
> >
> > Here's what I came up with later:
> >
> > UPDATE T t1, T t2 set t1.owner = 1, t2.owner = 1 where
> > t1.owner = 0 and t2.owner = 0 and t1.id = 2 and t2.id = 3;
> >
> > It seems an interesting update to me, in that it depends on
> > a multi-row condition. This makes it possible to do atomically
> > something that requires a carefully thought out synchronization
> > dance otherwise.
> >
> > 1) lock the first row with test-and-set
> > 2) if we didn't do the update, fail
> > 3) lock the second row with test-and-set
> > 4) if we didn't do the update, fail
> >
> > with it not being obvious what the correct order to lock the
> > resources is. (Lots of programmers fail to understand
> > that in locking multiple resources, it is necessary always
> > to lock them in a canonical order to avoid deadlock.)
>
> What makes you think the above update statement will avoid deadlock? Or
> starvation? Or any other concurrency problem?

What makes me think any of those things is my lack of a mental model of locking in DBMS products.

So, *would* the above update, (or my earlier one) being made from a variety of processes, be subject to deadlock or starvation? It seems clear it would be immune to race conditions, but again I don't really have a sufficient mental model to say.

Alternatively, the more important question is: what can I go read so as to be able to answer the above question myself?

Marshall Received on Sat Jul 29 2006 - 16:35:34 CEST