My web app is used by multiple users accessing the same data base. It is an Engineering application used to control and route changes to Engineering Drawings. There is a page that displays the current state of the drawing which includes buttons to initiate a change. I am concerned that two users may access the same drawing number(which is fine if they just view it). But suppose they both access it then one user makes a change(which will set a lock flag field). The other user has got busy but later desides to make a change to the drawing number they have pulled up on the screen. Since this data has not been refreshed it will not know there is a lock on it now.
How can I fix this so it does not happen? Do I put a timer on the page to force the user to do a refresh on the data? If so how do I do it?
It's common to put a timestamp for each update on the database. The user gets the timestamp (maybe in a hidden field) when they retrieve the record. If it doesn't match the database when they want to update the record, that means somebody else beat them to the update. The system says something like "Another user has changed this record. Please refresh it and see what it looks like." The user usually has to discard his changes, get the fresh record and start over. That's "optimistic" locking, meaning we don't think bad things will happen very often and we can stand the pain if they do. It's also called "first update wins" some times.
For "pessimistic" locking where we think this kind of problem is frequent enough that we want to avoid it, we might update the record with a "locked by userid" column when a user retrieves it for update. Then nobody else can retrieve it for update until he does an update or unlock to unset the "locked by" column. You have to make the user declare his intention to update. And the user might turn off the PC and walk away, leaving the record locked forever.
Any of those choices sound good? [ August 20, 2007: Message edited by: Stan James ]
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
As an aside, Martin Fowler's book "Patterns of Enterprise Application Architecture" has some quite good chapters on this topic.
The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus