List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 6 2003 2:09am
Subject:Re: I thought single UPDATE statements were atomic
View as plain text  
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
Thread
I thought single UPDATE statements were atomicMark Rages6 Jun
  • Re: I thought single UPDATE statements were atomicO'K Web Design6 Jun
    • Re: I thought single UPDATE statements were atomicMark Rages6 Jun
      • Re: I thought single UPDATE statements were atomicMark Matthews6 Jun
  • Re: I thought single UPDATE statements were atomicPaul DuBois6 Jun
    • Re: I thought single UPDATE statements were atomicMark Rages6 Jun
  • Re: I thought single UPDATE statements were atomicJon Haugsand6 Jun
    • Re: I thought single UPDATE statements were atomicMark Rages6 Jun
RE: I thought single UPDATE statements were atomicJosh Smith6 Jun
RE: I thought single UPDATE statements were atomicKen Simpson6 Jun
  • Re: I thought single UPDATE statements were atomicMark Matthews6 Jun