Edit: As @MaxVernon points out, the following is in no way a suggestion to use NOLOCK, and I very well should have just mentioned setting the transaction level to
READ UNCOMMITED and let the negative connotation stand there than bringing
NOLOCK up in the first place. So as originally posted:
The quick and simple is "Yes, the first query will block the second query unless a specific index hint is specified ( NOLOCK, sometimes called a "dirty read" ) or the second query's transaction isolation level is set to
READ UNCOMMITED ( which operates identically ), no, it does not."
In response to the additional detail provided in the question entailing the inclusion of a
WITH clause on the second
SELECT, being mutually exclusive or otherwise, the interactions between the two queries will be largely the same.
IF NOT EXISTS ( SELECT 1
WHERE name = 'Foo'
AND type = 'U' )
--DROP TABLE dbo.Foo;
CREATE TABLE dbo.Foo
Foo_PK BIGINT IDENTITY( 1, 1 ) NOT NULL,
PRIMARY KEY ( Foo_PK ),
CREATE NONCLUSTERED INDEX IX_Foo_x
ON dbo.Foo ( x );
INSERT INTO dbo.Foo ( Bar, x, y, z )
VALUES ( 1, 1, 1, 1 ), ( 0, 0, 0, 0 );
SET y = 0
WHERE x = 1;
-- COMMIT TRANSACTION;
In a separate session, run the following:
FROM dbo.Foo WITH ( NOLOCK );
You can examine the locks currently being held by running
sp_lock, preferably in a yet another separate session:
You should see a
KEY type lock being held by the spid performing the insert transaction in
X ( exclusive ) mode, not to be confused with the other
IX ( Intent-Exclusive ) locks. The lock documentation indicates that the while the
KEY lock is range-specific, it also prevents other transactions from inserting or updating the affected columns by altering the data contained therein so that it could fall within that range of the original query. As the lock itself being held is exclusive, the first query is preventing access to the resource from any other concurrent transaction. In effect, all rows of the column are locked, whether or not they fall within the range specified by the first query.
S lock being held by the second session will thus
WAIT until the
X lock clears, preventing another
X ( or
U ) lock from being taken on that resource from a different concurrent spid before the second session completes its read operation, justifying the existence of the
Now an edit for clarity: Unless I'm mistaken in what a dirty read is from the brief description of the risks mentioned here... Edit 3: I just realized I'm not considering the effect of a background checkpoint which writes an as of yet uncommitted transaction to disk, so yes, my explanation was misleading.
In the second query, the first batch can ( and in this case, will ) return uncommitted data. The second batch, running in the default transaction isolation level of
READ COMMITED will return only after a commit or rollback has been completed in the first session.
From here you can look at your query plans and the associated lock levels, but better yet, you can read all about locks in SQL Server here.