THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: Can SELECT (NOLOCK) block INSERTs?

The answer is yes. This may be obvious to some, but may not be so obvious to many. It’s good to look at a specific example to see how it may happen.

 

First of all, let’s be precise about what we are talking about here. Assume that we have a table called Junk. We are running a query on the table as follows:

 

SELECT * FROM Junk (NOLOCK);

 

The question is whether this may block the following INSERT:

 

INSERT Junk VALUES(1);

 

You would expect no blocking on INSERT. And in most cases, that would be case. After all, that’s what you use NOLOCK for. However, you should not be surprised that a NOLOCK query can cause blocking on an INSERT. Here is an example to demonstrate that.

 

On connection 1, run the following script to create the test table Junk and populate it with 2000 rows:

 

-- create a test table

create table junk (

i int,

filler char(4000) not null default 'abc'

)

go

 

-- populate it with 2000 rows

set nocount on

go

declare @i int

set @i = 1

while @i <= 2000

begin

  insert junk(i) values(@i)

  set @i = @i + 1

end

create index ix_junk on junk(i)

go

 

Now run the following SQL statement on connection 1. Note that it is doing a NOLOCK select from the Junk table:

 

-- run a NOLOCK query on it to

SELECT t1.* INTO #tmp

  FROM junk t1 (NOLOCK), junk t2 (NOLOCK)

 

While the above script is running on connection 1, run the following on connection 2 to the same database:

 

DBCC DBREINDEX(junk)

 

And then run the following on connection 3 to the same database:

 

INSERT Junk(i) VALUES(1);

 

You would see that the above INSERT statement being blocked for the duration of the NOLOCK query on connection 1.

 

If you check the blocking chain, you would see something similar to the following:

 

spid   kpid   blocked waittype waittime  lastwaittype   waitresource          

------ ------ ------- -------- --------- -------------- --------------------

69     4912   113     0x0008   4063      LCK_M_IX       TAB: 7:1168723216 []  

113    4904   115     0x0002   32953     LCK_M_SCH_M    TAB: 7:1168723216 []   

115    4604   0       0x0082   188       LOGBUFFER                            

 

Here, spid 115 is connection 1, spid 113 is connection 2, and spid 69 is connection 3. And object id 1168723216 is the Junk table. So you see, connection 1 is blocking connection 2 which, in turn, is blocking connection 3.

 

If you examine the locks (say with sp_lock), you would see the following (among others):

 

spid   dbid   ObjId       IndId  Type Resource         Mode     Status

------ ------ ----------- ------ ---- ---------------- -------- ------

69     7      1168723216  0      TAB                   IX       WAIT

113    7      1168723216  0      TAB                   Sch-M    WAIT

115    7      1168723216  0      TAB                   Sch-S    GRANT

 

What happens here is that, even though the NOLOCK query does not acquire a shared lock, it does acquire a Sch-S lock (Schema Stability lock). The DBCC DBREINDEX on connection 2 (spid 113) needs to acquire a Sch-M lock (Schema Modification lock) on the table. But since Sch-M is incompatible with Sch-S, it would have to wait until Sch-S is released, thus blocking. When connection 3 (spid 69) comes around to insert a row into the same table, it needs to acquire an IX lock (Intent Exclusive) on the table, and this lock is incompatible with Sch-M, thus blocking.

 

Now, DBCC DBREINDEX is simply being used as an example in this case. It could be any DDL on the table. In fact, it could be any statement that requires a Sch-M lock.

 

So, a NOLOCK query may not directly block an INSERT. It can certainly block an INSERT indirectly via a Sch-M lock. In fact, it can block any data manipulation operation via a Sch-M lock.

Published Monday, August 03, 2009 10:14 AM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

J. Morgan Smith said:

So is it the "INTO #tmp" that's causing the Sch-S lock and not the "NOLOCK"?  

Wouldn't you get the same thing without the NOLOCKs on connection 1?

August 3, 2009 10:14 AM
 

AaronBertrand said:

J. Morgan Smith, yes you would (in addition to other potential locks).  However Linchi's point (at least I think) is that a lot of people would not expect any locking at all, and might not understand why it is happening once they do encounter it...

August 3, 2009 11:18 AM
 

Linchi Shea said:

Morgan;

No, it's not INTO #tmp. I could have avoided using INTO #tmp in the example. You can do a SELECT (NOLOCK) scan on any large table to see the behavior. You just need to have the query run for long enough for the human eyes to see the blocking.

August 3, 2009 11:20 AM
 

Bernd Eckenfels said:

So, does any DML aquire the Schema modification lock or any other lock which is not compatible with Schm-S?  I dont worry about DDLs they dont happen so often in production and block all kinds of stuff.

August 3, 2009 8:09 PM
 

Linchi Shea said:

DMLs typically do not acquire Sch-M lock on the object they are manipulating. You may see a Sch-M on some metadata. But that's not what we are concerned when it comes to lock compatibility.

However, I do not know if a DML will never acquire a Sch-M on the target object. I'm not sure if this si documented anywhere. Someone else may be able to chime in with a more definitive answer.

August 4, 2009 9:52 PM
 

Jon Crawford said:

Realizing that I'm late to the party, is there a remedy that will avoid the block? Or since we've already thrown NOLOCK at it, our arsenal is empty, and it's just something we need to realize and deal with?

August 12, 2009 1:31 PM
 

Linchi Shea said:

There is no avoiding this type of blocking. You just have to manage it. Hopefully, your NOLOCK SELECT will be brief, and the duration of blocking will be brief as well. In practice, this should be a rare occurence anyway (that is, hopefully you don't run DDLs during the business hours when it matters greatly whether you are blocked or not).

August 12, 2009 2:27 PM
 

Sandesh R said:

Nice Article!

December 16, 2009 8:12 AM
 

SQLZealot said:

Why Spid 115 is showing the last wait type as "LOGBUFFER"? Does it have any impact on the cuase of the dealock?

December 26, 2011 12:12 AM
 

Ivan said:

I maybe having similar nolock issue; got a simple quick select with nolock on a big table; off-peak hours it runs in a minute; but peak hours (lots of inserts) same query runs upto hours. Anyway to aviod this ? thanks

January 3, 2012 6:02 PM
 

Sanjay Susheelan said:

Dear Linchi,

You are confusing folks,...

Actually it is the DBCC REINDEX which is being blocked by the SELECT..(NOLOCK)..because of the Schema Stability lock required by DBCC REINDEX.

So INSERT was never blocked by this.

Thanks

Sanjay

mail2sanjays@gmail.com

August 27, 2013 3:23 AM
 

baii said:

This information is what i wanted... :) Great!!! Thanks Linchi Shea

November 19, 2013 6:15 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement