Re: How to update multiple rows atomically
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 29 Jul 2006 13:55:29 GMT
Message-ID: <lNJyg.28551$pu3.384727_at_ursa-nb00s0.nbnet.nb.ca>
>>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?
Date: Sat, 29 Jul 2006 13:55:29 GMT
Message-ID: <lNJyg.28551$pu3.384727_at_ursa-nb00s0.nbnet.nb.ca>
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? Received on Sat Jul 29 2006 - 15:55:29 CEST