On 7 July 2010 17:59, tedd <t...@sperling.com> wrote:
> Hi gang:
> I have *my way* of handling this problem, but I would like to hear how you
> guys do it.
> Here's the problem -- let's say you have a database containing names and
> addresses and you want "approved" users to be able to access the data. As
> such, a user must login before accessing an editing script that would allow
> them to review and edit the data -- nothing complicated about that.
> However, let's say you have more than one user accessing the editing script
> at the same time and you want to make sure that any changes made to the
> database are done in the most efficient manner possible.
> For example, if two users access the database at the same time and are
> editing different records, then there's no real problem. When each user
> finishes editing they simply click submit and their changes are recorded in
> the database. However, if two (or more) users want to access the same
> record, then how do you handle that?
> Cheers,
> tedd

I've developed many multi-user applications (specialising in
Accounting and EPOS systems).

Primarily for DOS and Windows and using SQL Server and D-ISAM data
storage systems.

In all instances, multi-user locking is handled through the use of a
semaphore at the row level. We did look at semaphore locking at the
field level.

For DOS and Windows, the necessity for handling timeouts was reduced
by the fact that if the user was logged in to the app, then the lock
stays - and much to the annoyance of all other users. A simple process
to allow locks for a user to be dropped was required to handle the
instances were the user powered off/crashed/forgot password over lunch
situations. In real terms, the number of collisions was very low. Few
users would actually be entering data into the same record at the same
time. In the vast majority, all the users are in the same room or, at
least, the same building/office.

When I started the web versions of these apps, the semaphore locking
was still required, but was extended to include a timeout based upon
the session timeout.

We did realize that by using web based technology, some of our clients
were looking to allow their users to work from home. So informing the
user that a row was locked, how long it was locked for and who else
wanted it was well worth while.

So we introduced a new structure of lock requests. This was a simple
table containing the id of the current semaphore (all our semaphores
are in a single table), the user id of the person wanting the row and
when they requested the lock.

A user who is placed in the lock requests loop could reject their
request (i.e. come back later sort of thing).

The lock requests would be displayed to the lock holder so that they
could essentially get the message to hurry up - in the office someone
would call out "Is anyone in so-and-so?" and then someone would reply
"Oh yes! Sorry, just coming out now." sort of thing. This process
worked fine. The clients knew that only 1 edit could take place at a

With a distributed work force, the on screen visualisation worked
well. A small little drop down in the corner of the app. Nice and

As the server is more or less constantly being told of the presence of
a user (say once every 30 seconds), the server can easily detect a
dropped user and therefore undo the lock.

The user next in the lock requests table would automatically get the
lock and be informed via the ping that they had control of the row
(disabled inputs would become enabled, etc.). Their entry in the lock
requests table would be deleted and anyone else would see that they
have moved up the queue.

I hope this is of some use to you. Semaphores worked really well for
our apps. The transition from DOS/Windows to the web wasn't as easy as
it could have been - primarily due to the statelessness of the
requests. We did manage to do it without the need for any additional
monitors running on the server. All based upon simply tracking the
datetime of the lock requests, the table lock timeout and the session

In all instances, if a lock has to be cleared due to a timeout, all
the unwritten data (we used AJAX to send edits to the server for
holding) would be held, but the lock removed and the user forced to
login again.

On the old system, a lock could persist and cause everyone to wait. On
the web, a lock could NOT persist and the user creating the lock would
suffer the punishment of having to relogin, retrieve their edits
(which may now be stale) and to try again.

Whatever technique you use, I would recommend getting it as close to
the database as possible. The use of stored procedures, for us, was
essential. Lock evaluation and enforcement was all done within the SQL
server - all apps have to use the stored procedures and this makes the
apps a lot simpler. And allows any app to use the same locking
techniques irrespective of the language the developer wanted to use -
our apps allowed for third party additions to the database, but no
access to our code.

I hope this is of some sense to you. It certainly is an interesting topic.



PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to