Locking and transaction support is essential in any relational database supporting multiple users. Microsoft SQL Server has provided both of these features from the very beginning, and they are present even in the latest 2008 version. Even if you have used SQL Server before, there are slight differences in these features compared to earlier SQL Server versions. These differences might cause changed behavior in your own applications.
This article looks at the basics of locking features in SQL Server 2008, and peeks into the inner workings to learn how SQL Server implements locking. The examples are taken from SQL Server 2008, which is nowadays a common version. Note that the latest Windows Server 2008 and Windows 7 installations suggest installing SQL Server Service Pack 1 to ensure good compatibility.
Some of the information presented in this article applies to earlier versions of SQL Server as well. Thus, even if you haven’t yet migrated to or started using SQL Server 2008 in your development work, you can still learn how locking works in SQL Server’s previous versions, especially in version 2005.
Understanding Concurrency Control Options
When multiple users need to access the same data simultaneously, or better yet, update it concurrently, SQL Server must somehow control how these reads and writes to the database are carried out. The two basic controlling options are transaction isolation levels and–especially when updating data–locks.
You are probably familiar at least with the concept of both. However, SQL Server is a complex product, and thus both transaction isolation levels and locking have dozens of different options that will affect the way your applications behave when multiple users use the same database objects simultaneously. Depending on the options selected and the order of events occurring (read, then write, or the other way around), different operations are done by the database server.
Concurrency control is usually divided into two broad categories: pessimistic and optimistic concurrency control. Shortly put, pessimistic concurrency assumes that multiple users will try to update the same rows often, and thus locking is used to control the order in which updates succeed and which will have to wait.
On the other hand, optimistic concurrency can be said to assume the opposite: updates to the same database objects (table rows in particular) are few, and thus an error message on the client is enough to handle the situation when a second user tries to update the same object.
Of course, these are only the broad lines. To understand concurrency control and locking in detail, it’s best to start with a quick overview of transaction isolation levels in SQL Server 2008.
Briefly About Transaction Isolation Levels
SQL Server 2008 (Figure 1) supports five transaction isolation levels, each of which is part of the SQL standard from 1992. The main idea of different isolation levels is to control how other users (technically, other transactions) see modifications (inserts, updates and deletes) from other transactions. In addition, they specify how locks are being held, which in turn affects performance: how many users can read and write the same database objects at the same time.
Figure 1. SQL Server 2008 supports five different transaction isolation modes
The isolation levels supported by SQL Server 2008 are named READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE. The READ UNCOMMITTED is the least restricted level, and allows reading changes made by other transactions, even if those changes have not yet been committed. The other end of the spectrum is SNAPSHOT, which specifies that a transaction can only read committed data that existed when the transaction started. No chances will be visible during the lifetime of a transaction using the snapshot isolation.
The transaction isolation level can be set using T-SQL statements, but more commonly, it is set using the class libraries used by the application code. For instance when using the ADO.NET SqlConnection class (in the
System.Data.SqlClient namespace), you can use the
BeginTransaction method to specify which isolation level you want to use. The
IsolationLevel enumeration is used to specify the preferred option, and it defaults to
ReadCommitted. This corresponds to the database engine level