SET LOCK_TIMEOUT – How to Not Wait on Locked Query

http://blog.sqlauthority.com/2013/01/28/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.

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

http://blog.sqlauthority.com/2013/01/25/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.

Difference Between NOLOCK and NOWAIT Hints

http://blog.sqlauthority.com/2013/02/01/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.

SQLSolutions and SSIS

Friday, January 25, 2013

Useful date functions in SQL Server 2008

NOWAIT() and NOLOCK() and SET LOCK_TIMEOUT

SET LOCK_TIMEOUT – How to Not Wait on Locked Query

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

Difference Between NOLOCK and NOWAIT Hints