激情偷乱人伦小说视,在线最新短片福利

1 背景

1.1 報(bào)警情況

最近整理筆記，打算全部遷移到EVERNOTE。整理到鎖這一部分，里邊剛好有個(gè)自己記錄下來的案例，重新整理分享下給大家。

某日中午，收到報(bào)警短信，DB死鎖異常，單分鐘死鎖120個(gè)。

死鎖的xml文件如下：

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 - 查詢" 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="0x020000003acf4f010561e479685209fb09a7fd15239977c60000000000000000000000000000000000000000">
UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode /frame>
/executionStack>
inputbuf>
declare @SeqCode varchar(60)
declare @ReturnNum bigint
set @SeqCode='CGJS20160106'
while(1=1)
begin
UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode
end /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="suspended" 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 - 查詢" 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="0x020000001b4f23368af7bba99098c10dec46585804f1b4ce0000000000000000000000000000000000000000">
Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' /frame>
/executionStack>
inputbuf>
declare @SeqCode varchar(60)
declare @ReturnNum bigint
set @SeqCode='CGJS20160106'
while(1=1)
begin
Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' 
end
/inputbuf>
/process>
/process-list>
resource-list>
ridlock fileid="1" pageid="1541136" dbid="13" objectname="fin_test.dbo.FinanceReceiptNoRule" id="lock51e8a3980" mode="X" associatedObjectId="72057594040025088">
owner-list>
owner id="process18fd5d8cf8" mode="X" />
/owner-list>
waiter-list>
waiter id="process810b00cf8" mode="U" requestType="wait" />
/waiter-list>
/ridlock>
keylock hobtid="72057594040090624" dbid="13" objectname="fin_test.dbo.FinanceReceiptNoRule" 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>

表格結(jié)構(gòu)跟模擬數(shù)據(jù)如下：

--涉及表格：
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
--數(shù)據(jù)模擬
INSERT [dbo].[FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (N'TEST20150108', 1469, N'20150108', N'0', CAST(N'2015-01-08 05:05:49.163' AS DateTime), N'TEST')
GO
INSERT [dbo].[FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (N'TEST20150109', 1377, N'20150109', N'0', CAST(N'2015-01-09 04:50:26.610' AS DateTime), N'TEST')
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 如何監(jiān)控

捕獲死鎖有多種方式可以捕獲，這里介紹2種：SQL SERVER Profiler工具跟Extended Events。Profiler相對(duì)比較耗資源，但是由于只監(jiān)控死鎖這一項(xiàng)，所以性能影響不是很大，其可視化界面較易上手；Extended Events耗費(fèi)資源較少，實(shí)時(shí)記錄到倒數(shù)第二個(gè)死鎖，同時(shí)需要SQL語(yǔ)句來分析查詢記錄文件。

如何使用 Profiler監(jiān)控？

打開 SSMS，點(diǎn)擊工具>，選擇 SQL Server Profiler>，如下圖。

登錄到需要監(jiān)控的DB實(shí)例，填寫相應(yīng)的跟蹤屬性，首先是常規(guī)>頁(yè)面,如下圖。這里注意2個(gè)方面，第一，選擇 TSQL-Locks>模板，這個(gè)模板即可以用來監(jiān)控死鎖，也可以拿來觀察鎖申請(qǐng)與釋放情況，非常詳細(xì)，有事沒事可以多拿來看SELECT UPDATE DELETE等語(yǔ)句對(duì)鎖的申請(qǐng)及釋放情況；第二，監(jiān)控結(jié)果存儲(chǔ)，建議可以存放到某個(gè)表格中去，方便定期分析與統(tǒng)計(jì)。

接著填寫事件選擇>項(xiàng)，只需要選擇 deadlock graph> Events，其他都不需要打勾，最后點(diǎn)擊運(yùn)行就可以開始監(jiān)控了。

可以用一個(gè)萬(wàn)年常用的例子來檢查是否監(jiān)控正常，開3個(gè)查詢窗口，按照以下順序執(zhí)行則會(huì)發(fā)生資源占用及申請(qǐng)互斥導(dǎo)致死鎖，執(zhí)行完第5步，等待1-3s則發(fā)生死鎖。腳本提供如下：

--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 TRANSACTION
UPDATE Test_DL SET Name='a-test' WHERE ID=1

--session3 3 3 3 3 3 3 3 3 3 
BEGIN TRANSACTION
UPDATE Test_DL SET Name='b-test' WHERE ID=2

--session2 2 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

模擬死鎖SQL

監(jiān)控到的死鎖界面如下：

如何使用Extended Events監(jiān)控？

建立擴(kuò)展事件監(jiān)控的腳本如下：（擴(kuò)展事件很贊，2012版支持可視化操作，感興趣的可以上 MSDN了解:https://msdn.microsoft.com/zh-cn/library/bb630282.aspx，本文就不分析語(yǔ)法等知識(shí)點(diǎn)了）

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

查詢SQL如下，這里需要注意：查詢是基于buffer還是基于filer分析，一般buffer存儲(chǔ)的個(gè)數(shù)都是有限的，比如上文我們只分配了4M存儲(chǔ)，file分析則是完整的，但是要看保留的文件個(gè)數(shù)。這里我們給出buffer的查詢SQL如下，file的查詢大家感興趣的可以動(dòng)手寫下。

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)

這個(gè)SQL可以查詢的出非常詳細(xì)的資源爭(zhēng)奪情況，如果想要有效的使用擴(kuò)展事件，建議大家詳細(xì)查看下官網(wǎng)的xml語(yǔ)法（SQL SERVER對(duì)xml的支持也是棒棒噠，期待2016版中的json支持）

是不是很清晰，一目了然，有了這個(gè)就可以去分析拉！

2 分析

根據(jù)xml文件內(nèi)容或者擴(kuò)展事件的監(jiān)控內(nèi)容，都可以整理為以下信息（開頭的那個(gè)死鎖分析）：

查看事務(wù)1及事務(wù)2的執(zhí)行計(jì)劃如下：

結(jié)合表格及執(zhí)行計(jì)劃，可以大致推測(cè)死鎖過程：

會(huì)話1：

根據(jù)主鍵SeqCode查找到鍵值所在的索引頁(yè) Index_Page，找到該頁(yè)上面的 keyhashvalue 鍵值行 Index_key，對(duì)Index_Page持有IU鎖，對(duì)Index_key持有U鎖；
由于該表是堆表，bookmark lookup是通過 RID查找，即通過行標(biāo)識(shí)符查找，找到RID所對(duì)應(yīng)的行數(shù)據(jù)所在的數(shù)據(jù)頁(yè) Data_Page，然后在該頁(yè)面上找到RID指向槽號(hào)上的行數(shù)據(jù)，對(duì)該行數(shù)據(jù)持有U鎖；
這個(gè)時(shí)候，已經(jīng)查找到了需要更新的行數(shù)據(jù)，可以把數(shù)據(jù)頁(yè) Data_Page上的IU鎖升級(jí)為IX鎖，RID指向的行數(shù)據(jù) 從U鎖升級(jí)為X鎖，升級(jí)結(jié)束后，釋放索引頁(yè)跟鍵值行上面的 IU鎖及U鎖。
則此時(shí)，會(huì)話1 持有 Data_Page 上的IX鎖、RID行上的 X鎖.

這個(gè)過程中，剛好會(huì)話2進(jìn)行這樣的鎖申請(qǐng)：

找出事務(wù)2中持有鎖資源是哪個(gè)索引，可以根據(jù)sys.partitions 可以查看到72057594038910976是主鍵pk_FinanceReceiptNoRule，主鍵列是：SeqCode。
根據(jù)主鍵SeqCode查找到鍵值所在的索引頁(yè) Index_Page，找到該頁(yè)上面的鍵值行 Index_key，對(duì)Index_Page持有IU鎖，對(duì)Index_key持有U鎖；
由于該表是堆表，bookmark lookup是通過 RID查找，即通過行標(biāo)識(shí)符查找，找到RID所對(duì)應(yīng)的行數(shù)據(jù)所在的數(shù)據(jù)頁(yè) Data_Page，然后在該頁(yè)面上找到RID指向槽號(hào)上的行數(shù)據(jù)，準(zhǔn)備該行數(shù)據(jù)持有U鎖，但是發(fā)現(xiàn)RID行上被會(huì)話1持有了X鎖，導(dǎo)致其申請(qǐng) U鎖 Timeout。
則此時(shí) 會(huì)話2 持有 Index_Page上的IU鎖、Index_key上的U鎖、Data_Page上的IU鎖，請(qǐng)求 RID行的 U鎖。

假設(shè)這個(gè)時(shí)候，會(huì)話1 中又執(zhí)行了一次update操作（同一個(gè)事務(wù)中）：

根據(jù)主鍵SeqCode查找到鍵值所在的 索引頁(yè) Index_Page，找到該頁(yè)上面的 鍵值行 Index_key，對(duì)Index_Page持有IU鎖，準(zhǔn)備對(duì)Index_key持有U鎖，但是發(fā)現(xiàn) Index_key被會(huì)話2持有了U鎖。

那么這個(gè)時(shí)候死鎖就產(chǎn)生了(詳見下圖)：

會(huì)話1 持有 Data_Page 上的IX鎖、RID行上的 X鎖，申請(qǐng) Index_key 的U鎖（等待會(huì)話2釋放）
會(huì)話2 持有 Index_Page上的IU鎖、Index_key上的U鎖、Data_Page上的IU鎖，請(qǐng)求 RID行的 U鎖（等待會(huì)話1釋放）

3 解決

想法子除去RID查找，直接index就找到數(shù)據(jù)，就不會(huì)發(fā)生這個(gè)死鎖，也就是，在主鍵上面重新建立聚集索引，丟棄原先的非聚集索引主鍵。因?yàn)檫@樣排除了RID的U鎖申請(qǐng)與持有，直接是保持X鎖直至事務(wù)結(jié)束，同時(shí)可以直接根據(jù)主鍵來修改鍵值所在的數(shù)據(jù)頁(yè)，減少的RID查詢行的時(shí)間。

修改后的執(zhí)行計(jì)劃如下：

其鎖申請(qǐng)釋放的流程如下(詳見截圖)：

根據(jù)主鍵SeqCode查找到鍵值所在的索引頁(yè) Index_Page，找到該頁(yè)上面的 keyhashvalue 鍵值行 Index_key，對(duì)Index_Page持有IU鎖，對(duì)Index_key持有U鎖；
由于該表已經(jīng)是聚集索引表，主鍵所在的頁(yè)上包含行數(shù)據(jù)，則可以直接對(duì)Index_Page持有IU鎖升級(jí)為IX鎖，對(duì)Index_key持有U鎖升級(jí)為X鎖，避免了RID逐個(gè)找行數(shù)據(jù)的鎖申請(qǐng)

以上就是本文的全部?jī)?nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助，同時(shí)也希望多多支持腳本之家！

您可能感興趣的文章:

sql server中死鎖排查的全過程分享
詳解SQL Server中的事務(wù)與鎖問題
sqlserver:查詢鎖住sql以及解鎖方法
SQL Server學(xué)習(xí)筆記之事務(wù)、鎖定、阻塞、死鎖用法詳解
Sql Server如何查看被鎖的表及解鎖的方法
SqlServer查詢和Kill進(jìn)程死鎖的語(yǔ)句
sqlserver進(jìn)程死鎖關(guān)閉的方法
sqlserver鎖表、解鎖、查看銷表的方法
查找sqlserver查詢死鎖源頭的方法 sqlserver死鎖監(jiān)控
SQL Server三種鎖定模式的知識(shí)講解

標(biāo)簽：池州新鄉(xiāng) 文山大同黃山東營(yíng) 來賓濱州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Sql Server 死鎖的監(jiān)控分析解決思路》，本文關(guān)鍵詞 Sql,Server,死鎖,的,監(jiān)控,分析,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。