Inside SQL Server: Indexing and Locking

One of the most touted new features of SQL Server 7.0 is its ability to do true row-level locking. By locking only the data that is being processed, instead of an entire page containing numerous rows, SQL Server 7.0 drastically reduces blocking problems. (Blocking occurs when one user or application needs a lock on an object, but another user or application already has an incompatible lock on the object.) However, row-level locking alone can't solve all blocking problems. You still need to add appropriate indexes to your tables.

The Problem
Let's create a table to demonstrate why row-level locks by themselves won't solve all your blocking problems. The code in Listing 1 creates and populates the table. Use SQL Server 7.0's Query Analyzer or another querying tool to run the code in one connection.

Start a new connection and execute this partial transaction. (It's only a partial transaction because no COMMIT or ROLLBACK occurs inside the batch. SQL Server holds the locks until you execute a COMMIT or ROLLBACK command.)

BEGIN TRAN 
UPDATE testlocks
SET col1 = 7 
WHERE col1 = 1

EXEC sp_lock @@spid
   /* The output shows you one exclusive (X)
   	lock on a RID; this is the row that
	has been updated. */

In a third query window, run the following batch before you issue the ROLLBACK in the second query window.

USE pubs
UPDATE testlocks
SET col1 = 10
WHERE col1 = 3

Execute the ROLLBACK in the second window:

ROLLBACK TRAN