ADO.NET 3.5 Cookbook, 2nd Edition

7.12. Specifying Locking Hints in a SQL Server Database

Problem

You need to explicitly control locking behavior a T-SQL transaction.

Solution

Use SQL Server locking hints from ADO.NET.

The solution uses a table named LockingHint in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE LockingHint(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

Execute the following T-SQL batch to create the sample data this solution uses:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO LockingHint VALUES (1, 'Field1.1', 'Field2.1');
	INSERT INTO LockingHint VALUES (2, 'Field1.2', 'Field2.2');
	INSERT INTO LockingHint VALUES (3, 'Field1.3', 'Field2.3');
	INSERT INTO LockingHint VALUES (4, 'Field1.4', 'Field2.4');

The solution creates a DataTable and fills it with records with Id=2 and Id = 3 from the table LockingHint in the AdoDotNet35Cookbook database. The contents of the DataTable are output to the console. The select statement that retrieves the records locks uses an update lock (UPDLOCK). The UpdateRow() method is called twice to modify and update rows with Id=1 and Id=3. The row with Id=1 updates successfully, but the row with Id=3 times out because of the update lock on rows with Id= 2 and Id=3. The contents of the table LockingHint are output to the console at the end of the solution to confirm the results of the update on the database.

The C# code in Program.cs in the project SpecifyLockingHints ...