The Case of the Missing Shared Locks

re: Read Committed, Shared Locks, and Rollbacks

Uri Dimant — Mon, 01 Nov 2010 06:47:47 GMT

Paul

SQL Server 2005 (SP3)

>>>Perhaps unexpectedly, this query does not block – the result is >>>returned immediately, despite the exclusive lock.

Running the below in the second connection IS blocked

SELECT D.some_key,

D.some_value FROM

dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK);

What I am missing?

re: Read Committed, Shared Locks, and Rollbacks

Paul White — Mon, 01 Nov 2010 08:06:55 GMT

Hi Uri,

If you created the Demo table in tempdb, you need to run a manual CHECKPOINT after creating the table and inserting the test row.

I've updated the script to reflect that requirement - thanks for the feedback :)

Paul

re: Read Committed, Shared Locks, and Rollbacks

Uri Dimant — Mon, 01 Nov 2010 10:12:28 GMT

Hi Paul

Even running SELECT in the second connection without the hints does not block and we get result immediately

re: Read Committed, Shared Locks, and Rollbacks

Paul White — Mon, 01 Nov 2010 10:34:33 GMT

Uri,

Yes, that's right. The hints aren't required for the optimization to kick in - I added them to make it clear that shared row locks are being taken. Glad it's working for you now.

Paul

re: The Case of the Missing Shared Locks

Rishabh K — Mon, 19 Mar 2012 15:28:09 GMT

Hi Paul,

I tried the same thing on one of my database but as expected the second session was blocked. However it works in tempdb

Use MyDatabase;

CREATE TABLE TEST(ID int PRIMARY KEY,Name varchar(50))

INSERT INTO TEST values (1,'Rishabh')

--Session 1

BEGIN TRAN

select * from test with (XLOCK)

--session 2

select * from test with(rowlock,readcommittedlock)

A little doubt..In connection 1, the data is still in uncommitted mode(not available to others as Read committed ensures).

What I think is even if we specify rowlock in session 2, sql server will anyhow go for the rowlock assuming that there is only a single row ,so it will try to get a shared lock on that row and due to the fact that the row is X locked by the session 1, it will be blocked.

I know that IS is compatible with IX but here there is only one key that really needs the access(one with X lock in session 1 and one with S lock in session 2)

Am I missing anything or tempdb works in a different way as compared to other user databases ??

Thanks for all the great post!!!

re: The Case of the Missing Shared Locks

Rishabh K — Mon, 19 Mar 2012 15:31:17 GMT

I think I missed the checkpoint run..Thanks again