Re: How to update multiple rows atomically
- From: Bob Badour <bbadour@xxxxxxxxxxxxxxxx>
- Date: Sat, 29 Jul 2006 15:42:03 GMT
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.
.
- Follow-Ups:
- Re: How to update multiple rows atomically
- From: Marshall
- Re: How to update multiple rows atomically
- References:
- How to update multiple rows atomically
- From: Marshall
- Re: How to update multiple rows atomically
- From: Marshall
- Re: How to update multiple rows atomically
- From: Bob Badour
- Re: How to update multiple rows atomically
- From: Marshall
- How to update multiple rows atomically
- Prev by Date: Re: How to update multiple rows atomically
- Next by Date: Re: How to update multiple rows atomically
- Previous by thread: Re: How to update multiple rows atomically
- Next by thread: Re: How to update multiple rows atomically
- Index(es):
Relevant Pages
|
