scranmer
Posts: 14
|
| Posted: 06/01/2004, 4:34 AM |
|
Updating within multi user systems ie user b data overwritting user a's data if they both go to edit the same record at the same time.
My idea (a lot of changes to impliment but easy to manage) is to add last_maintained datetime to each db table. Retrieve this field and hold it in a hidden field on the page. Then in a BeforeUpdate trigger recheck with the DB that the last_maint date has not changed. If it has, return an error stating the fact and retrieve the original data back or also display a button so the user can determin when to retrieve the info and so letting the user make a note of any changes made.
My initial idea for the solution was to create a locking table. This can hold who, table name, primary key info, timedate. Where this falls down though is if the user just quits or kills the maintenance web page without exiting nicely (ie cancel/update button). You can use the timedate check to see when the lock expires BUT that then defeats the object if the expire time is too short and will p*ss people off if its too long.
Anyone else with a better solution?
Si.
|
 |
 |
BlinkyBill
Posts: 86
|
| Posted: 06/01/2004, 6:19 AM |
|
The first method you describe the most common way on implementing concurrency. Grab the last update datetime when it come to perform the update check this field and action the update if the datetime has not changed.
An alternative to actually doing the check before the update statement(that's one extra round trip remember), is to use the Username and Datetime as part of the where clause, then to check the number of records modifed by the update statement. SQL server for example provides @@ROWCOUNT to give the number of rows affected by the last statement.
An alternative method that we are using to write custom update that only updates the ACTUAL changes the user has made. So if User A changes The phone number, and user B changes city only one field is updated per user, the script that CCS generates out of the box doesn't handle this and you will need to write code to implement. This method still has shortcomming but it's an alternative.
|
|
|
|
E43509
Posts: 283
|
| Posted: 06/27/2004, 9:54 PM |
|
The most common method is to add a last update date/time to each table/row and check that before you perform an update. The web doesn't have that data consistency as you have in client server. Web pages are stateless which means they disconnect from the source. You gotta roll your own
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 07/06/2004, 3:51 AM |
|
To start a discussion and possibly trick peterr, DonB into shedding some ideas:
Two methods of multi-user access have been used through time.
Although various names are used I will use Optimistic and Pessimistic locking.
Optimistic locking would then be defined as first saved is first stored, Pessimistic being arbitrated by really locking table rows whilst updating. As stated earlier the web is a stateless environment so pessimistic locking would be hard to implement.
In an optimistic locking environment one would like to know the state of a row (content of all data) when read, permit a user to change all necessary data and when the user submits, get the current data of the row and check this against the previously obtained data. When comparison is equal, the users data can be submitted. If not equal post a message to the user "data has been changed since you .....'.
Then refresh te field that have changed, allowing the user to re-apply his changes and submit the data again (implementing a CTRL+Z method on fieldlevel).
While this works great for 'normal' data it does inflict a great problem when the user is actually working on memo-type (blob) data.
Suppose typing a multi page comment on a meeting, submitting the data and ending up with a "data changed by another user" message resetting your precious mutli page comment to a one liner.
We have used this method of optimistic locking with great success.
Using a checksum on the datarow. When the user Get's a record we do a checksum() on the row, store the checksum in a temporary variable, let the user do his changes and on submit, we get the row again do a checksum compare this with the stored checksum and decide to allow the update or show the "data changed by another user" message.
To provide for the memo-type (textarea) problem, one could actually show a message to the user "memo being edited by user: XYZ" thereby triggering the users to contact each other (in a closed group) and agree on a change policy. Although this would imply the creation of a user-table registring current activity, the implication on the application will be smaller as there are probably less memo containg tables compared to the total number of tables used AND such a table could already be uysed for logging user activity(!).
For a while now we have been wondering what method CodeChargeStudio (or its users) uses, if any.
We have not really been investigating very hard, but we will be soon.
Implementing the described optimistic locking method would actually not be that hard, using the anchors provided by CCS.
Any comment, tips ?
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
peterr
Posts: 5971
|
| Posted: 07/07/2004, 1:41 AM |
|
Hi,
In our support system we use somewhat of an emulated pessimistic locking. We use "date_locked" and "locked_by" fields to track who locked each record and when. The records aren't locked automatically but the user can view a record and then click the "Lock" button to indicate that he/she is making concious decision to lock and edit the record. The record stays locked for X amount of time or until the record is updated. However, other users can press the "Unlock" button and start making their own changes, which in our situation isn't very dangerous because the main record isn't actually edited, just new support responses are added.
Possibly there could be several variations of this approach implemented.
I would have to think more about this to come up with a different solution or analyze your approach in more detail. Possibly some mix of both methods could work.
This is an interesting topic though.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
marcwolf
Posts: 361
|
| Posted: 07/12/2004, 10:34 PM |
|
We use a similar technique except instead of a date/time we just use an integer.
When we retrieve the record we save the integer into a hidden field.
In the Validate method of the form we add this code
' Handles Record Versioning and Auditing
if (job.EditMode) Then
' We are editing, not adding
' Has this record been modified? Check Versioning
if job.version_no.value <> _
CCDLookUp("version_no", "job", "job_id=" & CCToSQL(job.job_id.value ,"Integer") , DBAIMS_DB) then
job.Errors.adderror "Unable to perform the operation.<br>The record has been Modified after you retrieved it."
Exit Function
end if
end if
To stop the value from becoming too big we also do this
.Version_No.Value = (.Version_No.Value + 1) mod 100
Using this number can also be handy for getting back the number of a newly created record.
with this function
' uses Application Variable UniqueRec fo incremenation
Function CV_UniqueRec()
Dim Val1
' Lock the application
Application.lock
Val1 = Application("UniqueRec")
If Val1 < 5000 then Val1 = 5000
Val1 = Val1 + 1
If Val1 > 20000 then Val1 = 0
Application("UniqueRec") = Val1
Application.UnLock
CV_UniqueRec = Val1
End Function
and with the BeforeBuildInsert assign this number to the Version_no
Then in the AfterExecuteInsert look for it - that will give you your Record ID, and then imediately set the Version_No back to 0.
As mentioned the above works extremely well and is a mainstay in our code.
Of interests and this will take a longer while to explain - we have a extremely functional date storage system that handles all timezones invisibily to the end user.
All dates are stored in the DB as GMT, and a couple of routines shift that date back to the user's timezone, or the servers timezone as required.
Let me know if anyone is iterested and I'll do a write up
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
|
