Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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

2 Answers 2

up vote -3 down vote accepted

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

share|improve this answer
    
In addition you can also set the transaction isolation level to read uncommitted. –  Zane Apr 26 '13 at 15:53

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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