No, not NOLOCK. You get the wrong query results, even when your query results aren’t supposed to be changing.
I’m talking about when you need to do writes, but you want your query to get along with others in high concurrency environments, without creating a blocking firestorm.
SET LOCK_TIMEOUT 1000 – if you run this before your query starts, SQL Server will wait patiently for X milliseconds before giving up and aborting your own query. This is useful when a lot of short queries are running all the time, and I don’t want to give up instantly – but I do want to give up if there’s a long-running query blocking me. If you pass in a value of 0 milliseconds, SQL Server gives up instantly. For example, in the below query, I’m trying to delete all of Jon Skeet’s posts, but I want to abandon my query if anyone has locks that stop me on either the Users or Posts tables, and those locks stop me for over 1 second:
1 2 3 4 5 6 |
SET LOCK_TIMEOUT 1000; DELETE p FROM dbo.Users u INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId WHERE u.DisplayName = N'Jon Skeet'; |
If you use this technique, or the other techniques described in this post, your query will fail with an error if it was blocked by someone. Most of us (myself included) don’t do a great job of checking for errors and retrying our query gracefully, so we could probably all use an hour to dig into Erland Sommarskog’s series on error handling to learn how to retry automatically.
WITH (NOWAIT) – this is kinda like setting lock_timeout 0, but it’s a table-level hint like NOLOCK. This means it only affects a single table in your query. For example, in the below query, I’m trying to delete all of Jon Skeet’s posts, but I want to abandon my query if someone has a lock on the Users table – but I’m willing to wait forever for locks on the Posts table:
1 2 3 4 |
DELETE p FROM dbo.Users u WITH (NOWAIT) INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId WHERE u.DisplayName = N'Jon Skeet'; |
SET DEADLOCK_PRIORITY LOW – this isn’t about blocking, but specifically deadlocks. When I set this at the beginning of my batch, I’m saying, “I’m willing to wait for blocking, but if a deadlock situation comes up, go ahead and kill my query, because I don’t mind trying it again.” You call it like we did LOCK_TIMEOUT:
1 2 3 4 5 6 |
SET DEADLOCK_PRIORITY LOW; DELETE p FROM dbo.Users u INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId WHERE u.DisplayName = N'Jon Skeet'; |
WAIT_AT_LOW_PRIORITY – this one is just for index maintenance, and sadly, it doesn’t work for creates or drops. “Online” index rebuilds aren’t completely online: they need a brief schema modification lock to swap in the new copy of the index. This hint lets your completed index lurk patiently in the background while other queries finish, and then swap in later:
1 2 3 4 5 |
ALTER INDEX PK_Id ON dbo.Users REBUILD WITH (ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF )) ); |
If that last one is helpful, then your next step is to do similar blocking avoidance with DML operations by using Kendra Little’s post on which operations support online & resumable.