Wow - what a lot of responses.
First a little more detail - use case (for example):
Take a customer record, a basic record has previously been
created and the
customer has completed some forms so we are now wanting to
complete all of the
details about contact information, financial details, key
assets, health
information, etc, etc. The system relies on a number of
classifications which
need to be looked up, anyways, editing this record can take upto
20 minutes. The size of this record certainly justifies breaking
it up into a number of
smaller records stored in separate tables, however the same
problem remains
because the user wants to see all of this information on 1
(scrollable) web
page, we are still holding a lock on the information for the
duration of the
request.
Potentially there are maybe 700 people who could be editing this
record for
different reasons. Accuracy of the information is critical - we
don't want
personal details being sent to a neighbour.
The nature of the application means that users will be reviewing
a record each
year, and if any changes are made they are often made to the
same record around
the same time of the year - increasing the possibility of two
people wanting to
edit the record at the same time.
Besides all of the above, the user has asked for exclusive
editing of a record -
such that other users can still read the existing record but
nobody else can
update the record while it is locked. The user understands the
concept of
networks issues and broken connections and therefore accepts a
timeout condition
on the lock of say 20 minutes.
Next, how I will do this:
1. To establish a lock:
a. I need a LOCK table: foreign-key, timestamp, userid
b. In my Session I need a collection of timestamps
c. Create a timestamp value - add it to my collection of timestamps
d. Add a record to the LOCK table using my timestamp value
e. Select from the lock table by foreign-key - if mine is the
only record then
read the record and begin editing, the lock was successful
2. Release the lock: (PS This is the difficult bit)
a. Retrieve the LOCK records by foreign-key and userid,
hopefully there should
only be one.
b. Check the timestamp value against my Session collection of
timestamp values
c. If the timestamp is in my collection then save the edited
record and delete
my LOCK record
d. If the timestamp is not in my collection then the save has
failed - tell the
user gently
3. Dealing with an existing lock:
a. While trying to establish a lock I find 2 LOCK records
b. The latest record is mine, ignore that and look at the
earlier record
c. Look at the timestamp on the earlier record, if older than 20
minutes then
delete this record and continue as normal
d. Look at the userid on the earlier record, if it is mine then
delete it and
continue as normal (assumes my browser died, rebooted my
computer, whatever,
it's very typical for people to go straight back to what they
were doing when
their system crashed)
e. If the timestamp is less than 20 minutes old then delete my
LOCK record and
report to the user that the record is locked by another user
4. You may want a maintenance process that cleans up broken
locks, automatically
deleting locks with a timestamp older than 24 hours. You could
schedule this
nightly or weekly - it's not critical.
Why the collection of timestamps?
1. It allows the user to use the back button on the browser,
either during
editing or after editing (if it didn't save correctly the first
time).
2. They might need to edit a related record in a different table
as part of
updating the main record - by storing each timestamp in a
session collection we
don't need to maintain these timestamps on our forms.
Why add the lock record first?
To avoid sequence problems like:
a: User 1 looks for lock on id=1 - none found
b: User 2 looks for lock on id=1 - none found
c: User 1 adds a lock record
d: user 2 adds a lock record
Now the worst possible outcome is:
a: User 1 adds a lock record on id=1
b: User 2 adds a lock record on id=1
c: Both users read and find two records with short timestamps -
they are both
informed the record is in use and to try again later - both
records are deleted.
Finally - the weakness:
1. The lock this gives you is only guaranteed for 20 minutes.
Once that time is
up somebody else can overwrite your lock with their own. It is
better to set
this timeout higher rather than lower.
2. You can overwrite your own lock. A user could open two
browsers and edit the
same record in each, the latter save overwriting the first.
Hmmm, I think this
comes under 'user error'.
I think that explains it fairly well.
Cheers
mc
-------------------------------------------------------------------
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]