Every once in a while, SQL Server database administrators find themselves in disagreements with their application developer counterparts – particularly when it comes to some of the latter’s Transact SQL (T-SQL) developmental practices. One of my first observations when I joined my current employer is that almost all T-SQL scripts written by application developers uses the NOLOCK table hint. However, from the interactions that I have had with these esteemed developers it doesn’t seem like they understand how the NOLOCK table hint works. Furthermore, although they seem to be aware of a distinction between NOLOCK and the WITH NOLOCK table hint, they again do not seem to comprehend how the two differ from one another. In this article, I explore the internal workings of the NOLOCK table hint and examine the implications of omitting the WITH keyword.
Understanding NOLOCK Hint
The default behaviour in SQL Server is for every query to acquire its own shared lock prior to reading data from a given table. This behaviour ensures that you are only reading committed data. However, the NOLOCK table hint allows you to instruct the query optimiser to read a given table without obtaining an exclusive or shared lock. The benefits of querying data using the NOLOCK table hint is that it requires less memory and prevents deadlocks from occurring with any other queries that may be reading similar data. The only drawback is that using the NOLOCK table hint may accidentally result into reading uncommitted “dirty” data. In the following sections, I will provide practical examples for using NOLOCK and WITH NOLOCK table hints.
For the purposes of this discussion, I will use a SQL Server 2016_FIFABallonDOr table that contains the top 3 footballers nominated for FIFA’s Ballon d’Or award in 2016. A preview of this SQL Server table is shown in
Table 1.
| Nominee | Club | Jersey Number | Votes | Date of Birth | Place of Birth | Nationality | Height |
| Antoine Griezmann | Atletico Madrid | 7 | 198 | 21 March 1991 | Mâcon | France | 1.75 m |
| Lionel Messi | FC Barcelona | 10 | 316 | 24 June 1987 | Rosario | Argentina | 1.70 m |
| Cristiano Ronaldo | Real Madrid | 7 | 745 | 05 February 1985 | Funchal | Portugal | 1.85 m |
Now let’s assume that having seen his opponents’ jersey number, Lionel Messi later decides to change his jersey number from 10 to 7. In order to reflect this change, we would need to run an UPDATE statement against our SQL Server table. The sample UPDATE script that we would need to run is shown in Script 1.
|
1 2 3 4 5 |
BEGIN TRANSACTION updateJerseyNr UPDATE A SET [Jersey Number] = 7 FROM [SQLShack].[dbo].[2016_FIFABallonDOr] A WHERE [Nominee] = 'Lionel Messi' |
You will notice that Script 1 doesn’t have a corresponding COMMIT TRANSACTION updateJerseyNr, which means that the changes we’ve just made haven’t been committed to disk yet. Thus, if another database user were to attempt to read the value of Lionel Messi’s jersey number as shown in Figure 1, they would receive an endless Executing query … message.
Well, what is happening here is that as part of retrieving Lionel Messi’s jersey number, the script in Figure 1 needs to firstly acquire a shared lock against the 2016_FIFABallonDOr table, but it ends up having to wait for Script 1 to complete its changes first.
This is where the NOLOCK hint can be useful as it can allow for the retrieval of Lionel Messi’s jersey number without having to wait for any shared locks as shown in Script 2 and Figure 3, respectively.
|
1 2 3 4 |
SELECT [Jersey Number] FROM [SQLShack].[dbo].[2016_FIFABallonDOr] (NOLOCK) WHERE [Nominee] = 'Lionel Messi' |
However, the dangers of using the NOLOCK hint is that if the transaction in Script 1 were to be rolled back, then the value of Lionel Messi’s jersey number would return back to 10, meaning if you were to rerun Script 2 you will get a different value then what you got earlier.
Benefits of Using the WITH Keyword
In terms of querying Lionel Messi’s jersey number by adding the WITH keyword in front of the NOLOCK hint, you would still retrieve similar results as shown in Script 3 and Figure 4, respectively. In fact, even the execution plan and TIME/IO statistics of Script 3 are still similar to that of Script 1.
|
1 2 3 |
SELECT [Jersey Number] FROM [SQLShack].[dbo].[2016_FIFABallonDOr] WITH (NOLOCK) WHERE [Nominee] = 'Lionel Messi' |
If the output of scripts using NOLOCK and WITH NOLOCK table hints is so identical, why then should we worry about using the one over the other? Well, it turns out that there are actually several differences between the two:
1. Support for hints without the WITH keyword will soon be deprecated
As per Microsoft documentation shown in Figure 5, by continuing to exclude the WITH keyword in a table hint means that you are basically increasing your technical debt as you will have to go back and refactor your scripts once this functionality is removed in future versions of SQL Server.
2. Specify Multiple Table Hints using the WITH keyword
Other than the fact that the makers of SQL Server have basically instructed us to the WITH keyword when specifying table hints, another benefit of using the WITH keyword is that you can include multiple table hints against the same table as shown in Script 4.
|
1 2 3 4 5 |
SELECT [Jersey Number] FROM [SQLShack].[dbo].[2016_FIFABallonDOr] WITH (TABLOCK, HOLDLOCK) WHERE [Nominee] = 'Cristiano Ronaldo' |
If you specify multiple hints after having omitted the WITH keyword will simply result into the error shown in Script 5 and Figure 6, respectively.
|
1 2 3 4 |
SELECT [Jersey Number] FROM [SQLShack].[dbo].[2016_FIFABallonDOr] (TABLOCK, HOLDLOCK) WHERE [Nominee] = 'Cristiano Ronaldo' |
3. Without the WITH Keyword you simply have a table Alias
At this point you should already be leaning towards always specifying the WITH keyword when using table hints but just to convince you further, omitting the WITH keyword can have undesirable consequences. If you forget to include NOLOCK inside round brackets then the NOLOCK hint can be mistaken for a table alias by the query optimiser, which means that the query would then have to wait for shared lock before it can start reading a given table. For instance, I forgot to include NOLOCK inside opening and closing round brackets and this led to the query executing endlessly as it waited for a shared lock.
Conclusion
There are benefits and drawbacks to specifying NOLOCK table hint as a result they should not just be included in every T-SQL script without a clear understanding of what they do. Nevertheless, should a decision be made to use NOLOCK table hint, it is recommended that you include the WITH keyword.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu