Concurrency Model Confusion II
In the first post of this series, I reviewed concurrency concepts and highlighted differences in concurrency terminology from a database and application perspective. I’ll now discuss how applications can leverage SQL Server features to meet concurrency objectives.
SQL Server Concurrency Terminology
Optimistic concurrency control inside the SQL Server 2005 database engine technically means that row versioning is used instead of a pessimistic locking approach. I believe this is a source of much confusion to both application developers and DBAs because row versioning isn’t required for applications to use optimistic concurrency control. Applications have used optimistic concurrency long before row versioning was introduced in SQL Server 2005. In fact, the rowversion data type (a.k.a. timestamp) exists specifically to facilitate optimistic concurrency control.
I don’t personally like calling SQL Server concurrency control methods either optimistic or pessimistic. I prefer to instead view concurrency control objectives from the application perspective and choose the SQL Server concurrency control features (transaction isolation levels, transactions, locking hints) that are most appropriate for the task at hand without getting tripped up on terminology. For example, SQL Server optimistic row versioning should not be used solely because the application uses optimistic concurrency. A better choice for a well-tuned, highly transactional application might very well be default pessimistic SQL Server concurrency control behavior.
Row Versioning and Concurrency
Row versioning isolation levels can improve concurrency because committed data can be read from the version store instead of acquiring locks on data read. All update and delete statements store pre-update versions of changed data in the tempdb version store once either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is turned on. SQL Server then returns these pre-update row versions to applications reading data in the READ_COMMITTED (with READ_COMMITTED_SNAPSHOT database option on) or SNAPSHOT transaction isolation levels. The main difference between row versioning in these isolation levels is that READ_COMMITTED is single-statement level and SNAPSHOT is transaction level (multi-statement). See Row Versioning-based Isolation Levels in the Database Engine in the Books Online for details.
A DBA can sometimes address blocking/deadlocking concurrency problems unilaterally by simply turning on the READ_COMMITTED_SNAPSHOT database option because statement-level row versioning is transparent to applications running in the default READ_COMMITTED isolation level. However, this shouldn’t be done indiscriminately because row versioning has an associated cost in terms of disk I/O, storage, CPU and DBA management. One can even make concurrency problems worse by enabling row versioning unless there are sufficient server resources to handle the additional work introduced by row versioning. See the Overhead of Row Versioning post in the SQL Server Storage team blog for more info on I/O and storage overhead.
SNAPSHOT isolation level row versioning can also improve concurrency. However, SNAPSHOT isolation is a different paradigm than most SQL Server developers are accustomed to. With SNAPSHOT isolation, applications have a consistent view of the database from the time the transaction starts so developers need to consider the ramifications of retrieving stale data and perhaps delegating the task of checking for changed data in an optimistic model to the database engine. I elaborate more on SNAPSHOT later.
Optimistic Concurrency from an Application Perspective
SQL Server applications have traditionally implemented optimistic concurrency by detecting conflicts when data are saved. This is done by either comparing either the original/current values of the target columns or a rowversion column. The READ_COMMITTED example below can run with or without row versioning enabled.
|
--retrieve latest values when user clicks edit SET TRANSACTION ISOLATION LEVEL READ_COMMITTED; SELECT ContactName, ContactTitle FROM dbo.Suppliers WHERE SupplierID = @SupplierID; |
|
--check for conflicts when user clicks save UPDATE dbo.Suppliers SET ContactName = @NewContactName, ContactTitle = @NewContactTitle WHERE SupplierID = @SupplierID AND ContactName = @OriginalContactName AND ContactTitle = @OriginalContactTitle; IF @@ROWCOUNT = 0 --a zero rowcount indicates data was deleted or changed BEGIN RAISERROR ('Contact information was changed by another user', 16, 1); END; |
The above optimistic concurrency technique requires no persistent database connection. Locks are held only for the short duration of the initial SELECT (and only when READ_COMMITTED_SNAPSHOT is off) and again during the subsequent update. No persistent database connection is needed because no explicit transaction is used. This same method can be used with or without the READ_COMMITTED_SNAPSHOT database option turned on.
Applications can also leverage SQL Server’s SNAPSHOT transaction isolation level to implement optimistic concurrency. In the following example, a transaction is started and persistent database connection is maintained until the COMMIT:
|
--retrieve latest values when user clicks edit SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SET XACT_ABORT ON; BEGIN TRAN; SELECT ContactName, ContactTitle FROM dbo.Suppliers WHERE SupplierID = @SupplierID; |
|
--no need to check for changed data when user clicks save UPDATE dbo.Suppliers SET ContactName = @NewContactName, ContactTitle = @NewContactTitle WHERE SupplierID = @SupplierID; COMMIT; |
Unlike the READ_COMMITED method, the UPDATE statement in the SNAPSHOT level doesn’t need to check for changed data. SQL Server will raise an exception during the UPDATE if data was changed by another session since the start of the transaction. In this SNAPSHOT example, error 3960 is raised if the Supplier row changed since the transaction started:
|
Msg 3960, Level 16, State 2, Line 13 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Suppliers' directly or indirectly in database 'Northwind' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement. |