In SQL Server, how do read locks work?

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

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.

StackExchange Database Administrators Q#57144, answer score: 17