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

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

左右兩邊兩個橢圓形的是正在存取的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

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

分析問題

我們可以看出有兩個Process試圖存取同一個Table的Key
點選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 跟table做Join來更新資料。
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會事先將兩邊資料進行排序，可以避免此問題，但須經過測試以避免效能問題。