2017年4月28日 星期五

[SQL Server] Deadlock案例分析 : 透過Join user-defined table type 來更新資料所引發的死結

在前幾個禮拜查看deadlock extended event時,發現有一段更新的語法被當作Victim(受害者)給放棄掉了,由於此段語法是重要的商業邏輯,且AP端沒有再次做Retry
立馬拉出deadlockgraphxml查看其發生的原因,如下圖:




左右兩邊兩個橢圓形的是正在存取的
Process
Deadlock priority: SQL Server會根據這個數值先把Proiority較低的當做Victim,如果都一樣的話則會放棄佔用資源較少的那個Process
 
中間的兩個正方形的則是被 Process佔有或者意圖存取的物件:
Hobt_Id : Heap or B-tree index Id,我們可以透過以下語法查到其對應的名稱:
SELECT o.name,p.index_id, i.*
FROM sys.partitions p
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.indexes i ON I.object_id = OBJECT_ID(o.[Name])
WHERE p.hobt_id = 72057594053853184




串連起整張圖的四條線是Process存取物件的方法
Owner mode : 已經在物件上佔用的Lock mode
Request mode : 意圖要在物件上放置的Lock mode

而後面的U跟X後面會提到,它們代表的是鎖的類型。

分析問題


我們可以看出有兩個Process試圖存取同一個TableKey
點選extended evnet裡面所提供的XML,我們可以獲得更完整的資訊。

 



我發現兩個Process都是指到同一個Procedure裡面的同一段Update語法
Procedure的內容呈現出來,內容如下:
 
ALTER PROC dbo.AccmulateStock
    @par1 dbo.uftt_Stcok READONLY
AS
BEGIN
   UPDATE T1
   SET T1.TotalStock = T1.TotalStock + T2.Stock
   FROM dbo.TestAccumulator T1
   INNER JOIN @par1 t2
       ON t1.ID = t2.ID
END

這段Proc很單純的透過傳入的User-defined table type tableJoin來更新資料。
User-defined table type的定義如下:

CREATE TYPE uftt_Stcok AS TABLE
(
    ID INT,
    Stock DECIMAL(19,2)
)

通常Deadlock都是發生在存取物件順序不一致所導致的,但這邊的動作只有一段更新存取同一個物件。
查看SPID/SBID不是相同的,也並非平行處理所造成的。

所以我懷疑是上面所帶進來的參數: @par1 dbo.uftt_Stcok READONLY
資料順序不一致所導致。

這邊我們回頭來看一下上面Deadlock report所提到的兩個Lock mode :
U(Update lock)
X(Exclusive lock)

當一段語法如Update .... where時,會先放置Shared Lock來查詢要更新的區塊,準備要更新時會將其轉為Update Lock,告訴大家: "我準備要做Update了",由於Update lock Update lock是互斥的,此時其他Process就不能"準備更新"同區間的資料,其目的也就是為了避免deadlock
最後真正在更新時就會放上Exclusive lock(X)exclusive shared lock是互斥的,這樣就可以保護其他Process讀到髒資料了
其相容性如下:

Existing granted mode


Requested mode
S
U
X
S
相容
相容
互斥
U
相容
互斥
互斥
X
互斥
互斥
互斥

所以我們假設遇到的狀況應該如下:
 

為了驗證我的假說,故意模擬AP端傳送的資料一個是正著排下來的ID,而另一個則是倒著排的。
我們透過以下語法建置table, type, procedure,並塞入262144筆資料(沒特別意義,純粹用次方的方法塞資料比用Cursor一筆一筆塞還要快)
 
USE [TestDB]

IF (NOT EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
    AND  TABLE_NAME = 'TestAccumulator'))
BEGIN
    CREATE TABLE dbo.TestAccumulator
    (
        ID INT IDENTITY(1,1) NOT NULL,
        TotalStock DECIMAL(19,2),
        CONSTRAINT PK_TestAccumulator PRIMARY KEY (ID)
    )
END

TRUNCATE TABLE dbo.TestAccumulator

INSERT INTO dbo.TestAccumulator
(
    TotalStock
)
SELECT RAND() * 1000000
GO
INSERT INTO dbo.TestAccumulator
(
    TotalStock
)
SELECT RAND() * 1000000
FROM dbo.TestAccumulator
GO 18

--2^18 Data
SELECT COUNT(*) FROM dbo.TestAccumulator

IF NOT EXISTS (
    SELECT * FROM sys.types
    WHERE is_table_type = 1 AND name = 'uftt_Stcok'
)
BEGIN
    CREATE TYPE uftt_Stcok AS TABLE
    (
        ID INT,
        Stock DECIMAL(19,2)
    )
END
GO

if object_id('dbo.AccmulateStock', 'p') is null
    exec ('create procedure AccmulateStock as select 1')
go
ALTER PROC dbo.AccmulateStock
    @par1 dbo.uftt_Stcok READONLY
AS
BEGIN
   UPDATE T1
   SET T1.TotalStock = T1.TotalStock + T2.Stock
   FROM dbo.TestAccumulator T1
   INNER JOIN @par1 t2
       ON t1.ID = t2.ID
END


接下來我們另外開啟
Session 1 (User defined table type 的資料正著排序) 並執行:
 
IF OBJECT_ID('tempdb..##Flag') IS NOT NULL
BEGIN
    DROP TABLE ##Flag
END
CREATE TABLE ##Flag
(
    IsStarted BIT
)
INSERT INTO ##Flag SELECT 0

DECLARE @t1 dbo.uftt_Stcok;
INSERT INTO @t1
SELECT TOP 150000 ID, 1
FROM dbo.TestAccumulator


WHILE(1 = 1)
BEGIN
    IF EXISTS(SELECT 1 FROM ##Flag WHERE IsStarted = 1)
    BEGIN
        EXEC dbo.AccmulateStock @t1
        BREAK;
    END
END

為了方便模擬兩個Session幾乎同時執行Stored Procedure,我這邊透過global temp table: ##Flag來當做Flag做流程控制,讓Session 1 While迴圈等待Session 2開始執行時才去同時執行。

接下來我們執行  Session 2 (User defined table type 的資料倒著排序):
DECLARE @t1 dbo.uftt_Stcok;
INSERT INTO @t1
SELECT TOP 150000 ID, 1
FROM dbo.TestAccumulator
ORDER BY ID DESC

UPDATE ##Flag SET IsStarted = 1

EXEC dbo.AccmulateStock @t1

果然如我們所願發生了Deadlock:

 



我們透過語法查看其背後的Cache plan:
 
SELECT Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans t1
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t2
CROSS APPLY sys.dm_exec_query_plan(plan_handle) t3
where t2.objectid = object_id('dbo.AccmulateStock', 'p')
 



我們可以發現原因就是因為:SQL Server Query optimizer無法判斷我們傳進來的User defined table type每行row的內容,只能知道它的count,所以選擇了Loop Join,由於Loop join的演算法就像是兩層for迴圈的查找方式,而count較少的user defined table type 被當做外層,接下來在逐筆的進去Table :TestAccumulator 做查找,寫成示意的code大致如下:
 
For each id i1 in @par do
     For each id i2 in TestAccumulator do
        If i1 and i2 satisfy the join condition
           Then output the this record

這樣兩個Process如果是相反排序的話,就會發生deadlock的問題.


解決方法

 
1. AP端在資料存取層事先將資料排序
2. 透過在User-defined table 定義Clustered index來將資料事先排序

CREATE TYPE [dbo].[uftt_Stcok] AS TABLE(
    [ID] [int] NOT NULL,
    [Stock] [decimal](19, 2) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
)
但需注意AP端傳進來的ID不得重複。

3. 透過Hint :  INNER MERGE JOIN 來避免存取順序問題:
 
if object_id('dbo.AccmulateStock', 'p') is null
    exec ('create procedure AccmulateStock as select 1')
go
ALTER PROC dbo.AccmulateStock
    @par1 dbo.uftt_Stcok READONLY
AS
BEGIN
   UPDATE T1
   SET T1.TotalStock = T1.TotalStock + T2.Stock
   FROM dbo.TestAccumulator T1
   INNER MERGE JOIN @par1 t2
       ON t1.ID = t2.ID
END


由於Merge Join會事先將兩邊資料進行排序,可以避免此問題,但須經過測試以避免效能問題。

沒有留言:

張貼留言