0

I have a database on which select, update and insert queries are run. At times it gave me deadlocks (key locks) while updating and selecting data from the table, it considered the select query to be the victim. After some research and examination of our queries, we added and removed certain indexes that would help in speeding up the queries. Now, after adding the indexes, I'm getting deadlocks (page locks) for the same select and update query. Why is this happening? Is there any way to reduce these deadlocks?

Deadlock is between

SELECT COUNT(*) AS [Id Count ] FROM TABLE WHERE ( ([name]) = 'abc' AND (([Id]) <> '4284167' OR [Id] IS NULL) AND ([Stage] <> 0) AND ( ([Result]) = 'P' OR ([Result] IS NULL OR [Result] = '') OR ([Result]) = 'Delay' ) )

...and

UPDATE TABLE SET [Result] = 'C' WHERE [Id] = 4284027 AND [Result] = 'P'

Before altering the indexes, these queries were deadlocked because of keylocks(Index).

Indexes involved due to deadlocks with Keylock

  1. Primary key, Non Clustered on column

    • ID

  2. Clustered Index on columns

    • [Date]
    • [Time]

  3. Non Clustered index on column

    • [Result]

  4. Non Clustered index on column

    • [Stage]

Objects that were involved in deadlocks were the Clustered Index on [Date][Time] and the Non Clustered Index on [Result].

Indexes involved due to deadlocks with PageLocks

  1. Primary Key, Clustered Index on column

    • ID

  2. Non Clustered Index on columns

    • [Date]
    • [Time]

  3. Non Clustered Index on columns:

    • Reffd
    • Date
    • Time
    • typeQuery
    • Result
    • Stage
    • Answer (Included Column)
    • FollowUpdate (Included Column)

Objects that were involved in deadlocks were Primary Key on [Id] and a Page

Query Plan for Keylock - Pagelock

  1. https://www.brentozar.com/pastetheplan/?id=ByK26kfyj[1]
  2. https://www.brentozar.com/pastetheplan/?id=SyzygeGJi

Query Plan for Pagelock - Pagelock

1.https://www.brentozar.com/pastetheplan/?id=S1wSNXzkj 2.https://www.brentozar.com/pastetheplan/?id=H1WhXmGys

Improve this question
2
  • 1
    To diagnose this deadlock properly, we need to see the full deadlock XDL graph (as text not images), and we need the query plans of both queries (please share via pastetheplan.com). Commented Aug 21, 2022 at 19:56
  • I'm guessing you want to modify the PK's non-clustered index to INCLUDE the Result column, also you probably want to change index #4 to (name, Result) INCLUDE (Id, Stage, SomeMoreColumnsHere) Commented Aug 21, 2022 at 20:06

1 Answer 1

Reset to default
1

Since, Deadlock graph is not included in the question yet. I would try to give few suggestions based on these two queries and changed index. Out of these two queries:

1.SELECT COUNT(*) as [Id Count ] FROM table WHERE ( ([name] ) = 'abc' AND (([Id] ) <> '4284167' or [Id] is null ) AND ([Stage]<>0) AND ( ([Result] ) = 'P' OR ([Result] Is Null OR [Result] = '' ) OR ([Result] ) = 'Delay' )) 2.update table set [Result]='C' where [Id] = 4284027 and [Result] ='P'

Second query is perfectly fine and would go for Index seek as Id is Primary Key clustered Index and you can't do anything for betterment for this one.

As far as first query is concerned, this has so many inequality operator along with OR operators, which will restrict your Index to go for seek operation and will need to go for range based scan.

Only column that I see which could be selective is name column having equality condition. I would prefer to create an index with name being the first column and then columns(stage and Result) followed based on selectivity - no more columns are required other than these three. So, index definition could look like below:

create index IX_SE on [dbo].[tablename](name, stage, Result)

Execution plan is provided for select * instead of select count(*) - which doesn't help much.

Index Non Clustered Index - [Date],[Time] is not very much required for these two queries but, could help in some other query at your environment.

In addition to above, please try to remove implicit conversion in the query wherever possible by using appropriate data type while performing comparison - Meaning Nvarchar should be compared with nvarchar value only and not with varchar.

Hope this takes you in making right judgement.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.