Re: How to update multiple rows atomically
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 29 Jul 2006 15:42:03 GMT
Message-ID: <flLyg.28601$pu3.386146_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?
Date: Sat, 29 Jul 2006 15:42:03 GMT
Message-ID: <flLyg.28601$pu3.386146_at_ursa-nb00s0.nbnet.nb.ca>
Marshall wrote:
> 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?
Ah, now, there's the rub. Whether it might experience deadlock or starvation or any other concurrency problem will depend on what concurrency mechanisms the dbms implements, which concurrency options the dba and various users choose, and how the dbms implements them. It will also depend on the other concurrent queries.
Most dbmses detect deadlock automatically and use timeout to deal with starvation etc. They don't do much to prevent deadlock or starvation etc. Received on Sat Jul 29 2006 - 17:42:03 CEST