CODEWITHSUNDEEP
c#sql-serverdatabaseentity-frameworktransactions
Bruniasty
Bruniasty

Reputation: 428

Deadlocks on long transactions

I am using EntityFramework 6 on SQL Server 2012 and .NET 4.5.1.

During long run transactions a second user occurs deadlocks. The problem is that the first user blocks records for PayrollListHumanResourceID=90FA9981-AFD3-43BF-AD92-AAE5E2A42B5A and the second one wants to take data for PayrollListHumanResourceID=6CFE74C3-F180-497C-8DDA-BCA8D075FF59.

The below code shows a transaction from SQL Profiler for an Entity Framework client. For the second user the last (sometimes the penultimate) exec goes deadlock. For the sake of example I deleted most of functions that work fine. I put a breakpoint for the first user just after DELETE section and before COMMIT in C# code. The first user has different p__linq value.

set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read uncommitted


begin tran;
 (...)
 exec sp_executesql N'DELETE [Extent1] FROM [dbo].[PayrollListErrors] AS [Extent1] WHERE [Extent1].[PayrollListHumanResourceID] = @p__linq__0',N'@p__linq__0 uniqueidentifier',@p__linq__0='6CFE74C3-F180-497C-8DDA-BCA8D075FF59'
 exec sp_executesql N'DELETE [Extent1] FROM [dbo].[PayrollListElementRelations] AS [Extent1] WHERE [Extent1].[PayrollListHumanResourceID] = @p__linq__0',N'@p__linq__0 uniqueidentifier',@p__linq__0='6CFE74C3-F180-497C-8DDA-BCA8D075FF59'
 exec sp_executesql N'DELETE [Extent1] FROM [dbo].[PayrollListElements] AS [Extent1] WHERE [Extent1].[PayrollListHumanResourceID] = @p__linq__0',N'@p__linq__0 uniqueidentifier',@p__linq__0='6CFE74C3-F180-497C-8DDA-BCA8D075FF59'

commit;

Below an image with table keys and indexes. PayrollListElements table

The general table built in my database is:

Lock escalation does not appear in my example. Changing isolation level has no effect. Deleting/Updating this table by the ID column works without any deadlock:

DELETE [Extent1] FROM [dbo].[PayrollListElements] AS [Extent1] WHERE [Extent1].ID = 30

The problem arises when rows are being deleted from tables and foreign key references are being checked. When I am deleting a row from a parent table, all child references are being checked. Despite all foreign keys have a non-clustered index some of them are being checked via index scan instead of index seek. If during this operation another user blocks at least one row from the scanned table - the delete operation will be blocked. It happens even when a blocking row has no reference with deleting data. Using the FORCESEEK Table Hint has no effect.

Delete execution plan

Deadlock graph.xdl

Delete execution plan.sql

Trace profiler.trc

Upvotes: 4

Views: 970

Answers (1)

usr
usr

Reputation: 171178

I think you have correctly identified the scans as the problem. The scans are run under effective serializable isolation so that no new rows can appear right after the check and violate the FK.

Even if it was possible to force these checks to use nested loops I would advise against that. This seems like a fairly brittle and manual fix. In fact I don't think it would be a complete fix; It would just reduce the likelihood of a deadlock. Even if the checks find no applicable rows range key locks will still be taken on the previous key. You can't avoid overlap.

The best idea that I have so far is to implement the most general solution to deadlocks possible: A retry loop that retries on SqlException.Number == 1205. You must retry the entire transaction. This always works and is safe.

An alternative would be to use something like a global lock to not run potentially conflicting operations at the same time. That's the nuclear option and a weapon of last resort because it destroys scalability of this particular part of the system.

Upvotes: 2

Related Questions