關鍵範圍鎖定定（Key-Rang Lock）是不是只在序列化層級中出現

以前一直認為關鍵範圍鎖定定只是在序列化隔離等級中才會出現，但是從論壇的一篇文章中看到Read-Committed隔離等級中竟然也出現了：

59:50. spid24s process id=process6463b88 taskpriority=0 logused=480 waitresource=KEY: 6:72057594065715200 (8500ea663c04) waittime=516 

ownerId=773364767 transactionname=test lasttranstarted=2013-06-04T08:59:49.450 
XDES=0x80092570 lockMode=RangeS-U schedulerid=22 kpid=2872 status=suspended 

spid=57 sbid=0 ecid=0 priority=0 trancount=3 

lastbatchstarted=2013-06-04T08:59:49.453 lastbatchcompleted=2013-06-04T08:59:49.450 
isolationlevel=read committed (2) xactid=773364767 currentdb=6 lockTimeout=4294967295 
clientoption1=671088672 clientoption2=128056

之後查到兩片文章提到SQL Server在一定的條件下會隱式的提升隔離等級到序列化。

微軟用串聯刪除的例子做了說明：

createtableFoo(FooIdintnotnullprimarykey)

createtableBar(FooIdintnotnull,BarIdintnotnull)

altertableBaraddconstraintPK_Barprimarykey (FooId,BarId)

altertableBaraddconstraintFK_Bar_Fooforeignkey (FooId)referencesFoo(FooId)ondeletecascade

insertintoFoovalues (1)insertintoBarvalues (1, 1)

settransactionisolationlevelreadcommitted

begintran

deletefromFoowhereFooId= 1 

committran

刪除的操作需要保持：

1.刪除Foo 表的資料其實也要刪除Bar表中相關聯的資料

2.在語句結束的時候，外鍵約束還是有效。

所以在read-committed隔離等級下，X locks一直持續到交易結束，但是不能避免在刪除的同時其他人插入資料。所以需要SERIALIAZABLE隔離等級。

所以，對於類似的操作，不需要使用者顯示的使用serialization 隔離等級，SQL Server會自動將隔離等級升級從而阻塞其他動作插入資料從而影響外鍵約束。在這種情況下，SQL Server會使用關鍵範圍鎖定定.這種方法可以避免資料損毀。

SERIALIZABLE層級的鎖會一直保持到交易結束，如果一個批次中有多條語句，可能需要一段時間釋放這些鎖。因為SQL Server知道使用了什麼鎖，並且什麼時候釋放才是安全的。

 

另外索引檢視表維護也是相同的情況。

更多資料可以參考：Read Committed isolation level, indexed views and locking behavior

Conor vs. Isolation Level Upgrade on UPDATE/DELETE Cascading RI

 

本文出自 “關注SQL Server技術” 部落格，請務必保留此出處http://lzf328.blog.51cto.com/1196996/1217126