www.webdeveloper.com
Results 1 to 7 of 7

Thread: Race Conditions when accessing MySQL?

  1. #1
    Join Date
    Mar 2005
    Posts
    250

    Race Conditions when accessing MySQL?

    In a new web site I'm building, I may have to create a page that queries the MySQL database for a value in a particular record, increments that value, then queries the database again to update the record with the new value.

    My question is: Is there a danger of a race condition here if this page is loaded by two users simultaneously? (i.e., user A's first query reads the value, then user B's first query reads it, they both increment, and the final value represents only a single increment after both insert queries) Or do PHP and MySQL already have built-in measures to prevent this?

    If it is a danger, what's a possible solution? And how high does the traffic on my site need to be before I should even bother with one?

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    I would think the easiest solution would be to do the incrementing as part of the query, something like:
    PHP Code:
    $query = <<<EOD
    UPDATE table_name SET column_name = column_name + $value WHERE key_column = $id"; 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Mar 2005
    Posts
    250
    Except that I also need to use the new value in the application. So if I did it that way, which would also require a subsequent query to read the new value, I'd run into the same problem: User A's first query increments the record value, then User B's query increments it again, then User A's next query retrieves the twice-incremented value instead of the proper one.

    But remember, this is all happening on a single page request. Like I said, I don't know if PHP or MySQL (or the web server) already prevents this kind of race condition. (If anyone knows, please enlighten me!)

    In researching, I've discovered the MySQL command LOCK TABLES, which would seem to do the job. My only question now is: If a query tries to obtain a table lock, and the table is already locked, does the script wait and try the query again, or fail?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    My understanding is that MySQL waits until it gets its lock. I'm not sure if MySQL has a timeout limit on that, but if your PHP script times out then the lock request will be dropped as soon as your script dies, since such death will release the database connection.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Mar 2005
    Posts
    250
    So the PHP script waits, then? The script doesn't die just as soon as it finds that a lock is already on the table? In other words, I don't have to manually script a check for a certain returned result and a repeat of the query if that result is returned?

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    My understanding is that MySQL will wait until either it gets a lock or the process requesting it dies. Basically, each read/write/lock request gets put on a queue, with MySQL deciding where on the queue it goes depending on what type of request and what type of lock (if any) is currently in place. So your PHP script is ignorant of the MySQL locking: all it sees is different response times from the mysql_query() calls. If for some reason you have a deadlock condition, then eventually your script might time out and die.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Mar 2005
    Posts
    250
    Excellent. You've been very helpful. Thanks!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles