SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dealing with Lag and Mysql

    Im continuing a post that I started about 2 months ago here:
    http://www.sitepoint.com/forums/showthread.php?t=499800

    Ive done quite a few tests and solutions with no luck. To sum up that thread im having a problem with people running multiple instances of the same URL in which is running a mysql update causing it to run multiple times.

    The solution that came close was from the last poster. I created a session as demonstrated in his example that locked it for when it was being processed.

    The new problem is that I have my sessions stored in mysql. That session variable in the database isnt updated until after the second or third script already ran therefore not locking it successfully when it was first run.

    This is what I was using to test. I setup a script which loads the slow script in several iframes. I then setup a logger to log the queries and the times that they ran. Here are my results... using microtime as the times shown below.

    0.58262400 1193598708
    ----------------------------------
    IFrame 1 runs: UPDATE table SET level=level+1 WHERE id=1;

    0.61139600 1193598708
    ----------------------------------
    IFrame 2 runs: UPDATE table SET level=level+1 WHERE id=1;

    As you can see they run nearly a few microseconds between each other. The script will process all the same queries twice there for leveling up the player multiple times in a single battle.

    Does anyone have any more ideas on how to lock the first script when it executes so the following instances of that script if ran even a few microseconds after wont be allowed to process?

    Thanks for any advice you can offer!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the problem you're running in to is called a race condition. you have action X that needs to be executed only if condition Y is true. the race condition is when you have two or more instances of the script running simultaneously and they both check condition Y at nearly the same time and perform action X.

    what you need are transaction. this requires that you change your table to innodb. then you can lock the row that has the player's battlesleft value:
    PHP Code:
    list($battlesleft) = mysql_fetch_array(mysql_query("select battlesleft from table where id = {$player[id]} for update"));
     
    if (
    $battlesleft] < 1) {
      echo 
    "you have no battles";
    } else {
      
    mysql_query("Update table set battlesleft=battlesleft-1 where id={$player[id]}");
    }

    mysql_query("commit"); 
    during the transaction (which is automatically opened when the SELECT ... FOR UPDATE statement is executed) that one row is locked on the mysql server in such a way that a race condition is impossible. if the player does have at least one battle left, their counter is reduced by one. either way, the COMMIT statement releases the transaction.

    while the transaction is open, if another script comes along and tries to check the battlesleft counter for that user then the mysql server waits for the lock on the row to be released before continuing. therefore, since only one script at a time has access to that counter, you're guaranteed no race condition.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awsome advice! I greatly appreciate it! I just have a few questions on how to utilize transactions.

    Since this locks the row in that table, should I lock it at the very beginning of the battle process when it selects stats and then commit it at the very very end once it decides winner and updates? Or should I utilize several transaction queries after the battle was processed and its doing the updating of the statistics and battles?

    Where it updates the results of the battle like the stats and the battles left is at the very end of the battle process script. Where it checks to see how many battles they have left is at the near beginning. So what would the best location/method to add this since it wont get around to doing it at the very end until after the other one already started a few microseconds after?

    Also are transactions available for mysql 4.1.22 that you know of? I just had my mysql upgraded to that. By chaning the table type will that affect any of the data within it?

    I greatly appreciate your help! Thank you!

    PS. Am I able to nominate you for an award or are you excluded from that since your running that?
    [EDIT] Bummer, I just got around to reading the whole thread.... Well despite not being able to you still deserve it!
    Last edited by Xiosen; Oct 28, 2007 at 22:06. Reason: Just read all the rules of nominating

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    a transaction only survives the lifetime of a connection. since your connections are closed automatically when your PHP script finishes, you have to keep that in mind.

    i suggest that any time you have race condition or a multi-step update that you use transactions. this will guarantee that you entire set of changes makes it to the database. look up ACID in wikipedia to understand what i mean. http://en.wikipedia.org/wiki/ACID

    and yes, mysql 4.1 supports innodb tables, which is ACID compliant and supports transaction.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


Bookmarks

Posting Permissions

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