Maintaining data integrity with multiple users

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I'm working on a multi-user system that at times may result in 2 users
accessing the same screen. These screens allow the users to both view
and update data. The problem comes when you have user A and user B
viewing the same screen and user A submits his page that writes to the
database. User B is now viewing a screen with invalid data. How do I
prevent this scenario from happening with PHP?

I thought about using a lock flag in a database table that gets set
when a page loads but realized there's a problem if the page is closed
and the the lock flag never gets reset.

What should I be doing to handle a situation like this?  



Re: Maintaining data integrity with multiple users

Quoted text here. Click to load it

PHP runs server-side.  Once user B sees something on his browser,
you can't update the screen again until user B clicks something.
Just about any page displayed on a browser will eventually
become out of date (even one that says who's President of the USA).

You can, however, save all the *OLD* data values in the page (probably
in hidden fields) and when the user submits his update, check that
the values haven't been "changed behind his back" by looking at the
database again and reject or merge such changes.  This also prevents
user B from submitting changes, pressing the BACK button, and
submitting the same page again (perhaps with further changes).
Deciding to merge the changes requires careful thought.  *Independent*
changes (e.g. one customer service rep changes address, the other
adds a service) could possibly be allowed through.

                    Gordon L. Burditt

Re: Maintaining data integrity with multiple users

Hi There,

The way I work this problem is by using a timestamp field in the DB showing  
when the record has been updated last. When a user views a record, the  
timestamp is saved in a hidden field within the form. In the event the user  
modifies the record, the timestamp within the form is compared to the  
timestamp in the DB. If there is a difference then the user is notified with  
an error message and is presented with the updated information.

Note that this only works well when there is small chance of data  
contention. If users frequently run into the situation where the data they  
are trying to modify is stale, they will begin to complain about  
functionality of the system. In cases where there is a high chance of data  
contention, I lean towards a system of record locking.

For record locking, there are two additional DB fields in addition to the  
last updated field. The first field is a record lock timestamp and the  
second is the record lock user id. When a user views a record, they are  
presented with a screen where the data is not shown in editable form fields  
but rather a data screen with a modify button usually located at the top.

If the user clicks on the modify button they are sent to a new page at which  
point the script will verify the record lock timestamp. If the field is  
null, then script will set the the the record lock timestamp and user id  
with the current system time and the id of the user modifying the data and  
then the user is presented with a editable form.

In the event the record lock timestamp is not null, the script first checks  
to see if it is stale by comparing it to the current system time. I usally  
allow 5 minutes for a record lock before it becomes stale. If the record is  
stale, the script sets the record lock information to the new user and  
presents the editable form. If the record lock is not stale, then the user  
is presented with a message saying that the record is being edited by  
another user.

When the user goes to save the modified record data, the script will verify  
that the proper user is updating the record, otherwise an error is  

When using record locking, it is important to have a timeout, otherwise you  
will find yourself with situations where a user locks a record and then  
exits the browser for some reason or another leaving DB records uneditable  
until an admin removes the record lock.


Re: Maintaining data integrity with multiple users

I think the timestamp should work just fine... thanks.

Site Timeline