Optimistic locking vs. pessimistic locking

If you have an education in computer science you typically learned about locking in databases. I was taught that the only safe way to update a database in a multi-user environment is to use a transaction:

1. acquire a lock on the record


2. read the current value from the record


3. calculate the new value


4. write the new value to the record


5. release the lock

Simple. Reliable. Safe.

And horribly slow...

Imagine thousands of users concurrently hammering your database. Most of these users will be working on different records. So for most of them, the lock is completely useless. But you're still paying the price for acquiring this lock on every database update. That is why this type of locking is called a pessimistic lock. You assume that multiple people will want to update that same record and prevent them from doing so.

I thought this was the only way to do locking, until I encountered something completely different about 10 years ago. At my company at the time we used an internally built software application to track stock levels of our products. And that application used the pessimistic lock pattern described above. The application worked fine for some time, but as it got more users it started to have more and more performance problems. I was brought in as part of a team to solve those problems. I immediately went to work on some of the heaviest operations and was able to bring startup performance back to acceptable levels in a few days. But the updates to the database were still going as slow as ever.

That's when the technical lead of my team came with an interesting suggestion. "Why don't we remove the database locks?" Of course the entire team was in shock. You couldn't remove the locks from the updates. That meant that the data could become corrupt. That is what we told the tech lead: you need locks to keep your data from becoming corrupt. "So why don't we ensure that the data won't become corrupt when we update it?"

Nobody understood what he meant. Keep in mind that all of us were brought up with the concept of pessimistic locking. So the lead asked us to show him the queries that were being fired against the database:

1. SELECT ID, StockCount FROM tblProducts WHERE ID=?


2. application code calculates the new stock for the product


3. UPDATE tblProducts SET StockCount=? WHERE ID=?

He thought about it for a while and then said "How about this?":

1. SELECT ID, StockCount FROM tblProducts WHERE ID=?


2. application code calculates the new stock for the product


3. UPDATE tblProducts SET StockCount=? WHERE ID=? AND StockCount=?

"So we pass in both the new value for StockCount and the value that we expect to be there. That way the update will only be performed when the StockCount has not been modified by anyone else."

I can still remember the shock going through my mind. Does this mean that I don't need to lock the record before reading it? What will we do when the StockCount did actually change between our SELECT and our UPDATE?

All these questions were answered in the next few hours. If some other user had performed a conflicting UPDATE while our code was busy figuring out the new stock vale, we'd have to reperform the entire sequence above. So the process turned more into a loop:

• while the stock value has not been updated:




1. SELECT ID, StockCount FROM tblProducts WHERE ID=?


2. application code calculates the new stock for the product


3. UPDATE tblProducts SET StockCount=? WHERE ID=? AND StockCount=?

And sure the code got a bit more complex. And definitely in the worst case, this code will send a lot more queries to the database than the one with pessimistic locking. After all, it will fire multiple SELECT and UPDATE statements. Potentially an unlimited number of them, because of the loop.

But that is exactly why people call this the optimistic locking pattern. As I said earlier, most concurrent updates to a database are to different records. So most updates don't have a conflict. But with pessimistic locking all updates pay the price of acquiring a lock. So all updates pay get a penalty for a small fraction of them that do require the locking.

Optimistic locking is built on the assumption that most updates are without conflicts. When there is no conflict, there is no penalty. And only when there has been a conflicting update, do we go into the loop of retrying and pay the penalty. The penalty will be much higher than with pessimistic locking, but it will only be paid for the cases where it is needed.

So there you have it, the difference between optimistic and pessimistic locking and how I learned about them. Pick whichever one you think will work best for your project. Both of them are perfectly suitable for keeping your data safe. It all depends on the ratio of "updates with conflicts" vs. "updates without conflicts" for your application.