Application Security Testing: An Integral Part of DevOps
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 - Sample Application.png]
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 as
SqlConnection, 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();
case 0: isolation = IsolationLevel.ReadUncommitted;
case 1: isolation = IsolationLevel.ReadCommitted;
case 2: isolation = IsolationLevel.RepeatableRead;
case 3: isolation = IsolationLevel.Serializable;
case 4: isolation = IsolationLevel.Snapshot;
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:
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
UPDATE statement, you can see that exclusive locks will be placed on the table keys (see again Figure 2).
[Figure 3 - Shared Locks.png]
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
private void refreshButton_Click(object sender, EventArgs e)
SqlConnection connection =
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();
// show the results on screen
locksDataGridView.DataSource = locks;
// free resources
SELECT statement executed to retrieve locking information is the following:
SELECT TOP 2000 [resource_type], [resource_description],
[request_mode], [request_type], [request_status]
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.
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
WITH keyword. But these are topics for another article.
SQL Server 2008 Books Online
Adjusting Transaction Isolation Levels
The SqlConnection.BeginTransaction method