PHP, MySQL persistent connections and row level locks

Dev Shed Forums Sponsor:

March 9th, 2013, 01:11 PM

colpaarm

300lb Bench!

Join Date: Aug 2001

Location: New York

Posts: 2,343

Time spent in forums: 5 Days 10 h 50 m 4 sec
Reputation Power: 60

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:

Quote:

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

Quote:

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

March 9th, 2013, 04:11 PM

colpaarm

300lb Bench!

Join Date: Aug 2001

Location: New York

Posts: 2,343

Time spent in forums: 5 Days 10 h 50 m 4 sec
Reputation Power: 60

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.

March 9th, 2013, 04:56 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,698

Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Months 1 Week 4 Days 4 h 53 m
Reputation Power: 8969

Send a message via Google Talk to requinix

It's much easier to solve the "the script ends abruptly if the user stops the page" problem: ignore_user_abort.

__________________
Site rules and FAQ | Project Suggestions | mod_rewrite guide | PHP New User Guide and FAQ - PHP Stickies | Regex Resources

March 9th, 2013, 05:24 PM

colpaarm

300lb Bench!

Join Date: Aug 2001

Location: New York

Posts: 2,343

Time spent in forums: 5 Days 10 h 50 m 4 sec
Reputation Power: 60

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.

March 9th, 2013, 05:57 PM

colpaarm

300lb Bench!

Join Date: Aug 2001

Location: New York

Posts: 2,343

Time spent in forums: 5 Days 10 h 50 m 4 sec
Reputation Power: 60

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.

March 11th, 2013, 08:54 AM

ManiacDan

Likely to be eaten by a grue.

Dev Shed God 10th Plane (9500 - 9999 posts)

Join Date: Oct 2006

Location: Pennsylvania, USA

Posts: 9,804

Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 2 Months 3 Weeks 17 h 28 m 53 sec
Reputation Power: 6112

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.

PHP, MySQL persistent connections and row level locks

Developer Shed Advertisers and Affiliates