mjsulliv Posted May 12, 2010 Share Posted May 12, 2010 I'm using PHP to build web pages that access a MySQL database. The users are concerned about multiple users editing the same table in the DB at the same time. My documentation that discusses LOCKs on the DB says that the lock will be release at the end of a script. In my case I'm locking the table in one pair of <?PHP ?> delimiters and unlocking in a different pair.I'm trying to make sure what constitutes the end of a script. I'm guessing that the <?PHP is the start of any "script" and the corresponding ?> is the end of that script. Is this the correct interpretation?Thanks --- Mike Link to comment Share on other sites More sharing options...
ShadowMage Posted May 12, 2010 Share Posted May 12, 2010 I'm trying to make sure what constitutes the end of a script. I'm guessing that the <?PHP is the start of any "script" and the corresponding ?> is the end of that script. Is this the correct interpretation?No. The server goes through each and every line of the file. That means everything inside the <?php ?> tags and everything outside. The end of the script is when there are no more lines of code to be called.So if you have the following:<html><body><p>This is a simple page</p><?phpecho "<p>This is inside PHP</p>";?><p>End of document</p></body></html> The line with </html> would be the end of the script. The HTML is then sent to the browser. Link to comment Share on other sites More sharing options...
mjsulliv Posted May 12, 2010 Author Share Posted May 12, 2010 Hmmm… Then I’m having trouble trying to envision conceptually how a DB interface is built using PHP. I have an “edit.php” page that displays a form where the user enters a table record number and clicks a submit button that performs LOCK and SELECT queries. The form is sent to the server which query’s the DB for the record. The “edit.php” page is then again displayed, this time with the retrieved record displayed. The user then uses the form fields to enter new data for the record and presses different submit button which performs UPDATE and UNLOCK queries on the DB. If the “script” ends every time the php file is exhausted how can the LOCK be made to survive ‘til after the UPDATE? Am I missing some concept of how PHP is supposed to be used to access a DB? Link to comment Share on other sites More sharing options...
chibineku Posted May 12, 2010 Share Posted May 12, 2010 At the end of a PHP script, if you haven't called it yourself, mysqli_close($conn) is implicitly called. If you call it sooner, then that is where the script gives up its database access and it becomes unlocked again. Link to comment Share on other sites More sharing options...
ShadowMage Posted May 12, 2010 Share Posted May 12, 2010 At the end of a PHP script, if you haven't called it yourself, mysqli_close($conn) is implicitly called. If you call it sooner, then that is where the script gives up its database access and it becomes unlocked again.But that still leaves the question:If the “script” ends every time the php file is exhausted how can the LOCK be made to survive ‘til after the UPDATE?I understand what you're asking, but I don't know how it can be done. Link to comment Share on other sites More sharing options...
chibineku Posted May 12, 2010 Share Posted May 12, 2010 You could use AJAX to perform the whole operation without leaving the page, and only when your callback function gets the nod that the update was successful will you unlock the database. What's your target audience - can you be sure they'll have JavaScript enabled, or is that a big no-no? Link to comment Share on other sites More sharing options...
mjsulliv Posted May 12, 2010 Author Share Posted May 12, 2010 But that still leaves the question:I understand what you're asking, but I don't know how it can be done.I'm going to try mysql_pconnect( ) which will create a persistent connection. The danger here is that the user acquires the lock but doesn't perform the up date for a long time thus locking other users out. I'm also concerned about a user that closes the page after aquiring the lock. Any idea if the "onUnload" attribute could handle this? Link to comment Share on other sites More sharing options...
mjsulliv Posted May 12, 2010 Author Share Posted May 12, 2010 You could use AJAX to perform the whole operation without leaving the page, and only when your callback function gets the nod that the update was successful will you unlock the database. What's your target audience - can you be sure they'll have JavaScript enabled, or is that a big no-no?Audience is at this point very small and contained the javaScript will not be a issue. I've never done any AJAX; maybe it's time to look at it, but I've done all of the stuff so far w/ just html, php, and javascript I'm a little reluctant to draw yet another language (I assume AJAX is a language) into the mix. Link to comment Share on other sites More sharing options...
thescientist Posted May 12, 2010 Share Posted May 12, 2010 well, AJAX isn't a language, so no worries there! :)it's method of using Javascripts XMLHttpRequest(). Check out the tutorials, its pretty cool. Link to comment Share on other sites More sharing options...
chibineku Posted May 12, 2010 Share Posted May 12, 2010 AJAX is just a way of using JavaScript to run PHP scripts without changing page.It's a pretty messy affair using neat JavaScript, but you can do it with the jQuery library very easily. jQuery is great anyway, if you aren't already using it. All you do is include it at the top like any other .js file, and an AJAX call looks like this:$.get('script.php', {'key': value, 'anotherKey': anotherValue}, function(data) {//data is whatever you had your PHP output, such as a simple echo 'good' or echo 'bad' //to tell you about the success of a script, or a mysqli error});Something to think about. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 12, 2010 Share Posted May 12, 2010 I would advise against manually locking and unlocking the tables. The storage engine handles locking on its own to ensure the consistency of individual transactions, and if you need more protection than that you'll need to implement something other than just locking people out. For example, keep a timestamp in the table to list the last time a particular record was updated. On the update page where you present the form, include that timestamp as a hidden input (or save it in the session). When they submit the form, compare the submitted timestamp to the one currently in the database. If the current timestamp is now later than the submitted timestamp, then the database was updated in the time it took them to hit submit, and you would redirect back to the form, have all of their data filled in so that they don't have to type it again, give them a message which describes the situation, and maybe even try to find the updated data so they can compare their data with the data that was just submitted. A strategy like this would ensure that no one ever gets locked out entirely. Link to comment Share on other sites More sharing options...
mjsulliv Posted May 13, 2010 Author Share Posted May 13, 2010 Thank you all for the ideas and advice it is MUCH appreciated. I'll probably explore most of the ideas mentioned and may follow up w/ a question or two. Thanks again.--- Mike Link to comment Share on other sites More sharing options...
mjsulliv Posted May 14, 2010 Author Share Posted May 14, 2010 Hello all. Thought it would be nice – and maybe helpful to someone later – to document my solution which I’ve tested and seems to be working.I ended up putting a time stamp in the record every time it is updated or inserted. The edit process reads the record, stores the time stamp in the $_Sessions array, and displays the data to the user. The user makes the desired changes and presses a submit/insert button. The script now locks the table and reads the yet unmodified record again, checks if the timestamp is still what it was when first read. If it’s the same it updates, otherwise it does not perform the update and notifies the user of the failure. The end of the script unlocks the table. With this process the lock is very short – essentially for the duration of the check and update of the script.Thanks again for the help; I find this form one of the best places to get help.--- Mike Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.