Skip to content

Deadlock Analysist

November 3, 2014

https://www.sqlshack.com/locking-sql-server/

https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

https://stackoverflow.com/questions/2099659/sql-server-2005-deadlock-with-nonclustered-index

https://blogs.msdn.microsoft.com/askjay/2011/01/28/how-does-sql-server-perform-deletes-in-my-table/

http://www.sql-server-performance.com/2004/advanced-sql-locking/1/

https://www.columbia.edu/sec/acis/db2/db2d0/db2d0109.htm#HDRCONCEPT

https://technet.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx

http://blogs.msdn.com/b/bartd/archive/2006/09/09/747119.aspx

http://blogs.msdn.com/b/saponsqlserver/archive/2013/01/31/deadlock-analysis-basic.aspx

http://www.sql-server-performance.com/2004/advanced-sql-locking/

The trace flags that get set are:

Trace flag Description Purpose
1204 Deadlock victim/chain output This trace flag produces detailed information about deadlocks, including participants and victims.
1222 XML Deadlock Information to the error log This trace flag pipes XML Deadlock information to the error log
3605 Send trace output to the error log This trace flag is necessary to get the above debug information into the error log. If it is not set, the other trace flags will not be able to write to the SQL Server Error log.

The ovals are showing more information about the thread:

Line Content
Server process id The Session ID (SPID) of the request
Server batch id An internal reference number for the batch, where the statement is running in. In an SAP system it is zero.
Execution context id When the query is executed in parallel, this shows the ID of one of the parallel running threads. In an SAP system it is mostly zero.
Deadlock priority If the thread has an associated deadlock priority, it is shown here. SAP does not use deadlock priority.
Log Used The amount of transaction log, that was already generated by this thread
Owner ID An internal reference number for the transaction.
Transaction descriptor An internal reference number for the state of the transaction.

In the middle of the graph, in the rectangles, information about the two participating locks is shown:

Line Content
Locktype Type of lock the thread holds or requests. Common values are “Key Lock”, “Page Lock” or ”RID Lock”
HoBt ID Heap or B-Tree ID, the ID of the internal allocation structure of the table or partition
Associated ObjId The ObjectID that is associated to the HoBt ID. The name of the object can be retrieved via the OBJECT_NAME function.
Index name The name of the index, on which the locks are held or requested.

The XML has a structure like:

XML Tag Description
Deadlock-list The surrounding bracket around all deadlocks (most of the time only one) deadlocks
     Deadlock First deadlock, with Victim Process ID
         Process-list The list of involved Processes (most of the time only two)
              Process Process number one, the victim. This section contains many attributes about the Process itself, they will be discussed later
                   ExecutionStack The statement that causes the deadlock, which SQL Handle and (sometimes) as a prepared statement.
                   Inputbuffer The complete input buffer of the process, in an SAP system most of the time only one statement, in our example the complete script of the connection. Has the statement from the ExecutionStack included.
              Process Process number two, the survivor or winner of the deadlock
                   ExecutionStack The same information as for the victim.
                   Inputbuffer The same information as for the victim.
Resource-List All the resources that are involved in the deadlock. Mostly only a list of two KEY locks, but might be other as well (e.g. PAGE locks)
     Keylock/PageLock Detailed information about the first lock itself, they will be discussed later.
         Owner-List The list of owner of this resource, mostly one process.
              Owner The owner with process id and lock mode (X, U, S etc.)
         Waiter-List The list of waiter of this resource; can be more than one.
              Waiter The waiter with process id and lock mode (X, U, S etc.) and request type (WAIT).
     Keylock/PageLock Detailed information about the second lock.
         Owner-List The same information as for the first lock.
              Owner The same information as for the first lock.
         Waiter-List The same information as for the first lock.
              Waiter The same information as for the first lock.

Here is an example (fragment), how this looks like in our case:

XML Tag Example
Deadlock-list
     Deadlock victim=”process3f9f4c8″
         Process-list
              Process id=”process3f9f4c8″ taskpriority=”0″ logused=”684″ waitresource=”KEY: 11:72057594038779904 (7e576f73234b)”             waittime=”2185″ ownerId=”286942698″ transactionname=”user_transaction”
lasttranstarted=”2013-02-01T12:00:44.620″           XDES=”0x86dab950″ lockMode=”U” schedulerid=”1″ kpid=”3640″ status=”suspended” spid=”59″ sbid=”0″ ecid=”0″ priority=”0″           trancount=”2″ lastbatchstarted=”2013-02-01T12:00:44.537″ lastbatchcompleted=”2013-02-01T11:51:13.350″             clientapp=”Microsoft SQL Server Management Studio – Query” hostname=”MyHostname” hostpid=”4868″ Loginname=”MyDomain\User” isolationlevel=”read committed (2)” xactid=”286942698″ currentdb=”11″ lockTimeout=”4294967295″ clientoption1=”671090784″             clientoption2=”390200″
              ExecutionStack UPDATE [SAPTABLE] set [SPRSL] = @1 WHERE [NAME]=@2 AND [SPRSL]=@3UPDATE SAPTABLE SET SPRSL = N’E’ WHERE NAME = N’Schulze’ AND SPRSL = N’D’
— Deadlock occured
                   Inputbuffer use [DL_Analysis]
BEGIN TRANSACTION
— Update the first row, starting with Miller
UPDATE SAPTABLE SET SPRSL = N’D’ WHERE NAME = N’Miller’ AND SPRSL = N’E’
— Now run the script for the deadlock partner
— we will wait in this connection for 10 seconds ….
WAITFOR DELAY ’00:00:10′
— .. and then will execute the next Update here (now Schulze)     UPDATE SAPTABLE SET SPRSL = N’E’ WHERE NAME = N’Schulze’ AND SPRSL = N’D’
— Deadlock occured
Resource-List
     Keylock/PageLock hobtid=”72057594038779904″ dbid=”11″ objectname=”DL_Analysis.dbo.SAPTABLE” indexname=”SAPTABLE~0″ id=”lockd047c00″ mode=”X” associatedObjectId=”72057594038779904″
         Owner-List
              Owner id=”process7041708″ mode=”X”
         Waiter-List
              Waiter id=”process3f9f4c8″ mode=”U” requestType=”wait”
     Keylock/PageLock hobtid=”72057594038779904″ dbid=”11″ objectname=”DL_Analysis.dbo.SAPTABLE” indexname=”SAPTABLE~0″ id=”lock28b09180″ mode=”X” associatedObjectId=”72057594038779904″
         Owner-List
              Owner id=”process3f9f4c8″ mode=”X”
         Waiter-List
              Waiter id=”process7041708″ mode=”U” requestType=”wait”

The process attributes are (from the example) :

Attribute Description
id=”process3f9f4c8″ The ID of the process
taskpriority=”0″ The priority of the task, most of the time 0.
logused=”684″ The amount of transaction log that was generated through this transaction. The process with the least amount will be the victim.
waitresource=”KEY: 11:72057594038779904 (7e576f73234b)” The resource, the process is waiting on. It is defines as Type (RID, KEY, PAGE etc.):DatabaseID:AllocationUnitID(HashValue of the Key)
waittime=”2185″ How long has the process waited on this resource?
ownerId=”286942698″ Internal number of the transaction.
transactionname=”user_transaction” The name or type of transaction
lasttranstarted=”2013-02-01T12:00:44.620″ When did the last transaction started?
XDES=”0x86dab950″
lockMode=”U” The lock mode the process is waiting for.
schedulerid=”1″ The CPU this process is running on.
kpid=”3640″ The Window Kernel Thread ID
status=”suspended” The status of the process (suspended, as it is waiting for the lock).
spid=”59″ The SQL Server Session ID
sbid=”0″ The System BatchID or Request ID
ecid=”0″ The Execution Context, when executed in parallel, otherwise Zero.
priority=”0″ The deadlock priority of the process.
trancount=”2″ How many nested transaction has the process open?
lastbatchstarted=”2013-02-01T12:00:44.537″ When did the last batch started?
lastbatchcompleted=”2013-02-01T11:51:13.350″ When did the last batch completed?
clientapp=”Microsoft SQL Server Management Studio – Query” The name of the client application.
hostname=”MyHostname” The hostname where the client application is running on.
hostpid=”4868″ The Window Process ID of the application
loginname=”MyDomain\MyUser” The user that is connected to the SQL Server.
isolationlevel=”read committed (2)” The transaction isolation level of the process.
xactid=”286942698″ Transaction ID
currentdb=”11″ Database ID
lockTimeout=”4294967295″ The maximum lock timeout.
clientoption1=”671090784″ The client options that are set (e.g. XACT_ABORT, ANSI_NULLS etc.)
clientoption2=”390200″ The client options that are set (cont).

The resource attributes are (from the example):

Resource Atrribute Description
hobtid=”72057594038779904″ The Heap-or-B-Tree ID, the internal ID for the allocation unit of the table
dbid=”11″ The database ID
objectname=”DL_Analysis.dbo.SAPTABLE” The object
indexname=”SAPTABLE~0″ The index the resource belongs to
id=”lockd047c00″  The internal representation of the lock.
mode=”X” The requested or owned lock mode
associatedObjectId=”72057594038779904″ The object id of the table, most of the time identical with the HoBtID. Only when the table is partitioned the two are different (one ObjectID has then multiple HoBtID).

“Decode” the -T1222 output to better understand the deadlock scenario. The deadlock is summarized by a “process-list” and a “resource-list”. A “process” is a spid or worker thread that participates in the deadlock. Each process is assigned an identifier, like “processdceda8”. A resource is a resource that one of the participants owns (usually a lock) that the other participant is waiting on. I like to use a format like the one below to summarize the deadlock.

For most lock types (including KEY locks, as shown in this example), SQL will directly identify the index by name in the output.  For some lock types, though, you’ll get an “associatedObjectId”, but no object name.  An example:

pagelock fileid=1 pageid=95516 dbid=9 objectname=”” id=lock177a9e280 mode=IX associatedObjectId=72057596554838016
The attribute “associatedObjectId” isn’t the type of Object ID that you’re probably familiar with; it’s actually a partition ID.  You can determine the database name by running “SELECT DB_NAME(9)”, where the “9” in this example comes from the “dbid” attribute, highlighted in blue.  Then you can determine the index and table name by looking up the associatedObjectId/PartitionId in the indicated database:

SELECT OBJECT_NAME(i.object_id), i.name
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.partition_id = 72057596554838016

Types of blocking

Shared (S)
Used for read operations that do not change or update data, such as a SELECT statement.

Update (U)
Used on resources that can be updated. Prevents a common form of deadlock  that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Exclusive (X)
Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Intent
Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Schema
Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

Bulk Update (BU)
Used when bulk copying data into a table and the TABLOCK hint is specified.

Applies to: SQL Server 2008 R2 and higher versions.

The following table shows the compatibility of the most commonly encountered lock modes.

Existing granted mode
Requested mode IS S U IX SIX X
Intent shared (IS) Yes Yes Yes Yes Yes No
Shared (S) Yes Yes Yes No No No
Update (U) Yes Yes No No No No
Intent exclusive (IX) Yes No No Yes No No
Shared with intent exclusive (SIX) Yes No No No No No
Exclusive (X) No No No No No No
NoteNote
An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions. Further, if two transactions attempt to
Advertisements

From → DBA

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: