Optimistic locking takes the “optimistic” view that data corruptions due to concurrent edits will occur rarely and no locking is needed, so it’s more important to allow concurrent access than to lock out concurrent updates. If a conflict occurs, the transaction must be aborted and repeated. Frameworks which support optimistic locking typically maintain a version field and raise an exception if one tries to update an object with an outdated version number.

Pessimistic locking takes the “pessimistic” view that users are highly likely to corrupt each other’s data, and that the only safe option is to lock the database and serialize data access, so at most one user has control of any piece of data at one time. This ensures data integrity, but reduces speed and the amount of concurrent activity the system can support.

Stackoverflow.com has an entry about optimistic vs. pessimistic locking – when to use optimistic and when to use pessimistic locking. It says correctly that the optimistic strategy is most useful for web applications 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.

Maybe the following analogy is helpful to understand the difference between both kinds of locking, too, although it is a bit slippery:

Optimistic locking is like leaving the door to the toilet open: you have a number of toilets, select one, and you are optimistic that nobody will come to use the same toilet, too. If someome comes, it will of course be embarassing, an exception is raised, and he must abort his try, but you hope that these conflicts are the execption.

Pessimistic locking is like always locking the door to the toilet: although there are a number of different toilets, you are expecting every time that somebody else will come to use one, too, and that the only safe option is to lock all toilet doors (which corresponds to page locking). If someone comes and wants to use a toilet, he cannot enter and must wait before the locked door.

In Ruby on Rails you can do both. Pessimistic Locking is possible through row-level locking using SELECT … FOR UPDATE and other lock types. Optimistic Locking means to hope for the best and handle the ActiveRecord::StaleObjectError.

About these ads