T-SQL查詢進階--SQL Server中的事務與鎖

標籤：修改   引擎   髒讀   允許   回覆   tab   查詢最佳化   解決方案   sql查詢   

為什麼需要鎖

在任何多使用者的資料庫中，必須有一套用於資料修改的一致的規則，當兩個不同的進程試圖同時修改同一份資料時，資料庫管理系統（DBMS）負責解決它們之間潛在的衝突。任何關聯式資料庫必須支援事務的ACID屬性，所以在開始瞭解鎖之前，首先簡單瞭解一下資料庫事務和事務的ACID屬性。

• 原子性（Atomicity）:原子性意味著資料庫中的事務執行是作為原子。即不可在分，整個語句要麼執行，要麼不執行
• 一致性（Consistency）:在事務開始之前和事務結束以後，資料庫的完整性條件約束沒有被破壞。（唯一約束，外鍵約束，Check約束等）和觸發器設定這一點有SQL SERVER進行保證的
• 隔離線（Isolation）:事務的執行是互不干擾的，一個事務不可能看到其他事務運行時，中間某一時刻的資料
•  持久性（Durability）:持久性表示在某個事務的執行過程中，對資料所作的所以改的都必須在事務成功結束前儲存某種實體儲存體裝置，這樣可以保證，所作的修改在任何系統癱瘓時不至於丟失。

理論上所有的事務之間應該是完全隔離的。但是實際上，要實現完全隔離的成本實在是太高(必須是序列化的隔離等級才能完全隔離)。所以， SQL Server通過鎖，就像十字路口的紅綠燈那樣，告訴所有並發的串連，在同一時刻上，那些資源可以讀取，那些資源可以修改。當一個事務需要訪問的資源加了其所不相容的鎖，SQL Server會阻塞當前的事務來達成所謂的隔離性。直到其所請求資源上的鎖被釋放。

為此，SQL Server在隔離和並發之間選擇了Read Commited作為資料庫的預設隔離等級。

多個使用者同時對資料庫的並行作業時會帶來以下資料不一致的問題:

  髒讀：一個事務讀取到了另外一個事務沒有提交的資料。

A修改了資料,隨後B又讀出該資料,但A因為某些原因取消了對資料的修改,資料恢複原值,此時B得到的資料就與資料庫內的資料產生了不一致

   幻讀：同一事務中，用同樣的操作讀取兩次，得到的記錄數不相同。

A讀取資料,隨後B又插入了資料,此時A再讀資料是發現前後兩次擷取的資料行集不一致

   不可重複讀取：在同一事務中，兩次讀取同一資料，得到內容不同。

A使用者讀取資料,隨後B使用者讀出該資料並修改,此時A使用者再讀取資料時發現前後兩次的值不一致

    丟失更新：事務T1讀取了資料，並執行了一些操作，然後更新資料。事務T2也做相同的事，則T1和T2更新資料時可能會覆蓋對方的更新，從而引起錯誤。

A,B兩個使用者讀同一資料並進行修改,其中一個使用者的修改結果破壞了另一個修改的結果,比如訂票系統

並發控制的主要方法是通過鎖，在一段時間內禁止使用者做某些操作以避免產生資料不一致理解SQL SERVER中的隔離等級

為了避免上述幾種事務之間的影響,SQL Server通過設定不同的隔離等級來進行不同程度的避免。 SQL Server提供了5種選項來避免不同層級的事務之間的影響。隔離等級由低到高分別為：

• 未提交讀（Read Uncommited）：最高的效能，但可能出現髒讀，不可重複讀取，幻讀
• 已提交讀（Read commited）：可能出現不可重複讀取，幻讀
• 可重複讀（Repeatable Read）：可能出現幻讀
• 序列化（Serializable）：最低的效能，Range鎖會導致並發下降
• 快照（SNOPSHOT）：這個是通過在tempDB中建立一個額外的副本來避免髒讀，不可重複讀取，會給tempDB造成額外負擔
鎖的模式
• 共用鎖定(S鎖）:用於讀取資源所加的鎖。擁有共用鎖定的資源不能被修改。共用鎖定預設情況下是讀取了資源馬上被釋放。
• 獨佔鎖定（X鎖）： 和其它任何鎖都不相容，包括其它獨佔鎖定。排它鎖用於資料修改，當資源上加了獨佔鎖定時，其他請求讀取或修改這個資源的事務都會被阻塞，知道獨佔鎖定被釋放為止。
• 更新鎖定（U鎖）： U鎖可以看作是S鎖和X鎖的結合，用於更新資料，更新資料時首先需要找到被更新的資料，此時可以理解為被尋找的資料上了S鎖。當找到需要修改的資料時，需要對被修改的資源上X鎖。SQL Server通過U鎖來避免死結問題。因為S鎖和S鎖是相容的，通過U鎖和S鎖相容，來使得更新尋找時並不影響資料尋找，而U鎖和U鎖之間並不相容，從而減少了死結可能性。
• 意圖鎖定（IS IX IU）：意圖鎖定與其說是鎖，倒不如說更像一個指標。在SQL Server中，資源是有層次的，一個表中可以包含N個頁，而一個頁中可以包含N個行。當我們在某一個行中加了鎖時。可以理解成包含這個行的頁，和表的一部分已經被鎖定。當另一個查詢需要鎖定頁或是表時，再一行行去看這個頁和表中所包含的資料是否被鎖定就有點太痛苦了。因此SQL Server鎖定一個粒度比較低的資源時，會在其父資源上加上意圖鎖定，告訴其他查詢這個資源的某一部分已經上鎖。比如，當我們更新一個表中的某一行時，其所在的頁和表都會獲得意向獨佔鎖定
• 快照（SNOPSHOT）：這個是通過在tempDB中建立一個額外的副本來避免髒讀，不可重複讀取，會給tempDB造成額外負擔
• 關鍵範圍鎖定(KEY-RANGE）:在使用可序列化交易隔離等級時，對於 Transact-SQL 陳述式讀取的記錄集，關鍵範圍鎖定可以隱式保護該記錄集中包含的行範圍。可序列化隔離等級要求每當在事務期間執行任一查詢時，該查詢都必須擷取相同的行集。關鍵範圍鎖定可防止其他事務插入其索引值位於可序列化事務讀取的索引值範圍內的新行，從而確保滿足此要求。

關鍵範圍鎖定可防止幻讀。通過保護行之間的鍵範圍，它還可以防止對事務訪問的記錄集進行幻插入。

關鍵範圍鎖定放置在索引上，指定開始索引值和結束索引值。此鎖將阻止任何要插入、更新或刪除任何帶有該範圍內的索引值的行的嘗試，因為這些操作會首 先擷取索引上的鎖。例如，可序列化事務可能發出了一個 SELECT 語句，以讀取其索引值介於 ‘AAA‘ 與 ‘CZZ‘ 之間的所有行。從 ‘AAA‘ 到 ‘CZZ‘ 範圍內的索引值上的關鍵範圍鎖定可阻止其他事務插入帶有該範圍內的索引值（例如 ‘ADG‘、‘BBD‘ 或 ‘CAL‘）的行。

• 架構鎖： SQL Server 使用架構鎖來保持表結構的完整性。不像其他提供資料隔離的鎖類型，架構鎖提供事務中對資料庫物件如表、視圖、索引的schema隔離。
• 大容量更新鎖定：在向表進行大容量資料複製且指定了 TABLOCK 提示時使用

鎖相容性

鎖的粒度

所謂所粒度，從本質上說就是，為了給事務提供完全的隔離和序列化，作為查詢或更新的一部分被鎖定的資料的總量（的大小）。Lock Manager需要在資源的並發訪問與維護大量低層級鎖的管理開銷之間取得平衡。比如，鎖的粒度越小，能夠同時訪問同一張表的並發使用者的數量就越大，不過維護這些鎖的管理開銷也越大。鎖的粒度越大，管理鎖需要的開銷就越少，而並發性也降低了。說明了鎖的大小與並發性之間的權衡取捨。

SQL Server支援的鎖粒度可以分為為行、頁、鍵、鍵範圍、索引、表或資料庫擷取鎖鎖定擴大

鎖定擴大是將許多較細粒度的鎖轉換成數量更少的較粗粒度的鎖的過程，這樣可以減少系統開銷，但卻增加了並發爭用的可能性。

當 SQL Server 資料庫引擎擷取低層級的鎖時，它還將在包含更低層級對象的對象上放置意圖鎖定：

1. 當鎖定行或索引鍵範圍時，資料庫引擎將在包含這些行或鍵的頁上放置意圖鎖定。
2. 當鎖定頁時，資料庫引擎將在包含這些頁的更進階別的對象上放置意圖鎖定。

除了對象上的意圖鎖定以外，以下對象上還需要意向頁鎖：非叢集索引的葉級頁、叢集索引的資料頁、堆資料頁。

鎖定擴大的閾值：

• 單個 Transact-SQL 陳述式在單個無分區表或索引上獲得至少 5,000 個鎖。
• 單個 Transact-SQL 陳述式在資料分割資料表的單個分區上獲得至少 5,000 個鎖，並且 ALTER TABLE SET LOCK_ESCALATION 選項設為 AUTO。
• 資料庫引擎執行個體中的鎖的數量超出了記憶體或配置閾值

TIPS:資料庫引擎不會將行鎖或關鍵範圍鎖定升級到頁鎖，而是將它們直接升級到表鎖。同樣，頁鎖始終升級到表鎖。

 

--查看鎖活動情況select * from sys.dm_tran_locks--查看事務活動情況dbcc opentran

可參考 ：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017

什麼是死結

什麼是死結

死結的本質是一種僵持狀態，是多個主體對於資源的爭用而導致的。在兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定資源，此時會造成這些任務永久阻塞，從而出現死結。理解死結首先需要對死結所涉及的相關觀念有一個理解。

在的例子中，每隊汽車都佔有一條道路，但都需要另外一隊汽車所佔有的另一條道路，因此互相阻塞，誰都無法前行，因此造成了死結。




死結產生的原因及四個必要條件

產生死結的原因主要是：

（1） 因為系統資源不足。

（2） 進程運行推進的順序不合適。

（3） 資源分派不當等。

如果系統資源充足，進程的資源請求都能夠得到滿足，死結出現的可能性就很低，否則就會因爭奪有限的資源而陷入死結。其次，進程運行推進順序與速度不同，也可能產生死結。

產生死結的四個必要條件：

（1） 互斥條件：一個資源每次只能被一個進程使用。

（2） 請求與保持條件：一個進程因請求資源而阻塞時，對已獲得的資源保持不放。

（3） 不剝奪條件:進程已獲得的資源，在末使用完之前，不能強行剝奪。

（4） 迴圈等待條件:若干進程之間形成一種頭尾相接的迴圈等待資源關係。

這四個條件是死結的必要條件，只要系統發生死結，這些條件必然成立，而只要上述條件之一不滿足，就不會發生死結。 死結的兩種類型

1、迴圈死結：兩個進程請求不同資源上的鎖，每一個進程都需要對方持有的該資源上的鎖，這時將發生迴圈死結。如

2、轉換死結：兩個或多個進程都在事務中持有同一資源上的共用鎖定，並且都想把它升級為獨佔鎖，但是，誰也沒法升級直到其他的進程釋放共用鎖定，如

SQL Server中產生死結的一些情況

1、由書籤尋找產生的死結：這類死結產生的原因是書籤尋找和更新資料產生的僵持狀態。簡單來說,就是由於Update語句對基本表產生X鎖，然後需要對錶上的索引也進行更新，而表上的索引正好被另一個串連進行尋找，加了S鎖，此時又產生書籤尋找去基本表加了X鎖的資料進行書籤尋找，此時形成死結

書籤尋找：當查詢最佳化工具使用非叢集索引進行尋找時，如果所選擇的列或查詢條件中的列只部分包含在使用的非叢集索引和叢集索引中時，就需要一個尋找（lookup）來檢索其他欄位來滿足請求。對一個有聚簇索引的表來說是一個鍵尋找（key lookup），對一個堆表來說是一個RID尋找（RID lookup），這種尋找即是——書籤尋找(bookmark lookup)。簡單的說就是當你使用的sql查詢條件和select返回的列沒有完全包含在索引列中時就會發生書籤尋找。

解決方案：這種死結可以通過Include列來減少書籤尋找，從而減少這種類型死結發生的機率。

2、由外鍵產生的死結： 這類死結產生的原因來自外鍵約束。當主表(也就是主鍵是從表外鍵的那個表)更新資料時，需要查看從表，以確定從表的外鍵列滿足外鍵約束。此時會在主表上加X鎖，但這並不能阻止同一時間，另一個SPID向從表添加被修改的主表主鍵，為瞭解決這個問題，SQL Server在進行這類更新時，使用Range鎖，這種鎖是當隔離等級為序列化時才有的，因此在這時雖然隔離等級可能是預設的已提交讀，但是行為卻是序列化。這很可能就會導致死結。

解決方案：向外鍵列添加索引，使得Range鎖加在索引上，而不是表本身。從而降低了死結發生的機率。

3、由於推進順序不當產生的死結：在多個事務對資源的使用順序不當，形成死結環路而引發的。

解決方案：盡量使資源的使用順序一致。這也是死結問題出現最多的一種情況。

如何查看死結

三、死結的預防與最佳化

預防死結

預防死結就是破壞四個必要條件中的某一個和幾個，使其不能形成死結。有如下幾種辦法：

1)破壞互斥條件

破壞互斥條件有比較嚴格的限制,在SQL Server中，如果商務邏輯上允許髒讀，則可以通過將隔離等級改為未提交讀或使用索引提示。這樣使得讀取不用加S鎖，從而避免了和其它查詢所加的與S鎖不相容的鎖互斥，進而減少了死結出現的機率。

2)破壞請求和等待條件

這點由於事務存在原子性，是不可破壞的，因為解決辦法是盡量的減少事務的長度，事務內執行的越快越好。這也可以減少死結出現的機率。

3)破壞不剝奪條件

由於事務的原子性和一致性，不剝奪條件同樣不可破壞。但我們可以通過增加資源和減少資源佔用兩個角度來考慮。

增加資源：比如說通過建立非叢集索引，使得有了額外的資源，查詢很多時候就不再索要鎖基本表，轉而鎖非叢集索引，如果索引能夠"覆蓋(Cover)"查詢，那更好不過。因此索引Include列不僅僅減少書籤尋找來提高效能，還能減少死結。

減少資源佔用:比如說查詢時，能用select col1,col2這種方式，就不要用select * .這有可能帶來不必要的書籤尋找

最大限度減少死結的方法

1. 按同一順序訪問對象： 按同一順序訪問對象也就是：第一個事務提交或復原後，第二個事務繼續進行，這樣不會發生死結。
2. 避免事務中的使用者互動： 避免編寫包含使用者互動的事務，因為運行沒有使用者互動的批處理的速度要遠遠快於使用者手動響應查詢的速度，例如回覆應用程式請求參數的提示。例如，如果事務正在等待使用者輸入，而使用者去吃午餐了或者甚至回家過周末了，則使用者將此事務掛起使之不能完成。這樣將降低系統的輸送量，因為事務持有的任何鎖只有在事務提交或復原時才會釋放。即使不出現死結的情況，訪問同一資源的其它事務也會被阻塞，等待該事務完成。
3. 保持事務簡短並在一個批處理中： 在同一資料庫中並發執行多個需要長時間啟動並執行事務時通常發生死結。事務已耗用時間越長，其持有排它鎖或更新鎖定的時間也就越長，從而堵塞了其它活動並可能導致死結。 保持事務在一個批處理中，可以最小化事務的網路通訊往返量，減少完成事務可能的延遲並釋放鎖。
4. 使用低隔離等級： 確定事務是否能在更低的隔離等級上運行，執行提交讀取允許事務讀取另一個事務已讀取（未修改）的資料，而不必等待第一個事務完成。使用較低的隔離等級（例如提交讀取）而不使用較高的隔離等級（例如可串列讀）可以縮短持有共用鎖定的時間，從而降低了鎖定爭奪。
5. 使用綁定串連： 使用綁定串連使同一應用程式所開啟的兩個或多個串連可以相互合作。次級串連所獲得的任何鎖可以象由主串連獲得的鎖那樣持有，反之亦然，因此不會相互阻塞。

最佳化死結的一些建議

(1)對於查詢頻繁的表盡量使用叢集索引;

(2)設法避免一次性影響大量記錄的SQL語句，特別是INSERT和UPDATE語句;

(3)設法讓UPDATE和DELETE語句使用合適的索引;

(4)使用嵌套事務時，避免提交和回退衝突;

(5)對資料一致性要求不高的查詢使用 WITH(NOLOCK)

(6)減小事務的體積，事務應最晚開啟，最早關閉，所有不是必須使用事務的操作必須放在事務外。

(7)查詢只返回你需要的列，不建議使用 SELECT * FROM 這種寫法。

 

      可參考：https://www.cnblogs.com/knowledgesea/p/3714417.html

T-SQL查詢進階--SQL Server中的事務與鎖