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
|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.
BEGIN TRANSACTION updateJerseyNr
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.
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.
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.
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.
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.
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.
- Recent Posts
Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.
View all posts by Sifiso W. Ndlovu
Latest posts by Sifiso Ndlovu (see all)
- Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow - February 14, 2020
- Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events - July 1, 2019
- Bulk-Model Migration in SQL Server Master Data Services - May 30, 2019
- SQL Server table hints – WITH (NOLOCK) best practices
- Entendiendo el Impacto de las sugerencias NOLOCK y WITH NOLOCK en SQL Server
- SQL Server NOLOCK and Top Optimization
- ETL optimization using SQL TRY Functions
- Understanding Skewed Data in SQL Server
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads. Read further to better understand the use of NOLOCK.What is the difference between Nolock and with Nolock in SQL Server? ›
The NOLOCK and WITH(NOLOCK) table hints functionalities are the same. However, it is recommended to use WITH(NOLOCK) is required due to the following reasons. Omitting the WITH KEYWORD is a deprecated feature: You should not omit WITH Keyword, as per the Microsoft docs.What is the disadvantage of with Nolock in SQL? ›
The CON of using RETRIEVAL NOLOCK is the possibility of false abends. These happen when the database is being updated concurrently by other tasks, and the retrieval program accesses records whose prefixes (set pointers) are in the midst of being updated.What are the dangers of Nolock? ›
- Read rows twice.
- Skip rows altogether.
- Show you changes that never actually got committed.
- Let your query fail with an error.
Table hints are used to override the default behavior of the query optimizer during the data manipulation language (DML) statement. You can specify a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options.What is the impact of Nolock in SQL Server? ›
What does the SQL Server NOLOCK hint do? The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.Why not to use Nolock in SQL Server? ›
For the NOLOCK table hint specifically, reading uncommitted data that could be rolled back after you have read it can lead to a Dirty read, which can occur when reading the data that is being modified or deleted during the uncommitted data read, so that the data you read could be different, or never even have existed.Is Nolock a dirty read? ›
While to the uninitiated, the NOLOCK phrasing may imply that a query using this hint will take no locks against any tables referred to in the query. That is not the case. What the NOLOCK hint does is enforce SQL Server's Read Uncommitted isolation level, which allows for what is known as dirty reads.Does with Nolock prevent deadlocks? ›
Pattern 1: Using NOLOCK to Stop Deadlocks
You see, NOLOCK stops locking during read operations. In effect, it throws the hinted table or index into READ UNCOMMITTED and allows dirty reads to occur. Locks are still necessary for data modification; only one process can update a row at a time.
The syntax is deprecated
You can also find the following quote in the topic Table Hint (Transact-SQL): "Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server.
The best "alternative" is simply to remove it. Any data movement inside a transaction is still visible within that transaction. Consider using snapshot isolation if wait-free reads are critical to you; but it comes with its own bag of worms so be careful to research it first.When to use Nolock in SQL Server? ›
If your query has multiple tables you can use the NOLOCK hint to say: I want READ UNCOMMITED against this table, but I'm not going to hint the other table. So you can specify it at a table level, what isolation level you want to use.What is the opposite of with Nolock? ›
Well, here is the answer – the behavior of NOLOCK and NOWAIT is absolutely opposite to each other but there is no guarantee to get the valid answer in either of them. NOWAIT will return error if the original table has (transaction) locked on it. NOLOCK will read the data irrespective of the (transaction) lock on it.How can I improve SQL query performance? ›
- Tip 1: Add missing indexes. ...
- Tip 2: Check for unused indexes. ...
- Tip 3: Avoid using multiple OR in the FILTER predicate. ...
- Tip 4: Use wildcards at the end of a phrase only. ...
- Tip 5: Avoid too many JOINs.
- Make sure the transactions are short. ...
- Our experts recommend changing the isolation level to SNAPSHOT ISOLATION or READ COMMITTED SNAPSHOT ISOLATION. ...
- Try to access objects by following a logical manner for multiple transactions.
Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query.Is with Nolock faster? ›
Much of the "my query got faster with NOLOCK" conclusions come (incorrectly) from avoiding being blocked. Under Read Committed, read queries can also block and deadlock with modification queries under some circumstances.How do you optimize a query? ›
- Define business requirements first. ...
- SELECT fields instead of using SELECT * ...
- Avoid SELECT DISTINCT. ...
- Create joins with INNER JOIN (not WHERE) ...
- Use WHERE instead of HAVING to define filters. ...
- Use wildcards at the end of a phrase only.
Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.What is the difference between Nolock and read committed? ›
NOLOCK Is equivalent to READUNCOMMITTED
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data.
Yes, you must use WITH(NOLOCK) on each table of the join. Your queries are not the same though. Try this: Begin a transaction and insert a row into table1 and table2. But, don't commit or rollback the transaction.What is a dirty read in SQL? ›
Dirty Reads A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits the update.How to check lock query in SQL Server? ›
- open transaction/session.
- exec a statement (that holds a lock on a resource)
- exec another statement on the same session.
- open another transaction/session and try to modify the resource locked at step 2.
You can use the sys. dm_tran_locks view, which returns information about the currently active lock manager resources.What are the four necessary conditions for preventing or avoiding deadlock *? ›
The four necessary conditions for a deadlock situation to occur are mutual exclusion, hold and wait, no preemption and circular set. We can prevent a deadlock by preventing any one of these conditions.What should not be done to avoid deadlock? ›
What should not be done to avoid deadlock? Explanation: To avoid deadlock situation in Java programming do not execute foreign code while holding a lock.What is the difference between preventing and avoiding deadlocks? ›
1. Deadlock prevention aims to prevent deadlocks from occurring by restricting the way resources are allocated. Deadlock avoidance aims to prevent deadlocks by dynamically analysing the resource allocation state of the system and deciding whether a resource request should be granted or not.What data types are deprecated in SQL Server? ›
The ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.Can we use Nolock for update statement? ›
NOLOCK is used for reading uncommitted data. As far as i know, this hint is not allowed in update statement. Your first update statement will give an error. But what is the difference as both statements doing same thing but second statement is getting executed successfully and first one is not....How to improve delete performance in SQL Server? ›
If you are deleting 95% of a table and keeping 5%, it can actually be quicker to move the rows you want to keep into a new table, drop the old table, and rename the new one. Or copy the keeper rows out, truncate the table, and then copy them back in.
A Microsoft SQL Server deadlock is a stalemate between two locked processes. When this occurs, all server activity stops, as each process waits for the other to give up its lock. The only solution is to cancel one of the processes.Which of the following locking hint is similar to Nolock? ›
7. Which of the following locking hint is similar to NOLOCK? Explanation: Do not issue shared locks and do not honor exclusive locks.What is deadlock in SQL Server? ›
A deadlock occurs when two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs. SQL Server handles deadlocks by terminating and rolling back transactions that were started after the first transaction.How to lock table in SQL Server? ›
The basic syntax used for LOCKING a table in SQL is as follows: LOCK [ TABLE ] [ ONLY ] table_name [ IN lock_mode MODE ] [ NOWAIT ]; The parameters used in the above-mentioned syntax are as follows: table_name: Name of the table on which you want to apply LOCK.How to apply locks in SQL Server? ›
- Exclusive (X)
- Shared (S)
- Intent exclusive (IX)
- Intent shared (IS)
- Shared with intent exclusive (SIX)
Nolock means you can read some locked rows (with shared locks). But you still have to wait on other locks. Tablockx means you block whole table with exclusive lock for other queries - other session cannot make locks, you cannot be blocked after you block whole table. Tablockx is mostly used for rapid inserts.What are 10 words that have no opposite? ›
A few more examples : inertia, ineptitude, immacculate, impeccable, nonchalant, nonplussed, unkempt, uncouth.What is it called when words are opposite? ›
Frequently described as "words that are their own opposites," contronyms (contranym is a variant spelling) are also known as Janus words, antagonyms, and autoantonyms. These are words that have developed contradictory meanings.How do I fix slow running queries in SQL Server? ›
- Examine the query plan of the query.
- Update Statistics.
- Identify and apply Missing Indexes. ...
- Redesign or rewrite the queries.
- Identify and resolve parameter-sensitive plans.
- Identify and resolve SARG-ability issues.
Steps to take to improve performance of queries:
- Create all primary and foreign keys and relationships among tables. - Avoid using Select*, rather mention the needed columns and narrow the resultset as needed. - Implement queries as stored procedures. - Have a WHERE Clause in all SELECT queries.
- Avoid auto-increment primary key. ...
- Avoid joining table records (left join, outer join, inner join, etc) ...
- Don't use SQL lock. ...
- Avoid aggregation functions. ...
- Try to use SQL function only with a single record query.
Deadlock can be prevented by eliminating any of the four necessary conditions, which are mutual exclusion, hold and wait, no preemption, and circular wait. Mutual exclusion, hold and wait and no preemption cannot be violated practically. Circular wait can be feasibly eliminated by assigning a priority to each resource.What is the root cause of deadlock in SQL Server? ›
A deadlock problem occurs when two (or more than two) operations already want to access resources locked by the other one. In this circumstance, database resources are affected negatively because both processes are constantly waiting for each other. This contention issue is terminated by the SQL Server intervention.How to check deadlock victim in SQL Server? ›
Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.What are the 5 basic SQL commands give examples for each? ›
- SELECT - extracts data from a database.
- UPDATE - updates data in a database.
- DELETE - deletes data from a database.
- INSERT INTO - inserts new data into a database.
- CREATE DATABASE - creates a new database.
- ALTER DATABASE - modifies a database.
- CREATE TABLE - creates a new table.
- Choose Appropriate Data Type. ...
- Avoid nchar and nvarchar. ...
- Avoid NULL in the fixed-length field. ...
- Avoid * in SELECT statement. ...
- Use EXISTS instead of IN. ...
- Avoid Having Clause. ...
- Create Clustered and Non-Clustered Indexes. ...
- Keep clustered index small.
In various SQL implementations, a hint is an addition to the SQL standard that instructs the database engine on how to execute the query. For example, a hint may tell the engine to use or not to use an index (even if the query optimizer would decide otherwise).What is the difference between with Nolock and read uncommitted in SQL? ›
The WITH (NOLOCK) option behaves like the READ UNCOMMITTED isolation level. But whereas the isolation level applies for the entire connection, WITH NOLOCK applies to a specific table. The WITH (NOLOCK) option behaves like the READ UNCOMMITTED isolation level, because the first one can't read dirty reads.Is with Nolock going away? ›
The NOLOCK hint has been deprecated in favor of READ COMMITTED SNAPSHOT (RCSI). Starting with SQL Server 2022, these hints will no longer be honored, and the transaction will operate under default isolation level (RCSI, if enabled, or READ COMMITTED if not).How to use hints in SQL Server? ›
Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query.
- Try to keep transactions short; this will avoid holding locks in a transaction for a long period of time.
- Access objects in a similar logical manner in multiple transactions.
- Create a covering index to reduce the possibility of a deadlock.
The timeout period refers to the length of time a transaction waits to acquire a lock before it gives up. A deadlock occurs when two or more transactions are waiting for each other in a cyclic fashion.What are the pros and cons of read uncommitted? ›
The advantage is that it can be faster in some situations. The disadvantage is the result can be wrong (data which hasn't been committed yet could be returned) and there is no guarantee that the result is repeatable.How to check locking and blocking in SQL Server? ›
In SQL Server Management Studio (SSMS) Object Explorer, right-click the top-level server object, expand Reports, expand Standard Reports, and then select Activity - All Blocking Transactions. This report shows current transactions at the head of a blocking chain.Which joins are the fastest? ›
In case there are a large number of rows in the tables and there is an index to use, INNER JOIN is generally faster than OUTER JOIN. Generally, an OUTER JOIN is slower than an INNER JOIN as it needs to return more number of records when compared to INNER JOIN.