有幾個(gè)朋友留言建議結(jié)合例子來演示一下, 上篇已經(jīng)說過鎖的幾種類型, 可以利用系統(tǒng)動(dòng)態(tài)視圖sys.dm_tran_locks查看到,重要的欄位如下:

?

resource_type	被鎖的資源類型(Database, FILE, Object,PAGE,KEY,EXTENT,RID,APPLICATION,METADATA,HOBT,APPOCATION_UNIT)
request_mode	鎖的類型(共享鎖,更新鎖,排它鎖, 架構(gòu)鎖等)
resource_description	資源描述
request_session_id	Request session ID

?

?

一: 下面以AdventureWorks2008為示例數(shù)據(jù)庫做簡要的說明,

過濾掉一般的數(shù)據(jù)庫的共享鎖, 作為示例必須要看到鎖, 所以用WITH(HOLDLOCK)來保持鎖.

1. Shared locks (S) 共享鎖

USE?AdventureWorks2008 ?

?

BEGIN?TRAN ?

select?*?from?Sales.SalesOrderHeader?WITH(HOLDLOCK) ?

where?SalesOrderID='43662'? ?

?

???? ?

SELECT?resource_type,?request_mode,?resource_description,

request_session_id,?DB_NAME(resource_database_id)as?resource_database ?

FROM???sys.dm_tran_locks ?

WHERE??resource_type?<>?'DATABASE'?

?

--ROLLBACK?TRAN?

在事務(wù)回滾之前, 查看鎖的類型:

其他session對Table只讀, 不能更新, 在開一個(gè)新的session測試:

select?*?from?Sales.SalesOrderHeader??where?SalesOrderID='43662'?

?

go ?

update?Sales.SalesOrderHeader?set?OrderDate=GETDATE()?where?SalesOrderID='43662'?

select可以正常執(zhí)行, update語句一直處于等待狀態(tài), 等待上面的session釋放鎖.

2. Update locks (U): 更新鎖是共享鎖和獨(dú)占鎖的組合.用UPDLOCK保持更新鎖

USE?AdventureWorks2008 ?

?

BEGIN?TRAN ?

select?*?from?Sales.SalesOrderHeader?WITH(UPDLOCK) ?

where?SalesOrderID='43662'? ?

???? ?

SELECT?resource_type,?request_mode,?resource_description,

request_session_id,DB_NAME(resource_database_id)as?resource_database ?

FROM???sys.dm_tran_locks ?

WHERE??resource_type?<>?'DATABASE'?

?

ROLLBACK?TRAN?

查看到鎖的信息:

?

3.Exclusive locks (X): 獨(dú)占鎖是為了鎖定數(shù)據(jù)被一個(gè)session修改的數(shù)據(jù), 而不能夠被另外的session修改. 只能指定NOLOCK來讀取.

?

USE?AdventureWorks2008 ?

?

BEGIN?TRAN ?

?

update?Sales.SalesOrderHeader?set?ShipDate=GETDATE()?

where?SalesOrderID='43662'?

???? ?

SELECT?resource_type,?request_mode,?resource_description,

request_session_id,DB_NAME(resource_database_id)as?resource_database--,* ?

FROM???sys.dm_tran_locks ?

WHERE??resource_type?<>?'DATABASE'?

?

ROLLBACK?TRAN?

查看鎖:

?

4.Intent locks (I): 意向鎖用于建立鎖的層次結(jié)構(gòu). 意向鎖包含三種類型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。

數(shù)據(jù)庫引擎使用意向鎖來保護(hù)共享鎖（S 鎖）或排他鎖（X 鎖）放置在鎖層次結(jié)構(gòu)的底層資源上。 意向鎖之所以命名為意向鎖，是因?yàn)樵谳^低級(jí)別鎖前可獲取它們，因此會(huì)通知意向?qū)㈡i放置在較低級(jí)別上。

意向鎖有兩種用途：

防止其他事務(wù)以會(huì)使較低級(jí)別的鎖無效的方式修改較高級(jí)別資源。

提高數(shù)據(jù)庫引擎在較高的粒度級(jí)別檢測鎖沖突的效率。

5. Schema locks (Sch): 架構(gòu)鎖

Schema stability lock(Sch-S): 保持架構(gòu)穩(wěn)定性,用在生成執(zhí)行計(jì)劃時(shí),不會(huì)阻止對數(shù)據(jù)的訪問.

Schema modification lock (Sch-M):用在DDL操作時(shí).當(dāng)架構(gòu)正在被改變時(shí), 阻止對對象數(shù)據(jù)的訪問.

USE?AdventureWorks2008 ?

?

BEGIN?TRAN ?

CREATE?TABLE?MyTable?(ID?INT,?NAME?VARCHAR(20),COUNTRY?VARCHAR(15)) ?

?

SELECT?resource_type,?request_mode,?resource_description ?

FROM???sys.dm_tran_locks ?

WHERE??resource_type?<>?'DATABASE'?order?by?request_mode ?

?

ROLLBACK?TRAN?

6. Bulk Update locks (BU)

數(shù)據(jù)庫引擎在將數(shù)據(jù)大容量復(fù)制到表中時(shí)使用了大容量更新 (BU) 鎖, 并指定了 TABLOCK 提示或使用 sp_tableoption 設(shè)置了 table lock on bulk load 表選項(xiàng). 大容量更新鎖（BU 鎖）允許多個(gè)線程將數(shù)據(jù)并發(fā)地大容量加載到同一表, 同時(shí)防止其他不進(jìn)行大容量加載數(shù)據(jù)的進(jìn)程訪問該表.

7. Key - Range locks

在使用可序列化事務(wù)隔離級(jí)別時(shí), 對于 Transact-SQL 語句讀取的記錄集, 鍵范圍鎖可以隱式保護(hù)該記錄集中包含的行范圍. 鍵范圍鎖可防止幻讀. 通過保護(hù)行之間鍵的范圍, 它還防止對事務(wù)訪問的記錄集進(jìn)行幻像插入或刪除.

二: 死鎖與死鎖解除

1. 死鎖

使用或管理數(shù)據(jù)庫都不可避免的涉及到死鎖. 一旦發(fā)生死鎖, 數(shù)據(jù)相互等待對方資源的釋放,會(huì)阻止對數(shù)據(jù)的訪問, 嚴(yán)重會(huì)造成DB掛掉. 當(dāng)資源被鎖定, 無法被訪問時(shí), 可以終止訪問DB的那個(gè)session來達(dá)到解鎖的目的(即 Kill掉造成鎖的那個(gè)進(jìn)程).

在兩個(gè)或多個(gè)任務(wù)中，如果每個(gè)任務(wù)鎖定了其他任務(wù)試圖鎖定的資源，此時(shí)會(huì)造成這些任務(wù)永久阻塞，從而出現(xiàn)死鎖。 例如：

事務(wù) A 獲取了行 1 的共享鎖。

事務(wù) B 獲取了行 2 的共享鎖。

現(xiàn)在，事務(wù) A 請求行 2 的排他鎖，但在事務(wù) B 完成并釋放其對行 2 持有的共享鎖之前被阻塞。

現(xiàn)在，事務(wù) B 請求行 1 的排他鎖，但在事務(wù) A 完成并釋放其對行 1 持有的共享鎖之前被阻塞。

事務(wù) B 完成之后事務(wù) A 才能完成，但是事務(wù) B 由事務(wù) A 阻塞。該條件也稱為循環(huán)依賴關(guān)系： 事務(wù) A 依賴于事務(wù) B，事務(wù) B 通過對事務(wù) A 的依賴關(guān)系關(guān)閉循環(huán)。

除非某個(gè)外部進(jìn)程斷開死鎖，否則死鎖中的兩個(gè)事務(wù)都將無限期等待下去。 Microsoft SQL Server 數(shù)據(jù)庫引擎死鎖監(jiān)視器定期檢查陷入死鎖的任務(wù)。 如果監(jiān)視器檢測到循環(huán)依賴關(guān)系，將選擇其中一個(gè)任務(wù)作為犧牲品，然后終止其事務(wù)并提示錯(cuò)誤。 這樣，其他任務(wù)就可以完成其事務(wù)。 對于事務(wù)以錯(cuò)誤終止的應(yīng)用程序，它還可以重試該事務(wù)，但通常要等到與它一起陷入死鎖的其他事務(wù)完成后執(zhí)行。

2. 死鎖檢測

2.1 SQL Server 數(shù)據(jù)庫引擎自動(dòng)檢測 SQL Server 中的死鎖循環(huán)。數(shù)據(jù)庫引擎選擇一個(gè)會(huì)話作為死鎖犧牲品，然后終止當(dāng)前事務(wù)（出現(xiàn)錯(cuò)誤）來打斷死鎖。

2.2 查看DMV: sys.dm_tran_locks

2.3 SQL Server Profiler能夠直觀的顯示死鎖的圖形事件.

三: 鎖兼容性

鎖兼容性控制多個(gè)事務(wù)能否同時(shí)獲取同一資源上的鎖。 如果資源已被另一事務(wù)鎖定，則僅當(dāng)請求鎖的模式與現(xiàn)有鎖的模式相兼容時(shí)，才會(huì)授予新的鎖請求。 如果請求鎖的模式與現(xiàn)有鎖的模式不兼容，則請求新鎖的事務(wù)將等待釋放現(xiàn)有鎖或等待鎖超時(shí)間隔過期。 例如，沒有與排他鎖兼容的鎖模式。 如果具有排他鎖（X 鎖），則在釋放排他鎖（X 鎖）之前，其他事務(wù)均無法獲取該資源的任何類型（共享、更新或排他）的鎖。 另一種情況是，如果共享鎖（S 鎖）已應(yīng)用到資源，則即使第一個(gè)事務(wù)尚未完成，其他事務(wù)也可以獲取該項(xiàng)的共享鎖或更新鎖（U 鎖）。 但是，在釋放共享鎖之前，其他事務(wù)無法獲取排他鎖。

點(diǎn)擊查看大圖

?

四: 總結(jié)

鎖的原理比較抽象,對用戶來說是透明的,不用過多的關(guān)注. 應(yīng)用程序一般不直接請求鎖. 鎖由數(shù)據(jù)庫引擎的一個(gè)部件（稱為“鎖管理器”）在內(nèi)部管理. 當(dāng)數(shù)據(jù)庫引擎實(shí)例處理Transact-SQL 語句時(shí), 數(shù)據(jù)庫引擎查詢處理器會(huì)決定將要訪問哪些資源. 查詢處理器根據(jù)訪問類型和事務(wù)隔離級(jí)別設(shè)置來確定保護(hù)每一資源所需的鎖的類型. 然后, 查詢處理器將向鎖管理器請求適當(dāng)?shù)逆i. 如果與其他事務(wù)所持有的鎖不會(huì)發(fā)生沖突, 鎖管理器將授予該鎖.

原文：http://www.poluoluo.com/jzxy/201011/98759.html

轉(zhuǎn)載于:https://www.cnblogs.com/xiaxiaoping/p/3146452.html

總結(jié)

以上是生活随笔為你收集整理的SQL Server中表锁定的原理及解锁演示的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。