Archive

Archive for the ‘Deadlock’ Category

Resolve the Deadlock issue

December 12, 2011 Leave a comment

Resolve the Deadlock issue

 

Deadlock:

 

Deadlocking occurs when two or more SQL Server processes have locks on separate database objects and each process is trying to acquire a lock on an object that the other processes have previously locked.  For example, process one has an exclusive lock on object one, process two has an exclusive lock on object two, and process one also wants an exclusive lock on object two, and object two wants an exclusive lock on object one. Because two processes can’t have an exclusive lock on the same object at the same time, the two processes become entangled in a deadlock, with neither process willing to yield of its own accord.

 

Application log got the message:

 

Error captured:

Msg 1205, Sev 13, State 56, Line 1 : Transaction (Process ID 276) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001]

 

Step for resolution:

 

  • By Enable the Trace
  • Captured the Deadlock information by Profiler
  • Step to capture the Deadlock information by Perfmon:

 

 

 

To identify the deadlock I have enable the trace on the SQL server so if is there any deadlock encounter in Sql end it will log at SQL error log file and we can figure the root cause of deadlock.

 

Enable the Trace:

I have Turn on deadlock trace {   DBCC TRACEON (1204, 1222)}        

This will enable deadlock tracing for all existing connections and new. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag reports deadlock information formatted, first by processes and then by resources. So if is there any deadlock occurred at least it could be logged at SQL error log.

SQL error log report:

 

Deadlock encountered …. Printing deadlock information

2011-05-19 06:31:42.18 spid3    

2011-05-19 06:31:42.18 spid3     Wait-for graph

2011-05-19 06:31:42.18 spid3    

2011-05-19 06:31:42.18 spid3     Node:1

2011-05-19 06:31:42.20 spid3     PAG: 9:1:4122955               CleanCnt:2 Mode: SIU Flags: 0x2

2011-05-19 06:31:42.20 spid3      Grant List 0::

2011-05-19 06:31:42.20 spid3        Owner:0x38661880 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:754 ECID:0

2011-05-19 06:31:42.20 spid3        SPID: 754 ECID: 0 Statement Type: DELETE Line #: 448

2011-05-19 06:31:42.20 spid3        Input Buf: RPC Event: sp_create_order;1

2011-05-19 06:31:42.20 spid3      Grant List 1::

2011-05-19 06:31:42.20 spid3      Requested By:

2011-05-19 06:31:42.20 spid3        ResType:LockOwner Stype:’OR’ Mode: IX SPID:549 ECID:0 Ec:(0x5523F520) Value:0x1ef2dae0 Cost:(0/3C)

2011-05-19 06:31:42.20 spid3    

2011-05-19 06:31:42.20 spid3     Node:2

2011-05-19 06:31:42.20 spid3     PAG: 9:1:3598450               CleanCnt:2 Mode: SIU Flags: 0x2

2011-05-19 06:31:42.20 spid3      Grant List 0::

2011-05-19 06:31:42.20 spid3      Grant List 1::

2011-05-19 06:31:42.20 spid3        Owner:0x38611f00 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:549 ECID:0

2011-05-19 06:31:42.20 spid3        SPID: 549 ECID: 0 Statement Type: DELETE Line #: 448

2011-05-19 06:31:42.20 spid3        Input Buf: RPC Event: sp_executesql;1

2011-05-19 06:31:42.20 spid3      Requested By:

2011-05-19 06:31:42.20 spid3        ResType:LockOwner Stype:’OR’ Mode: IX SPID:754 ECID:0 Ec:(0x47939548) Value:0x38661340 Cost:(0/3C)

 

In the “Wait-for-graph” entry, you have Node 1 and Node 2. In each node, you have a grant section and a request section. The grant section is the “Grant List”, and the request section is the “Request By.”
In each node, you can identify the following:

  • The SPID.
  • The command the SPID was executing.
  • The resource.
  • The lock mode on the resource.

Query:

select * from sysobjects where id = 4122955 

select * from sysindexes where indid = 1 and id = 4122955 

 

 

Captured the log by Profiler:

 

In SQL Server 2005 was introduced, new events were added to the SQL Server 2005 Profiler that makes identifying deadlocks very easy and I used to same technique.

 

Events selected in profiler:

 

Deadlock graph

·         Lock: Deadlock

·         Lock: Deadlock Chain

·         RPC:Completed

·         SP:StmtCompleted

·         SQL:BatchCompleted

·         SQL:BatchStarting

 

 

 

 

 

 

 

 

Step to capture the Deadlock information by Perfmon:

Use SQL Profiler to trace deadlock events and get the resource ID of the table or index under contention. The steps to do this are:

  1. Start SQL profiler
  2. On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
  3. Click the Events tab, only add Locks\Lock:deadlock and Locks\Lock:deadlock chain
  4. Click the Data columns tab, add DatabaseID, IndexID, ObjectID

This trace will record all deadlocks on this SQL Server instance, along with the ID of the source table of contention. To translate the database id and object id into names (although DatabaseName and ObjectName are selectable columns, the data is not always presented in the trace), you can do

 

 

 

 

To help minimize deadlocks:

  • Access objects in the same order.
  • Avoid user interaction in transactions.
  • Keep transactions short and in one batch.
  • Use a lower isolation level.
  • Use a row versioning-based isolation level.
    • Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
    • Use snapshot isolation.

 

Advertisements
Categories: Deadlock