Re: I thought single UPDATE statements were atomic

[Paul DuBois]

At 18:52 -0500 6/5/03, Mark Rages wrote:
>According to the docs, single update statements are atomic.

That's correct.

>
>So why doesn't this work?
>
>mysql> create table t (num INT, UNIQUE (num));
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> insert into t values ('1');
>Query OK, 1 row affected (0.00 sec)
>
>mysql> insert into t values ('2');
>Query OK, 1 row affected (0.00 sec)
>
>mysql> select * from t;
>+------+
>| num  |
>+------+
>|    1 |
>|    2 |
>+------+
>2 rows in set (0.00 sec)
>
>mysql> update t set num=num+1;
>ERROR 1062: Duplicate entry '2' for key 1
>mysql>

Consider what happens if MySQL tries to update the first record and
then the second, version what happens if it tries to update the second
record and then the first.

Then add an ORDER BY clause that will cause MySQL to update the records
in the order that doesn't result in duplicate keys.

>
>Help!
>
>Regards,
>Mark
>markrages@stripped


-- 
Paul DuBois
http://www.kitebird.com/
sql, query