This post is more of a T-SQL trick and NOT a recommendation practice, but since I addressed this in a recent customer case I thought I would share it.
Problem: Application needs to lock a row for the duration of the transaction and ensure no data from the row can be read by another user under read committed isolation. Clearly you can see the downside to concurrency and why I would not recommend this design. However, this customer needed to support an application that was ported from another platform until they could redesign.
The following is the test schema.
create table tblTest
iID int identity(1,1) NOT NULL, -- NC Index
strData char(10), -- Clustered Index
dtDateTime DateTime, -- Not indexed
create unique clustered index idxClus on tblTest(strData)
create unique nonclustered index idxNC on tblTest(iID)
insert into tblTest values('One', GetDate())
insert into tblTest values('Two', GetDate())
insert into tblTest values('Three', GetDate())
It sounded pretty simple, just use an XLOCK or similar hint and lock the row such as the following.
select * from tblTest (XLOCK) where strData = 'Two'
select iID from tblTest where strData = 'Two'
THIS DOES NOT BLOCK CONNECTION #2.
SQL Server optimizes use of resources so the second connection uses the non-clustered index to retrieve the data. This is possible because the NC index has the iID and the clustering key. No user could update the data because the leaf row in the clustered index is locked and would prevent actual changes.
Instead you could use an update with the OUTPUT clause to replace the select statement in connection #1.
update tblTest set strData = 'Two' -- Update a clustered index column to self to force KEY locks on CL and NC indexes
output inserted.* -- Return the rows that were updated
where strData = 'Two'
The update forces KEY locks on the clustered and any non-clustered index using the clustering key. This will block read committed connections like connection #2.
The OUTPUT clause allows the application to retrieve row data with a simple change to the statement the application issues.
SQL Server Principal Escalation Engineer