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]
