Blocking and Blocked Processes - SQL Server Query Performance Tuning - page 394

Row-Level Lock
This lock is maintained on a single row within a table and is the lowest level of lock on a database table. When a query
modifies a row in a table, an RID lock is granted to the query on the row. For example, consider the transaction on the
following test table:

--Create a test table
IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1 (C1 INT);
INSERT INTO dbo.Test1
VALUES (1);
GO

BEGIN TRAN
DELETE dbo.Test1
WHERE C1 = 1;

SELECT dtl.request_session_id,
dtl.resource_database_id,
dtl.resource_associated_entity_id,
dtl.resource_type,
dtl.resource_description,
dtl.request_mode,
dtl.request_status
FROM sys.dm_tran_locks AS dtl
WHERE dtl.request_session_id = @@SPID;
ROLLBACK

The dynamic management view, sys.dm_tran_locks , can be used to display the lock status. The query against
sys.dm_tran_locks in Figure 20-1 shows that the DELETE statement acquired, among other locks, an exclusive RID
lock on the row to be deleted.

Figure 20-1. Output from sys.dm_tran_locks showing the row-level lock granted to the DELETE statement

■ Note i explain lock modes later in the chapter in the “lock Modes” section.

Granting an RID lock to the DELETE statement prevents other transactions from accessing the row.