SQL Server Articles, Software Tools and Online Courses on SQLNetHub

Updating SQL Server Tables Without Causing Blocking

by
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.

 

About the NOLOCK Table Hint

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.

 

About the READPAST Table Hint

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?

The answer is 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:

 

UPDATE [TABLE_NAME] WITH (READPAST)
SET ...
WHERE ...

 

 

Strengthen you SQL Server development skills!
Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

Discussion

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.

Note: As with any case where you might use table hints, you need be very careful when using them. Always make sure that you take into consideration all the possibilities when working with table hints. Moreover, Microsoft advises in Microsoft Docs the following: “Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.” (learn more)

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub (https://www.sqlnethub.com)

© SQLNetHub



Views: 11,699