[SQL Server]deadlock in tempdb

2018-01-15

1650
0
SQL_SERVER
2020-12-31

我想很多人都會自行撰寫一些tsql來收集資料庫相關資料，因為只要重新啟動sql service…等，

相關系統統計資料都會消失(執行計畫、DMV…)，同時我們也可以藉由這些資料來定義我們資料庫的基準線(base line)，

但收集資料的TSQL還請謹慎，因為這可能是deadlock在tempdb的主因。

幾個禮拜前，某位朋友詢問是否有空處理一家客戶SQL Server問題，

因為自從它們賣硬體+SQL Server+入門管理課程給這位客戶後，這家客戶的系統發生deadlock很頻繁，

由於當時的入門管理課程，是我去這家客戶進行教育訓練的，業務希望我能幫忙看看，

我先請對方窗口把最近8小時所有的deadlock report寄給我(監控deadlock是我入門管理課程其中一小部分)，

沒想到超過90%的deadlock都在tempdb，而主要是因為該客戶自行寫了滿多收集資料庫的TSQL，

並透過Agent job每60秒重複執行，但開發人員使用temp table是很平常的事情，

下面我簡單重現這deadlock。

Session 1(模擬開發人員的SP邏輯)
while(1=1)
begin
create table #mytest
(	
c1 int
,c2 varchar(10)
,c3 varchar(max)
,c4 nvarchar(max)
)
insert into #mytest select 1,'rico',REPLICATE('ricoisme',100),REPLICATE('sherryis',100)
drop table #mytest
end

session2(模擬自行收集資料庫資料邏輯)
declare @dbfilestats table
(
dbsize bigint
,logsize  bigint
,ftsize bigint
);

declare @allocateUnits table( 
        total_pages bigint
,       used_pages bigint
,       data_pages bigint
,       container_id bigint
,       type tinyint
); 
while(1=1)
begin

insert @dbfilestats 
SELECT  SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      , SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      , SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files --with(nolock);

INSERT @allocateUnits SELECT total_pages, used_pages, data_pages, container_id, type 
FROM sys.allocation_units; --with(nolock);

end

同時執行這兩條session後，你很快會發生deadlock錯誤訊息

查看deadlock report

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph 
FROM ( SELECT XEvent.query('.') AS XEvent 
       FROM ( SELECT CAST(target_data AS XML) AS TargetData 
              FROM sys.dm_xe_session_targets st 
                   JOIN sys.dm_xe_sessions s 
                   ON s.address = st.event_session_address 
              WHERE s.name = 'system_health' 
                    AND st.target_name = 'ring_buffer' 
              ) AS Data 
              CROSS APPLY 
                 TargetData.nodes 
                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent ) 
      ) AS src;

我擷取重點如下

process4b5aca8 waitresource="KEY: 2:327680 (0c1b2be9e892)" transactionname="CRowsetALUs::GetRow"

isolationlevel="read committed (2)"

process4c2dc28 waitresource="KEY: 2:458752 (d7d9f51f27b5)" transactionname="DROPOBJ"

isolationlevel="read committed (2)"

可以看到，雖然交易都是等待不同的resource，但還是有可能發生deadlock，resource-list可以讓我們更清楚deadlock主因。

<resource-list>
    <keylock hobtid="327680" dbid="2" objectname="tempdb.sys.sysrowsets" indexname="clust" id="lock36d03e700" mode="X" associatedObjectId="327680">
      <owner-list>
        <owner id="process4c2dc28" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process4b5aca8" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="458752" dbid="2" objectname="tempdb.sys.sysallocunits" indexname="clust" id="lock30682c900" mode="U" associatedObjectId="458752">
      <owner-list>
        <owner id="process4b5aca8" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process4c2dc28" mode="X" requestType="convert" />
      </waiter-list>
    </keylock>
  </resource-list>

Object都是SQL Server內部的系統資料表，但因為所有process都在異動tempdb的物件，

所以這些資訊都需要連動寫入內部的系統資料表(sys.sysrowsets和sys.sysallocunits)，

導致process4c2dc28->等待process4b5aca8釋放tempdb.sys.sysallocunits的s lock，

而process4b5aca8->也在等待process4c2dc28釋放sys.sysrowsets的x lock，

於是process4c2dc28-> process4b5aca8-> process4c2dc28就形成了deadlock。

解決的方法很簡單，我建議針對這些查詢system table都改用uncommitted isolation level即可，

避免select這些系統資料表放上s lock，而可能影響到主要系統交易，

後來我也有建議客戶可建立DC來取代這些自行開發的輪子(如果用SQL2016，可以使用query store來保留這些資訊)，

雖然使用DC也有可能會發生deadlock，但依照以前的經驗，往往犧牲的都是DC的內部交易，

所以我們大可忽略這些deadlock message，減少我們管理SQL Server負擔。

參考

System Base Tables