Share via

Using Locking Like Synchronization Object

  • Article

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
)
go

create unique clustered index idxClus on tblTest(strData)
create unique nonclustered index idxNC on tblTest(iID)
go

insert into tblTest values('One', GetDate())
insert into tblTest values('Two', GetDate())
insert into tblTest values('Three', GetDate())
go

It sounded pretty simple, just use an XLOCK or similar hint and lock the row such as the following.

Connection #1

begin tran
select * from tblTest (XLOCK) where strData = 'Two'

Connection #2

begin tran
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.

begin tran

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.

Bob Dorr
SQL Server Principal Escalation Engineer

Comments

  • Anonymous
    August 27, 2008
    PingBack from http://hubsfunnywallpaper.cn/?p=2661

  • Anonymous
    September 15, 2008
    interesting. You mentioned it is more of a T-SQL trick and NOT a recommendation practice. so what is the recommending way to exclusively lock a row and stop another connection reading it. thx jt