Database Reference
In-Depth Information
The resource locked by the
RID
lock can be represented in the following format from the
resource_description
column:
DatabaseID:FileID:PageID:Slot(row)
In the output from the query against
sys.dm_tran_locks
in Figure
20-1
, the
DatabaseID
is displayed separately
under the
resource_database_id
column. The
resource_description
column value for the
RID
type represents
the remaining part of the
RID
resource as
1:23819:0
. In this case, a
FileID
of
1
is the primary data file, a
PageID
of
23819
is a page belonging to the
dbo.Test1
table identified by the
C1
column, and a
Slot (row)
of
0
represents the
row position within the page. You can obtain the table name and the database name by executing the following
SQL statements:
SELECT OBJECT_NAME(1668200993),
DB_NAME(5);
The row-level lock provides very high concurrency since blocking is restricted to the row under effect.
Key-Level Lock
This is a row lock within an index, and it is identified as a
KEY
lock. As you know, for a table with a clustered index, the
data pages of the table and the leaf pages of the clustered index are the same. Since both of the rows are the same for
a table with a clustered index, only a
KEY
lock is acquired on the clustered index row, or limited range of rows, while
accessing the rows from the table (or the clustered index). For example, consider having a clustered index on the
Test1
table.
CREATE CLUSTERED INDEX TestIndex ON dbo.Test1(C1);
Next, rerun the following code:
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 corresponding output from
sys.dm_tran_locks
shows a
KEY
lock instead of the
RID
lock, as you can see
in Figure
20-2
.
