Just Code‎ > ‎

TSQL - Looking at locking; can you lock a row using select statement?

posted Sep 21, 2011, 1:23 PM by Peter Henell   [ updated Sep 21, 2011, 11:58 PM ]

My mission: Can you lock a single row, preventing everyone else from reading it, using a single select statement.

Today i have been doing some experiments on locking using hints in select statements. I found some very odd results, none of my queries were being blocked even when i used the XLOCK query hint. I was checking that the exclusive locks had actually been taken. However after searching Ye olde internet i found this post explaining my results

The Case of the Missing Shared Locks
SQL Server contains an optimization that allows it to avoid taking row-level shared (S) locks in some circumstances.  Specifically, it can skip shared locks if there is no risk of reading uncommitted data without them.
The optimization only applies to row-level shared locks, so if we specifically request a different granularity, the query blocks

Setup tables needed for this test:
create table Customer(
CustId int primary key identity,
name varchar(50) not null
)

create table Customer2(
CustId int primary key NONCLUSTERED identity,
name varchar(50) not null
)

insert Customer(name) values ('peter'),('abbas'),('taffe'),('wrest')
insert Customer2(name) values ('peter'),('abbas'),('taffe'),('wrest')
CHECKPOINT -- important to get the same results. Commited data that have not been written to disk might actually block you even when using my examples.

Run this in session 1, change the lock hints to try out different ways 
begin tran

select * from customer with(updlock,holdlock) where custid = 1

select @@SPID

Run this in sessino 2, to check if the row is locked:
select * from customer where custid = 1



Use this query to look at the locks required by the queries ( run in a third session).
select    resource_type
        , request_mode
        , request_type
        , request_session_id 
from sys.dm_tran_locks  
where request_session_id <> @@SPID
order by request_session_id, resource_type


These are my results
-- SELECT with xlock, rowlock
-- does not block select on key
PAGE    IX    LOCK    52
KEY        X    LOCK    52
OBJECT    IX    LOCK    52

-- SELECT with xlock, holdlock
-- does not block select on key
KEY        X    LOCK    52
OBJECT    IX    LOCK    52
PAGE    IX    LOCK    52

-- SELECT with xlock, paglock
-- DOES block select on key, BUT ALSO ALL OTHER ROWS IN THAT PAGE.
KEY        X    LOCK    52
OBJECT    IX    LOCK    52
PAGE    X    LOCK    52

-- SELECT with udplock. 
-- does not block select on key
PAGE    IX    LOCK    52
KEY        X    LOCK    52
OBJECT    IX    LOCK    52


-- SELECT with udplock, holdlock 
-- does not block select on key
KEY        U    LOCK    52
OBJECT    IX    LOCK    52
PAGE    IU    LOCK    52


-- SELECT with updlock, rowlock
-- does not block select on key
PAGE    IU    LOCK    52
KEY        U    LOCK    52
OBJECT    IX    LOCK    52




-- SELECT with updlock, rowlock on NONCLUSTERED table
-- does not block select on key
OBJECT    IX    LOCK    52
PAGE    IU    LOCK    52
PAGE    IU    LOCK    52
RID        U    LOCK    52

-- SELECT with updlock on NONCLUSTERED table
-- does not block select on key
KEY        U    LOCK    52
OBJECT    IX    LOCK    52
PAGE    IU    LOCK    52
PAGE    IU    LOCK    52
RID        U    LOCK    52


-- SELECT with xlock on NONCLUSTERED table
-- DOES block select on key
KEY        X    LOCK    52
OBJECT    IX    LOCK    52
PAGE    IX    LOCK    52
PAGE    IX    LOCK    52
RID        X    LOCK    52


-- SELECT with xlock,rowlock on NONCLUSTERED table
-- DOES block select on key
KEY        X    LOCK    52
OBJECT    IX    LOCK    52
PAGE    IX    LOCK    52
PAGE    IX    LOCK    52
RID        X    LOCK    52

-- SELECT with holdlock on NONCLUSTERED table
-- does not block select on key
KEY        S    LOCK    52
OBJECT    IS    LOCK    52
PAGE    IS    LOCK    52
PAGE    IS    LOCK    52
RID        S    LOCK    52
using Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (Hypervisor) 
Comments