當前位置: 首頁  >  SqlServer  >  SQL Server 鎖機制

SQL Server 鎖機制 TOP

鎖兼容性圖:

一、鎖的粒度:

比較需要注意的是RID/KEY、HoBT/PAGE這兩對兒的區別,RID和HoBT是針對堆表的,即沒有聚集索引的表。

二、鎖的模式:

1.關于其中的S、U、X鎖:

共享鎖

共享鎖(S 鎖)允許并發事務在封閉式并發控制下讀取 (SELECT) 資源。 資源上存在共享鎖(S 鎖)時,任何其他事務都不能修改數據。 讀取操作一完成,就立即釋放資源上的共享鎖(S 鎖),除非將事務隔離級別設置為可重復讀或更高級別,或者在事務持續時間內用鎖定提示保留共享鎖(S 鎖)。

更新鎖

更新鎖(U 鎖)可以防止常見的死鎖。 在可重復讀或可序列化事務中,此事務讀取數據 [獲取資源(頁或行)的共享鎖(S 鎖)],然后修改數據 [此操作要求鎖轉換為排他鎖(X 鎖)]。 如果兩個事務獲得了資源上的共享模式鎖,然后試圖同時更新數據,則一個事務嘗試將鎖轉換為排他鎖(X 鎖)。 共享模式到排他鎖的轉換必須等待一段時間,因為一個事務的排他鎖與其他事務的共享模式鎖不兼容;發生鎖等待。 第二個事務試圖獲取排他鎖(X 鎖)以進行更新。 由于兩個事務都要轉換為排他鎖(X 鎖),并且每個事務都等待另一個事務釋放共享模式鎖,因此發生死鎖。

若要避免這種潛在的死鎖問題,請使用更新鎖(U 鎖)。 一次只有一個事務可以獲得資源的更新鎖(U 鎖)。 如果事務修改資源,則更新鎖(U 鎖)轉換為排他鎖(X 鎖)。

排他鎖

排他鎖(X 鎖)可以防止并發事務對資源進行訪問。 使用排他鎖(X 鎖)時,任何其他事務都無法修改數據;僅在使用 NOLOCK 提示或未提交讀隔離級別時才會進行讀取操作。

數據修改語句(如 INSERT、UPDATE 和 DELETE)合并了修改和讀取操作。 語句在執行所需的修改操作之前首先執行讀取操作以獲取數據。 因此,數據修改語句通常請求共享鎖和排他鎖。 例如,UPDATE 語句可能根據與一個表的聯接修改另一個表中的行。 在此情況下,除了請求更新行上的排他鎖之外,UPDATE 語句還將請求在聯接表中讀取的行上的共享鎖。

2.關于其中的意向鎖:
意向鎖有兩種用途:
  • 防止其他事務以會使較低級別的鎖無效的方式修改較高級別資源。
  • 提高數據庫引擎在較高的粒度級別檢測鎖沖突的效率。

3.關于其中的架構鎖:

數據庫引擎在表數據定義語言 (DDL) 操作(例如添加列或刪除表)的過程中使用架構修改 (Sch-M) 鎖。 保持該鎖期間,Sch-M 鎖將阻止對表進行并發訪問。 這意味著 Sch-M 鎖在釋放前將阻止所有外圍操作。

某些數據操作語言 (DML) 操作(例如表截斷)使用 Sch-M 鎖阻止并發操作訪問受影響的表。

數據庫引擎在編譯和執行查詢時使用架構穩定性 (Sch-S) 鎖。 Sch-S 鎖不會阻止某些事務鎖,其中包括排他 (X) 鎖。 因此,在編譯查詢的過程中,其他事務(包括那些針對表使用 X 鎖的事務)將繼續運行。 但是,無法針對表執行獲取 Sch-M 鎖的并發 DDL 操作和并發 DML 操作。

4.關于其中的大容量更新鎖:
大容量更新鎖(BU 鎖)允許多個線程將數據并發地大容量加載到同一表,同時防止其他不進行大容量加載數據的進程訪問該表。 在滿足以下兩個條件時,數據庫引擎使用大容量更新 (BU) 鎖。
  • 使用 Transact-SQL BULK INSERT 語句或 OPENROWSET(BULK) 函數,或者您使用某個大容量插入 API 命令(如 .NET SqlBulkCopy)、OLEDB 快速加載 API 或 ODBC 大容量復制 API 來將數據大容量復制到表。
  • TABLOCK指定提示或表大容量加載上的鎖表選項設置使用sp_tableoption。
5.關于其中的鍵范圍鎖:
在使用可序列化事務隔離級別時,對于 Transact-SQL 語句讀取的記錄集,鍵范圍鎖可以隱式保護該記錄集中包含的行范圍。 鍵范圍鎖可防止虛擬讀取。 通過保護行之間鍵的范圍,它還防止對事務訪問的記錄集進行虛擬插入或刪除。

關于鍵范圍鎖可以參考官網,或者另一篇博客SQL Server事務隔離級別中對于可序列化讀隔離級別的加鎖說明。

 

三、鎖升級

SQL Server數據庫會發生鎖升級,官網說明的鎖升級觸發條件為,如果沒有使用 ALTER TABLE SET LOCK_ESCALATION 選項來禁用表的鎖升級并且滿足以下任一條件時,觸發鎖升級:
  • 單個 Transact-SQL 語句在單個無分區表或索引上獲得至少 5,000 個鎖。
  • 單個 Transact-SQL 語句在已分區表的單個分區上獲得至少 5,000 個鎖,并且 ALTER TABLE SET LOCK_ESCALATION 選項設為 AUTO。
  • 數據庫引擎實例中的鎖的數量超出了內存或配置閾值。
  • 如果由于鎖沖突導致無法升級鎖,則數據庫引擎每當獲取 1,250 個新鎖時便會觸發鎖升級。
對于鎖升級的優化官網提供如下建議:
  1. 使用READ_COMMITTED_SNAPSHOT事務隔離級別。
  2. 使用SNAPSHOT事務隔離級別。
  3. 使用READ UNCOMMITTED事務隔離級別。
一般情況下我們只需要把READ_COMMITTED_SNAPSHOT選項打開即可,可以避免select加鎖,從而避免阻塞和鎖升級。
此外還可以打開1211和1224來避免鎖升級,但是極度不推薦,鎖升級本身就是為加快鎖獲取的效率而設計的,根本解決辦法還是優化SQL。
 
參考文檔
SQL Server 事務鎖定和行版本控制指南:https://msdn.microsoft.com/zh-cn/library/jj856598(v=sql.120).aspx
Views:1209   Posted at:2018-02-26
收藏 推薦 打印 | 錄入:Admin | 閱讀:0
图色模拟辅助赚钱吗