Post date: Sep 21, 2011 8:23:59 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 transelect * from customer with(updlock,holdlock) where custid = 1select @@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 <> @@SPIDorder by request_session_id, resource_type
These are my results
-- SELECT with xlock, rowlock-- does not block select on keyPAGE IX LOCK 52KEY X LOCK 52OBJECT IX LOCK 52-- SELECT with xlock, holdlock-- does not block select on keyKEY X LOCK 52OBJECT IX LOCK 52PAGE IX LOCK 52-- SELECT with xlock, paglock-- DOES block select on key, BUT ALSO ALL OTHER ROWS IN THAT PAGE.KEY X LOCK 52OBJECT IX LOCK 52PAGE X LOCK 52-- SELECT with udplock. -- does not block select on keyPAGE IX LOCK 52KEY X LOCK 52OBJECT IX LOCK 52-- SELECT with udplock, holdlock -- does not block select on keyKEY U LOCK 52OBJECT IX LOCK 52PAGE IU LOCK 52-- SELECT with updlock, rowlock-- does not block select on keyPAGE IU LOCK 52KEY U LOCK 52OBJECT IX LOCK 52-- SELECT with updlock, rowlock on NONCLUSTERED table-- does not block select on keyOBJECT IX LOCK 52PAGE IU LOCK 52PAGE IU LOCK 52 RID U LOCK 52-- SELECT with updlock on NONCLUSTERED table-- does not block select on keyKEY U LOCK 52OBJECT IX LOCK 52PAGE IU LOCK 52PAGE IU LOCK 52 RID U LOCK 52-- SELECT with xlock on NONCLUSTERED table-- DOES block select on keyKEY X LOCK 52OBJECT IX LOCK 52PAGE IX LOCK 52PAGE IX LOCK 52 RID X LOCK 52-- SELECT with xlock,rowlock on NONCLUSTERED table-- DOES block select on keyKEY X LOCK 52OBJECT IX LOCK 52PAGE IX LOCK 52PAGE IX LOCK 52 RID X LOCK 52-- SELECT with holdlock on NONCLUSTERED table-- does not block select on keyKEY S LOCK 52OBJECT IS LOCK 52PAGE IS LOCK 52PAGE 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)