Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
2  
A very short explanation exists in the naming. Optimistic locking works well when the chance of a conflicting lock is low. We are optimistic about the interaction of multiple processes. Pessimistic locking works well when the chance of a conflicting lock is high. We are pessimistic about the interaction of multiple processes. Both will perform sub-optimally where their opposite would be more appropriate. –  Mark Storey-Smith Mar 2 '13 at 21:37
    
optimistic locking may or may not be faster than pessimistic locking, depending on your workload. –  AlexKuznetsov Mar 4 '13 at 0:48
add comment

3 Answers 3

up vote 4 down vote accepted

Duplicate question from:

http://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.

share|improve this answer
    
Thanks @Ali Razeghi (+1) - I think dba.se is a more appropriate place for this question. Also, although this is a superb answer, it does not answer my question of performance (when one is faster than the other). Thanks again! –  Mara Mar 2 '13 at 20:48
    
Hi Mara, that's a good point. I have expanded the answer. Thanks. –  Ali Razeghi Mar 2 '13 at 20:59
add comment

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.".

share|improve this answer
add comment

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.

share|improve this answer
    
interesting point a1ex07, optimsitic locking does still include locking however, as writes will always block other writes, correct? –  Ali Razeghi Mar 2 '13 at 21:26
    
No it doesn't. That's why it's "faster". –  Erwin Smout Mar 2 '13 at 23:49
    
That could be the case for Oracle but for MS SQL Server, since it uses the 'read committed' isolation level by default, optimistic locking will allow readers and writer threads to work concurrently, but writes will block writes until the blocking thread commits. –  Ali Razeghi Mar 9 '13 at 17:47
    
@Ali Razeghi : I'm not sure I follow your point. In SQLServer with read committed writers block readers by default unless ` READ_COMMITTED_SNAPSHOT` is turned on. Optimistic locking is not a lock on db resource (row/page/table), but rather some kind of agreement between all applications that use database to not update record if the version doesn't match expected. –  a1ex07 Mar 9 '13 at 19:24
    
@a1ex07: the point is that sql server's snapshot isolation levels use pessimistic write locking - writers do block other writers. –  Eamon Nerbonne Mar 20 '13 at 16:19
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.