Understanding SQL Server Locks

SQL Server uses locks to implement transaction isolation and to ensure the information stored in a database is consistent. Locks prevent one user from reading or changing a row that is being changed by another user. For example, when you update a row, a row lock is placed on that row to prevent another user from updating the row at the same time.

Types of SQL Server Locks

SQL Server uses many types of locks, some of which are shown in Table 14.5. This table shows the locks in ascending order of locking granularity, which refers to the size of the resource being locked. For example, a row lock has a finer granularity than a page lock.

Table 14.5: SQL Server Lock Types

LOCK TYPE	DESCRIPTION
Row (RID)	Placed on a row in a table. Stands for row identifier. Used to uniquely identify a row.
Key (KEY)	Placed on a row within an index. Used to protect key ranges in serializable transactions.
Page (PAG)	Placed on a page, which contains 8KB of row or index data.
Extent (EXT)	Placed on an extent, which is a contiguous group of 8 data or index pages.
Table (TAB)	Placed on a table and locks all the rows and indexes in that table.
Database (DB)	Used to lock the whole database when the database administrator puts it into single user mode for maintenance.

SQL Server Locking Modes

SQL Server uses different locking modes that determine the level of locking placed on the resource. These locking modes are shown in Table 14.6. You'll see these locking modes in the next section.

Table 14.6: SQL Server Locking Modes

LOCKING MODE	DESCRIPTION
Shared (S)	Indicates that a transaction is going to read from the resource using a SELECT statement. Prevents other transactions from modifying the locked resource. A shared lock is released as soon as the data has been read-unless the transaction isolation level is set to REPEATABLE READ or SERIALIZABLE.
Update (U)	Indicates that a transaction intends to modify a resource using an INSERT, UPDATE, or DELETE statement. The lock must be escalated to an exclusive lock before the transaction actually performs the modification.
Exclusive (X)	Allows the transaction to modify the resource using an INSERT, UPDATE, or DELETE statement. No other transactions can read from or write to a resource on which an exclusive lock has been placed.
Intent shared (IS)	Indicates that the transaction intends to place a shared lock on some of the resources with a finer level of granularity within that resource. For example, placing an IS lock on a table indicates that the transaction intends to place a shared lock on some of the pages or rows within that table. No other transactions may place an exclusive lock on a resource that already has an IS lock on it.
Intent exclusive (IX)	Indicates that the transaction intends to place an exclusive lock on a resource with a finer level of granularity. No other transactions may place an exclusive lock on a resource that already has an IX lock on it.
Shared with intent exclusive (SIX)	Indicates that the transaction intends to read all of the resources that have a finer level of granularity and modify some of those resources. For example, placing a SIX lock on a table indicates that the transaction intends to read all the rows in that table and modify some of those rows. No other transactions may place an exclusive lock on a resource that already has a SIX lock on it.
Schema modification (Sch-M)	Indicates that a Data Definition Language (DDL) statement is going to be performed on a schema resource, for example, DROP TABLE. No other transactions may place a lock on a resource that already has a Sch-M lock on it.
Schema stability (Sch-S)	Indicates that a SQL statement that uses the resource is about to be performed, such as a SELECT statement for example. Other transactions may place a lock on a resource that already has a Sch-S lock on it; only a schema modification lock is prevented.
Bulk update (BU)	Indicates that a bulk copy operation to load rows into a table is to be performed. A bulk update lock allows other processes to bulk-copy data concurrently into the same table, but prevents other processes that are not bulk-copying data from accessing the table. For further information on bulk-copying data to a table, see the SQL Server Books Online documentation.

Viewing SQL Server Lock Information

You can view the lock information in a database using SQL Server Enterprise Manager. You open the Management folder, open the Current Activity node, and then open either the Locks/Process ID node or the Locks/Object node. The Locks/Process ID node shows you the locks placed by each process; each process has a SPID number that is assigned by SQL Server to identify the process. The Locks/Object node shows you the locks placed on each resource by all processes.

Tip 

You can also view lock information by executing the sp_lock stored procedure, although Enterprise Manager organizes the information in a more readable format.

Assume you've started the following transaction (using Query Analyzer, for example) with the following T-SQL statements:

 USE Northwind BEGIN TRANSACTION UPDATE Customers SET CompanyName = 'Widgets Inc.' WHERE CustomerID = 'ALFKI' 

This places a shared lock on the Northwind database and a number of locks on the Customers table, which you can view using Enterprise Manager. Figure 14.3 shows these locks using the Locks/ Process ID node of Enterprise Manager. The SPID of 51 corresponds to Query Analyzer where I ran the previous T-SQL statements. As you can see from this figure, a number of locks are placed by the previous T-SQL statements.

Figure 14.3: Viewing the locks using the Locks/ Process ID node of Enterprise Manager

To roll back the previous transaction, perform the following T-SQL statement:

 ROLLBACK TRANSACTION 

To release the locks, perform the following T-SQL statement:

 COMMIT TRANSACTION 

The information in the right pane of Figure 14.3 shows the locks, and this information is divided into the following columns:

• Object The object being locked.

• Lock Type The type of lock, which corresponds to one of the types shown earlier in Table 14.5.

• Mode The locking mode, which corresponds to one of the modes shown earlier in Table 14.6.

• Status The lock status, which is either GRANT (lock was successfully granted), CNVT (lock was converted), or WAIT (waiting for lock).

• Owner The owner type of the lock, which is either Sess (session lock) or Xact (transaction lock).

• Index The name of the index being locked (if any).

• Resource The resource identifier of the object being locked (if any).

Transaction Blocking

One transaction may block another transaction from obtaining a lock on a resource. For example, let's say you start a transaction using the following T-SQL, which is identical to the T-SQL in the previous section:

 USE Northwind BEGIN TRANSACTION   UPDATE Customers   SET CompanyName = 'Widgets Inc.'   WHERE CustomerID = 'ALFKI' 

As you saw in the previous section, this places a number of locks on the Customers object.

If you then attempt to update the same row-without ending the previous transaction-using the following T-SQL statements:

 USE Northwind UPDATE Customers SET CompanyName = 'Alfreds Futterkiste' WHERE CustomerID = 'ALFKI' 

then this UPDATE will wait until the first transaction has been committed or rolled back. Figure 14.4 shows these two transactions being started in Query Analyzer. The first transaction, which is shown in the upper part of Figure 14.4, is blocking the transaction on the bottom.

Figure 14.4: The transaction on the top part is blocking the transaction on the bottom.

To commit the previous transaction and release the locks for the first transaction, you may perform the following T-SQL statement:

 COMMIT TRANSACTION 

This allows the second UPDATE shown at the bottom of Query Analyzer to get the appropriate lock to update the row and proceed, as shown in Figure 14.5.

Figure 14.5: Once the top transaction is committed, the bottom UPDATE proceeds.

Setting the Lock Timeout

By default, a SQL statement will wait indefinitely to get a lock. You can change this by executing the SET LOCK_TIMEOUT command. For example, the following command sets the lock timeout to 1 second (1,000 milliseconds):

 SET LOCK_TIMEOUT 1000 

If a SQL statement has to wait longer than 1 second, SQL Server will return an error and cancel the SQL statement.

You can execute the SET LOCK_TIMEOUT command in C# code also. For example:

 mySqlCommand.CommandText = "SET LOCK_TIMEOUT 1000"; mySqlCommand.ExecuteNonQuery(); 

You'll see the use of the SET LOCK_TIMEOUT command in the next section.

Blocking and Serializable/Repeatable Read Transactions

Serializable and repeatable read transactions lock the rows they are retrieving so that other transactions cannot update those rows. Serializable and repeatable read transactions do this so that the rows aren't changed after they've been read.

For example, if you select the row from the Customers table with a CustomerID of ALFKI using a serializable transaction, and then attempt to update that row using a second transaction, then the second transaction will be blocked. It is blocked because the serializable transaction locks the retrieved row and the second transaction is unable to get a lock on that row.

Listing 14.5 shows an example of this. The second transaction sets the lock timeout to 1 second. This means the program throws a SqlException rather than simply hanging when the second transaction is unable to get a lock on the ALFKI row in the Customers table.

Listing 14.5: Block.cs

 /*   Block.cs illustrates how a serializable command locks   the rows it retrieves so that a second transaction   cannot get a lock to update one of these retrieved rows   that has already been locked */ using System; using System.Data; using System.Data.SqlClient; class Block {   public static void DisplayRows(     SqlCommand mySqlCommand   )   {     mySqlCommand.CommandText =       "SELECT CustomerID, CompanyName "+       "FROM Customers "+       "WHERE CustomerID IN ('ALFKI', 'J8COM')";     SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();     while (mySqlDataReader.Read())     {       Console.WriteLine("mySqlDataReader[\" CustomerID\"] = "+         mySqlDataReader["CustomerID"]);       Console.WriteLine("mySqlDataReader[\" CompanyName\"] = "+         mySqlDataReader["CompanyName"]);     }     mySqlDataReader.Close();   }   public static void Main()   {     // create and open two SqlConnection objects     SqlConnection serConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     SqlConnection rcConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     serConnection.Open();     rcConnection.Open();     // create the first SqlTransaction object and start the transaction     // by calling the BeginTransaction() method of the SqlConnection     // object, passing the IsolationLevel of Serializable to the method     SqlTransaction serializableTrans =       serConnection.BeginTransaction(IsolationLevel.Serializable);     // create a SqlCommand and set its Transaction property     // to serializableTrans     SqlCommand serializableCommand =       serConnection.CreateCommand();     serializableCommand.Transaction = serializableTrans;     // call the DisplayRows() method to display rows from     // the Customers table;     // this causes the rows to be locked, if you comment     // out the following line then the INSERT and UPDATE     // performed later by the second transaction will succeed     DisplayRows(serializableCommand); // *     // create the second SqlTransaction object     SqlTransaction readCommittedTrans =       rcConnection.BeginTransaction(IsolationLevel.ReadCommitted);     // create a SqlCommand and set its Transaction property     // to readCommittedTrans     SqlCommand readCommittedCommand =       rcConnection.CreateCommand();     readCommittedCommand.Transaction = readCommittedTrans;     // set the lock timeout to 1 second using the     // SET LOCK_TIMEOUT command     readCommittedCommand.CommandText = "SET LOCK_TIMEOUT 1000";     readCommittedCommand.ExecuteNonQuery();     try     {       // insert a new row into the Customers table       Console.WriteLine("Inserting new row into Customers table "+         "with CustomerID of J8COM");       readCommittedCommand.CommandText =         "INSERT INTO Customers ("+         "CustomerID, CompanyName "+         ") VALUES ( " +         "  'J8COM', 'J8 Company' "+         ")";       int numberOfRows = readCommittedCommand.ExecuteNonQuery();       Console.WriteLine("Number of rows inserted = "+ numberOfRows);       // update the ALFKI row in the Customers table       Console.WriteLine("Setting CompanyName to 'Widgets Inc.' for "+         "for row with CustomerID of ALFKI");       readCommittedCommand.CommandText =         "UPDATE Customers "+         "SET CompanyName = 'Widgets Inc.' "+         "WHERE CustomerID = 'ALFKI'";       numberOfRows = readCommittedCommand.ExecuteNonQuery();       Console.WriteLine("Number of rows updated = "+ numberOfRows);       // display the new rows and rollback the changes       DisplayRows(readCommittedCommand);       Console.WriteLine("Rolling back changes");       readCommittedTrans.Rollback();     }     catch (SqlException e)     {       Console.WriteLine(e);     }     finally     {       serConnection.Close();       rcConnection.Close();     }   } } 

Warning 

If you compile and run this program as is, then it will throw a SqlException. This is intentional, as it shows you that the attempt to get a lock timed out. If you comment out the first call to the DisplayRows() method in this program [marked with an asterisk (*)], then the program doesn't throw a SqlException. This is because commenting out the first call to DisplayRows() stops the serializable transaction from retrieving and therefore locking the rows. The second transaction is then able to get the lock on the ALFKI row.

The output from this program is as follows (notice it throws a SqlException when the lock timeout period is exceeded):

 mySqlDataReader["CustomerID"] = ALFKI mySqlDataReader["CompanyName"] = Alfreds Futterkiste Inserting new row into Customers table with CustomerID of J8COM System.Data.SqlClient.SqlException: Lock request time out period exceeded.  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,   TdsParserState state)  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,   TdsParserState state)  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()  at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler,   SqlDataReader dataStream)  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()  at Block.Main() 

Try commenting out the first call to DisplayRows() in the program, and then recompile and run it again. This time the second transaction will be able to get the lock on the row and proceed.

Deadlocks

A deadlock occurs when two transactions are waiting for locks that the other transaction currently has. Consider the following two transactions:

Transaction 1 (T1):

 BEGIN TRANSACTION UPDATE Customers SET CompanyName = 'Widgets Inc.' WHERE CustomerID = 'ALFKI' UPDATE Products SET ProductName = 'Widget' WHERE ProductID = 1 COMMIT TRANSACTION 

Transaction 2 (T2):

 BEGIN TRANSACTION UPDATE Products SET ProductName = ' Chai' WHERE ProductID = 1 UPDATE Customers SET CompanyName = ' Alfreds Futterkiste' WHERE CustomerID = 'ALFKI' COMMIT TRANSACTION 

Notice that T1 and T2 both update the same rows in the Customers and Products table. If T1 and T2 are executed in series at different times, T1 is executed and completed, followed by T2, then there's no problem. However, if T1 and T2 are executed at the same time with their UPDATE statements intermeshed, then a deadlock occurs. Let's take a look at an example of this using the following steps:

1. T1 is started.

2. T2 is started.

3. T1 locks the Customers row and updates the row.

4. T2 locks the Products row and updates the row.

5. T2 waits for the lock on the Products row, which is currently held by T1.

6. T1 waits for the lock the Customers row, which is currently held by T2.

In step 5, T2 is waiting for a lock held by T1. In step 6, T1 is waiting for a lock held by T2. Thus, a deadlock occurs since both transactions are waiting for each other. Both transactions hold mutually required locks. SQL Server will detect the deadlock and roll back one of the transactions. SQL Server rolls back the transaction that is the least expensive to undo, and also returns an error indicating that a deadlock occurred.

You can also choose the transaction that is to be rolled back using the T-SQL SET DEADLOCK_ PRIORITY command, which uses the following syntax:

 SET DEADLOCK_PRIORITY { LOW | NORMAL | @variable } 

where

• LOW indicates the transaction has a low priority and is the one to roll back in the event of a deadlock.

• NORMAL indicates that the default rule is to be applied, meaning the least expensive transaction is rolled back.

• @variable is a T-SQL character variable that you set to 3 for LOW or 6 for NORMAL.

For example, the following command sets DEADLOCK_PRIORITY to LOW:

 SET DEADLOCK_PRIORITY LOW 

You can also execute the SET DEADLOCK_PRIORITY command in C# code. For example:

 t2Command.CommandText = "SET DEADLOCK_PRIORITY LOW"; t2Command.ExecuteNonQuery(); 

Tip 

You can reduce the risk of a deadlock occurring in your program by keeping your transactions as short as possible; that way, the locks are held on the database objects for as short a period as possible. You should also access tables in the same order when executing multiple transactions at the same time; that way, you reduce the risk of transactions holding mutually required locks.

Listing 14.6 shows a program that illustrates the T1 and T2 transaction deadlock scenario described earlier. Each UPDATE is executed using a separate thread to simulate the interleaved updates shown earlier in the six steps.

Listing 14.6: Deadlock.cs

 /*   Deadlock.cs illustrates how two transactions can   deadlock each other */ using System; using System.Data; using System.Data.SqlClient; using System.Threading; class Deadlock {   // create two SqlConnection objects   public static SqlConnection t1Connection =     new SqlConnection(       "server=localhost;database=Northwind;uid=sa;pwd=sa"     );   public static SqlConnection t2Connection =     new SqlConnection(       "server=localhost;database=Northwind;uid=sa;pwd=sa"     );   // declare two SqlTransaction objects   public static SqlTransaction t1Trans;   public static SqlTransaction t2Trans;   // declare two SqlCommand objects   public static SqlCommand t1Command;   public static SqlCommand t2Command;   public static void UpdateCustomerT1()   {     // update the row with a CustomerID of ALFKI     // in the Customers table using t1Command     Console.WriteLine("Setting CompanyName to 'Widgets Inc.' "+       "for row with CustomerID of ALFKI using t1Command");     t1Command.CommandText =       "UPDATE Customers "+       "SET CompanyName = 'Widgets Inc.' "+       "WHERE CustomerID = 'ALFKI'";     int numberOfRows = t1Command.ExecuteNonQuery();     Console.WriteLine("Number of rows updated = "+ numberOfRows);   }   public static void UpdateProductT2()   {     // update the row with a ProductID of 1     // in the Products table using t2Command     Console.WriteLine("Setting ProductName to 'Widget' "+       "for the row with ProductID of 1 using t2Command");     t2Command.CommandText =       "UPDATE Products "+       "SET ProductName = 'Widget' "+       "WHERE ProductID = 1";     int numberOfRows = t2Command.ExecuteNonQuery();     Console.WriteLine("Number of rows updated = "+ numberOfRows);   }   public static void UpdateProductT1()   {     // update the row with a ProductID of 1     // in the Products table using t1Command     Console.WriteLine("Setting ProductName to 'Chai' "+       "for the row with ProductID of 1 using t1Command");     t1Command.CommandText =       "UPDATE Products "+       "SET ProductName = 'Chai' "+       "WHERE ProductID = 1";     int numberOfRows = t1Command.ExecuteNonQuery();     Console.WriteLine("Number of rows updated = "+ numberOfRows);   }   public static void UpdateCustomerT2()   {     // update the row with a CustomerID of ALFKI     // in the Customers table using t2Command     Console.WriteLine("Setting CompanyName to 'Alfreds Futterkiste' "+       "for row with CustomerID of ALFKI using t2Command");     t2Command.CommandText =       "UPDATE Customers "+       "SET CompanyName = 'Alfreds Futterkiste' "+       "WHERE CustomerID = 'ALFKI'";     int numberOfRows = t2Command.ExecuteNonQuery();     Console.WriteLine("Number of rows updated = "+ numberOfRows);   }   public static void Main()   {     // open the first connection, begin the first transaction,     // and set the lock timeout to 5 seconds     t1Connection.Open();     t1Trans = t1Connection.BeginTransaction();     t1Command = t1Connection.CreateCommand();     t1Command.Transaction = t1Trans;     t1Command.CommandText = "SET LOCK_TIMEOUT 5000";     t1Command.ExecuteNonQuery();     // open the second connection, begin the second transaction,     // and set the lock timeout to 5 seconds     t2Connection.Open();     t2Trans = t2Connection.BeginTransaction();     t2Command = t2Connection.CreateCommand();     t2Command.Transaction = t2Trans;     t2Command.CommandText = "SET LOCK_TIMEOUT 5000";     t2Command.ExecuteNonQuery();     // set DEADLOCK_PRIORITY to LOW for the second transaction     // so that it is the transaction that is rolled back     t2Command.CommandText = "SET DEADLOCK_PRIORITY LOW";     t2Command.ExecuteNonQuery();     // create four threads that will perform the interleaved updates     Thread updateCustThreadT1 = new Thread(new ThreadStart(UpdateCustomerT1));     Thread updateProdThreadT2 = new Thread(new ThreadStart(UpdateProductT2));     Thread updateProdThreadT1 = new Thread(new ThreadStart(UpdateProductT1));     Thread updateCustThreadT2 = new Thread(new ThreadStart(UpdateCustomerT2));     // start the threads to actually perform the interleaved updates     updateCustThreadT1.Start();     updateProdThreadT2.Start();     updateProdThreadT1.Start();     updateCustThreadT2.Start();   } } 

Note 

You can think of a thread as a separate process in your program, and each thread appears to execute in parallel with the other threads. For a detailed discussion of threads, see the book Mastering Visual C# .NET by Jason Price and Mike Gunderloy (Sybex, 2002).

The program shown in Listing 14.6 contains the following methods:

• UpdateCustomerT1() Updates the row with a CustomerID of ALFKI in the Customers table using the first transaction. Specifically, it sets the CompanyName to Widgets Inc.

• UpdateProductT2() Updates the row with a ProductID of 1 in the Products table using the second transaction. Specifically, it sets the ProductName to Widget.

• UpdateProductT1() Updates the row with a ProductID of 1 in the Products table using the first transaction. Specifically, it sets the ProductName to Chai.

• UpdateCustomerT2() Updates the row with a CustomerID of ALFKI in the Customers table using the second transaction. Specifically, it sets the CompanyName to Alfreds Futterkiste.

These methods will be called by the threads to perform the interleaved updates.

Note 

This program indicates that the second transaction is to be rolled back when deadlock occurs using the SET DEADLOCK_PRIORITY LOW command.

The output from this program is as follows:

 Setting CompanyName to 'Widgets Inc.' for row  with CustomerID of ALFKI using t1Command Number of rows updated = 1 Setting ProductName to 'Widget' for the row  with ProductID of 1 using t2Command Number of rows updated = 1 Setting ProductName to 'Chai' for the row  with ProductID of 1 using t1Command Setting CompanyName to 'Alfreds Futterkiste' for row  with CustomerID of ALFKI using t2Command Unhandled Exception: System.Data.SqlClient.SqlException: Transaction (Process ID 53) was deadlocked on  {lock} resources with another process and has been chosen as the deadlock victim.  Rerun the transaction.  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,   TdsParserState state)  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,  TdsParserState state)  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()  at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler,   SqlDataReader dataStream)  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()  at Deadlock.UpdateCustomerT2() Number of rows updated = 1