Understanding SQL Server LOCKS on SELECT queries

Question

I m wondering what is the benefit to use SELECT WITH  NOLOCK  on a table if the only other queries affecting that table are SELECT queries   How is that handled by SQL Server  Would a SELECT query block another SELECT query   I m using SQL Server 2012 and a Linq-to-SQL DataContext    EDIT    About performance      Would a 2nd SELECT have to wait for a 1st SELECT to finish if using a locked SELECT  Versus a SELECT WITH  NOLOCK

User · Answer

select with no lock - will select records which may / may not going to be inserted. you will read a dirty data.

for example - lets say a transaction insert 1000 rows and then fails.

when you select - you will get the 1000 rows.

User · Answer

At my work  we have a very big system that runs on many PCs at the same time  with very big tables with hundreds of thousands of rows  and sometimes many millions of rows    When you make a SELECT on a very big table  let s say you want to know every transaction a user has made in the past 10 years  and the primary key of the table is not built in an efficient way  the query might take several minutes to run   Then  our application might me running on many user s PCs at the same time  accessing the same database  So if someone tries to insert into the table that the other SELECT is reading  in pages that SQL is trying to read   then a LOCK can occur and the two transactions block each other   We had to add a  NO LOCK  to our SELECT statement  because it was a huge SELECT on a table that is used a lot by a lot of users at the same time and we had LOCKS all the time   I don t know if my example is clear enough  This is a real life example

User · Answer

A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock  and those are compatible with one another   So no - one SELECT cannot block another SELECT   What the WITH  NOLOCK  query hint is used for is to be able to read data that s in the process of being inserted  by another connection  and that hasn t been committed yet   Without that query hint  a SELECT might be blocked reading a table by an ongoing INSERT  or UPDATE  statement that places an exclusive lock on rows  or possibly a whole table   until that operation s transaction has been committed  or rolled back    Problem of the WITH  NOLOCK  hint is  you might be reading data rows that aren t going to be inserted at all  in the end  if the INSERT transaction is rolled back  - so your e g  report might show data that s never really been committed to the database   There s another query hint that might be useful - WITH  READPAST   This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively  The SELECT will not block  and it will not read any  dirty  un-committed data - but it might skip some rows  e g  not show all your rows in the table

User · Answer

I have to add an important comment  Everyone is mentioning that NOLOCKreads only dirty data  This is not precise  It is also possible that you ll get same row twice or whole row is skipped during your read  Reason is that you could ask for some data in same time when SQL Server is re-balancing b-tree    Check another threads  https   stackoverflow com a 5469238 2108874  http   www sqlmag com article sql-server quaere-verum-clustered-index-scans-part-iii aspx      With the NOLOCK hint  or setting the isolation level of the session to READ UNCOMMITTED  you tell SQL Server that you don t expect consistency  so there are no guarantees  Bear in mind though that  inconsistent data  does not only mean that you might see uncommitted changes that were later rolled back  or data changes in an intermediate state of the transaction  It also means that in a simple query that scans all table index data SQL Server may lose the scan position  or you might end up getting the same row twice

User · Answer

On performance you keep focusing on select  Shared does not block reads  Shared lock  blocks update  If you have hundreds of shared locks it is going to take an update a while to get an exclusive lock as it must wait for shared locks to clear       By default a select  read  takes a shared lock  Shared  S  locks allow concurrent transactions to read  SELECT  a resource  A shared lock as no effect on other selects  1 or a 1000       The difference is how the nolock versus shared lock effects update or insert operation    No other transactions can modify the data while shared  S  locks exist on the resource   A shared lock blocks an update  But nolock does not block an update     This can have huge impacts on performance of updates   It also impact inserts   Dirty read  nolock  just sounds dirty   You are never going to get partial data    If an update is changing John to Sally you are never going to get Jolly   I use shared locks a lot for concurrency   Data is stale as soon as it is read   A read of John that changes to Sally the next millisecond is stale data   A read of Sally that gets rolled back John the next millisecond is stale data    That is on the millisecond level   I have a dataloader that take 20 hours to run if users are taking shared locks and 4 hours to run is users are taking no lock   Shared locks in this case cause data to be 16 hours stale   Don t use nolocks wrong   But they do have a place   If you are going to cut a check when a byte is set to 1 and then set it to 2 when the check is cut - not a time for a nolock

User · Answer

The SELECT WITH  NOLOCK  allows reads  of uncommitted data  which is equivalent to having the READ UNCOMMITTED isolation level set on your database  The NOLOCK keyword allows finer grained control than setting the isolation level on the entire database   Wikipedia has a useful article  Wikipedia  Isolation  database systems   It is also discussed at length in other stackoverflow articles

[sql-server] Understanding SQL Server LOCKS on SELECT queries

Examples related to sql-server

Examples related to tsql

Examples related to linq-to-sql