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:
UPDATE [TABLE_NAME] WITH (READPAST)
SET ...
WHERE ...
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: