Understanding SQL Server LOCKS on SELECT queries

by

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.

More Related Contents:

  1. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  2. Update a table using JOIN in SQL Server?
  3. Comma separated results in SQL
  4. T-SQL stored procedure that accepts multiple Id values
  5. Understanding PIVOT function in T-SQL
  6. Calculate running total / running balance
  7. How do I escape a percentage sign in T-SQL?
  8. Using RegEx in SQL Server
  9. List of all index & index columns in SQL Server DB
  10. Why use a READ UNCOMMITTED isolation level?
  11. How do you count the number of occurrences of a certain substring in a SQL varchar?
  12. How much size “Null” value takes in SQL Server
  13. Find index of last occurrence of a sub-string using T-SQL
  14. Hidden Features of SQL Server
  15. SQL Server 2008 Empty String vs. Space
  16. How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
  17. How do I exclude Weekend days in a SQL Server query?
  18. EXEC sp_executesql with multiple parameters
  19. Preserving ORDER BY in SELECT INTO
  20. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  21. Can you call a webservice from TSQL code?
  22. How to add custom attributes to SQL connection string?
  23. Multiple Column Pivot in T-SQL
  24. Joining multiple tables returns NULL value
  25. Which join syntax is better?
  26. Compute MD5 hash of a UTF8 string
  27. LPAD in SQL Server 2008
  28. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  29. How do I remove non-breaking spaces from a column in SQL server?
  30. What are row, page and table locks? And when they are acquired?

Leave a Comment