How to update multiple rows atomically
Date: 28 Jul 2006 08:49:41 -0700
Message-ID: <1154101781.332935.84080_at_75g2000cwc.googlegroups.com>
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
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?
Thanks,
Marshall Received on Fri Jul 28 2006 - 17:49:41 CEST