Benefits and drawbacks
Optimistic locking has a couple of advantages:
■ It is easy to implement in a JDBC/iBATIS application, and it is supported by many persistence frameworks.
■ Optimistic locking, unlike pessimistic locking, does not prevent an application from using certain SQL SELECT statement features. As you’ll see a bit later, some databases have restrictions that prevent pessimistic locking from working with some kinds of views and nested SELECT statements, etc.
There are, however, various drawbacks and issues:
■ All potentially conflicting transactions must use optimistic locking. Otherwise, errors will occur. Fortunately, this isn’t an issue when using a persistence framework because optimistic locking is specified declaratively on a per-class basis, which ensures that it will be used consistently.
■ The easiest way to implement optimistic locking is to use a version column.
But it is not always possible to add a version column to a legacy schema that you have no control over. What’s more, you might not be able to modify the legacy applications that also use the schema to increment the version column.
■ Optimistic locking does not guarantee that a transaction will be able to update the rows that it read. If those rows have been changed by another transaction, it will have to start over, which can be inefficient.
■ Optimistic locking does not prevent inconsistent reads. Fortunately, many applications can tolerate some amount of inconsistency.
When to use it
Despite these drawbacks, optimistic locking is a useful concurrency mechanism. A general recommendation is that an application should use optimistic locking unless:
■ The database schema does not support optimistic locking. It’s a legacy schema whose tables have columns that contain values such as floating point values that cannot be compared and you cannot add a version or timestamp column.
■ The application must be guaranteed to be able to update the rows that it read.
■ The application requires consistent reads.