0

I'm currently getting the error:

System.Data.SqlClient.SqlException: Transaction (Process ID 109) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

From what I understand from my research, the system is killing the process with the smallest amount of access to the database to resolve a deadlock. This isn't ideal, because it guarantees that this process will enter an error state. I'm in a situation where it would not be undesirable for the process to wait for the deadlock to resolve; this isn't a super time-critical process.

Here are my questions:

  1. Is it possible to instruct Entity Framework via connection string or some other configuration to simply halt the least expensive deadlocked process rather than killing it? If so, how?
  2. Is it possible to report the stack trace for all dead locked processes, instead of just the process that got killed? It's basically impossible for me to troubleshoot otherwise.

Updated: I understand that it may not be possible to resolve the deadlock by simply halting one process. In this case, just focus on the second question.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Brian Gradin
  • 2,165
  • 1
  • 21
  • 42
  • The fact that it is a deadlock means that it will never resolve itself. One process has to be killed. – Fran Feb 05 '18 at 17:52
  • This is really a SQL Server issue, and therefore you might get much better answers either by a) tagging it with SQL Server, or b) moving it to dba.stackexchange – jleach Feb 05 '18 at 17:58
  • you can set the [DEADLOCK_PRIORTY](https://social.msdn.microsoft.com/Forums/en-US/a84cd042-cabc-4807-84dc-fa596a0bffce/set-deadlockpriority-for-a-datacontext?forum=linqtosql) on the dbcontext. but you really should be looking at your process as to why you've got deadlocks in the first place. – Fran Feb 05 '18 at 17:59
  • @Fran right, I'm just not sure how to do that unless I know what is deadlocking with what (unless I just arbitrarily pore over code), hence the question about how to determine this – Brian Gradin Feb 05 '18 at 18:02
  • The database is the one throwing the error, so I'd suggest trying to find the problem from the database out, rather than from the client in (go to the source, not the "victim"). If this is SQL Server, take a look at SQL Profiler to look at the activity. To find the competing processes, there are SQL queries to do that based on the flavor of your dbms. – Kevin Fichter Feb 05 '18 at 18:20
  • That may be a duplicate for [This question](https://stackoverflow.com/questions/12422986/sql-query-to-get-the-deadlocks-in-sql-server-2008), which may also answer your question nº 2. – Bruno Miquelin Feb 05 '18 at 18:37

2 Answers2

1

You can get the deadlock information from the system_health XEvent session.

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session

Or with a custom XEvent session for Azure SQL Database, like this:

CREATE EVENT SESSION [deadlocks] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report(
    ACTION(sqlserver.tsql_stack))
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=ON)
GO
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
-3

I recommend WinDbg, a multipurpose debugge for win. Moreover, you can try this

https://www.codeproject.com/Articles/20459/Deadlock-Detection-in-Existing-Code

Can be old a little bit, but good for learning.

MinhLee
  • 21
  • 2
  • 9