Saturday, August 27, 2011

Updating SQL Server Tables Without Causing Blocking

Even though the SQL Server Database Engine automatically sets the best possible locking hints on the underlying database objects of the various T-SQL operations that executes, there are cases
where we need to manually control locking as a part of the business logic in our T-SQL script.

A popular locking hint is the NOLOCK as it is being used many times in environments with high concurrency. By using the NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. Of course, this means that the query may see inconsistent data (dirty reads), that is data not yet committed, etc. The NOLOCK hint can only be used in SELECT statements.

Now imagine the following scenario: You need to design a special UPDATE statement that will be updating an unknown number of records in a table which is being concurrently accessed by several other T-SQL statements (mostly UPDATE statements). The above statement will be updating the table with non-critical information meaning that if it skips some records the first time, it can update them the second time and so on.

If even one of the other UPDATE statements has locked a row that needs to be modified by your UPDATE statement, this will cause the latter to wait (blocking). However, in the case where waiting is not a very "desired" option what should you do? That leads us to the following question: What table hint can be used?

Answer: The READPAST locking hint :)

The READPAST locking hint when used, instructs the SQL Server Database Engine to skip row-level locks.
This means that the UPDATE statement using READPAST will only update the table rows that are not locked by another operation. In the opposite case, the Database Engine would block the UPDATE statement's execution until the rest of the target rows' locks are released. A typical UPDATE statement with the READPAST locking hint would look like this:

SET ...

Even if the above locking hint can become quite handy, as well as the rest of the locking hints, you always need to have in mind that you should use them very carefully as you might cause locking issues in the database. SQL Server Query Optimizer typically selects the best execution plan for a query, so it is not recommended for inexperienced developers and administrators to make use of the locking hints.

As a last note, the READPAST hint can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels.

My Latest Projects:

Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (