Re: How to update multiple rows atomically

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.)

Marshall Received on Sat Jul 29 2006 - 06:00:00 CEST