Optimistic locking---PoEAA

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.