I’m curious to see if anyone has implemented their own record locking on the application side and how they accomplished it. What I’m looking for here would not be a db mechanism.
Assuming two users are on the same page looking to edit some fields. One user would edit field1 while the other would edit field2. If not programmed correctly the user who’s update got there second could potentially overwrite field1’s value with the old one. Ideally, a solution would not only prevent this, but would let the second user know what field had changed since he loaded the page.
My first thought is to supply a timestamp of last update that would be sent back as part of the POST and ensure they still match.
That’s one of the main reasons db locking was created.
The first user having done a select for update would have locked out the second user who can see the info in read only mode until after the first user either commits or rolls back their update.
Depends upon the scripting language. I know that ColdFusion has CFLOCK that can lock exclusively or readonly, not just database but files or the session scope, as well.
As far as building a custom locking mechanism… nope… never tried. Sorry.
Everything relating to the data including locking belongs in the db layer. If you place it somewhere else then you are jumbling the layers instead of keeping them properly separate.
The locking should be completely independent of the application layer use of the data.
Not all engines support data locking. In any case, I’m curious how people would attack this problem in the application layer.
EDIT: Additionally, record locking from a UI perspective has to be different from a db perspective. If two users enter a page at the same time, make edits to different fields and submit that update, no db level record locking is going to stop the fact that one of their updates are going to be lost. This is where the application layer comes in.
That should not be able to happen. One will always come before the other.
At an application level you would do a “read for update” call when loading the page to allow the data to be updated. The first such call would return OK while the second would return READONLY.
When the first user either SAVEs or ABANDONs their update then the lock would be removed and the second person would be able to do a “read for update” in order to be able to apply their changes.
This much of the process belongs in the application layer.
It is how the lock itself is implemented that is handled by the db layer.
I’m not so sure, I realize we’re talking in microseconds (nanoseconds?) so the possibilty is extremely remote and for all extent and purposes it is impossible. But I think at least theoretically it is possible that more than one might be at the exact same time.
No matter how close together they are in time one would be processed first and would lock the other out. This would be a process where single threading is required and so even if they occurred at exactly the same time one of the requests would be processed before the other.
I’m not talking about the database running into some sort of a race condition here. Here is the example I’m trying to cover again:
Assume user1 enters the edit page for a record of your web application 12:00:00 (hh:mm:ss)
User 2 enters at 12:00:01 (time does not matter here, what matters is that both users see the same state of the record)
User1 edits field1 at 12:00:30 from ‘foo’ to ‘bar’ and submits (this is a simple POST of all rows back to the application. The application runs an update statement with all columns tot he given row id)
User 2 edits field2 from ‘baz’ to ‘bat’ and submits the change anytime after 12:00:30. Field1 is still ‘foo’ at this point because they have not refreshed
User1’s changes are now gone. The logical action here is for the application to notify the user that something has changed since their ‘edit session’ and refuse the change without them reviewing what has happened.
So… No db db engine locking is gong to help in this scenario. Do we not agree on this?
No - db locking is exactly the way to resolve this.
User one accesses the page at 12:00:00 and does a “read for update”… They then start making their changes.
User two accesses the page at 12:00:01 and does a “read for update” and discovers the page locked. They can see the content but it is read only until the lock is cleared.
User one clears the lock either by saving their changes or cancelling the change at which time user two is then able to do a successful “read for update” to start making their changes.
The “read for update” and “save” and “cancel” calls are handled by the application layer via calls to the db layer which locks the appropriate database records in order to ensure that the application layer lock cannot be bypassed by database calls that don’t know about the application layer locking.
You would have a time limit in which the update needs to be made. If person one has not applied their update within the time limit then their lock would be released and others would then be able to request a “read for update”. If person one tried to apply their change after the time limit then a check would be made to see if the information was locked or had been changed and their update would only be allowed if neither of those applied.
Yes. The control of when the locks are applied and released and responses to attempts to access locked information would be handled in the application layer. The actual locks on the data would be implemented in the db layer.
So we are mixing between layers now. This also adds a great deal of complexity to database abstraction. @felgall, let’s allow my topic to go back to applicaiton layer discussion, please.
Maybe you could have a check of “current data state” at the time of submit vs. “data state at time of form loading” (hidden inputs?) and if any are different let the user know some how?
I was thinking the same kind of idea. When a user visits the edit page, there could be a hidden field for the record’s updated datetime. And when that user finally submits, the app uses that hidden field to check whether the record was changed since the user last accessed it (that is, whether there is a newer updated datetime).
Once you establish that someone is submitting stale content, the bare minimum to do would be to give them the edit page back (with all their changes intact, of course) with an error message saying that the content was changed by someone else since you started editing. A little nicer would be if you showed the user’s current work side-by-side with the latest version from the DB. Nicer still would be if you could diff and highlight the differences. And if you want to go for the super brownie points, you could give the user some mechanism to merge their changes into the latest version. The diffing and merging could even be all client-side, JS based.
