[ Team LiB ]Recipe 6.14 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;


// 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

NOLOCK
Do not issue shared locks and do not recognize exclusive locks. Applies
only to the SELECT statement.
PAGLOCK Use page locks where a single table lock would normally be used.
ROWLOCK Use row-level locking instead of page-level and table-level locking.
TABLOCK
Use table-level locking instead of row-level and page-level locking. By
default, the lock is held until the end of the statement.
TABLOCKX
Use an exclusive table lock preventing other users from reading or
updating the table. By default, the lock is held until the end of the
statement.
Table 6-23. SQL Server Locking Hints for Other Functions
Locking
hint
Description
READPAST
Skip locked rows that would ordinarily appear in the result set rather than
blocking the transaction by waiting for other transactions to release locks
on those rows. Applies only to transactions with an isolation level of
READ COMMITTED. Applies only to the SELECT statement.
UPDLOCK
Use update locks instead of shared locks when reading a table. This
allows you to read data and later update it with a guarantee that it has not
changed since you last read it while other users are not blocked from
reading the data. Cannot be used with NOLOCK or XLOCK.
XLOCK
Use an exclusive lock that is held until the end of the transaction on all
data processed by the statement. Can be specified with either PAGLOCK
or TABLOCK granularity. Cannot be used with either NOLOCK or