Understanding Concurrency Control in SQL Server

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.

 

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

READ COMMITTED

.

Understanding Locking Modes

To gain an understanding of how locking works, you need to be aware of different transaction levels and the different lock modes ("types") supported by SQL Server 2008. The following list shows the most common lock modes, along with their abbreviation in parenthesis:

• Shared locks (S)
• Update locks (U)
• Exclusive locks (X)
• Intent locks (I)
• Schema locks (two types, SCH-M and SCH-S)
• Bulk Update locks (BU)
• Key-range locks (R)

SQL Server supports many different lock modes and this can be somewhat complex at times. Learning is best to start from exclusive locks, which are the easiest to understand. Shortly put, an exclusive lock (X) is placed on a database object whenever it is updated with an INSERT or UPDATE statement.

At the other end of the spectrum, the shared locks (S) is put to a database object whenever it is being read (using the SELECT statement), depending on the selected transaction isolation mode. Between the shared and exclusive locks, there is the update lock (U), which can be thought of as an in-between mode between shared and exclusive locks. The main purpose of the update lock is to prevent deadlocks where multiple users simultaneously try to update data.

Intent locks are used to indicate that a certain lock will be later placed on a certain database object. Intent locks are used because locks can form hierarchies. Intent locks prevent the potential situation where a newly-acquired lock might invalidate locks on a lower level in the hierarchy.

Finally, schema locks (SCH-M and SCH-S) are used to prevent changes to object structure, bulk update locks (BU) are used when updating or inserting multiple rows using a bulk update, and key-range locks (R) are used to lock ranges of keys in an index. Key-range locks are used with the SERIALIZABLE transaction isolation level to prevent phantom reads, for instance.

In addition to singular locking modes, certain locking modes can be combined with one another. This is especially true with intent locks, which are can be form pairs such as intent shared (IS), intent update (IU), and so forth.

Because maintaining locks can be an expensive operation performance-wise, SQL Server supports a feature called multigranular locking. This means that locks can be placed on different levels, depending on the situation. For instance, a lock can be places on a single table row, a table page (internal storage unit), an entire table, and so on. Sometimes, SQL Server needs to place multiple locks on different levels. These locks then form a locking hierarchy, and intent locks play a role in this, as described above.

Lock Compatibility and Reading Information About Locks

As an application developer or a database administrator, you need to understand how locking works. Part of this understanding is to know how locking compatibility affects your applications. SQL Server calls this technically lock compatibility, but some of it is just common sense: an update cannot be made while another user has already locked the same data exclusively.

Because there are over 20 different locking mode combinations in SQL Server 2008, the SQL Server documentation (Books Online, BOL) contains a complete matrix of all the different locking combinations and their outcome. For instance, will the two types of locks live peacefully together (no locking conflict), or will either an error or delay occur (a conflict)? When using ADO.NET, a statement that causes a lock conflict will usually wait until a timeout occurs, or until the lock is released by another transaction. As locking compatibility has dozens of tiny options, for the purpose of this article, it is enough to understand the main rule: if a database object is already locked by another transaction, a new lock can only be placed on the object if the two lock modes are compatible with one another. Also, almost no other lock type is compatible with the exclusive (X) lock, meaning that if an exclusive lock is already placed on the object, most later lock requests fail.

How SQL Server places locks on object depends on the transaction isolation level currently active. For instance, the default read committed isolation mode does not place shared locks when reading data (think SELECT statements), but if the isolation level is for example repeatable read (a step higher level), then shared locks will be placed on objects that are being read. This is to make sure the identical data can be read again.

All this sounds interesting, but how could you see locking in action yourself? The answer lies in a system view called sys.dm_tran_locks kept by SQL Server. You can query this view, and thus find information about locking in real-time. Although you could use the SQL Server Management Studio to retrieve this information, it's easy to write a .NET application with C# that shows you the results in a nice, easy-to-read grid.

Such an application can be seen in Figure 2. On the left, you can see the application's main form, which contains several buttons to connect and disconnect from the database, start a transaction with the selected transaction isolation level and either commit or roll back the transaction. At the bottom, you can find a button to execute the entered SQL statement (such as SELECT or UPDATE), and finally the button to open the Locks window, shown on the right. From this window, you can see locks acquired by SQL Server. The lock mode abbreviation (such as S or X) is shown in the request_mode column.

 

Figure 2. A simple .NET application can easily retrieve real-time information about locks

The implementation of the application is very straightforward, and uses classes such asSqlConnection, SqlTransaction and SqlCommand from the familiar System.Data.SqlClient namespace. For instance, the following code is executed when the Start Transaction button is clicked:

private void startTransactionButton_Click(  object sender, EventArgs e)
{  IsolationLevel isolation = new IsolationLevel();  switch (isolationLevelComboBox.SelectedIndex)  {    case 0: isolation = IsolationLevel.ReadUncommitted;        break;    case 1: isolation = IsolationLevel.ReadCommitted;        break;    case 2: isolation = IsolationLevel.RepeatableRead;        break;    case 3: isolation = IsolationLevel.Serializable;        break;    case 4: isolation = IsolationLevel.Snapshot;        break;  }  transaction = connection.BeginTransaction(isolation);  MessageBox.Show("Transaction has been started.", this.Text);
}

With this application, you can execute any SQL statement you prefer against the selected SQL Server database. For instance when using the Northwind database that is one of SQL Server's sample databases, you could test reading with statements like following:

SELECT *
FROM Customers

It is exciting to try to run this statement with different transaction isolation levels and note how locking changes. For instance when the repeatable read isolation mode is selected, the locks window will display many shared locks on the Customers table (Figure 3). In a similar fashion, if you execute an UPDATEstatement, you can see that exclusive locks will be placed on the table keys (see again Figure 2).

(Full Size Image)


Figure 3. On the right, many shared (S) locks can be seen after a simple SELECT statement is executed using the repeatable read transaction isolation level.

This is the code that is able to retrieve the locking information from the sys.dm_tran_locks view:

private void refreshButton_Click(object sender, EventArgs e)
{  SqlConnection connection =    new SqlConnection(connectionString);  string sql = "SELECT TOP 2000 [resource_type], " +      "[resource_description], [request_mode], " +      "[request_type], [request_status] " +      "FROM [sys].[dm_tran_locks] " +      "WHERE ([resource_type] <> 'DATABASE')";  SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);  DataTable locks = new DataTable();  adapter.Fill(locks);  // show the results on screen  locksDataGridView.DataSource = locks;    // free resources  adapter.Dispose();  connection.Dispose();
}

The actual SELECT statement executed to retrieve locking information is the following:

SELECT TOP 2000 [resource_type], [resource_description],
[request_mode], [request_type], [request_status]
FROM [sys].[dm_tran_locks]
WHERE ([resource_type] <> 'DATABASE')

Notice how the "TOP 2000" limitation is set as a safeguard against slowing down the database, if there are thousands of locks active currently. Furthermore, the resource type "DATABASE" is excluded from the results, as this lock type is always placed on a database level for each active connection to the database, and thus isn't relevant information for the purposes of this article.

Conclusion

Locking is an imperative feature in any database supporting multiple, simultaneous users. In SQL Server, locking is closely tied to transaction isolation levels. These two features together largely control how simultaneous reads and writes are coordinated in SQL Server.

In this article, you learned the basics of SQL Server 2008's locking, and saw how you can retrieve real-time locking information by querying a system view from your own code. An application similar to the one shown in this article is a great way to learn how SQL Server implements locks, as you can directly observe the locks in place after you've executed an SQL statement with a transaction isolation mode of your choice.

If you are interested in learning more about SQL Server's locking features, the Books Online (BOL) is an invaluable place to start and find reference information. When you study locking further, you can also learn about more advanced features such as lock escalation, lock partition and table hints with the WITHkeyword. But these are topics for another article.

Originally written by :Jani Jarvinen | Taken with thanks from :  CodeGuru