I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks.

Select * from Student;

Please consider the case where the statement would not be running inside a begin tran block.

  • 1
    This is a much more complicated question than you might think. The answer depends on many things (what is the session transaction isolation level? is read committed snapshot isolation turned on? does the scanned index have options set to prevent either row or page locks?) and can even change during the statement running (how many rows are in the table? is the table partitioned?). Here is a good point to start reading.
    – Jon Seigel
    Apr 26 '13 at 13:26

Yes it does take a shared lock on the rows that it reads by default (it also takes an Intent Shared lock on all the pages of the clustered index that it will read), this is done to prevent dirty reads. However there are ways to bypass this (SQL Server has the nolock hint). If the statement is not in a BEGIN TRAN the lock is released after the SELECT statement has run.

More info can be found here:

http://msdn.microsoft.com/en-us/library/ms184286(v=sql.105).aspx http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

  • In addition you can also set the transaction isolation level to read uncommitted.
    – Zane
    Apr 26 '13 at 15:53
  • 1
    So if the SELECT reads ten rows, are the ten shared locks held until all ten rows have been read or are they acquired and released per row? Oct 7 '17 at 18:26

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server can avoid taking shared row-level locks when there is no danger of reading uncommitted data without them (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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