Specifying Locking Hints in a SQL Server Database

Problem

You need to pessimistically lock rows in an underlying SQL Server database.

Solution

Use SQL Server locking hints from ADO.NET.

The sample code contains three event handlers:

Start Tran Button.Click

Creates a SQL SELECT statement to retrieve the Orders table from the Northwind database. A locking hint, either UPDLOCK or HOLDLOCK , is added to the statement as specified. A Connection is opened and a Transaction started on it with an isolation level of ReadCommitted . A DataAdapter is used on the transacted connection to fill a DataTable . A CommandBuilder is created to generate updating logic. The default view of the table is bound to the data grid on the form.

Cancel Button.Click

Clears the data grid, rolls back the transaction, and closes the connection.

Form.Closing

Rolls back the transaction if it exists and closes the connection.

The C# code is shown in Example 6-39.

Example 6-39. File: UsingLockingHintsForPessimisticLockingForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; private SqlConnection conn; private SqlTransaction tran; // . . . private void startButton_Click(object sender, System.EventArgs e) { startButton.Enabled = false; String sqlText = "SELECT * FROM Orders WITH "; // Add pessimistic locking as specified by user. if(updLockRadioButton.Checked) sqlText += "(UPDLOCK)"; else if(holdLockRadioButton.Checked) sqlText += "(HOLDLOCK)"; // Create connection. conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); conn.Open( ); // Start the transaction. tran = conn.BeginTransaction(IsolationLevel.ReadCommitted); // Create the command. SqlCommand cmd = new SqlCommand(sqlText, conn, tran); // Create the DataAdapter and CommandBuilder. SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder cb = new SqlCommandBuilder(da); // Fill table using the DataAdapter. DataTable dt = new DataTable( ); da.Fill(dt); // Bind the default view of the table to the grid. dataGrid.DataSource = dt.DefaultView; cancelButton.Enabled = true; dataGrid.ReadOnly = false; } private void cancelButton_Click(object sender, System.EventArgs e) { cancelButton.Enabled = false; // Unbind the table from the grid. dataGrid.DataSource = null; // Roll back the transaction and close the connection. tran.Rollback( ); conn.Close( ); startButton.Enabled = true; }

Discussion

A lock is an object indicating that a user has a dependency on a resource. Locks ensure transactional integrity and database consistency by preventing other users from changing data being read by a user and preventing users from reading data being changed by a user. Locks are acquired and released by user actions; they are managed internally by database software.

A locking hint can be specified with SELECT , INSERT , DELETE , and UPDATE statements to instruct SQL Server as to the type of lock to use. You can use locking hints when you need control over locks acquired on objects. The SQL Server Optimizer automatically determines correct locking; hints should be used only when necessary. Locking hints override the current transaction isolation level for the session.

A locking hint is specified following the FROM clause using a WITH clause. The hint is specified within parentheses and multiple hints are separated by commas.

Tables Table 6-21, Table 6-22, and Table 6-23 describe the different locking hints that you can use, categorized according to their function.

Table 6-21. SQL Server locking hints for isolation level

Table 6-22. SQL Server locking hints for granularity

Table 6-23. SQL Server Locking Hints for Other Functions

There are a number ways to get information about database locks:

• The system stored procedure sp_lock returns a result set containing all active locks.
• The syslockinfo table in the master database contains information about all granted, converting, and waiting lock requests . It is a denormalized view of the data structures used internally by the lock manager.
• The SQL Server Profiler can be used to monitor and record locking information.
• The Windows Performance Monitor has a SQL Server Locks Object counter that can be used to monitor lock activity.

For more information about database locks, using locking hints, or monitoring database locks, see Microsoft SQL Server Books Online.