Irrespective of the where clause;

When table is heap (no clustered indexes) then does a select query make sql server to lock all rows until it has finished reading all the rows?

Where as in case of table with clustered index, sql server will lock the rows one by one and release them immediately after that row is read?

Any reference links on this topic will be really helpful.

1 Answer 1


Heap tables support many lock granularities, including RID (row identifier), PAGE, partition, and table.

From the documentation: Locking in the Database Engine

Applications do not typically request locks directly. Locks are managed internally by a part of the SQL Server Database Engine called the lock manager. When an instance of the SQL Server Database Engine processes a Transact-SQL statement, the SQL Server Database Engine query processor determines which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager.

The storage engine determines the initial locking granularity at runtime, using information and hints from the query processor. You can influence this decision with hints like ROWLOCK and PAGLOCK. The engine might attempt to escalate locks to the partition or table level during execution.

This is not completely "irrespective of the where clause" because cardinality estimation is one factor passed from QP to SE that influences the locking granularity decision. Taking and releasing locks is not for free. If a large number of operations are anticipated, the engine may choose e.g. page granularity instead of row.

Another factor is the presence of a row goal, for example because the query has a TOP clause, or something equivalent. If the engine can expect to locate the required number of rows without reading too much data, it may choose RID locks.

The type and duration of locks also depends on the transaction isolation level as mentioned in the quote. It can also depend on the implementation of the isolation level. For example, read committed using versioning (RCSI) typically does not take shared locks when reading, but locking read committed does.

Under locking read committed (the default for non-cloud SQL Server), individual shared locks are usually released as soon as possible. For RID locks, this means each row is share locked while it is being processed. That lock is usually released before acquiring a lock on the next row. See Read Committed Isolation Level by Craig Freedman.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.