Recurring deadlocks

Question

Recurring deadlocks

sql-lover

SSCoach

Points: 18530
More actions
March 23, 2016 at 2:39 pm

#312255

I've been having recurring and continue deadlocks due an store procedure with this code:
--UPDATE t1
-- SET Invalid = 1
--FROM dbo.Table1 t1
--INNER JOIN dbo.Table2 t2 WITH (NOLOCK)
--ON (t1.UserID = t2.UserID
--AND t2.UserCompanyID = @CompanyID)
--WHERE t1.[Status] NOT IN (1, 2)
-- AND Invalid = 0
Basically, it changes the status of a report table from X to 1, which is invalid. This is runs concurrently , means, it can be called by another client, based on ID, and does the same, hence, why so many deadlocks during heavy workload.
Here is the count for Status, Table1
1: 88 rows
2: 1256 rows
3: 964084 rows
4: 1663 rows
5: 45366 rows
6: 2 rows
7: 86 rows
Here is one of the Indexes that should be supporting this query, but it is not, the plan is a cluster Index scan:
CREATE NONCLUSTERED INDEX [nc1] ON [dbo].[Table1]
(
[UserID] ASC,
[Invalid] ASC,
[Status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
The 2nd NCI is
CREATE NONCLUSTERED INDEX [nc2] ON [dbo].[Table1]
(
[RequestDate] ASC,
[Invalid] ASC
)
INCLUDE ( [Table1_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
And the CLuster Index is a surrogate key, on Table1_ID
Any suggestion on how to eliminate the recurring deadlock? By the way, T1 has 1012545 rows and T2 has 99157 rows

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

You can use a UpdLock lockhint on Table1.Updlock allows other reads to acquire Shared locks but will block other transactions from trying to get update or X lock on the records its trying to update.If the Updlock is part of a bigger transaction you may want to add HoldLock hint as well so that the Update lock is held till the end of the transaction.

Having indexes on values with low cardinality should be avoided.You have Clustered index on status and Invalid which I guess will have very low cardinality .Updating CI indexes will place locks probably X locks on the underlying NC indexes as well.So even if you place an Updlock hint in your update statement other concurrent transactions would face blocking for a long time.

Between are there any cascading updates or triggers on Table1 ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Can you post the full DDL for the tables, indices, triggers etc.?

😎

sql-lover SSCoach Points: 18530 More actions · Answer 3

Sachin Nandanwar (3/23/2016)
You can use a UpdLock lockhint on Table1.Updlock allows other reads to acquire Shared locks but will block other transactions from trying to get update or X lock on the records its trying to update.If the Updlock is part of a bigger transaction you may want to add HoldLock hint as well so that the Update lock is held till the end of the transaction.
Having indexes on values with low cardinality should be avoided.You have Clustered index on status and Invalid which I guess will have very low cardinality .Updating CI indexes will place locks probably X locks on the underlying NC indexes as well.So even if you place an Updlock hint in your update statement other concurrent transactions would face blocking for a long time.
Between are there any cascading updates or triggers on Table1 ?

No, no triggers, but you're right about the cardinality issue. I don't really like to table's design, to be honest, I can't change the schema, so I was thinking about Index change or play with isolation, as you suggested.

The other alternative, which may require business approval, is doing the change async. Wait till specific hour, or log that on a different table, but that is a more drastic change.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Can you post the actual (not estimated) execution plan as a .sqlplan attachment please? It will answer a lot of questions.

Rule #1 when dealing with a deadlocking query - reduce the time it takes to run.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sql-lover SSCoach Points: 18530 More actions · Answer 5

ChrisM@Work (3/24/2016)
Can you post the actual (not estimated) execution plan as a .sqlplan attachment please? It will answer a lot of questions.
Rule #1 when dealing with a deadlocking query - reduce the time it takes to run.

I've attached a screenshot of the actual plan. I am not so sure I can attach the whole thing due privacy issues. But it is a pretty simple plan.

I know reducing the time it takes to run reduces the chance of a deadlock, but don't see how that can be possible here.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

sql-lover (3/24/2016)
ChrisM@Work (3/24/2016)
Can you post the actual (not estimated) execution plan as a .sqlplan attachment please? It will answer a lot of questions.
Rule #1 when dealing with a deadlocking query - reduce the time it takes to run.
I've attached a screenshot of the actual plan. I am not so sure I can attach the whole thing due privacy issues. But it is a pretty simple plan.
I know reducing the time it takes to run reduces the chance of a deadlock, but don't see how that can be possible here.

I'm sorry but that renders the plan about as useful as a bicycle to a fish. SQL Sentry Plan Explorer will do your obfuscations for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sql-lover SSCoach Points: 18530 More actions · Answer 7

ChrisM@Work (3/24/2016)
sql-lover (3/24/2016)
ChrisM@Work (3/24/2016)
Can you post the actual (not estimated) execution plan as a .sqlplan attachment please? It will answer a lot of questions.
Rule #1 when dealing with a deadlocking query - reduce the time it takes to run.
I've attached a screenshot of the actual plan. I am not so sure I can attach the whole thing due privacy issues. But it is a pretty simple plan.
I know reducing the time it takes to run reduces the chance of a deadlock, but don't see how that can be possible here.
I'm sorry but that renders the plan about as useful as a bicycle to a fish. SQL Sentry Plan Explorer will do your obfuscations for you.

Grrr! lol ... I knew you were going to make that comment.... let me see if I can do that

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

sql-lover (3/24/2016)
ChrisM@Work (3/24/2016)
sql-lover (3/24/2016)
ChrisM@Work (3/24/2016)
Can you post the actual (not estimated) execution plan as a .sqlplan attachment please? It will answer a lot of questions.
Rule #1 when dealing with a deadlocking query - reduce the time it takes to run.
I've attached a screenshot of the actual plan. I am not so sure I can attach the whole thing due privacy issues. But it is a pretty simple plan.
I know reducing the time it takes to run reduces the chance of a deadlock, but don't see how that can be possible here.
I'm sorry but that renders the plan about as useful as a bicycle to a fish. SQL Sentry Plan Explorer will do your obfuscations for you.
Grrr! lol ... I knew you were going to make that comment.... let me see if I can do that

Heh no worries! Here's a little encouragement for you. Earlier this week I demo'd a query tuning tip to some folk here ate work. The execution time dropped from ten and a half seconds to less than three milliseconds with one modest change. If it were an update with a deadlocking issue, then the chance of deadlocks might have changed from "extremely likely" to "extremely unlikely".

Bear in mind that reducing duration almost always equates to less pages read and hence a smaller footprint of locks in tables.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 9

With the execution plan it'll be easier, but just looking at the query and the indexes, it can't use any of those indexes because the WHERE clause filters on Invalid and Status but neither of those is the leading edge of an index. So, it's scanning the cluster. And yeah, I see the join criteria, but that's unlikely to matter at this point.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101099 More actions · Answer 10

Are you certain that query is the one that is deadlocking? So that query wasn't really commented out in the proc?!

Do you have on trace flags, at least 1222, to help get deadlock info?

At root, it's very likely the clustered indexes on table1 and table2 are not the best, which will lead to deadlocking (or additional deadlocking). Clustering by default on an identity column is most often not best (no, really, it's not). If you're willing to review the indexes, I'll provide a basic script to look at missing index stats, index usage stats and selected index operational stats, and help you decide if the existing clustering index is right or not.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

sql-lover (3/24/2016)
ChrisM@Work (3/24/2016)
Can you post the actual (not estimated) execution plan as a .sqlplan attachment please? It will answer a lot of questions.
Rule #1 when dealing with a deadlocking query - reduce the time it takes to run.
I've attached a screenshot of the actual plan. I am not so sure I can attach the whole thing due privacy issues. But it is a pretty simple plan.
I know reducing the time it takes to run reduces the chance of a deadlock, but don't see how that can be possible here.

The clustered index update shows 85% of total cost. If that's anything like close, then the rest of the statement - the two table reads and the join - are very efficient. Both reads show ordinary indexes rather than the clustered indexes you suggested in your initial post.

Edit: also, four compute scalars in the picture of the plan indicates that there's something else going on - is your actual query different to the one you've posted or have you got data type issues here?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Here's a simple model utilising an index to seek on Invalid, Status, UserID in that order. There's one predicate for each of the Status values, and no residual predicate - no pages are read unnecessarily (well, possibly).

DROP TABLE #t1

CREATE TABLE #t1 (ID INT IDENTITY(1,1) NOT NULL, Invalid BIT, [Status] TINYINT, UserID INT)

INSERT INTO #t1 (Invalid, [Status], UserID)

SELECT --r,

Invalid = CASE (r%5) WHEN 0 THEN 0 ELSE 1 END,

[Status] = 1 + (r%7),

UserID = r

FROM (SELECT TOP 10000 r = ABS(CHECKSUM(NEWID())) FROM sys.columns) d

CREATE UNIQUE CLUSTERED INDEX ucx_Stuff ON #t1 (ID)

CREATE INDEX ix_Helper1 ON #t1 (Invalid, UserID) INCLUDE (Status)

CREATE INDEX ix_Helper2 ON #t1 (Invalid, Status, UserID)

DROP TABLE #t2

CREATE TABLE #t2 (ID INT IDENTITY(1,1) NOT NULL, UserID INT, UserCompanyID INT)

INSERT INTO #t2 (UserID, UserCompanyID)

SELECT UserID, [Status] FROM #t1

CREATE UNIQUE CLUSTERED INDEX ucx_Stuff ON #t2 (ID)

CREATE INDEX ix_Helper1 ON #t2 (UserCompanyID, UserID)

----------------------------------------------------------------

DECLARE @CompanyID INT = 3

UPDATE t1

SET Invalid = 1

FROM #t2 t2

INNER loop JOIN #t1 t1

ON t1.Invalid = 0

AND t1.[Status] IN (3,4,5,6,7)

AND t1.UserID = t2.UserID

WHERE t2.UserCompanyID = @CompanyID

GO

----------------------------------------------------------------

DECLARE @CompanyID INT = 3

UPDATE t1

SET Invalid = 1

FROM #t1 t1

WHERE t1.Invalid = 0

AND t1.[Status] > 2 --not in (1,2)

AND EXISTS (

SELECT 1

FROM #t2 t2

WHERE t1.UserID = t2.UserID

AND t2.UserCompanyID = @CompanyID)

------------------------------------------------------------------------------------------

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden