Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Question

I received the error that is in question, but do not have a north where to start investigating the cause of this error, ie not a North where to start. I'm getting worried about the situation. can anybody help me?

My execution stack is like this:

TARGETSITE: System.Data.DataTable GetDataTable(System.String, System.Data.CommandType, System.Collections.Generic.Dictionary`2[System.String,System.Object])

STACKTRACE:    at IUS.SQL.GetDataTable(String strQuery, CommandType Tipo, Dictionary`2 parametros)
   at IUS.VerificacaoCliente.Areas(ProfileCommon Profile)
   at Relatorio_Prazos.Page_Load(Object sender, EventArgs e)
   at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
   at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Answer 1

Resolving deadlocks can be tricky because you need to be knowledgable about all of the different types of objects and locks and how they can prevent each other's sessions from making any progress. Your stack trace can be used to indicate what resources your process was using, but not the survivor process. Some deadlocks have more than two sessions involved. You have several options to obtain the facts so that an investigation can be conducted.

• Run DBCC TRACEON (1204, 1222) to enable the trace flags indicated so that the information is saved into the SQL Server Error Log
• Use Profiler and select the Deadlock graph event under the Locks section, then recreate the deadlock
• Use Extended Events to capture the deadlock if you have SQL Server 2008 R2 or above

There is also third party monitoring software like Spotlight or Idera that can be configured to capture the deadlock graph and send it to you in an email.

Once you have the information about which objects or resources are involved, you then have the challenge of resolving the problem. Since everything in the deadlock graphs and xml are object IDs and hobts, you will need to look them up using object_name(obj_id), selecting from sys.objects or, in the case of a hobt (heap or b-tree) you would select from sys.partitions.

That should help you get started.