背景

       當(dāng)用戶(hù)并發(fā)嘗試訪問(wèn)同一數(shù)據(jù)的時(shí)，SQL Server嘗試用鎖來(lái)隔離不一致的數(shù)據(jù)和使用隔離級(jí)別查詢(xún)數(shù)據(jù)時(shí)控制一致性(數(shù)據(jù)該如何讀取)，說(shuō)起鎖就會(huì)聯(lián)想到事務(wù)，事務(wù)是一個(gè)工作單元，包括查詢(xún)／更新數(shù)據(jù)和數(shù)據(jù)定義。

鎖

鎖類(lèi)型

在SQL Server中，根據(jù)資源的不同，鎖分為以下三種類(lèi)型：
    行鎖：是SQL Server中數(shù)據(jù)級(jí)別中粒度最小的鎖級(jí)別，行鎖根據(jù)表是否存在聚集索引，分為鍵值鎖和標(biāo)識(shí)鎖
    頁(yè)鎖：針對(duì)某個(gè)數(shù)據(jù)頁(yè)添加的鎖，在T-SQL語(yǔ)句中，使用了頁(yè)鎖就不會(huì)在使用相同類(lèi)型的行鎖，反之依然，在對(duì)數(shù)據(jù)頁(yè)加鎖后，無(wú)法在對(duì)其添加不兼容的鎖
    表鎖：添加表鎖則無(wú)法添加與其不兼容的頁(yè)?鎖和行鎖

鎖模式

   共享鎖(S)：發(fā)生在數(shù)據(jù)查找之前，多個(gè)事務(wù)的共享鎖之間可以共存
   排他鎖(X)：發(fā)生在數(shù)據(jù)更新之前，排他鎖是一個(gè)獨(dú)占鎖，與其他鎖都不兼容
   更新鎖(U)：發(fā)生在更新語(yǔ)句中，更新鎖用來(lái)查找數(shù)據(jù)，當(dāng)查找的數(shù)據(jù)不是要更新的數(shù)據(jù)時(shí)轉(zhuǎn)化為S鎖，當(dāng)是要更新的數(shù)據(jù)時(shí)轉(zhuǎn)化為X鎖
   意向鎖：發(fā)生在較低粒度級(jí)別的資源獲取之前，表示對(duì)該資源下低粒度的資源添加對(duì)應(yīng)的鎖，意向鎖有分為：意向共享鎖(IS) ，意向排他鎖(IX)，意向更新鎖(IU)，共享意向排他鎖(SIX)，共享意向更新鎖(SIU)，更新意向排他鎖(UIX)
   共享鎖／排他鎖／更新鎖一般作用在較低級(jí)別上，例如數(shù)據(jù)行或數(shù)據(jù)頁(yè)，意向鎖一般作用在較高的級(jí)別上，例如數(shù)據(jù)表或數(shù)據(jù)。鎖是有層級(jí)結(jié)構(gòu)的，若在數(shù)據(jù)行上持有排他鎖的時(shí)候，則會(huì)在所在的數(shù)據(jù)頁(yè)上持有意向排他鎖. 在一個(gè)事務(wù)中，可能由于鎖持有的時(shí)間太長(zhǎng)或個(gè)數(shù)太多，出于節(jié)約資源的考慮，會(huì)造成鎖升級(jí)
   除了上述的鎖之外，還有幾個(gè)特殊類(lèi)型的鎖，例如架構(gòu)鎖，架構(gòu)鎖包含兩種模式，架構(gòu)穩(wěn)定鎖(Sch-S)和架構(gòu)更新鎖(Sch-M) ,架構(gòu)穩(wěn)定鎖用來(lái)穩(wěn)定架構(gòu)，當(dāng)查詢(xún)表數(shù)據(jù)的時(shí)候，會(huì)對(duì)表添加架構(gòu)穩(wěn)定鎖，防止架構(gòu)發(fā)生改變。當(dāng)執(zhí)行DDL語(yǔ)句的時(shí)候，會(huì)使用架構(gòu)更新鎖，確保沒(méi)有任何資源對(duì)表的占用。大數(shù)據(jù)量的表避免執(zhí)行DDL操作，這樣會(huì)造成架構(gòu)更新鎖長(zhǎng)時(shí)間占用資源，影響其他操作,除非必要不然不要執(zhí)行DDL語(yǔ)句，如在必要的情況下添加字段，需要先給字段初始化，在設(shè)置為非空。

鎖的兼容性

如何查看一個(gè)事務(wù)中所請(qǐng)求的鎖類(lèi)型和鎖的順序，可使用SQL Profiler 查看 Mode 屬性

數(shù)據(jù)準(zhǔn)備

IF OBJECT_ID('dbo.Nums','u') IS NOT NULL    DROP TABLE dbo.Nums;
GOCREATE TABLE dbo.Nums
(     ID INT PRIMARY KEY,     NUM INT);
GO
IF EXISTS(SELECT * FROM SYS.SEQUENCES WHERE OBJECT_ID=OBJECT_ID('dbo.NumSequence'))    DROP SEQUENCE dbo.NumSequence;
GOCREATE SEQUENCE dbo.NumSequence    MINVALUE 1
    MAXVALUE 1000
    NO CYCLEGODECLARE @num AS INT = NEXT VALUE FOR dbo.NumSequenceINSERT INTO dbo.Nums VALUES(@num,@num);
GO 1

運(yùn)行UPDATE dbo.Nums SET Num += 1
查看SQL Profiler 的跟蹤，可以清楚的看到鎖的請(qǐng)求順序和類(lèi)型(請(qǐng)自定配置跟蹤模版，以便于想要看到自己想要的屬性)

事務(wù)的隔離級(jí)別

事務(wù)

事務(wù)是一個(gè)工作單元，包含查詢(xún)／修改數(shù)據(jù)以及修改數(shù)據(jù)定義的多個(gè)活動(dòng)的組合，說(shuō)起事務(wù)就需要提起事務(wù)的四個(gè)基本特性ACID：
   原子性：事務(wù)要么全部成功，要么全部失敗。
   一致性：事務(wù)為提交前或者事務(wù)失敗后，數(shù)據(jù)都和未開(kāi)始事務(wù)之前一致
   隔離性：事務(wù)與事務(wù)之間互不干擾
   持久性：事務(wù)成功后會(huì)被永久保存起來(lái)，不會(huì)在被回滾

隔離級(jí)別

事務(wù)的隔離級(jí)別控制并發(fā)用戶(hù)的讀取和寫(xiě)入的行為，即不同的隔離界別對(duì)鎖的控制方式不一樣，隔離級(jí)別主要分為兩種類(lèi)型：悲觀并發(fā)控制和樂(lè)觀并發(fā)控制，悲觀并發(fā)控制有：READ UNCPOMMITTED ／ READ COMMITTED (會(huì)話(huà)默認(rèn)) ／REPEATABLE READ ／ SERIALIZABLE . 樂(lè)觀并發(fā)控制主要以在Tempdb中創(chuàng)建快照的方式來(lái)實(shí)現(xiàn)，有：SNAPSHOT 和 READ COMMITTED SHAPSHOT,也被稱(chēng)為基于行版本的控制的隔離級(jí)別。

READ UNCOMMITTED

此隔離級(jí)別的主要特點(diǎn)是可以讀取其他事務(wù)中未提交更改的數(shù)據(jù)，該隔離級(jí)別下請(qǐng)求查詢(xún)的數(shù)據(jù)不需要共享鎖，這樣對(duì)于請(qǐng)求的行正在被更改，不會(huì)出現(xiàn)阻塞，這就造成了臟讀.此隔離級(jí)別是最低的隔離級(jí)別，并發(fā)性良好，但是對(duì)于數(shù)據(jù)的一致性方面有缺陷，在一些不重要的查詢(xún)中可以采用這種方式
以上面的表為例，開(kāi)始兩個(gè)會(huì)話(huà)，在會(huì)話(huà)1中運(yùn)行如下代碼：

BEGIN TRAN    UPDATE  dbo.Nums SET NUM = 10
    WHERE ID = 1

開(kāi)啟會(huì)話(huà)2并且運(yùn)行如下代碼：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDGOSELECT * FROM dbo.Nums WHERE ID = 1

查看運(yùn)行結(jié)果
?
在事務(wù)未提交成情況下，卻讀取到了數(shù)據(jù)，這就是臟讀，可以通過(guò)SQL Profiler 查看具體的請(qǐng)求鎖的類(lèi)型和順序。
?

如圖可以看出，對(duì)于會(huì)話(huà)2只請(qǐng)求了架構(gòu)穩(wěn)定鎖(Sch-S) 并未請(qǐng)求共享鎖

READ COMMITTED

此隔離級(jí)別可以看作是對(duì)READ UMCOMMITTED 隔離級(jí)別的升級(jí)，解決帶了臟讀的問(wèn)題，主要方式是對(duì)應(yīng)查詢(xún)數(shù)據(jù)的請(qǐng)求需要先請(qǐng)求共享鎖定，由于鎖之間的兼容性，造成阻塞，但是該模式也會(huì)帶來(lái)一個(gè)問(wèn)題那就是不可重復(fù)讀，在同一事務(wù)中的兩個(gè)相同的查詢(xún) 查出來(lái)的結(jié)果不一致，主要是因?yàn)樵摳綦x級(jí)別對(duì)應(yīng)共享鎖并不會(huì)一致保持，在兩條查詢(xún)語(yǔ)句之間是沒(méi)有鎖存在的，這樣其他事務(wù)就是更新數(shù)據(jù)
以上面的表為例，開(kāi)始兩個(gè)會(huì)話(huà)，在會(huì)話(huà)1中運(yùn)行如下代碼：

BEGIN TRAN    UPDATE  dbo.Nums SET NUM = 10
    WHERE ID = 1

在會(huì)話(huà)2中運(yùn)行如下代碼，該會(huì)話(huà)會(huì)被阻塞

SET TRANSACTION ISOLATION LEVEL READ COMMITTEDGOSELECT * FROM dbo.Nums WHERE ID = 1

打開(kāi)會(huì)話(huà)3運(yùn)行如下語(yǔ)句，查看當(dāng)前阻塞狀態(tài)，連接信息，阻塞語(yǔ)句等其他信息

SELECT request_session_id,resource_type,resource_database_id,DB_NAME(resource_database_id) AS dbname,resource_associated_entity_id,request_mode,request_status FROM sys.dm_tran_locks

運(yùn)行結(jié)果如圖：
?
從圖中可以看出當(dāng)前，會(huì)話(huà)55的請(qǐng)求狀態(tài)為WAIT，也就是阻塞狀態(tài)，圖中54為UPDATE操作的DML正在持有一個(gè)更新鎖(X).進(jìn)一步查看進(jìn)程的相關(guān)信息，運(yùn)行如下代碼

SELECT session_id,most_recent_session_id,connect_time,last_read,last_write, most_recent_sql_handle FROM sys.dm_exec_connections WHERE session_id IN (54,55)

?
可以看到各個(gè)進(jìn)程的連接時(shí)間，最后一次讀取時(shí)間和最后一次寫(xiě)入時(shí)間，和對(duì)應(yīng)的T-SQL語(yǔ)句，要想查看具體的語(yǔ)句信息請(qǐng)運(yùn)行如下代碼

SELECT session_id,text FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS A WHERE session_id IN (54,55)

?
可以具體的查看到執(zhí)行語(yǔ)句，要想知道具體某個(gè)會(huì)話(huà)阻塞原因，即正在等待哪個(gè)會(huì)話(huà)的資源，運(yùn)行如下語(yǔ)句

SELECT session_id,blocking_session_id,command,text,database_id,wait_type,wait_resource,wait_time FROM sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle)WHERE blocking_session_id > 0

?
從圖中可以看出，會(huì)話(huà)55正在等待會(huì)話(huà)54及競(jìng)爭(zhēng)的資源信息，等待類(lèi)型和等待時(shí)間，從上述的語(yǔ)句可以輕松查看想要知道的信息，對(duì)于各個(gè)會(huì)話(huà)對(duì)鎖的請(qǐng)求順序和類(lèi)型請(qǐng)自行查看SQL Profiler.
下面我們來(lái)說(shuō)說(shuō)不可重復(fù)讀的問(wèn)題,新建會(huì)話(huà)1運(yùn)行如下代碼

SET TRANSACTION ISOLATION LEVEL READ COMMITTEDGOBEGIN TRAN    SELECT * FROM dbo.Nums WHERE ID=1
    WAITFOR DELAY '00:00:10'
    SELECT * FROM dbo.Nums WHERE ID=1

新建會(huì)話(huà)2并運(yùn)行如下代碼

BEGIN TRAN    UPDATE dbo.Nums SET NUM+=1 WHERE ID = 1  
    COMMIT TRAN

查看會(huì)話(huà)1的運(yùn)行結(jié)果如圖，從圖中可以看出兩次讀取出來(lái)的數(shù)據(jù)不一致，這就是不可重復(fù)讀
?

REPEATABLE READ

此隔離級(jí)別可以看作的是READ COMMITTED 的升級(jí)，該模式可以解決READ COMMITTED 的不可重復(fù)讀的問(wèn)題，主要是因?yàn)樵摷?jí)別下對(duì)共享鎖的占用時(shí)間較長(zhǎng)，會(huì)一直持續(xù)到事務(wù)的結(jié)束。但是該模式也會(huì)存在一個(gè)叫做幻讀的缺陷，幻讀指的是在查找一定范圍內(nèi)的數(shù)據(jù)時(shí)，其他事務(wù)對(duì)該范圍的數(shù)據(jù)進(jìn)行INSERT操作，導(dǎo)致再次執(zhí)行相同的查詢(xún)語(yǔ)句，查詢(xún)的結(jié)果可能多或者是和第一句不一致，造成幻讀的原因是因?yàn)楸绘i定的數(shù)據(jù)行是在第一次查詢(xún)數(shù)據(jù)時(shí)確定的，對(duì)未來(lái)的數(shù)據(jù)并沒(méi)有鎖。此隔離級(jí)別不建議在更新頻率較高的環(huán)境下使用，會(huì)造成性能不佳
以上面的表為例，打開(kāi)兩個(gè)會(huì)話(huà)，在會(huì)話(huà)1中運(yùn)行下面的代碼：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READGOBEGIN TRAN    SELECT * FROM dbo.Nums WHERE ID=1
    WAITFOR DELAY '00:00:10'
    SELECT * FROM dbo.Nums WHERE ID=1

打開(kāi)會(huì)話(huà)2并且運(yùn)行如下代碼

BEGIN TRAN    UPDATE dbo.Nums SET NUM+=1 WHERE ID = 1  COMMIT TRAN

查看結(jié)果：
?
運(yùn)行過(guò)程中可以發(fā)現(xiàn)UPDATE的DML會(huì)一直等待會(huì)話(huà)1中事務(wù)的提交，并不會(huì)造成不可重復(fù)讀，下面來(lái)演示下幻讀的問(wèn)題，重新打開(kāi)兩個(gè)會(huì)話(huà)，在會(huì)話(huà)1中運(yùn)行下面的代碼：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READGOBEGIN TRAN    SELECT * FROM dbo.Nums 
    WAITFOR DELAY '00:00:10'
    SELECT * FROM dbo.Nums 
COMMIT TRAN

打開(kāi)會(huì)話(huà)2運(yùn)行如下代碼：

BEGIN TRAN    INSERT INTO dbo.Nums VALUES(2,2)COMMIT TRAN

運(yùn)行結(jié)果：
?
會(huì)話(huà)2并沒(méi)有被阻塞，這次查看下會(huì)話(huà)1的運(yùn)行結(jié)果可以看到，讀取出了2行數(shù)據(jù)，被稱(chēng)為幻讀，關(guān)于鎖的請(qǐng)求類(lèi)型和順序請(qǐng)打開(kāi)SQL Profiler 自行查看.

SERIALIZABLE

此隔離級(jí)別可以看作是 REPEADTABLE READ 的升級(jí)，解決了幻讀的問(wèn)題，因?yàn)樵撃Ｊ较虏粌H可以鎖定第一次查詢(xún)的數(shù)據(jù)行，還可以鎖定未來(lái)滿(mǎn)足條件的數(shù)據(jù)行，是一個(gè)區(qū)間鎖的概念，該級(jí)別不會(huì)出現(xiàn)上述的問(wèn)題，但是相對(duì)的代價(jià)就是一致性強(qiáng)犧牲了并發(fā)性
以上表為例，修改會(huì)話(huà)1的隔離級(jí)別為 SERIALIZABLE，代碼如下：

?SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

從結(jié)果可以看到會(huì)話(huà)2一直在等待會(huì)話(huà)1的完成，關(guān)于鎖的請(qǐng)求類(lèi)型和順序請(qǐng)打開(kāi)SQL Profiler 自行查看.

SNAPSHOT

當(dāng)前隔離級(jí)別和接下來(lái)要介紹的隔離級(jí)別都是樂(lè)觀并發(fā)控制的兩種模式，又稱(chēng)行版本控制的隔離級(jí)別，在tempdb中存儲(chǔ)事務(wù)未提交之前的數(shù)據(jù)行，使用基于行版本的控制隔離級(jí)別不會(huì)請(qǐng)求共享鎖，對(duì)于查詢(xún)數(shù)據(jù)的請(qǐng)求直接從快照讀取，但是這種快照方式還是很消耗性能的，尤其是對(duì)于更新或刪除操作,仍然會(huì)出現(xiàn)阻塞. SNAPSHOT級(jí)別對(duì)快照的讀取是以事務(wù)為單位的。同一個(gè)事務(wù)中的讀取操作都會(huì)讀取同一快照，無(wú)論其他事務(wù)是否更新了快照。在 READ COMMITTED 的隔離級(jí)別下還是會(huì)從快照讀取，但是其他模式就按照本身的控制方式進(jìn)行控制,目標(biāo)是源表，只有SNAPSHOT隔離級(jí)別可以檢測(cè)沖突。
要使用該隔離級(jí)別需要在數(shù)據(jù)庫(kù)中打開(kāi)任意會(huì)話(huà)執(zhí)行如下代碼：

ALTER DATABASE TEST  SET ALLOW_SNAPSHOT_ISOLATION ON

以上面的表為例，打開(kāi)兩個(gè)會(huì)話(huà)，在會(huì)話(huà)1中運(yùn)行如下代碼：

BEGIN TRAN    UPDATE dbo.Nums set NUM +=1
    WHERE ID = 1

打開(kāi)會(huì)話(huà)2并運(yùn)行如下代碼：

SET TRANSACTION ISOLATION LEVEL SNAPSHOTGOBEGIN TRAN    SELECT * FROM dbo.Nums  
    WHERE ID = 1

此時(shí)會(huì)話(huà)2并沒(méi)有被阻塞，而是返回了之前的版本，結(jié)果如下：
?
切換會(huì)會(huì)話(huà)1運(yùn)行 COMMIT TRAN ,緊接著繼續(xù)在會(huì)話(huà)2中在執(zhí)行一遍相同的查詢(xún)，執(zhí)行結(jié)果如下
??
發(fā)現(xiàn)與上次的結(jié)果相同，但是會(huì)話(huà)1明明已經(jīng)提交了，為什么還是原來(lái)的數(shù)據(jù)呢，這是因?yàn)樵撃Ｊ降奶攸c(diǎn)，要是想讀取新的數(shù)據(jù)需要，需要提交本次事務(wù)，繼續(xù)在會(huì)話(huà)2中運(yùn)行如下代碼：

COMMIT TRANBEGIN TRAN    SELECT * FROM dbo.Nums  
    WHERE ID = 1COMMIT TRAN

結(jié)果如圖所示：
?
下面看一個(gè)沖突檢測(cè)的例子
重新打開(kāi)兩個(gè)會(huì)話(huà)，在會(huì)話(huà)1中運(yùn)行如下代碼：

SET TRANSACTION ISOLATION LEVEL SNAPSHOTGOBEGIN TRAN    SELECT * FROM dbo.Nums  
    WHERE ID = 1

打開(kāi)會(huì)話(huà)2運(yùn)行如下代碼：

?BEGIN TRAN 
    UPDATE dbo.Nums SET NUM =10000
    WHERE ID =1

回到會(huì)話(huà)1，繼續(xù)運(yùn)行如下代碼：

UPDATE dbo.Nums SET NUM =100
    WHERE ID =1

此時(shí)會(huì)話(huà)1出現(xiàn)阻塞，可以通過(guò)執(zhí)行如下語(yǔ)句：

SELECT session_id,blocking_session_id,command,text,database_id,wait_type,wait_resource,wait_time FROM sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle) WHERE blocking_session_id > 0

結(jié)果如圖所示：
?
從圖中可以看出競(jìng)爭(zhēng)的資源是源表的數(shù)據(jù)行，并不是快照的，這就說(shuō)明對(duì)于UPDATE 或者是DELETE 最終的目標(biāo)是源表，切換會(huì)話(huà)2 運(yùn)行 COMMIT TRAN 發(fā)現(xiàn)會(huì)話(huà)1中出現(xiàn)了錯(cuò)誤：
??
READ COMMITTED SNAPSHOT 模式對(duì)于沖突檢測(cè)這一案例結(jié)果是不支持，會(huì)話(huà)1中的更新操作會(huì)成功，讀者可以自行實(shí)驗(yàn)。

READ COMMITTED SNAPSHOT

同SNAPSHOT很像，但對(duì)于快照的讀取是以語(yǔ)句為單位的，同一個(gè)事務(wù)中的查詢(xún)數(shù)據(jù)的語(yǔ)句每次都讀取快照的最新版
要使用該隔離級(jí)別需要在數(shù)據(jù)庫(kù)中打開(kāi)任意會(huì)話(huà)執(zhí)行如下代碼：

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON

以上表為例，打開(kāi)2個(gè)會(huì)話(huà)，在會(huì)話(huà)1運(yùn)行如下代碼：

BEGIN TRAN 
    UPDATE dbo.Nums SET NUM +=1
    WHERE ID =1

打開(kāi)會(huì)話(huà)2，并運(yùn)行如下代碼：

BEGIN TRAN 
    SELECT * FROM dbo.Nums 
    WHERE ID =1

運(yùn)行結(jié)果為：
?
是從快照中讀取出來(lái)的，繼續(xù)在會(huì)話(huà)1中運(yùn)行 COMMIT TRAN ，之后在會(huì)話(huà)2中的當(dāng)前事務(wù)中繼續(xù)執(zhí)行相同的查詢(xún)，結(jié)果如下：
?
這就是之前所說(shuō)的語(yǔ)句為單位的讀取快照，在這里有一個(gè)很有趣的現(xiàn)象就是，在會(huì)話(huà)2中并未設(shè)置隔離級(jí)別，這是因?yàn)槟J(rèn)情況下的隔離級(jí)別為 READ COMMITTED 由于運(yùn)行了如上語(yǔ)句修改數(shù)據(jù)庫(kù)標(biāo)記，故，會(huì)話(huà)的默認(rèn)的隔離級(jí)別變成了 READ COMMITTED SNAPSHOT，當(dāng)顯示修改為其他隔離級(jí)別是，則會(huì)按照修改后的隔離級(jí)別運(yùn)行。若修改會(huì)話(huà)2的隔離級(jí)別為 READ UNCOMMITTED 時(shí)，并不會(huì)進(jìn)行快照查詢(xún)，仍然出現(xiàn)了臟讀。

對(duì)于解決臟讀／不可重復(fù)讀／幻讀等問(wèn)題，可以通過(guò)升級(jí)隔離級(jí)別的方式解決問(wèn)題。

死鎖

說(shuō)起鎖的問(wèn)題，那當(dāng)然少不了談起死鎖這種現(xiàn)象，主要發(fā)生于兩個(gè)或多個(gè)事務(wù)之間存在相互阻塞，造成死鎖，在SQL Server 中會(huì)犧牲工作最少的事務(wù)，SQL Server 可以設(shè)置一個(gè)DEADLOCK_PRIORITY的會(huì)話(huà)選項(xiàng)設(shè)置事務(wù)的在發(fā)生死鎖的情況下?tīng)奚捻樞颍翟?10～10之間，在發(fā)生死鎖的情況下，會(huì)優(yōu)先犧牲數(shù)值最低的事務(wù)，不管其做的工作有多么的重要，當(dāng)存在平級(jí)的時(shí)候，將根據(jù)工作數(shù)量進(jìn)行犧牲。
下面來(lái)演示一個(gè)死鎖的例子，以上面的表為例，并創(chuàng)建一個(gè)Nums副本表取名CopyNums,并添加(1,1)記錄，打開(kāi)兩個(gè)會(huì)話(huà)，在會(huì)話(huà)1中執(zhí)行如下代碼：

SET DEADLOCK_PRIORITY 0BEGIN TRAN    UPDATE dbo.Nums SET NUM=100
    WHERE ID = 1

打開(kāi)會(huì)話(huà)2運(yùn)行如下代碼：

SET DEADLOCK_PRIORITY 1BEGIN TRAN    UPDATE  dbo.CopyNums SET NUM = 100
    WHERE ID = 1

切換回會(huì)話(huà)1 繼續(xù)運(yùn)行如下代碼：

SELECT * FROM dbo.CopyNums 
    WHERE ID = 1

此時(shí)會(huì)發(fā)生阻塞，等待排他鎖(X)釋放，切換會(huì)話(huà)2運(yùn)行如下代碼：

SELECT * FROM dbo.Nums    WHERE ID = 1

此次也會(huì)發(fā)生阻塞，但是阻塞一會(huì)你就會(huì)發(fā)現(xiàn)，會(huì)話(huà)1終止了，并出現(xiàn)如下錯(cuò)誤：
?
為什么會(huì)終止的是會(huì)話(huà)1呢？可以發(fā)現(xiàn)在會(huì)話(huà)中我們?cè)O(shè)置了 DEADLOCK_PRIORITY，會(huì)犧牲數(shù)值低的那個(gè)會(huì)話(huà)事務(wù)，查看SQL Profiler 可以發(fā)現(xiàn)，確實(shí)有死鎖現(xiàn)象發(fā)生(為了清晰僅顯示死鎖)
?
那么既然死鎖會(huì)發(fā)生，就要有對(duì)應(yīng)的避免死鎖的對(duì)策：
   1. 事務(wù)時(shí)間越長(zhǎng)，保持鎖的時(shí)間就越長(zhǎng)，造成死鎖的可能性就越大，檢查事務(wù)中是否放置了過(guò)多的不應(yīng)該屬于同一工作單元的邏輯，有的話(huà)請(qǐng)移除到，從而縮短事務(wù)的時(shí)間
   2. 上述死鎖發(fā)生的關(guān)鍵在于訪問(wèn)順序的問(wèn)題，將兩個(gè)會(huì)話(huà)中的語(yǔ)句變成一個(gè)順序(都先操作Nums 或者 CopyNums )，就沒(méi)有了死鎖現(xiàn)象，所以在沒(méi)有邏輯的單元中，調(diào)換順序也會(huì)減少死鎖的發(fā)生
   3. 考慮選擇隔離級(jí)別，不同隔離級(jí)別對(duì)鎖的控制方式不一樣，例如：行版本控制就不會(huì)請(qǐng)求共享鎖(S)

作者：摯誠(chéng)v8

出處：http://www.cnblogs.com/retop/

本文版權(quán)歸作者和博客園共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面

http://www.cnblogs.com/retop/p/7073432.html

延伸閱讀

• LINQ TO SQL和Entity Framework 的關(guān)系 你了解多少？ 2017-07-26
• MyBatis-sql映射文件 2017-07-21
• SQL數(shù)據(jù)庫(kù)操作(CURD) 2017-07-13
• SQL Server AlwaysOn配置兩個(gè)節(jié)點(diǎn)加共享文件夾仲裁見(jiàn)證 2017-07-13
• SQL-結(jié)構(gòu)化查詢(xún)語(yǔ)言(1) 2017-07-12
• SQL執(zhí)行過(guò)程中的性能負(fù)載點(diǎn) 2017-07-10
• SQL Server特殊用法筆記 2017-07-07
• hive集成sentry的sql使用語(yǔ)法 2017-07-04
• SQL Server--獲取磁盤(pán)空間使用情況 2017-06-27
• SQL 基礎(chǔ)知識(shí)梳理（七）- 集合運(yùn)算 2017-06-27

SQL數(shù)據(jù)庫(kù)操作(CURD)