List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 20 2001 3:50pm
Subject:Re: Transaction Counter flawed
View as plain text  
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
>value.
>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)

SELECT LAST_INSERT_ID()

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.

>
>Kevin


-- 
Paul DuBois, paul@stripped
Thread
Transaction Counter flawedKevin Slean20 Sep
  • long running queriesMichael Blood20 Sep
  • Re: Transaction Counter flawedPaul DuBois20 Sep