David Fowler explains what an intent lock is and why it’s useful:
Let’s just imagine a World without intent locks for a moment. In that World, a user has just decided to select a row from our database. SQL at that point is going to put down a shared lock against the row.
Now what’s going to happen when another user decides to modify a bunch of rows? Now because of the number or rows involved in this modification, SQL is going to want to take out an exclusive page lock. Where’s the issue here?
Read on to learn what the issue is.
Jonathan Kehayias walks us through an interesting scenario:
A recent consulting engagement was focused on blocking issues inside of SQL Server that were causing delays in processing user requests from the application. As we started to dig into the issues being experienced, it became clear that from a SQL Server standpoint, the problem revolved around sessions in a Sleeping status that were holding locks inside of the Engine. This is not a typical behavior for SQL Server, so my first thought was that there was some sort of application design flaw that was leaving a transaction active on a session that had been reset for connection pooling in the application, but this was quickly proven not to be the case since the locks were later being released automatically, there was just a delay in this occurring. So, we had to dig in further.
Click through to learn what the root cause was.
Dave Bland clarifies how frequently deadlock checks occur:
Because deadlocks happen when two task permanently block each other, without a deadlock, both process will simply block forever. Of course this could never be good in a production system. It is important that these situations be identified and dealt with in some manner. This is where SQL Server database engine steps in, it is frequently searching the lock manager looking for deadlocks.
Click through for the answer.
Max Vernon has a couple scripts to analyze deadlocks in SQL Server:
Deadlock detection and analysis in SQL Server is important for the overall health of affected applications. This post shows how to setup an Extended Events session to capture deadlock details. It also provides a stored procedure to capture details from the Extended Events session. The stored procedure enables simplified permanent storage of those deadlock detection events. Next, we’ll set up a SQL Server Agent Job to run the stored procedure on an ongoing basis. Finally, we’ll see several examples of how to query the captured events. These queries support making the necessary changes to both the application and database design.
Click through for a description of what a deadlock is as well as scripts to help find and fix them.
Dave Bland explains how you can use DEADLOCK_PRIORITY to control which process gets rolled back:
Before getting into how to set the DEADLOCK_PRIORITY, let’s quickly go over what the options are. There are two ways to set the priority of a process. The first option is to use one of the keywords, LOW, NORMAL or HIGH. The second approach is to use a numeric value between -10 and 10. The lowest value will be chosen as the victim. For example, LOW will be the victim of the other process is HIGH and -1 will be the victim if the other process is greater than -1.
As I recall, index operations (like rebuilds) are automatically set to a low priority.
Erik Darling takes us through an experiment:
Let’s also say that bad query is taking part in a modification.
UPDATE u2
SET u2.Reputation *= 2
FROM Users AS u
JOIN dbo.Users AS u2
ON CHARINDEX(u.DisplayName, u2.DisplayName) > 0
WHERE u2.Reputation >= 100000;
AND u.Id <> u2.Id;This query will run for so long that we’ll get sick of waiting for it. It’s really holding up writing this blog post.
Erik rewrites this query a couple of times. Click through to learn what he does and why he does it.
Erik Darling tries to figure out why his locks can’t get ahead in the rat race:
The first thing I found is that there were 16 attempts at promotion, and four successful promotions.
Why did this seem weird? I dunno.
Why would there be only 4 successful attempts with no competing locks from other queries?
Why wouldn’t all 16 get promotions?
Find out the answer to this and much, much more if you click the link.
Louis Davidson shares some thoughts on exclusive locks in SQL Server:
You will find that the SELECT statement executes, ignoring the exclusive lock, because it is not a write lock, and the data on the page has not been changed.
The main reason people try to do this is to force access to a row in a single threaded manner. For example, building their own sequence number, either in a row they update, or by trying to do MAX() on all of the data in a table to make sure only one reader gets the same value.
This is generally a bad idea, since locking an entire table is a generally bad idea, but if you needed to block readers, you can couple the XLOCK with a PAGLOCK. So, change the first reader to:
BEGIN TRANSACTION;
SELECT *
FROM Demo.Test WITH (XLOCK,PAGLOCK);
As Louis points out in the summary, locking is complicated. Having a good understanding of the locking model will serve you well, though.
Ned Otter runs into a tricky issue:
I have a client that used Itzik Ben-Gan’s solution of creating a filtered nonclustered columnstore index to achieve batch mode on a rowstore (in fact I proposed that the client consider it). They have an OLTP system, and often perform YTD calculations. When they tested, processing time was reduced by 30 to 50 percent, without touching a single line of application code. If that ain’t low hanging fruit, I don’t know what is —
However, during testing, I noticed some intermittent blocking that didn’t make sense to me. But I couldn’t nail it down, and they went live with the “filtered nonclustered columnstore index” solution.
Once they deployed – and there was a lot of concurrency – I could see what had eluded me during my proof of concept: blocking in tempdb.
Read on for the repro and check out Ned’s UserVoice bug report.
Josh Simar shares a deadlock graph which I have entitled The Pit Of Despair:
I can’t make heads or tails of that but I can tell you that seems like a really bad brawl for resources. It’s like a Jerry Springer show with a few extras thrown in. Since I knew that my graph wasn’t going to be helpful in this instance I went to the actual xml and tried to figure out how I could tune this to make it better in the future. I needed to know exactly where the issue was so the waitresource pointer is a good place to start.
You will see many blog articles on how to find SQL wait resources when the resource type is a key, a page, or an object (I suggest Kendra Little’s blog post) There is however a noticeable glut on articles explaining RID (a RID is a key on a table with no clustered index). I finally found how to tie a RID to an actual resource name but it was used for corruption so the details were a bit hazy at first.
Click through for this work of database art as well as a script which links RIDs back to specific object names.
Kevin Feasel
2019-07-26
Locks, Blocks, and Deadlocks