SQL Server deadlock Monitoring Analysis Solution, SQL Server

SQL Server deadlock Monitoring Analysis Solution, SQL Server

1. Background

1.1 alert status

Recently, I plan to migrate all my notes to EVERNOTE. Sort out the lock part, and there is a case recorded by myself. I will try again and share it with you.

At noon of a day, I received an alert message, indicating that the DB deadlock was abnormal and there were 120 deadlocks per minute.

The xml file of the deadlock is as follows:

<Deadlock-list> <deadlock victim = "process810b00cf8"> <process-list> <process id = "process810b00cf8" taskpriority = "0" logused = "0" waitresource = "RID: 13: 1: 1541136: 62 "waittime =" 7682 "ownerId =" 3396587959 "transactionname =" UPDATE "lasttranstarted =" 2016-01-08T12: 03: 51.067 "XDES =" 0xa99746d08 "lockMode =" U "schedulerid =" 41 "kpid =" 17308 "status =" suspended "spid =" 108 "sbid =" 0 "ecid =" 0 "priority =" 0 "trancount =" 2 "lastbatchstarted =" 2016-01-08T12: 03: 51.067 "lastbatchcompleted =" 2016-01-08T12: 03: 51.067 "lastattention =" 1900-01-01T00: 00: 00.067 "clientapp =" Microsoft SQL Server Management Studio-query "hostname =" test-server "hostpid =" 1433 "loginname =" xinysu "isolationlevel =" read committed (2) "xactid =" 3396587959 "currentdb =" 13 "lockTimeout =" 4294967295 "clientoption1 =" 671098976 "clientoption2 =" 390200 "> <executionStack> <frame procname =" adhoc "line =" 7 "stmtstart =" 214 "stmtend =" 484 "sqlhandle =" weight "> UPDATE FinanceReceiptNoRule SET NowSeqValue = @ ReturnNum, ISRUNNING = '0', LastWriteTime = GETDATE () WHERE IsRunning = '1' AND SeqCode = @ SeqCode </frame> </executionStack> <inputbuf> declare @ SeqCode varchar (60) declare @ ReturnNum bigintset @ SeqCode = 'cgjs20160106 'while (1 = 1) beginUPDATE into SET NowSeqValue = @ ReturnNum, ISRUNNING = '0', LastWriteTime = GETDATE () WHERE IsRunning = '1' AND SeqCode = @ SeqCodeend </inputbuf> </process> <process id = "process18fd5d8cf8" taskpriority = "0" logused = "248" waitresource = "KEY: 13: 72057594040090624 (b3ade7c5980c) "waittime =" 4 "ownerId =" 3396522828 "transactionname =" user_transaction "lasttranstarted =" 2016-01-08T12: 03: 05.310 "XDES =" 0x18c1db63a8 "lockMode =" U "schedulerid =" 57 "kpid =" 16448 "status =" suincluded "spid =" 161 "sbid =" 0 "ecid =" 0 "priority =" 0 "trancount =" 2 "lastbatchstarted =" 2016-01-08T12: 03: 58.737 "lastbatchcompleted =" 2016-01-08T12: 03: 33.847 "lastattention =" 2016-01-08T12: 03: 33.850 "clientapp =" Microsoft SQL Server Management Studio-query "hostname =" test-server "hostpid =" 1433 "loginname =" xinysu "isolationlevel =" read committed (2) "xactid =" 3396522828 "currentdb =" 13 "lockTimeout =" 4294967295 "clientoption1 =" 671090784 "clientoption2 =" 390200 "> <executionStack> <frame procname =" adhoc "line =" 6 "stmtstart =" 210 "stmtend =" 400 "sqlhandle =" success "> Update dbo. financeReceiptNoRule Set [IsRunning] = '1' where SeqCode = @ SeqCode and IsRunning = '0' </frame> </executionStack> <inputbuf> declare @ SeqCode varchar (60) declare @ ReturnNum bigintset @ SeqCode = 'cgjs20160106 'while (1 = 1) beginUpdate dbo. financeReceiptNoRule Set [IsRunning] = '1' where SeqCode = @ SeqCode and IsRunning = '0' end </inputbuf> </process-list> <resource-list> <ridlock fileid = "1" pageid = "1541136" dbid = "13" objectname = "inline" id = "lock51e8a3980" mode = "X" associatedObjectId = "72057594040025088"> <owner -list> <owner id = "process18fd5d8cf8" mode = "X"/> </owner-list> <waiter id = "process810b00cf8" mode = "U" requestType = "wait"/> </waiter-list> </ridlock> <keylock hobtid = "72057594040090624" dbid = "13" objectname = "inline" indexname = "PK_FINANCERECEIPTNORULE" id = "lock7b2c6bc80" mode = "U" associatedObjectId = "72057594040090624"> <owner-list> <owner id = "process810b00cf8" mode = "U"/> </owner-list> <waiter-list> <waiter id = "process18fd5d8cf8" mode = "U" requestType = "wait"/> </waiter-list> </keylock> </resource-list> </deadlock> </deadlock-list>

The table structure and simulation data are as follows:

-- TABLE involved: create table [dbo]. [FinanceReceiptNoRule] ([SeqCode] [varchar] (60) not null, [NowSeqValue] [bigint] NULL, [SeqDate] [varchar] (14) not null, [IsRunning] [varchar] (1) NULL, [LastWriteTime] [datetime] NULL, [Prefix] [varchar] (4) NULL) ON [PRIMARY] GO -- simulate data INSERT [dbo]. [FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (n'test20150108 ', 1469, N '000000', N '0', CAST (n'2017-01-08 05:05:49. 163 'as DateTime), n' test') GOINSERT [dbo]. [FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (n'test20150109 ', 1377, N '000000', N '0', CAST (n'2017-01-09 04:50:26. 610 'as DateTime), n' test') go alter table [dbo]. [CONSTRAINT] add constraint [CONSTRAINT] primary key nonclustered ([SeqCode] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

1.2 How to monitor

There are multiple methods to capture deadlocks. Here we will introduce two types: SQL SERVER Profiler tool and Extended Events. Profiler is relatively resource-consuming, but because only the deadlock is monitored, the performance is not very affected, and its visual interface is easier to use. Extended Events consumes less resources, real-time records are recorded to the second to last deadlock, and SQL statements are required to analyze and query the record files.

How to Use Profiler monitoring?

Open SSMS, click <tools>, and select <SQL Server Profiler>, as shown in.

Log on to the DB instance to be monitored and fill in the corresponding tracking properties. The first step is the <General> page, as shown in. Here, pay attention to two aspects. First, select the <TSQL-Locks> template, which can be used to monitor deadlocks or observe lock application and release details, you can check the lock application and release status of select update delete and other statements. Second, we recommend that you store the monitoring results in a table to facilitate regular analysis and statistics.

Then fill in the <Event Selection> item and select <deadlock graph> Events. You do not need to check the other items and click "run" to start monitoring.

You can use examples commonly used in 10 thousand years to check whether monitoring is normal. Open three query windows and run the statements in the following order. Resource occupation and mutual exclusion will occur, resulting in deadlocks. After completing step 1, A deadlock occurs when you wait for 1 to 3 seconds. The script provides the following:

-- Session 1 create table Test_DL (id int not null primary key, name varchar (100); insert into Test_DL (id, name) select 1, 'a '; insert into Test_DL (id, name) select 2, 'B '; -- session2 2 2 2 2 2 2 2 2 2 begin transactionupdate Test_DL SET Name = 'a-test' where id = 1 -- session3 3 3 3 3 3 3 3 3 3 begin transactionupdate Test_DL SET Name = 'B-test' WHERE ID = 2 -- session2 2 2 2 2 2 2 2 2 SELECT * FROM Test_DL WHERE ID = 2 -- session3 3 3 3 3 3 3 3 3 3 SELECT * FROM Test_DL where id = 1 simulate a deadlock SQL statement

The monitored deadlock interface is as follows:

 

How to Use Extended Events monitoring?

The script for setting up extended event monitoring is as follows: (Extended events are awesome. Version 2012 supports visualized operations. If you are interested, refer to MSDN for more information: https://msdn.microsoft.com/zh-cn/library/bb630282.aspx)

CREATE EVENT SESSION [DeadLock] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'F:\events\deadlock\deadlock.xel',max_file_size=(20)),ADD TARGET package0.ring_buffer(SET max_events_limit=(100),max_memory=(10240),occurrence_number=(50))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)GO

The query SQL statement is as follows. Note: whether the query is based on buffer or filer analysis. Generally, the number of buffer storage is limited. For example, we allocated only 4 MB of storage, file analysis is complete, but it depends on the number of files retained. Here we provide the buffer query SQL as follows. If you are interested in file query, you can write it down.

DECLARE @deadlock_xml XMLSELECT @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)') hostnameFROM @deadlock_xml.nodes('//event') as tb(col)

This SQL statement can be used to query detailed resource competition conditions. If you want to effectively use extended events, it is recommended that you view the xml syntax on the official website in detail (SQL SERVER also has great support for xml, and we look forward to the json support in version 2016)

 

Is it very clear and clear at a glance? With this, you can analyze and pull it!

2 Analysis

You can organize the following information based on the xml file content or the monitoring content of extended events (the deadlock analysis at the beginning ):

 

View the execution plans of transaction 1 and transaction 2 as follows:

 

Combined with tables and execution plans, we can roughly predict the deadlock process:

Session 1:

• Locate the index page Index_Page of the key value based on the primary key SeqCode, find the keyhashvalue line Index_key on the page, hold the IU lock on Index_Page, and hold the U Lock on Index_key;
• Because the table is a heap table, bookmark lookup searches through the RID, that is, through the row identifier to find the data page Data_Page of the row data corresponding to the RID, on the page, locate the row data indicated by the RID pointing to the slot number and hold the U Lock on the row data;
• At this time, you have found the row data to be updated. You can upgrade the IU lock on the data page Data_Page to the IX lock, and the row data pointed by the RID is upgraded from the U lock to the X lock, after the upgrade, release the IU lock and U Lock on the index page and key value line.
• In this case, Session 1 holds the IX lock on Data_Page and the X lock on the row of the RID.

In this process, Session 2 applies for such a lock:

• Find the index of the lock resource held in transaction 2. You can view that 72057594038910976 is the primary key pk_FinanceReceiptNoRule according to sys. partitions, and the primary key column is SeqCode.
• Find the index page Index_Page of the key value based on the primary key SeqCode, find the key value line Index_key on the page, hold the IU lock on Index_Page, and hold the U Lock on Index_key;
• Because the table is a heap table, bookmark lookup searches through the RID, that is, through the row identifier to find the data page Data_Page of the row data corresponding to the RID, then, on the page, locate the row data indicated by the RID pointing to the slot number and prepare the row data to hold the U Lock. However, it is found that the row is held by session 1 with the X lock, this causes it to apply for the U lock Timeout.
  
• In this case, Session 2 holds the IU lock on Index_Page, the U Lock on Index_key, And the IU lock on Data_Page, requesting the U Lock of the RID row.

Assume that at this time, Session 1 executes another update operation (in the same transaction ):

Locate the key value based on the SeqCode of the primary keyIndex page Index_Page. FindKey-value rowIndex_key, which holds the IU lock on Index_Page and prepares to hold the U Lock on Index_key. However, it is found that Index_key is held by session 2.

The deadlock occurs at this time (For details, refer ):

• Session 1 holds the IX lock on Data_Page and the X lock on the row of the RID, apply for the U Lock of Index_key (waiting for session 2 to be released)
• Session 2 holds the IU lock on Index_Page, the U Lock on Index_key, And the IU lock on Data_Page, and requests the U Lock of the RID row (waiting for session 1 To be released)

 

3 Solution

If you try to remove the RID lookup method, you can find the data directly using the index. This deadlock does not occur. That is, you can re-create a clustered index on the primary key to discard the original primary key of the non-clustered index. In this way, the application and hold of the U Lock of the RID are ruled out, and the X lock is directly maintained until the transaction ends. At the same time, you can directly modify the data page of the key value based on the primary key, the time when the RID query row is reduced.

The modified execution plan is as follows:

 

The lock application release process is as follows (see for details ):

• Locate the index page Index_Page of the key value based on the primary key SeqCode, find the keyhashvalue line Index_key on the page, hold the IU lock on Index_Page, and hold the U Lock on Index_key;
• Because the table is already a clustered index table, the page where the primary key is located contains row data, you can directly upgrade the IU lock held on Index_Page to the IX lock, the U Lock held on Index_key is upgraded to the X lock, which avoids applying for a lock for the row-by-row data of the RID.

 

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!