SQL SERVER – Difference Between NOLOCK and NOWAIT Hints

It is interesting to see how a blog evolves with the time and user interacts with each blog post. Earlier I wrote two of the blog posts on NOWAIT and SET LOCK_TIMEOUT. I have received very good response on this subject. Please read following two blog posts before continuing this blog post.

Here is the follow up question which I received from reader after reading above blog posts.

“I now understand that NOWAIT hints do not return result and errors out if there is a lock on the table. Does this mean I should use the NOLOCK hint to retrieve the results?”

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.

In either case there can be incorrect data or unexpected result. There is no guarantee to get the appropriate data.

Here is the example of the how both provide different result on similar situation.

In this sample scenario we will create a table with a single row. We will open a transaction and delete a single row. We will now close the transaction and read the query with once with NOLOCK hint and once with NOWAIT hint. After the test we will rollback original transaction, which was deleting the record. As we will rollback transaction there will be no change in resultset however, we will get very different results in the case of NOLOCK and will get error in case of NOWAIT.

First Let us create a table:

USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO

Now let us open three different connections.

Run following command in the First Connection:

BEGIN TRAN
DELETE FROM
First
WHERE ID = 1

Run following command in the Second Connection:

SELECT ID, Col1
FROM First WITH (NOWAIT)
WHERE ID = 1

Run following command in the Third Connection:

SELECT ID, Col1
FROM First WITH (NOLOCK)
WHERE ID = 1

You can notice that result is as discussed earlier. There is no guarantee to get 100% correct result in either case. In the case of NOLOCK and will get error in case of NOWAIT. If you want to get the committed appropriate result, you should wait till the transaction lock on the original table is released and read the data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Basic Explanation of SET LOCK_TIMEOUT – How to Not Wait on Locked Query

In earlier blog post SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query, we learned how we can use NOWAIT query hint to not wait on any locked query and return error. The Query Hint works on query and table level. There is similar setting which can work at a connection level as well,  it is SET LOCK_TIMEOUT. When any connection starts the value of the SET LOCK_TIMEOUT is -1, which means that the query has to wait for infinite time for the lock to be released on another query. If you want to simulate the scenario of SET LOCK_TIMEOUT to match NOWAIT query hint, it should be set to value 0. Let us see a similar example where we demonstrate how SET LOCK_TIMEOUT works.

First Let us create a table:

USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO

Now let us open two different connections.

Run following command in the First Connection:

BEGIN TRAN
DELETE FROM
First
WHERE ID = 1

Run following command in the Second Connection:

SET LOCK_TIMEOUT 2000
BEGIN TRAN
SELECT
ID, Col1
FROM First
WHERE ID = 1

In this case, I have set the value of the SET LOCK_TIMEOUT to 2000 milliseconds. This query will wait for 2 seconds to another query to release the lock. If another query does not release the lock in 2 seconds, it will display the following error:

Msg 1222, Level 16, State 45, Line 3
Lock request time out period exceeded.

This is the same error which we have discussed in an earlier blog post here.

Here are a couple of very interesting differences between SET LOCK_TIMEOUT and NOWAIT query hint.

  • User can configure the time to wait before throwing error in SET LOCK_TIMEOUT, whereas in case of NOWAIT it is always zero (o). 
  • The scope of the SET LOCK_TIMEOUT is wholly connected however the scope of NOWAIT is the table where it is applied.

I guess, now it is very clear when to use what. In my daily routine I usually use SET LOCK_TIMEOUT as it gives me more flexibility and achieves the same purpose as NOWAIT.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query

Everybody knows about NOLOCK  but not everyone knows about NOWAIT. They are different and they have an entire different purpose. In this blog post we will not talk about NOLOCK but we will see how NOWAIT will work. The idea of writing about blog post is based on the question I received in recent Bangalore User Group presentation. Here is the quick conversation with one of the attendee I had after my presentation. I did not ask the name of the attendee so I will have to address him as an attendee here. If you are reading this blog post, please let me know your name and I will replace it with your name.

Attendee: Pinal, in SQL Server when we have one query under transaction, when we have to read the data from the table we use NOLOCK, which gives us dirty data.

Pinal: Yes, that is correct.

Attendee: In our application we do not want to wait for the query to execute, as the way the application we have built, we have to either return the results immediately or inform user that they should retry after a while as an application (database) is busy right now. Is there any other mechanism besides NOLOCK which we can use which inform us that the table from which we are trying to retrieve the data is locked under other transaction. This way, we can pass the same message to the user and they can re-attempt to fetch the data after a specific amount of the time.

Pinal: So, basically you do not want to use NOLOCK as it gives you dirty data and you do not want to wait also till the tables under under transactions is available. You rather prefer error instead or message that your query is not going to execute immediately.

Attendee: Yes, absolutely correct.

Pinal: Use NoWait or SET LOCK_TIMEOUT.

Attendee: I have never used either of them, do you have an example on your blog?

Pinal: No, I have not blogged about it but I will for sure blog how they work.

I hope the above conversation also explains the real world scenario and business need of such feature.

NOWAIT is table hint and it instructs the database engine to return a message as soon as a lock is encountered on a table. Let us see an example.

First Create the following table in the database:

USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO

Now open two different connections:

Connection 1: Run following query

BEGIN TRAN
DELETE FROM
First
WHERE ID = 1

Connection 2: Run following query

BEGIN TRAN
SELECT
ID, Col1
FROM First WITH(NOWAIT)
WHERE ID = 1

As soon as you run above query in connection 2 it will give you following error:

Msg 1222, Level 16, State 45, Line 2
Lock request time out period exceeded.

The reason is because we have used NOWAIT as a query hint. When this hint is encountered by SQL Server it will give an error 1222 back instead of waiting for transaction on another window to complete. As I said NOWAIT is very different than NOLOCK but very similar to SET SET LOCK_TIMEOUT. In future blog posts, we will see how SET SET LOCK_TIMEOUT works. The matter of fact SET SET LOCK_TIMEOUT is more flexible than NOWAIT and I often use it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server – Understanding Table Hints with Examples

Today we have a very interesting subject to look at. I tried to look for help online but have not found any other documentation besides what we have from the Book Online.

Let us try to understand what are the different kinds of hints available in SQL Server and how they are helpful.

What is a Hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.

Before we continue to explore this subject, we need to consider one very important fact and say some words of caution. SQL Server Query optimizer is a very smart tool and it makes a best selection of execution plan. Suggesting hints to the Query Optimizer should be attempted when absolutely necessary and by experienced developers who know exactly what they are doing (or in development as a way to experiment and learn).

There are three different kinds of hints. Let us understand the basics of each of them separately.

Please continue reading article here.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Query Optimizer Hint ROBUST PLAN – Question to You

While cleaning up my bookmarks this week, I stumbled upon a very small interesting thing. I can proudly call myself a pro at finding stuffs, but after continuously hunting online I could not gather comprehensive information about this topic. I was actually looking for a practical example for Query Optimizer Hint “ROBUST PLAN”. Before I seek help from you, let us first try to understand what query optimizer hints is and then we will move on to the concept of “ROBUST PLAN”.

To put it simply, Query hints is a T-SQL clause which on running directs T-SQL query to run in a particular manner using suggested index, joins or other logic. Query hints can force the kind of execution plan SQL Server needs. In general, SQL Server query optimizer selects the best execution plan itself to run any query; it is a good idea to just keep it at its default setting.

“ROBUST PLAN” is a kind of query hint which works quite differently than other hints. It does not improve join or force any indexes to use; it just makes sure that a query does not crash due to over the limit size of row. Let me elaborate upon it. In a table, there is an outsized row that crashes any operation going over it. T-SQL has specific query hint where query engine ignores such an outsized row which will potentially crash it and stop from working. Using ROBUST PLAN, query optimizer can be forced to ignore such errors that are generated because of outsized rows. This usually does not happen with SQL Server 2005 because of its tremendous capacity of row size (2 GB). However, here we are not talking about row size beyond the limit; we are talking about SQL operation that considers the given row to be too large for this operation, even if the row is in MB or less.

Now, after having thoroughly gone through the above two explanations, I request you all to find out which operation can generate this error and compel us to use ROBUST PLAN hint. I am disappointed after my extensive online search. I could not find a single example or information by anyone where they have suggested that they needed this hint. According to me, this is a useful feature which can be used wisely, as this hint reduces the performance as well in some cases. For queries where the entire execution is not working, this is at least a better option where you can expect to obtain some answer.

I am looking for a query which runs fine with option “ROBUST PLAN” but without it the query fails to produce any result and generates error.

If you know the answer, then please share it with me. I will post your answer here with due credit.

Reference : Pinal Dave (http://blog.SQLAuthority.com)


While cleaning up my bookmarks this week, I stumbled upon a very small interesting thing. I can proudly call myself a pro at finding stuffs, but after continuously hunting online I could not gather comprehensive information about this topic. I was actually looking for a practical example for Query Optimizer Hint “ROBUST PLAN”. Before I seek help from you, let us first try to understand what query optimizer hints is and then we will move on to the concept of