Optimistic vs. Pessimistic Locking consequences

Putting on my "database guy" hat, a few days I was asked to comment on locking techniques for databases and to point out some problems with using transactions as a pessimistic locking technique.  Here's what I said...

Some definitions:

Optimistic locking -- that's where you assume things will go well and design your locks so that they handle conflicts as the exceptional case

Pessimistic locking -- the converse where you assume conflicts are likely and create some kind of reservation system where sections are locked while they are edited

[In response to the suggestion, you must] remember that transactions are not really durable things and that any strategy that is designed around transactions having very long life (e.g. minutes) must also be designed around the chance that such a transaction is aborted for any number of reasons, not the least of which is deadlock.
The math of the situation tells you that the longer running a transaction is the lower the likelihood you can commit it.
So using something as simple as open transactions for your locking strategy is pretty much right out. 
Now the moment you choose a scheme where a given client applies a (pessimistic) software lock on some set of objects you have another set of issues -- what if the client disconnects?  How to you administer and recover these locks?  What operations do you allow on the locked objects?  How will you provide (manually) the isolation that's needed (if any) to hide any in-flight changes that are committed from the database perspective but pending based on business rules?
An optimistic locking strategy provides one omnibus solution to these problems.  Other solutions are possible but you should not underestimate their difficultly because they create interim/valid committed states.  States you might find yourself in, for instance, after a backup, with no supporting client info what-so-ever.
It's not that the pessimistic lock is inherently bad but once you've decided on such a path, you then have a variety of other problems to solve.  By its nature optimistic locking does not create interim valid states and so finesses many of these issues.

It turns out many of these same issues apply to pretty much any free threaded datastructure.

Skip to main content