SQL SERVER - Talking about the Solution of Deadlock Monitoring and Analysis

Keywords: SQL Server Session SQL xml less

The deadlock Interface monitored is as follows:

How to use Extended Events for monitoring?

The script for establishing extended event monitoring is as follows: (Extended event is very good, the 2012 version supports visual operation, and you can learn about it on MSDN if you are interested: https://msdn.microsoft.com/zh-cn/library/bb630282.aspx, this article will not analyze the grammar and other knowledge points)

1 CREATE EVENT SESSION [DeadLock] ON SERVER 
2 ADD EVENT sqlserver.xml_deadlock_report 
3 ADD TARGET package0.event_file(SET filename=N'F:\events\deadlock\deadlock.xel',max_file_size=(20)),
4 ADD TARGET package0.ring_buffer(SET max_events_limit=(100),max_memory=(10240),occurrence_number=(50))
5 WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
6 GO

Query SQL as follows, here we need to note: query is based on buffer or file analysis, the number of buffer storage is generally limited, such as the above we only allocated 4M storage, file analysis is complete, but it depends on the number of retained files. Here we give buffer's query SQL as follows. File's query is interesting and can be written down by hand.

DECLARE @deadlock_xml XML
SELECT @deadlock_xml=(
                       SELECT 
                              ( 
                                SELECT
                                      CONVERT(XML, target_data)
                                FROM sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
                                WHERE s.name = 'deadlock' AND st.target_name = 'ring_buffer'
                              ) AS [x]
                       FOR XML PATH('') , TYPE
                      )

SELECT 
dateadd(hour,+6,tb.col.value('@timestamp[1]','varchar(max)')) TimePoint,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[1]','VARCHAR(MAX)') statement_parameter_k,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[2]','VARCHAR(MAX)') statement_k,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[3]','VARCHAR(MAX)') statement_parameter,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[4]','VARCHAR(MAX)') [statement],
tb.col.value('(data/value/deadlock/process-list/process/@waitresource)[1]','VARCHAR(MAX)') waitresource_k,
tb.col.value('(data/value/deadlock/process-list/process/@waitresource)[2]','VARCHAR(MAX)') waitresource,
tb.col.value('(data/value/deadlock/process-list/process/@isolationlevel)[1]','VARCHAR(MAX)') isolationlevel_k,
tb.col.value('(data/value/deadlock/process-list/process/@isolationlevel)[2]','VARCHAR(MAX)') isolationlevel,
tb.col.value('(data/value/deadlock/process-list/process/@waittime)[1]','VARCHAR(MAX)') waittime_k,
tb.col.value('(data/value/deadlock/process-list/process/@waittime)[2]','VARCHAR(MAX)') waittime,
tb.col.value('(data/value/deadlock/process-list/process/@clientapp)[1]','VARCHAR(MAX)') clientapp_k,
tb.col.value('(data/value/deadlock/process-list/process/@clientapp)[2]','VARCHAR(MAX)') clientapp,
tb.col.value('(data/value/deadlock/process-list/process/@hostname)[1]','VARCHAR(MAX)') hostname_k,
tb.col.value('(data/value/deadlock/process-list/process/@hostname)[2]','VARCHAR(MAX)') hostname
FROM @deadlock_xml.nodes('//event') as tb(col)

This SQL can query very detailed resource contention situation, if you want to effectively use extended events, we recommend that you take a detailed look at the xml grammar of the official website (SQL SERVER support for xml is also great, looking forward to the 2016 version of json support)

Is it very clear, clear at a glance, with this can be analyzed!?

2 Analysis

Depending on the content of the xml file or the monitoring content of the extended event, you can organize the following information (the deadlock analysis at the beginning):

Look at the execution plans for transaction 1 and transaction 2 as follows:

 

Combining tables and implementation plans, deadlock processes can be roughly inferred:

Conversation 1:

• Find Index_Page of index page where the key value is located according to the primary key SeqCode, find Index_key of key hashvalue key line above the page, hold IU lock for Index_Page and U lock for Index_key;
• Because the table is a heap table, bookmark lookup is searched by RID, that is, by line identifier, to find the data page where the row data corresponding to RID is located, and then find the row data pointing to the slot number on the page, holding a U lock on the row data;
• At this time, we have found the row data that need to be updated. We can upgrade the IU lock on the data page Data_Page to the IX lock. The row data pointed by RID is upgraded from the U lock to the X lock. After the upgrade, we release the IU lock and the U lock on the index page and the key value row.
• At this point, session 1 holds IX locks on Data_Page and X locks on RID rows.

During this process, Session 2 happens to apply for such a lock:

• Find Lock Resources in Transaction 2According to sys.partitions, you can see that 72057594038910976 is the primary key pk_FinanceReceiptNoRule and the primary key column is SeqCode.
• According to the primary key SeqCode, find Index_Page of index page where the key value is located, find the key line Index_key above the page, hold IU locks for Index_Page and U locks for Index_key.
• Because the table is a heap table, bookmark lookup is searched by RID, that is, by line identifier, to find the data page where the row data corresponding to RID is located, and then find the row data pointing to the slot number on the page, prepare the row data to hold the U lock, but find that session 1 holds the X lock on the RID row, resulting in its application for the U lock Timeout.
• Session 2 holds IU locks on Index_Page, U locks on Index_key, and IU locks on Data_Page, requesting U locks on RID rows.

Suppose that at this point, Session 1 performs an update operation (in the same transaction):

• According to the primary key SeqCode, find the index page Index_Page where the key value is located, find the key line Index_key on the page, hold the IU lock on Index_Page, prepare to hold the U lock on Index_key, but find that Index_key is held by session 2.

Then a deadlock occurs (see figure below for details):

• Session 1 holds IX locks on Data_Page, X locks on RID rows, and applies for U locks on Index_key (waiting for Session 2 to release)
• Session 2 holds IU locks on Index_Page, U locks on Index_key, and IU locks on Data_Page, requesting U locks on RID rows (waiting for Session 1 to release)

3 solution

Without RID lookup, the idea of finding data directly by index will not lead to this deadlock, that is, to re-establish the aggregated index on the primary key and discard the original non-aggregated index primary key. Because this excludes the application and holding of U-lock in RID, it keeps X-lock until the end of the transaction, and can modify the data page where the key value is located directly according to the primary key, thus reducing the time of inquiring rows in RID.

The revised implementation plan is as follows:

The process of applying for release of the lock is as follows (see screenshot for details):

• Find Index_Page of index page where the key value is located according to the primary key SeqCode, find Index_key of key hashvalue key line above the page, hold IU lock for Index_Page and U lock for Index_key;
• Since the table is already an aggregated index table and the page where the primary key is located contains row data, the IU lock can be upgraded to IX lock directly for Index_Page, and the U lock for Index_key can be upgraded to X lock, thus avoiding the lock application of RID for row-by-row data.