In SQL Server, how do read locks work?

Question

Suppose I have the following long-running query

UPDATE [Table1]
SET [Col1] = 'some value'
WHERE [Col2] -- some clause which selects thousands of rows

and suppose the following query is executed while the above query is running

SELECT *
FROM [Table1]

Does the first query prevent the second query from running until the first query is done? If so, does the first query prevent the second query from running on all of the rows or just the rows involved in the WHERE clause?

EDIT:

Suppose the second query is

SELECT [Col1], [Col2]
FROM [Table1]
WHERE [Col2] -- some clause whose matching elements overlap those from
             -- the clause in the first query and which has additional matching elements

Answer 1

I recommend you read Understanding how SQL Server executes a query, it has an explanation of how reads and writes work and how locking works.

The 10000ft view goes as follows:

• read operators acquire shared locks on the data they read, before reading the data
• write operators acquire exclusive locks on the data they modify before modifying the data
• data locks are just strings, eg. a hash of the key being read scoped by database and object.
• the lock manager maintains a list of all locks granted and detects incompatibilities, according to the Lock Compatibility matrix
• incompatible requests are suspended until the incompatible grant blocking them is released
• operators use a lock hierarchy to declare intent to read or update data at higher level (page or table level, ignoring partition level options). This allow operators to lock entire tables w/o locking every individual row
• lock lifetime and range locks are used to enforce higher isolation levels

This is really just the tip of the ice berg. The subject is vast. In your example, nobody can answer your question about what is being actually locked because it will depend on many factors. Of course, no application should issue a SELECT * FROM Table1 because it's missing a WHERE clause and is using *. These are bad practices because, among other things, they will lead exactly to lock contention.

If you encounter read vs. write locks you need to look into row versioning and snapshot isolation. Read Understanding Row Versioning-Based Isolation Levels.

Answer 2

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
                FROM    sys.objects
                WHERE   name = 'Foo'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.Foo;
    CREATE TABLE dbo.Foo
    (
        Foo_PK          BIGINT IDENTITY( 1, 1 ) NOT NULL,
                            PRIMARY KEY ( Foo_PK ),
        Bar             BIT,
        x               BIT,
        y               BIT,
        z               BIT
    );

    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 );
END;    
GO

BEGIN TRANSACTION;

UPDATE  dbo.Foo
    SET y = 0
WHERE   x = 1;

-- COMMIT TRANSACTION;

In a separate session, run the following:

SELECT  *
FROM    dbo.Foo WITH ( NOLOCK );
GO

SELECT  *
FROM    dbo.Foo;

You can examine the locks currently being held by running sp_lock, preferably in a yet another separate session:

EXECUTE dbo.sp_lock;

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.

The 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 S lock.

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.