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.