SQL Server: Best usage for NOWAIT hint

Question

just read about NOWAIT hint and found if table is lock then NOWAIT hint return error message. so how it could be beneficial?

please discuss best scenario where people use NOWAIT hint to handle the situation. i am curious to know what is the benefit of NOWAIT hint ?

Thanks

Answer 1

Yeah, I also learnt about that hint recently. I knew about SET LOCK_TIMEOUT, but this is a reduction of that hint to a single table. And with the fixed timeout value of 0.

I would say all uses of this hint are on the advanced side. A possible scenario is this: say that you find that there is a deadlock that appears often, and one of the processes needs to be fast, but the other is more of a background process and can be held back. You can add SET DEADLOCK_PRIORITY LOW to the background processes, but there are two issues with this:

1. It may take five seconds until SQL Server unrolls the deadlock, and that can be a long time for the important process.
2. You don't want the background process to fail entirely.

Instead you add the NOWAIT hint (or use SET LOCK_TIMEOUT) in the slow process. You have TRY-CATCH around the operation and on error 1222 (lock timeout), you use the WAITFOR statement to wait for some time, maybe 100 ms, maybe a second and retry.

This is certainly not something you do every day.

Answer 2

Hi @Sudip Bhatt ,

NOWAIT could be a better solution in the situation that application does not want to read dirty data and also does not want to wait for other transaction to be finished.

For example, you are an end user of ticketing application and you have been allotted a ticket which has been shared with multiple customers. You want to access your ticket but being modified by someone else. The event organizers do not want you to read the data when it is being modified and also do not want to hold you up.

This scenario is perfect example of using NOWAIT which will not allow you to read the data and will also throw an immediate error which can be tracked and handled by front end application in form of error handling and can display an user friendly message such as ” Your booking is being modified by some other user, please try after some time”.

Reference:SQL Hints: NOLOCK and NOWAIT Explained

NOWAIT is very different from NOLOCK but very similar to SET SET LOCK_TIMEOUT.

But the matter of fact SET SET LOCK_TIMEOUT is more flexible than NOWAIT and you could consider to use it instead of NOWAIT.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table