Maintaining data integrity with multiple users

[~john]

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?

thanks,

~john

[Gordon Burditt]

>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?

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

[Someuser]

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
presented.

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.

Cheers,
James

[~john]

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