Why is optimistic locking faster than pessimistic locking?

Question

Both forms of locking cause a process to wait for a correct copy of the record if its currently in use by another process. With pessimistic locking, the lock mechanism comes from the DB itself (a native lock object), whereas with optimistic locking, the lock mechanism is some form of row versioning like a timestamp to check whether a record is "stale" or not.

But both cause a 2nd process to hang. So I ask: why is optimistic locking generally considered faster/superior than pessimistic locking? And, are there are use cases where pessimistic is preferred over optimistic? Thanks in advance!

Answer 1

Duplicate question from:

https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking

Copy/Pasting answer from the above link:

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.

If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

In the latter case you open the transaction with the TxID and then reconnect using that ID. The DBMS maintains the locks and allows you to pick the session back up through the TxID. This is how distributed transactions using two-phase commit protocols (such as XA or COM+ Transactions) work.

Edit (Adding more info to address the performance question):

Performance wise it depends on your environment. Take in the following factors to decide:

you're going to find optimistic will be better due to concurrency in most situations. Depending on the RDBMS and environment this might be less or more performant however. Typically with Optimistic locking you will find that the value needs to be row versioned somewhere.

With MS SQL Server for example, it gets moved to TempDB and something between 12-14 bytes are appended at the end of the column. Turning on optimistic locking with an isolation level such as Snapshot Isolation can cause fragmentation and your fill factor will need to be adjusted as the rows now have additional data at the end which could cause a page near full to cause a page split, which will lower your performance. If your TempDB is under optimized then this will not be as fast.

So I guess a checklist is:

• -Do you have sufficient IO/resources to handle the form of row versioning? If not, you are adding overhead. If so, then if you are reading the data often while you are often locking it for writes, you will notice a good improvement on concurrency across reads and writes (although writes will still block writes, reads will no longer block writes and vice versa)
• -Is your code susceptible to deadlocks or do you experience locking? If you are not experiencing long locks or a lot of deadlocks, then the additional overhead of Optimistic locking wouldn't make things faster, of course, in most cases we're talking milliseconds here.
• -If your DB is big (or on very limited hardware) and your data pages are near full, depending on the RDBMS, you could cause major page splits and data fragmentation so make sure to consider reindexing after turning it on.

Those are my thoughts on the matter, open to hearing more from the community.

Answer 2

You misunderstand optimistic locking.

Optimistic locking does not cause transactions to wait for each other.

Optimistic locking possibly causes a transaction to fail, but it does so without any "lock" ever having been taken. And if a transaction fails because of optimistic locking, the user is required to start all over again. The word "optimistic" derives from exactly the expectation that the condition that causes transactions to fail for this very reason, will occur only very exceptionally. "Optimistic" locking is the approach that says "I will not be taking actual locks because I hope they won't be needed anyway. If it turns out I was wrong about that, I will accept the inevitable failure.".

Answer 3

Optimistic locking is generally faster because there is actually no locking from database point of view. It's entirely up to application whether to respect version column (or pseudo-column, like ora_rowscn) or not. Normally you have many applications connected to the same database, so db becomes shared resource, and if it hangs, all the clients will be affected.

With optimistic locking strategy , 'hanging' happens on client side and does not affect others.

However, if a record is updated frequently, you may end up re-reading it too many times (in case of optimistic locking), thus defeating the most benefits of optimistic strategy.

I'd not agree about superiority of either approach; both of them can be misused. Pessimistic is more error-prone just because it's more dangerous : locking occurs on db level, depends on RDMS you may not have control on what is locked (lock escalation), you need to take care of locking order manually.

Answer 4

Optimistic locking assumes concurrent transactions can complete without affecting each other. So Optimistic locking is faster because no locks are enforced while doing transactions. It is prevention from causing concurrency problems not cure. The transaction just verifies(three ways Datasets, Timestamp Data type, Check old and new value) the data that no other transaction has modified the data. In case of modification the transaction is rolled back.

Pessimistic locking assumes that concurrent transactions will conflict with each other, so it requires lock, it is done by specifying ISOLATION level (Read Uncommitted, Read Committed, Repeatable Read and Serializable) of transaction management.It cures concurrency problems by acquiring lock. locks serve to protect shared resources or objects(Tables, Data Rows, Data Blocks, Cached Items, Connections and Entire Systems). We have many types of locks as shared locks,update lock, inset lock, exclusive locks, transaction locks, DML locks, schema locks and backup-recovery locks.

to get more idea

Answer 5

It is false to say that pessimistic locking is slower rather than optimistic or to say that optimistic is faster. One classic query to demonstrate this unappropriat way of thinking is to do an aggregate on the different RDBMS, like :

SELECT COUNT(*) FROM atable

You will see that, in the RDBMS thats support natively optimistic approach, the time takes by this query is much more significant than is those who have a natively pessimistic lock

For instance on my PC, the same query take 27 ms on SQL Server and 109 on PostGreSQL...

The extra overhead needed in reading dead versions of MVCC rows and do not count the ghosts records in the aggregate adds an extra costs that the pessimistic does'nt have !