Re: How to update multiple rows atomically

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 28 Jul 2006 15:56:41 GMT
Message-ID: <Zsqyg.28120$pu3.372463_at_ursa-nb00s0.nbnet.nb.ca>


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?
>
> When I try this on MySQL, it says:
>
> ERROR 1093 (HY000):
> You can't specify target table 't' for update in FROM clause
>
> Is this a real issue, or is it just MySQL being its usual difficult
> self?
>
> Is my update statement decent, or are there better approaches?

Try using the SQL approximation of the Tutorial D 'with' statement by giving the T in the subselect a distinct name. Received on Fri Jul 28 2006 - 17:56:41 CEST

Original text of this message