Re: I thought single UPDATE statements were atomic - Mark Rages - com.mysql.lists.mysql

11 messages in com.mysql.lists.mysqlRe: I thought single UPDATE statement...

From	Sent On	Attachments
Mark Rages	05 Jun 2003 16:52
O'K Web Design	05 Jun 2003 18:06
Mark Rages	05 Jun 2003 18:17
Mark Matthews	05 Jun 2003 18:44
Paul DuBois	05 Jun 2003 19:09
Mark Rages	05 Jun 2003 20:13
Jon Haugsand	06 Jun 2003 04:07
Josh Smith	06 Jun 2003 05:32
Mark Rages	06 Jun 2003 10:06
Simpson, Ken	06 Jun 2003 10:18
Mark Matthews	06 Jun 2003 10:54

Subject:	Re: I thought single UPDATE statements were atomic
From:	Mark Rages (mark...@mlug.missouri.edu)
Date:	06/05/2003 08:13:09 PM
List:	com.mysql.lists.mysql

On Thu, Jun 05, 2003 at 09:09:36PM -0500, Paul DuBois wrote:

At 18:52 -0500 6/5/03, Mark Rages wrote:

According to the docs, single update statements are atomic.

That's correct.

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.

I didn't realize the physical order of the rows would be so important in a "relational" database.

I am using "mysql Ver 11.18 Distrib 3.23.54, for pc-linux (i686)" so ORDER BY isn't going to work.

I guess I'll have to do something like this:

When I need to increment a group of rows:

begin; update row N... update row 2... update row 1... commit;

And when I need to decrement a group of rows:

begin; update row 1... update row 2... update row N... commit;

What a hack! Isn't there some better way? I guess I could forget about enforcing a consistent database and drop the unique constraint on the column.