Database locking strategies is one of the most complex areas of application development -- and one of the most critical for reliable application performance and behavior. The basic issues, common strategies, and general recommendations on when and how to use different locking strategies are outlined in this article.

One of the most complex areas for application developers of multi-user, server-based systems to deal with is the impact of locking strategies. Implemented badly, this can lead to all types of issues, from performance problems to deadlocks to unexpected application behavior. The purpose of this article is to lay out the basic issues, common strategies and patterns, and some recommendations as to when and how to use locking strategies.

This article assumes that the developer has control of the SQL being used, and does not fully address the specifics of using an Object Relational (OR) mapping tool, which may add additional complexity.

Transaction vs. session locking
Database management systems provide for locking within the scope of a transaction. Read or update locks can be acquired to prevent inconsistent behavior in the application; the actual locks in use depend on the type of SQL statement used by the developer and the isolation level specified on the database connection. However, database locking in and of itself may not be sufficient. In some scenarios, a longer term "logical lock" may need to be held to prevent changes to data that is presently being worked on by a user. This will be referred to as session locking to distinguish it from the explicit database locking capabilities that only exist within the transaction scope, and which we will refer to as transaction locking. Session locking in systems with a user interface is typically used to solve the well known "intervening update" problem; this is the situation where the user reads a record, makes changes, and updates the database, but where a second user has also read the same record in parallel, and has also made changes to it. In this scenario, the changes made by one of the users will be lost. This can also apply to message-based systems where multiple updates can be delivered and where sequence is important. It should be clearly understood this is locking by convention -- there are no physical locks held; the solution relies on applications understanding and agreeing to honor the locking strategy.

There are two common approaches to dealing with this problem. One is to use a pessimistic approach whereby a logical lock, understood and honored by all applications that may touch the data, is used to block access to the row (or rows) of data used in the business transaction. This data can be "locked" for a substantial time period, so should be used judiciously.

The alternative, optimistic approach is not to lock anything, but to maintain information about the data in use so that it is possible to tell whether it has been modified during the user's session (for example, by saving a timestamp or sequence number that is part of the row). If the application, at update time, sees that this value has been modified, then the update cannot take place, and the user is informed that he or she must try again.

As will be discussed later, pessimistic session locking has several problematic situations that need to be addressed, such as the cleaning up of abandoned locks.

Transaction locking options
Transaction locks (database locks) are those that occur within a single transactional unit of work. This section looks at some of the pros and cons of using optimistic and pessimistic approaches to database locking.

Pessimistic transaction locking: In the pessimistic locking scheme, explicit locks are taken against rows using the SQL SELECT FOR UPDATE statement. Data is then modified, and an SQL UPDATE is issued. In this case, it is clear that all rows have been successfully updated, as they were physically locked prior to the updates being attempted. The locks will remain in place until the transaction commits. As physical database locks are held for a longer time duration than with an optimistic locking strategy, there is more of a chance for performance and throughput being impacted. There is also more chance of deadlock situations occurring. Deadlock problems can be reduced by using the lowest possible isolation level (discussed later) by always accessing tables in the same sequence, and by minimizing lock duration by performing as much business processing as possible prior to obtaining the locks. Deadlocks can also occur due to lock escalation -- when too many locks are held at the same time, possibly causing the database manager to change from row locks to page or even table locks. Again, minimizing lock lifetimes is critical.

Certain critical updates should always be done with pessimistic locking. An example would be retrieving and caching the next available batch of keys from a primary key sequence table. This is an infrequently used operation within a short-lived transaction, and there is no reason to take the risk of the update failing.

It should be noted that there are situations where the FOR UPDATE clause is not available for a given SELECT statement. In this case, there may need to be some application restructuring, or optimistic locking may need to be used instead.

Optimistic transaction locking: Unlike the pessimistic locking scenario, where the application explicitly locks the row (or rows) that is about to be updated, no actual locks are held in this case until the rows are updated, and those locks are implicitly taken by the database manager. The application reads rows that are to be updated using a normal SQL SELECT, with no FOR UPDATE option. Data is modified, and the affected rows are re-written. The update is in some way overqualified to ensure that only rows that are in the same state as those originally read are changed. This can be done by using a timestamp or sequence number contained in the row, or by adding every column in the row to the WHERE clause of the UPDATE. The latter option is not very efficient; the first two are preferred. Additionally, some columns, such as BLOB types, are not available for use in an overqualified update. It should be noted that some OR Mapping tools (such as entity beans) will use overqualified updates.

If multiple rows are updated in a single SQL UPDATE, it is difficult to know which rows were successfully updated and which failed, so optimistic locking should only be used when dealing with single rows in the UPDATE statement, rather than sets of rows. However, you can iterate through a set of rows and update each row individually while using optimistic locking techniques. Optimistic transaction locking is generally preferred for its performance characteristics and reduced likelihood of deadlock situations. However, this should be evaluated on a case by case basis -- there are situations where pessimistic locking is necessary. If a large number of rollbacks caused by optimistic lock exceptions are occurring, it may be time to rethink your strategy. Even with optimistic locking, physical locks will be held on updated rows until the end of the transaction (commit time), so it is recommended to complete the transaction as soon as possible after updating the database.

Session locking options
Session locks are those that span multiple transactional units of work; they cover the entire duration of some business process. This section looks at the specifics and the pros and cons of using optimistic and pessimistic approaches to session locking.Optimistic session locking: In this scenario, a user retrieves data from the database with no locks. Included with the data is sufficient information such that the user can tell, at some future point, if the row has been modified in the database; this could be by using a timestamp or epoch (sequence) number in the table, or merely by saving every single data element (column) as it stands at the time of initial read access. The user can view the data, make changes, and then invoke a transaction to commit those changes.

At the point of attempting to commit the update, the application will verify that no other user has modified the data. If it has, the change will be rejected and the user notified that there has been an intervening update. If there is a set of rows to be updated, some may be successful and some may fail. All decisions are made by the application, not by the database management system. This pattern is described in Patterns of Enterprise Application Architecture by Martin Fowler as optimistic offline lock (see Resources).