PHP Database multiple user entry

alohaaaron

Hi, I have a web form that displays data from a mysql database. Multiple people access the web page and update the data and then hit the submit button to post it to the database. If person A is viewing and making updates to the same record through the web as person B, how will I keep the data accurate? Person A's update may overwrite person B's. Thanks!

NogDog

MySQL Transactional and Locking Statements

alohaaaron

Thanks. Is there any other way to do this type of multiple user transaction to a database through a web form without locking records on the database end? Maybe through a session? Also are there similar commands for MS SQL Server?

NogDog

Sorry, I get so used to 95% of the questions here being MySQL-related that I sometimes make that assumption. 🙂

I'm sure that MSSQL has the same sort of functionality, but I've never had occasion to work with it, yet.

As far as doing any sort of script-level locking, it's a little messy in that each user's request is spawned as its own, independent process (or instance, or whatever it's called), so there's not some PHP-global memory space you can use to alert other instances that you are using the database.

One possible work-around would be to use [man]flock/man to get/release a lock on some arbitrary file. Then your script would only run its queries once it successfully gets a lock, and then release the lock (via [man]fclose[/man]) once it's done.

But if you can do it via database transactions/locking, that will probably be more efficient and robust, so I'd try going that route, first.

alohaaaron

Thanks! The trouble is I have one user that is managing the records and needs to view multiple records at a time displayed like an Excel sheet (grid view) with input text fields. Then they make changes to multiple fields and hit "save" or "submit". Other users are opening up individual records one at a time. With database record locking when would the lock be released so another user could update that record?

flock seems to be more for files than a database though.

NogDog

alohaaaron wrote:
Thanks! The trouble is I have one user that is managing the records and needs to view multiple records at a time displayed like an Excel sheet (grid view) with input text fields. Then they make changes to multiple fields and hit "save" or "submit". Other users are opening up individual records one at a time. With database record locking when would the lock be released so another user could update that record?

Assuming database locking, any lock would be released as soon as either an appropriate SQL statement is submitted to release the lock or else when the database connection is closed, either explicitly (such as by [man]mssql_close/man) or automatically when a script completes

flock seems to be more for files than a database though.

Yes, I was just suggesting it as a kludgy way to do locking at the script level instead of at the database level. Basically you'd have to build a loop into your script to keep trying to get a lock on the specified file before doing the database transactions. (You would need to build a timeout mechanism in case the file lock does not get released for some reason, probably logging an error when such an unlock problem occurs.

Roger_Ramjet

All databases have to deal with concurrent updates, or rather the programmer does and the db provides tools to help. These vary from db to db.

In mssql you have a special column data type called 'TIMESTAMP'. This is nothing like a timestamp as we know it elsewhere - it does not store a date/time value. Instead it stores an incremented integer that is unique across the db. Every time any record is updated, its 'timestamp' is updated. If you want to know if a record has been updated since you last read it you compare the timestamp value with the one you read. If it has not changed then you perform your own update operation: if it has changed then you inform the user and present the new data to them for a new edit.

This method is necessary because even with transactions and record locks, you could miss the lock. If the record was locked-updated-unlocked between your read and your attempt to update you would not know that the underlying data had changed and so overwrite the other user's update.

A moment's thought will show that individual users could be continually beaten too it on a busy db. So ways are needed to queue each user so that everyone eventually gets their turn to perform the update. How you do this depends upon the db engine and it's tools. Normally we just apply a write lock so that everyone who reads it after you opened it for update knows that an update is in progress,; or rather their client software knows. You can then retry your own lock at a random interval until you get your chance - some dbs do all this for you including the queueing. I forget how mssql handles it but books-online (MS BOL) will tell you.

Sxooter

The way Roger mentions works well enough. You might need a "scraper" program to come in and remove old write locks occasionally should a user be disconnected.

Another method that often works well is to get your data something like this: (this is in pgsql syntax, you'll have to translate)

select editfield, md5(editfield) from table where id=$rec

Then create your form with editfield in a textarea, and md5(editfield) in a hidden.

When you go to apply the update, you do it like this:

update table set editfield='$neweditfielddata' where md5(editfield) = $md5editfield;

And check the number of rows updated. If it's 0 then the field was changed while you had it in your editor and you can't apply the changes (or maybe can, depending on how you do it)

Adnate

When a user requests a record to edit I firstly run an UPDATE query that locks the record.
$query = “UPDATE table SET locked_on = $date_time, locked_by = Suser_id WHERE id = $id AND (locked_by = 0 OR locked_by = $user_id OR locked_on < $time_out)”;

where:
$date_time = time(); ie is the current time measured in the number of seconds since the Unix Epoch (I tend to control all my date and times by reference to the PHP server)

$time_out is $date_time adjusted by the required lock out period
eg for a six minute lock $time_out = $date_time – (6 * 60);

$user_id is the individual id for the user requesting the record

$id = the id of the record to be edited

If the affected rows == 1 then you can run a SELECT query and carry on with the editing process.

If the affected rows == 0 then the record is locked out to another user and, maybe you will allow read only access with a suitable message.

You need to check that the lock is still valid when you come to submit the form. For example, if the user submits the form after the time out period another user may have successfully opened it for editing. So I revalidate the lock and run the same UPDATE query before I process the form. If affected rows == 1 all is OK, and this will occur even if I try to submit the form way past the time out period provided another user has not reset the lock. If affected rows == 0, then I need to create a message that the user has been timed out.

When the form is finally processed unlock the record by adding the following to the query that updates the record:

“... $locked_by = 0 ...”

Now I can build on this process by adding some JavaScript that fires off an http request before the lock out period. The request runs the same UPDATE query thus constantly refreshing the lock for so long as the user has his browser open. Hence the request must parse the $user_id and $id values. The JavaScript could have a long stop period at which point it stops refreshing but kicks in a countdown timer. For example, let’s say I set a 6 minute lock out period. I might refresh the lock using the http request once every 2 minutes up to a maximum of 9 refreshes (20 minutes in all) and then a 3 minute countdown kicks in. The 3 minute countdown would also kick in if the http request failed; so we need to make sure that the sum of the interval and countdown (2 + 3 = 5mins) is less than the desired time out period.

This approach allows a short(ish) lock out period (6 mins in my example) but provided the browser remains open and active that period can be extended to a maximum (23 mins in my example).

There is no need to run a CRON or any other scheduled script to release the locks since they are timestamped and this controls the ability to subsequently lock the record to a new user.