SQL SERVER死鎖該如何深度分析

這篇文章將為大家詳細講解有關SQL SERVER死鎖該如何深度分析，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

說道SQL SERVER ，其實這個數據庫在企業中是廣泛使用的，這就必須提到為什么企業廣泛使用SQL SERVER ，.NET  VS  JAVA ，在開發中我們目前有兩大陣營，.NET 陣營和 JAVA 陣營，而一般來說使用.NET 開發是很愿意把 SQL SERVER 作為首選數據庫的， 尤其是在中小型企業中，開發快，出色的性能，以及各種漂亮的圖形化，讓開發和管理，變得很簡單，在講求效率的今天，企業中還是有一大部分使用.NET 及 SQL SERVER 來滿足企業的各種需求。

今天想說的是SQL SERVER 中程序員，DBA ，所有人都不愿意看見的死鎖，DEAD LOCK。 出現這個情況，說明我們的整個系統在設計，以及硬件方面都可能存在一些，問題。所以我們必須要搞清楚死鎖為什么會形成。

為了讓說明變得簡單，我這邊需要創建一些表

CREATE TABLE Countrys

    (

             CountryID INT NOT NULL IDENTITY(1, 1)

            CONSTRAINT PK_Country PRIMARY KEY,

            CountryName VARCHAR(100),

            PopulationSize INT NOT NULL

    )

CREATE TABLE Citys

    (

            CityID INT NOT NULL IDENTITY(1, 1)

            CONSTRAINT PK_City PRIMARY KEY,

            CountryID INT NOT NULL,

           CityName VARCHAR(100) NOT NULL,

           PopulationSize INT NOT NULL,

        CountOfHospitals INT NOT NULL,

        SomeProperty1 VARCHAR(100) NOT NULL,

        SomeProperty2 VARCHAR(100) NOT NULL,

        SomeProperty3 VARCHAR(100) NOT NULL,

        SomeProperty4 VARCHAR(100) NOT NULL,

        SomeProperty5 VARCHAR(100) NOT NULL,

        SomeProperty6 VARCHAR(100) NOT NULL,

        SomeProperty7 VARCHAR(100) NOT NULL,

        SomeProperty8 VARCHAR(100) NOT NULL,

        SomeProperty9 VARCHAR(100) NOT NULL,

        SomeProperty10 VARCHAR(100) NOT NULL,

        SomeProperty11 VARCHAR(100) NOT NULL,

        SomeProperty12 VARCHAR(100) NOT NULL,

        SomeProperty13 VARCHAR(100) NOT NULL,

        SomeProperty14 VARCHAR(100) NOT NULL,

        SomeProperty15 VARCHAR(100) NOT NULL,

        SomeProperty16 VARCHAR(100) NOT NULL,

        SomeProperty17 VARCHAR(100) NOT NULL,

        SomeProperty18 VARCHAR(100) NOT NULL,

        SomeProperty19 VARCHAR(100) NOT NULL,

        SomeProperty20 VARCHAR(100) NOT NULL,

        SomeProperty21 VARCHAR(100) NOT NULL,

        SomeProperty22 VARCHAR(100) NOT NULL,

        SomeProperty23 VARCHAR(100) NOT NULL,

        SomeProperty24 VARCHAR(100) NOT NULL,

        SomeProperty25 VARCHAR(100) NOT NULL,

        SomeProperty26 VARCHAR(100) NOT NULL,

        SomeProperty27 VARCHAR(100) NOT NULL,

        SomeProperty28 VARCHAR(100) NOT NULL,

        SomeProperty29 VARCHAR(100) NOT NULL,

        SomeProperty30 VARCHAR(100) NOT NULL,

        SomeProperty31 VARCHAR(100) NOT NULL,

        SomeProperty32 VARCHAR(100) NOT NULL,

        SomeProperty33 VARCHAR(100) NOT NULL,

        SomeProperty34 VARCHAR(100) NOT NULL,

        SomeProperty35 VARCHAR(100) NOT NULL,

        SomeProperty36 VARCHAR(100) NOT NULL,

        SomeProperty37 VARCHAR(100) NOT NULL,

        SomeProperty38 VARCHAR(100) NOT NULL,

        SomeProperty39 VARCHAR(100) NOT NULL,

        SomeProperty40 VARCHAR(100) NOT NULL,

        CONSTRAINT FK_Country

            FOREIGN KEY (CountryID)

            REFERENCES dbo.Country (CountryID)

    )

GO

CREATE INDEX IDX_City_Not_Covering_Index

    ON dbo.Citys (CountryID)

    INCLUDE (CityName)

GO 

________________________________________________________ 

下面就開始變魔術，請開3個REQUEST 窗口，以 1 ， 2 ，3，來標記語句是在哪個窗口上執行的

1

2

1

2

到目前為止，還么有出現死鎖，（死鎖怎么看，可以通過 EXEVENT 或自身  查看死鎖的腳本來看，后面會給出腳本，EXEVENT  這里就不介紹了，雖然比腳本好用多了）

1  在窗口以繼續執行下面的腳本

INSERT INTO dbo.Citys

VALUES

    (1,          -- CountryID - int

     'New York', -- CityName - varchar(100)

     8538000,    --As Google just said and that's cool!

     1000,       --From the ceiling

        )

2 在窗口2 繼續執行下面的腳本

INSERT INTO dbo.Citys

VALUES

    (2,        -- CountryID - int

     'Delhi',  -- CityName - varchar(100)

     19980000, --As Google just said and that's cool!

     2000,     --From the ceiling

    ) 

1 我們在窗口1  繼續查詢

2 我們在窗口2 也查詢

然后魔術上演，在窗口2 出現死鎖提示

EXEVENT 中也出現了死鎖圖示

估計有些SQL SERVER DBA 已經清晰的明白這里發生了什么，為什么會死鎖？ 如果已經明白了，那就真的么有必要往下看了。

————————————————————————————

死鎖發生在給CITYS 表插入記錄的時候，當插入CITYS 表  "NEW YORK“的時候，給這一行加了 X 鎖，（行鎖） ，當我在插入CITYS 表 “Delhi" 時,給Delhi 這一行（應該是第二行加上了X鎖） 到目前為止，還算相安無事。

但我馬上做了一個糟糕的操作，我查詢了CITY 表，雖然我建立了索引，（這里買一個關子，好好想想，我還是用上面的操作，只需要改一個地方，就可以避免這次死鎖，和查詢的方式有關）

，這是就加上了 S 鎖，而不巧的是，這個S鎖也鎖住了 "NEW YORK"和Delhi,然后就是觸發死鎖的發生最后一步，我查詢 Delhi ， 而這個查詢也要給 "NEW YORK" 上一個 S 鎖，好了，由于之前每行都有 X 鎖，所以雖然行鎖可以避免對本行的 S 鎖產生等待，但不能對其他行避免等待，所以兩次查詢，變成了死鎖，互相等待。（估計我說完了，有人已經暈了，畫張圖，馬上就明白了）

看完圖應該明白，為什么產生死鎖，也明白本星期四某些故障的原因，和這個很類似，所以某些時候，并發害死人呀

其實要解決這個問題，也很簡單，但又不簡單，尤其在一個復雜的系統中，串行不出問題，不代表并行不會出現問題，大量的死鎖就是這么“神奇”的出現。

最后，可以通過以下腳本來，查看某些死鎖，或者鎖授予的情況。

SELECT OBJECT_NAME(p.object_id) AS TableName,

       dtl.resource_type,

       dtl.resource_description,

       --c.CityName AS Locked_Row,

       --c.CityID AS Locked_Row_ID,

       dtl.request_mode,

       dtl.request_type,

       dtl.request_status,

       dtl.request_session_id,

       dtl.request_owner_type,

       dtl.request_owner_id

FROM   sys.dm_tran_locks AS dtl

LEFT JOIN sys.partitions AS p

    ON p.hobt_id = dtl.resource_associated_entity_id

關于SQL SERVER死鎖該如何深度分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。