Sql-server – Deadlock from select statements

deadlocksql serversql-server-2008

I am getting deadlock from 2 select statements. What is the reason for that? And how can I fix this issue?

enter image description here

Deadlocked processes:   11278 
Victim process: 5185
Lock type:  Index/(Key)

Both queries are the same

object: unicas_ux.dbo.Course, lock mode: Exclusive (X), index: NCIDX_Course_TermId

(@P0 bigint)select courses0_.TermId as TermId17_130_1_, courses0_.Id
as Id1_45_1_, courses0_.Id as Id1_45_0_, courses0_.ConvertedGrade as
Converte2_45_0_……courses0_.VerifiedCredits as Verifie14_45_0_,
courses0_.VerifiedDate as Verifie15_45_0_, courses0_.VerifiedGrade as
Verifie16_45_0_ from dbo.Course courses0_ where courses0_.TermId=@P0
order by courses0_.CreatedDate asc

Best Answer

Deadlock happens when one query acquires a lock on a object (rows, data pages, extent, tables etc) and other resource tries to access it. Smallest unit in SQL Server is data pages and SQL holds a lock on page while working on it. So, yes it is possible that two select statement can create deadlock.

Solution:

WITH(NOLOCK) - If your data is fine to be read dirty
Use Read committed snapshot or Snapshot isolation
Try to keep transaction small and in batches
Use bound connection - it will allow two connection to use same transaction & lock

This answer is based on your question, as your screenshot doesn't contain any information.

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues] 
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table. Another thread is then taking an IX on the issue table (which is blocked). This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.

Sql-server – Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X

No, this is happening because you have a deadlocked resource. What you need to do is capture the deadlock information with a SQL Trace (you can use SQL Profiler) or with trace flags.

This will give the information (and with a trace, you can get the deadlock graph) to show you what two sessions were having the conflict resulting in the deadlock and a victim to be chosen (which is why you see the error you posted).

Deadlocks happen. If they are extremely infrequent then it's probably not an issue to worry about. But if they are happening all the time, then you need to drill down into what query(ies) is(are) causing them. You may need to rework your DML, or alter your transaction isolation level if it is appropriate (allowing dirty reads is not the answer), or even go with an optimistic isolation level like Snapshot Isolation or Read Committed Snapshot Isolation. But great consideration needs to be made here, as those optimistic isolation levels utilize row-versioning which will hammer tempdb, so if you don't have an optimial configuration for tempdb you could bring your performance to its knees.

Best Answer

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

Sql-server – Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X

Related Question