SQL Server - NOLOCK vs READPAST: Do you know the difference between the two?

Views: 2.471
Reading Time: 4 minutes

Hey guys!
In this article I would like to demonstrate in practice the use of 2 query hints commonly used by developers to avoid data reading locks, which are NOLOCK and READPAST, and effectively demonstrate the effect of these hints on a query.

The idea of ​​writing this article came through a question sent in the group “SQL Server - DBA”, from Telegram, and also an old desire to write about it whenever I see environments where almost every query has NOLOCK.

After reading this post, you will be able to understand exactly how these 2 hints work and will use them wisely and only when convenient. No more putting NOLOCK / READPAST on all your queries huh!

If your environment has a lot of competition and locks, blocks and deadlocks are frequent and a problem for you, I suggest thinking of a more complete approach than using these hints, which would be to use the Read Committed Snapshot (RCSI) isolation mode, which allows you to use Read Commited mode without locking readings when open transactions occur. Since not everything is flowers, there are some side effects when using this mode, such as possible performance degradation. If you want to know more about it, I suggest reading the article Read Committed Snapshot Isolation: Writers Block Writers (RCSI), by the great master Brent Ozar.

To demonstrate how NOLOCK and READPAST work, let's create a table with some records:

Current table:

[Video] - NOLOCK vs READPAST

NOLOCK or READ UNCOMMITED

Isolation mode is well known by DEVs, which allows you to return the current data from the table without waiting for the end of transactions in progress, making the data "dirty" read, ie if there are transactions changing the data of the tables being queried, these records will be returned as they are, even if the transactions are still open (no commit or rollback) and this data is not final yet.

This type of reading can be very useful in cases of high concurrency and locks and deadlocks in the environment, but keep in mind the side effects of using hint NOLOCK (equivalent to the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED or WITH (READUNCOMMITTED) command) .

NOLOCK Practical Example

In a SQL Server Management Studio (SSMS) session, run the following command:

Open another session in SSMS and run the command below:

Note that these 2 sessions have opened a transaction, made changes to the data, but have not yet been committed and not rolled back, ie this modified data can still be considered as “temporary” data since it is not committed data and confirmed. If there is a problem with the flow or simply the user wants to undo these changes, he can roll back these transactions and this entered / changed data never existed "officially".

Even so, if still in this scenario, you open a new session and try to query the data, you will see that your session will be waiting indefinitely until transactions are terminated with COMMIT or ROLLBACK. This is because the default read mode of SQL Server is READ COMMITED, ie it returns only data that has already been committed to the database.

Using hint NOLOCK, you can return the data without waiting for these transactions to finish, but data that has not yet been committed will be returned as if it were already final data:

If these sessions roll the data, you ended up querying data that never really existed. This in a report could end up producing incorrect results.

READPAST

Another way to read data from tables that are being altered by open transactions without waiting for these transactions to finish is by using hint READPAST. Unlike NOLOCK, READPAST does not allow dirty reads (save exceptions), but understand how it works: READPAST will only return data that is not affected by open transactions.

NOLOCK Practical Example

In a SQL Server Management Studio (SSMS) session, run the following command:

Open another session in SSMS and run the command below:

And now, let's try to read the data using hint READPAST and see how the uncommitted records will be returned:

Using hint READPAST, you can return data without waiting for these transactions to finish, but data that has not yet been committed will not be returned by SELECT, ie records can be ignored in this read mode. When you are using aggregate functions such as SUM, MAX, MIN, in this read mode, the final values ​​may be quite different from the actual values, as records may be ignored.

NOLOCK or READPAST?

To finalize and summarize this article, both NOLOCK and READPAST have as their main justification for use, the ability to read data in tables without waiting for active transactions to finish, minimizing the occurrence of locks and blocks, even if these records are being changed by these transactions.

When using NOLOCK (or READ UNCOMMITED), if the table has 10 rows and 3 are being changed by UPDATE / DELETE, the SELECT command will return all 10 rows, already with "updated" data. New inserted rows that have not yet been committed will also be returned by SELECT.

When using READPAST, if the table has 10 rows and 3 are being changed by UPDATE / DELETE, the SELECT command will return only the 7 rows that are not changing. New inserted rows that have not yet been committed will NOT be returned by SELECT.

References:
https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/transaction-isolation-levels?view=sql-server-2017
https://www.tiagoneves.net/blog/isolation-level-no-sql-server/
http://www.diegonogare.net/2013/01/transaction-isolation-level-voc-est-usando-certo/
https://imasters.com.br/data/entendendo-transaction-isolation-level-no-sql-server