Question On Database Updates Via ASP 1

[RebelFox]

I'm trying to get a few bits of theory straight in my head regarding ASP. I am a little confused about locking and updates. Hopefully somebody can help me understand how integrity is maintained.

Scenario
---------
Consider an ASP page that reads a record from an Access database via a standard recordset connection. The same page uses the record fields to build an html form in which the user can overtype/change the field values. The user can then hit a 'submit changes' button which will action a second ASP page to apply the the changes.

The second ASP page executes an SQL action command to update the record in the database with the new field values.

My area of confusion
--------------------
What happens if two users try and update the record with different field changes?

They both display the record details in the first page but the second user is slower than the first user hitting the 'submit changes' and hits the button after the first users update has been completed. Will both set of updates against the same record be executed? If so does this mean script programming inherently has no way of exclusive locking a record over a series of ASP pages where a user enters changes and then updates them? Even if I locked the record for the form display for exclusive access there is still a window of opportunity for a second user to get hold of the record in between the first users session form page releasing the lock to call the second page and the second page grabbing the lock again to update the record.

Question
--------
Is this just part of scripting and al you can do is limit the potential for multiple updates at almost the same time?

Many thanks

 

[palbano]

>> My area of confusion

You do not appear to have an "area of confusion" you seem to understand the situation clearly.

This is a "standard" problem of multi-user application development. If you have ever worked with a source control product you should be familiar with how those products handle the problem.

-pete

 

[mwolf00]

An option would be to add the field 'lastUpdated' to each record. Before you update a record, make sure that the lastUpdated field is the same as when the info was loaded. Since Access doesn't support triggers, you should make sure to update the field whenever you update the record...

Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[palbano]

mwolf00,

Ok so now at the second update for the same record you can discover that the record was already updated since it was read, if i follow your post.

So now what do you do? ;-)

In some cases a "checkout/checkin" mechanism is desired but without knowing the requirements for the specific project it's just not possible to "know" the correct approach.

Most of the time this problem is not how to technically solve the problem rather it is discovering the "appropriate" logical path to follow given the application and the intended audience. In some cases you find a can’t please all the people all the time situation. :-(

-pete

 

[mwolf00]

Yeah, it's not perfect, but you can prevent overwriting the update inadvertantly. You open the update page with the new data preloaded and force the second user to review the update again - kind of a pain. You could even compare the two sets of data and give the second user the option to overwrite the new data - depends on how robust you want to make it... Better then just overwriting...

Of course, you could go past that and add a "checked out" field which says that the record is open for update already and not allow the second person to even load the form into the update page. Then you could decide to automatically check it back in after a certain time period if it hadn't been checked in manually. Once again, depends on how robust you want to make the application...

Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[Skittle]

Ok, thanks for your help. Its good to know I have at least understood the problem correctly. The idea of a last update time is good. A 'locked' field on the record makes sense but would require strict navigation settings to prevent a user leaving an update session via a frames link thus leaving the 'locked' setting on the field.

In my current application It will be rare I have more than one user at a time updating the database but at least I now have an idea on how to solve the problem on my next, more demanding one.

Thanks.

 

[mwolf00]

The 'locked' idea would have some means of unlocking - you could do an "onUnload" event on the page that checks to see if the form has been submitted or you could use a session timer so that if the user leaves the update open for more than 15 minutes (or whatever) - you automatically close it...

Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179