#1
  1. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,353
    Rep Power
    62

    PHP, MySQL persistent connections and row level locks


    Ok, I'm starting to get more traffic on my site and want to close up a race condition I knew was always there, but now can be an issue. Specifically, I have a chess website and the game consists of a chess object with two player objects, each starts off with 16 piece objects (king, queen, two rooks, etc.). I then serialize this object and store it in the database. So far, so good.

    There can occur instances, however, when one person is attempting to make a move while another person is doing something else with the object. So because of this, I want to do row level locking. I can do this with

    Code:
    //turn auto commit off
    select * from table1 where id=1 for update
    //do my updates
    //commit the updates
    This works fine and dandy. Any other scripts that do a select...for update on that row will be blocked until the other script commits. If a different row is selected, all is good. And then I encountered another problem, one that happens often enough. Sometimes, I notice a user will have a crappy connection. Part of the php page will run, but not the whole thing. It's running so slowly because of the poor connection, that the user stops the script and tries to run it again. If this happens in the above before the commit happens but after the select, that row is locked with no way (that I can tell) of unlocking it.

    Then, I ran into this little bit of information from php.net:

    The persistent connection of the mysqli extension however provides built-in cleanup handling code. The cleanup carried out by mysqli includes:
    ...
    Release locks acquired with GET_LOCK()
    This appears to be exactly what I need. So I created a simple test

    PHP Code:
    <?php
    print "Let's begin!<br />";
    $cnx->autocommit(FALSE);
    $sql "/* " __FILE__ "*/
              select
                *
              from table1
              where
                id=1 for update"
    ;
                
    $result mysqli_query($cnx$sql) or die("Couldn't run $sql because: " mysqli_error($cnx)));
    $row mysqli_fetch_array($result);

    sleep(10);
    $cnx->commit();
    print 
    "The stuff is {$row['stuff']}";
    ?>
    As you can see, I select a row for update, wait ten seconds, then display one of the fields. While that ten seconds is passing, I try selecting that row in mysqlworkbench and it waits until the commit happens. Right after, it displays no problem. Now for the real test. I run the script above, then stop it while it's waiting. Then I run it again. The row is not locked and all is well. I've tried this exact same experiment previously, but $cnx wasn't created as a persistent connection and when I stopped the script before the commit then tried running it again, the row was locked and there was nothing I could do to unlock it. I guess I could have restarted the database, not sure if that would help. Basically, I waited a few hours and it unlocked. It was just a test table on my local machine, so no harm. But point is, I obviously don't want that happening in my production environment.

    So the question becomes, do any of you guys have experience with this? Have you run into issues? From this php.net page, it says

    There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does. In either case, you can use register_shutdown_function() to register a simple cleanup function to unlock your tables or roll back your transactions. Better yet, avoid the problem entirely by not using persistent connections in scripts which use table locks or transactions (you can still use them elsewhere).
    Now this seems to contradict what was said previously, but I'm guessing the first one was for persistent connections and mysqli and this second quote is for mysql_pconnect, but I would like to get some assurances before I launch it live. Thanks for any help you guys can give.
    Correspondence chess
    nothingbutchess.com
  2. #2
  3. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,353
    Rep Power
    62
    Hmmm...clearly I don't understand how row level locks work. At one point, toying around with test scripts, I stopped a script before the commit. The row was locked for about 15 minutes, inexplicably. I created a new entry and the old one unlocked mysteriously. I'd love to hear from someone using mysqli and row level locks.
    Correspondence chess
    nothingbutchess.com
  4. #3
  5. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,295
    Rep Power
    9400
    It's much easier to solve the "the script ends abruptly if the user stops the page" problem: ignore_user_abort.
  6. #4
  7. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,353
    Rep Power
    62
    Hmmm...looks like this should do the trick. And it has a function, so you don't have to change it in php.ini. I'll try it for a while and see how it goes. Thanks for the info.
    Correspondence chess
    nothingbutchess.com
  8. #5
  9. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,353
    Rep Power
    62
    Ok, this looks pretty impressive. I put a 5 second sleep statement near the beginning of the page in question, then stopped the page. I waited about ten seconds, then checked and confirmed that the page actually ran and updated the database appropriately. I'll test this some more, but this looks like the solution. Thanks, never knew this existed.
    Correspondence chess
    nothingbutchess.com
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    I have nothing to add to this thread other than the fact that when I first read it, I thought you said you had a cheese website, and I was super interested as to how you could have this locking problem with a cheese website.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.

IMN logo majestic logo threadwatch logo seochat tools logo