Seeking advice about implementing Application Level Pessimistic Locking
Joined: Apr 16, 2012
I am working on a fielded 2 tier application suite. The clients are implemented in Java using Hibernate, and they connect to a SQL Server DB. Currently we are using optimistic locking to handle concurrent updates from multiple client applications, however we are finding that this is not graceful enough for our users. Frequently there will be 2 or more users unknowingly editing the same DB record which includes many fields. With optimistic locking, the first one to save their change wins and everyone else's time is wasted as their changes are tossed out. We have considered implementing merge windows but this seems like an awkward solution given the number of fields in some of our records and the large number of edit windows in our applications.
What we would like to do is implement edit windows that will open in a read-only mode if another user has opened the record for editing first. We want to avoid long running transactions though, for scalability reasons. At the moment, the only way we can envision doing this is by implementing a custom locking scheme (using either separate tables to hold locks, or adding lock fields to the tables that hold records which can be edited). We would then shift the responsibility of handling locks away from the DB and into the applications. We would use very short transactions with pessimistic locks to edit the lock tables (or fields) and then manage the locks at the application level.
I've done some searching for whether there are any patterns to follow for this sort of scheme but I've found very little discussion of custom locking schemes or "application level pessimistic locking". I did come across this thread which links to an article with a couple of sentences, but not much advice. The fact that it is so hard to find information about such a scheme is a red flag to me, so I'm wondering if anyone can provide advice about whether we should pursue this, if there are alternative ways to achieve this, or if someone could point me to some additional reading about custom locking schemes. Any help or insights would be greatly appreciated!
Joined: Apr 16, 2012
The lack of responses to my post makes me think I either 1) asked a really hard question, 2) posted to the wrong place, or 3) wrote a wordy and confusing question...
I looked around the forums here but didn't see any obvious home for architecture questions, did I miss a better home for this post?
To simplify my question: Does anyone know of good resources about architecting a system with pessimistic locking to support 1k-10k simultaneous users?
How can it be that optimistic locking is the only viable approach to handling record locking for a large number of users? (As my google searches seem to imply )