Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 170

Conversion Lock Modes (SIX), (SIU), (UIX)
SQL Server also provides the facility to convert shared, update, or exclusive locks to shared with
intent exclusive (SIX), shared with intent update (SIU), or update with intent exclusive (UIX). This
happens when a statement inside a transaction already holds a lock at a coarse granularity (a table)
but now needs to modify a component of the resource held at a much i ner granularity (a row). The
lock held against the coarse granularity needs to rel ect this.
Consider the following example of a SIX lock (code i le Ch6ConversionLockModes.sql ):
USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT BusinessEntityID
,FirstName
,MiddleName
,LastName
,Suffix
FROM Person.Person;
SELECT resource_type
,resource_subtype
,resource_description
,resource_associated_entity_id
,request_mode
,request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
UPDATE Person.Person
SET Suffix = 'Junior'
WHERE FirstName = 'Syed'
AND LastName = 'Abbas';
SELECT resource_type
,resource_subtype
,resource_description
,resource_associated_entity_id
,request_mode
,request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
ROLLBACK TRANSACTION;
A transaction has selected all rows from the Person.Person table. This generates a table-level
shared lock, as shown in Figure 6-13.