At 11:37 AM -0400 9/20/01, Kevin Slean wrote:
>I have come across a flawed transaction counter in a mysql application that
>I now support. I believe it is flawed because it does not use record
>locking to deal with concurrency and was hoping someone could give me ideas
>on how to correct.
>The transaction counter works like this:
>1) Web transaction starts.
>2) Transaction queries counter value stored in mysql table.
>3) Transaction increments counter in its own address space.
>4) Transaction applies update to original counter with newly incremented
>5) Web transaction ends.
>Since many of these transactions run concurrently (over 100 at the same
>time), and steps 2, 3, and 4 are not atomic, they are probably stepping on
>each other's results.
>Any ideas are appreciated.
UPDATE tbl_name SET counter = LAST_INSERT_ID(counter+1)
This does steps 2-4 in such a way that clients won't interfere with
each other. The second query returns the value that you updated
the counter to, regardless of whether other clients have changed it
in the meantime.
Paul DuBois, paul@stripped