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 - 08:55:29 CDT
