Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to monitor deadlocks in Sql Server. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

The xml file for the deadlock is as follows:

UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE () WHERE IsRunning='1' AND SeqCode=@SeqCode declare @ SeqCode varchar (60) declare @ ReturnNum bigintset @ SeqCode='CGJS20160106'while (1x 1) beginUPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE () WHERE IsRunning='1' AND SeqCode=@SeqCodeend Update dbo.FinanceReceiptNoRule Set [IsRunning] ='1' where SeqCode=@SeqCode and IsRunning='0' declare @ SeqCode varchar (60) declare @ ReturnNum bigintset @ SeqCode='CGJS20160106'while (1x 1) beginUpdate dbo.FinanceReceiptNoRule Set [IsRunning] ='1' where SeqCode=@SeqCode and IsRunning='0' end

The table structure and simulation data are as follows:

-- involving forms: 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-- data simulation INSERT [dbo]. [FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime] [Prefix]) VALUES (Native TEST20150108, 1469, Noble 20150108, Numb0, CAST (Noble 2015-01-08 05CAST 05CAST AS DateTime), NumberTEST') GOINSERT [dbo]. [FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (Native TEST20150109, 1377, Noble 20150109, Nissan 20150109, Native, CAST (Noble 2015-01-09 04, 50, 26.610 AS DateTime) GO ALTER TABLE [dbo]. [FinanceReceiptNoRule] ADD CONSTRAINT [pk_FinanceReceiptNoRule] 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 several ways to capture deadlocks. Here are two ways to capture deadlocks: SQL SERVER Profiler tools and Extended Events. Profiler consumes relatively resources, but because it only monitors deadlocks, the performance impact is not great, and its visual interface is easy to use; Extended Events consumes less resources and records to the penultimate deadlock in real time, and SQL statements are needed to analyze the query record file.

How to use Profiler monitoring?

Open SSMS, click, and select, as shown below.

Log in to the DB instance to be monitored, and fill in the corresponding tracking properties, starting with the page, as shown in the following figure. We should pay attention to two aspects here: first, select a template, which can be used to monitor deadlocks and observe lock application and release, which is very detailed. If you have nothing to do, you can take a look at the lock application and release by statements such as SELECT UPDATE DELETE. Second, the monitoring results are stored and recommended to be stored in a table to facilitate regular analysis and statistics.

Then fill in the entry, just select Events, nothing else needs to be ticked, and finally click run to start monitoring.

You can use a ten-thousand-year-old example to check whether the monitoring is normal. If you open three query windows and execute them in the following order, there will be a deadlock caused by resource consumption and mutual exclusion of applications. After step 5, deadlock occurs after waiting for 1-3 seconds. The script is provided as follows:

-- session 1CREATE TABLE Test_DL (id int not null primary key, name varchar); INSERT INTO Test_DL (id,name) select 1, insert INTO Test_DL (id,name) select 2 -- session2 2 2 2 BEGIN TRANSACTIONUPDATE Test_DL SET Name='a-test' WHERE ID=1--session3 3 3 3 BEGIN TRANSACTIONUPDATE Test_DL SET Name='b-test' WHERE ID=2--session2 2 2 2 SELECT * FROM Test_DL WHERE ID=2--session3 3 3 3 SELECT * FROM Test_DL WHERE ID=1 simulated deadlock SQL

The monitored deadlock interface is as follows:

How to use Extended Events monitoring?

The script for establishing extended event monitoring is as follows: (extended events are great. Version 2012 supports visual operations. Those who are interested can go to MSDN to learn: https://msdn.microsoft.com/zh-cn/library/bb630282.aspx, this article will not analyze grammar and other knowledge points.)

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 is as follows. Note here: generally, the number of buffer storage is limited whether the query is based on buffer or filer analysis. For example, we only allocated 4m storage above, while file analysis is complete, but it depends on the number of files retained. Here we give the query SQL of buffer as follows, and the query of file can be written down if you are interested.

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 can query out very detailed resource contention. If you want to use extension events effectively, it is recommended that you check the xml syntax of the official website (SQL SERVER's support for xml is also very good. Look forward to json support in version 2016).

Is not very clear, at a glance, with this can be analyzed pull!

2 Analysis

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

View the execution plan for transaction 1 and transaction 2 as follows:

Combined with the table and the implementation plan, we can roughly speculate about the deadlock process:

Session 1:

Find the index page Index_Page where the key value is located according to the primary key SeqCode, find the keyhashvalue key value line Index_key on the page, hold the IU lock for Index_Page and hold the U lock for Index_key. Because the table is a heap table, bookmark lookup uses RID lookup, that is, through row identifier lookup, to find the data page Data_Page where the row data corresponding to RID is located, and then find the row data on which RID points to the slot number, and hold a U lock for that row data. At this time, you have found the row data that needs to be updated, you can upgrade the IU lock on the data page Data_Page to IX lock, and the row data pointed to by RID is upgraded from U lock to X lock. After the upgrade, release the IU lock and U lock on the index page and key value row. At this point, session 1 holds the IX lock on the Data_Page and the X lock on the RID line.

In this process, session 2 happens to make such a lock request:

Find out which index holds the lock resource in transaction 2. According 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 the index page Index_Page where the key value is located, find the key value line Index_key on the page, hold the IU lock for Index_Page and hold the U lock for Index_key. Because the table is a heap table, bookmark lookup finds the data page Data_Page where the row data corresponding to RID is located through RID lookup, that is, through row identifier lookup, and then finds the row data on the RID pointing to the slot number, and prepares the row data to hold a U lock, but finds that session 1 holds an X lock on the RID row, resulting in its application for U lock Timeout. At this point, session 2 holds the IU lock on Index_Page, the U lock on Index_key, the IU lock on Data_Page, and requests the U lock of the RID line.

Suppose that at this time, another update operation is performed in session 1 (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 value line Index_key on the page, hold the IU lock for Index_Page, and prepare to hold the U lock for Index_key, but it is found that Index_key is held by session 2.

Then a deadlock occurs at this time (see the following figure for details):

Session 1 holds the IX lock on the Data_Page, the X lock on the RID line, the U lock applying for Index_key (waiting for session 2 to be released) session 2 holds the IU lock on Index_Page, the U lock on Index_key, the IU lock on Data_Page, and requests the U lock of the RID line (waiting for session 1 to be released)

3 solution

Try to get rid of the RID lookup, and index will find the data directly, and the deadlock will not occur, that is, the clustered index will be re-established on the primary key and the original nonclustered index primary key will be discarded. Because this excludes the U lock application and holding of RID, it directly maintains the X lock until the end of the transaction, and the data page where the key value is located can be modified directly according to the primary key, reducing the time of RID query rows.

According to the primary key SeqCode, find the index page Index_Page where the key value is located, find the keyhashvalue key value row Index_key on the page, hold the IU lock for Index_Page, hold the U lock for Index_key; because the table is already an aggregated index table, and the page where the primary key is located contains row data, you can upgrade the IU lock to IX lock directly for Index_Page, and upgrade to X lock for Index_key lock, which avoids the lock application for RID to find row data one by one.

The above is how to monitor deadlocks in Sql Server. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.