數(shù)據(jù)庫(kù)中有一張叫后宮佳麗的表，每天都有幾百萬(wàn)新的小姐姐插到表中，光陰荏苒，夜以繼日，日久生情，時(shí)間長(zhǎng)了，表中就有了幾十億的小姐姐數(shù)據(jù)。

[[344492]]

圖片來(lái)自 Pexels

 

看到幾十億的小姐姐，每到晚上，我可愁死了，這么多小姐姐，我翻張牌呢?

辦法當(dāng)然是精兵簡(jiǎn)政，刪除那些 age>18 的，給年輕的小姐姐們留位置...

于是我在數(shù)據(jù)庫(kù)中添加了一個(gè)定時(shí)執(zhí)行的小程序，每到周日，就自動(dòng)運(yùn)行如下的腳本：

delete from `后宮佳麗` where age>18 

一開(kāi)始還自我感覺(jué)良好，后面我就發(fā)現(xiàn)不對(duì)了，每到周日，這個(gè)腳本一執(zhí)行就是一整天。

運(yùn)行的時(shí)間有點(diǎn)長(zhǎng)是小事，重點(diǎn)是這大好周日，我再想讀這張表的數(shù)據(jù)，怎么也讀不出來(lái)了，怎是一句空虛了得，我好難啊......

[[344493]]

 

為什么?編不下去了，真實(shí)背景是公司中遇到的一張有海量數(shù)據(jù)表，每次一旦執(zhí)行歷史數(shù)據(jù)的清理，我們的程序就因?yàn)樽x不到這張表的數(shù)據(jù)，瘋狂地報(bào)錯(cuò)。

后面一查了解到，原來(lái)是因?yàn)槎〞r(shí)刪除的語(yǔ)句設(shè)計(jì)不合理，導(dǎo)致數(shù)據(jù)庫(kù)中數(shù)據(jù)由行鎖(Row lock)升級(jí)為表鎖(Table lock)了!😂

解決這個(gè)問(wèn)題的過(guò)程中把數(shù)據(jù)庫(kù)鎖相關(guān)的學(xué)習(xí)了一下，這里把學(xué)習(xí)成果，分享給大家，希望對(duì)大家有所幫助。

我將討論 SQL Server 鎖機(jī)制以及如何使用 SQL Server 標(biāo)準(zhǔn)動(dòng)態(tài)管理視圖監(jiān)視 SQL Server 中的鎖，相信其他數(shù)據(jù)的鎖也大同小異，具有一定參考意義。

鋪墊知識(shí)

在我開(kāi)始解釋 SQL Server 鎖定體系結(jié)構(gòu)之前，讓我們花點(diǎn)時(shí)間來(lái)描述ACID(原子性，一致性，隔離性和持久性)是什么。

ACID 是指數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)在寫(xiě)入或更新資料的過(guò)程中，為保證事務(wù)(transaction)是正確可靠的，所必須具備的四個(gè)特性：

• 原子性(atomicity，或稱(chēng)不可分割性)
• 一致性(consistency)
• 隔離性(isolation，又稱(chēng)獨(dú)立性)
• 持久性(durability)

ACID

①原子性(Atomicity)

一個(gè)事務(wù)(transaction)中的所有操作，或者全部完成，或者全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。

事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤，會(huì)被回滾(Rollback)到事務(wù)開(kāi)始前的狀態(tài)，就像這個(gè)事務(wù)從來(lái)沒(méi)有執(zhí)行過(guò)一樣。即，事務(wù)不可分割、不可約簡(jiǎn)。

②一致性(Consistency)

在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞。這表示寫(xiě)入的資料必須完全符合所有的預(yù)設(shè)約束、觸發(fā)器、級(jí)聯(lián)回滾等。

③隔離性(Isolation)

數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫(xiě)和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。

事務(wù)隔離分為不同級(jí)別，包括未提交讀(Read uncommitted)、提交讀(read committed)、可重復(fù)讀(repeatable read)和串行化(Serializable)。

④持久性(Durability)

事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。

來(lái)源維基百科：

https://zh.wikipedia.org/wiki/ACID 

事務(wù)(Transaction)

事務(wù)是進(jìn)程中最小的堆棧，不能分成更小的部分。

此外，某些事務(wù)處理組可以按順序執(zhí)行，但正如我們?cè)谠有栽瓌t中所解釋的那樣，即使其中一個(gè)事務(wù)失敗，所有事務(wù)塊也將失敗。

鎖定(Lock)

鎖定是一種確保數(shù)據(jù)一致性的機(jī)制。SQL Server 在事務(wù)啟動(dòng)時(shí)鎖定對(duì)象。事務(wù)完成后，SQL Server 將釋放鎖定的對(duì)象。可以根據(jù) SQL Server 進(jìn)程類(lèi)型和隔離級(jí)別更改此鎖定模式。

這些鎖定模式是：

①鎖定層次結(jié)構(gòu)

SQL Server 具有鎖定層次結(jié)構(gòu)，用于獲取此層次結(jié)構(gòu)中的鎖定對(duì)象。數(shù)據(jù)庫(kù)位于層次結(jié)構(gòu)的頂部，行位于底部。

下圖說(shuō)明了 SQL Server 的鎖層次結(jié)構(gòu)：

 

 

②共享(S)鎖(Shared (S) Locks)

當(dāng)需要讀取對(duì)象時(shí)，會(huì)發(fā)生此鎖定類(lèi)型。這種鎖定類(lèi)型不會(huì)造成太大問(wèn)題。

③獨(dú)占(X)鎖定(Exclusive (X) Locks)

發(fā)生此鎖定類(lèi)型時(shí)，會(huì)發(fā)生以防止其他事務(wù)修改或訪問(wèn)鎖定對(duì)象。

④更新(U)鎖(Update (U) Locks)

此鎖類(lèi)型與獨(dú)占鎖類(lèi)似，但它有一些差異。我們可以將更新操作劃分為不同的階段：讀取階段和寫(xiě)入階段。

在讀取階段，SQL Server 不希望其他事務(wù)有權(quán)訪問(wèn)此對(duì)象以進(jìn)行更改,因此，SQL Server 使用更新鎖。

⑤意圖鎖定(Intent Locks)

當(dāng) SQL Server 想要在鎖定層次結(jié)構(gòu)中較低的某些資源上獲取共享(S)鎖定或獨(dú)占(X)鎖定時(shí)，會(huì)發(fā)生意圖鎖定。

實(shí)際上，當(dāng) SQL Server 獲取頁(yè)面或行上的鎖時(shí)，表中需要設(shè)置意圖鎖。

SQL Server locking

了解了這些背景知識(shí)后，我們嘗試再 SQL Server 找到這些鎖。SQL Server 提供了許多動(dòng)態(tài)管理視圖來(lái)訪問(wèn)指標(biāo)。

要識(shí)別 SQL Server 鎖，我們可以使用 sys.dm_tran_locks 視圖。在此視圖中，我們可以找到有關(guān)當(dāng)前活動(dòng)鎖管理的大量信息。

在第一個(gè)示例中，我們將創(chuàng)建一個(gè)不包含任何索引的演示表，并嘗試更新此演示表：

CREATE TABLE TestBlock 
(Id INT , 
Nm VARCHAR(100)) 
 
INSERT INTO TestBlock 
values(1,'CodingSight') 
In this step, we will create an open transaction and analyze the locked resources. 
BEGIN TRAN 
UPDATE TestBlock SET   Nm='NewValue_CodingSight' where Id=1 
select @@SPID 

 

再獲取到了 SPID 后，我們來(lái)看看 sys.dm_tran_lock 視圖里有什么：

select * from sys.dm_tran_locks WHERE request_session_id=74 

 

此視圖返回有關(guān)活動(dòng)鎖資源的大量信息,但是是一些我們難以理解的一些數(shù)據(jù)。

因此，我們必須將 sys.dm_tran_locks join 一些其他表：

SELECT dm_tran_locks.request_session_id, 
       dm_tran_locks.resource_database_id, 
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname, 
       CASE 
           WHEN resource_type = 'OBJECT' 
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id) 
           ELSE OBJECT_NAME(partitions.OBJECT_ID) 
       END AS ObjectName, 
       partitions.index_id, 
       indexes.name AS index_name, 
       dm_tran_locks.resource_type, 
       dm_tran_locks.resource_description, 
       dm_tran_locks.resource_associated_entity_id, 
       dm_tran_locks.request_mode, 
       dm_tran_locks.request_status 
FROM sys.dm_tran_locks 
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id 
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id 
WHERE resource_associated_entity_id > 0 
  AND resource_database_id = DB_ID() 
 and request_session_id=74 
ORDER BY request_session_id, resource_associated_entity_id 

 

在上圖中，您可以看到鎖定的資源。SQL Server 獲取該行中的獨(dú)占鎖。(RID：用于鎖定堆中單個(gè)行的行標(biāo)識(shí)符)同時(shí)，SQL Server 獲取頁(yè)中的獨(dú)占鎖和 TestBlock 表意向鎖。

這意味著在 SQL Server 釋放鎖之前，任何其他進(jìn)程都無(wú)法讀取此資源，這是 SQL Server 中的基本鎖定機(jī)制。

現(xiàn)在，我們將在測(cè)試表上填充一些合成數(shù)據(jù)：

TRUNCATE TABLE       TestBlock 
DECLARE @K AS INT=0 
WHILE @K <8000 
BEGIN 
INSERT TestBlock VALUES(@K, CAST(@K AS varchar(10)) + ' Value' ) 
SET @K=@K+1 
 END 
--After completing this step, we will run two queries and check the sys.dm_tran_locks view. 
BEGIN TRAN 
 UPDATE TestBlock  set Nm ='New_Value' where Id<5000 

 

 

在上面的查詢(xún)中，SQL Server 獲取每一行的獨(dú)占鎖。現(xiàn)在，我們將運(yùn)行另一個(gè)查詢(xún)：

 

BEGIN TRAN 
 UPDATE TestBlock  set Nm ='New_Value' where Id<7000 

 

在上面的查詢(xún)中，SQL Server 在表上創(chuàng)建了獨(dú)占鎖，因?yàn)?SQL Server 嘗試為這些將要更新的行獲取大量 RID 鎖。

這種情況會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎中的大量資源消耗，因此，SQL Server 會(huì)自動(dòng)將此獨(dú)占鎖定移動(dòng)到鎖定層次結(jié)構(gòu)中的上級(jí)對(duì)象(Table)。

我們將此機(jī)制定義為 Lock Escalation，這就是我開(kāi)篇所說(shuō)的鎖升級(jí)，它由行鎖升級(jí)成了表鎖。

根據(jù)官方文檔的描述存在以下任一條件，則會(huì)觸發(fā)鎖定升級(jí)：

• 單個(gè) Transact-SQL 語(yǔ)句在單個(gè)非分區(qū)表或索引上獲取至少 5,000 個(gè)鎖。
• 單個(gè) Transact-SQL 語(yǔ)句在分區(qū)表的單個(gè)分區(qū)上獲取至少 5,000 個(gè)鎖，并且 ALTER TABLE SET LOCK_ESCALATION 選項(xiàng)設(shè)置為 AUTO。
• 數(shù)據(jù)庫(kù)引擎實(shí)例中的鎖數(shù)超過(guò)了內(nèi)存或配置閾值。

鏈接如下：

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms184286(v=sql.105) 

如何避免鎖升級(jí)

防止鎖升級(jí)的最簡(jiǎn)單，最安全的方法是保持事務(wù)的簡(jiǎn)短，并減少昂貴查詢(xún)的鎖占用空間，以便不超過(guò)鎖升級(jí)閾值，有幾種方法可以實(shí)現(xiàn)這一目標(biāo)。

①將大批量操作分解為幾個(gè)較小的操作

例如，在我開(kāi)篇所說(shuō)的在幾十億條數(shù)據(jù)中刪除小姐姐的數(shù)據(jù)：

delete from `后宮佳麗` where age>18 

我們可以不要這么心急,一次只刪除 500 個(gè)，可以顯著減少每個(gè)事務(wù)累積的鎖定數(shù)量并防止鎖定升級(jí)。

例如：

SET ROWCOUNT 500 
delete_more: 
     delete from `后宮佳麗` where age>18 
IF @@ROWCOUNT > 0 GOTO delete_more 
SET ROWCOUNT 0 

②創(chuàng)建索引使查詢(xún)盡可能高效來(lái)減少查詢(xún)的鎖定占用空間

如果沒(méi)有索引會(huì)造成表掃描可能會(huì)增加鎖定升級(jí)的可能性，更可怕的是，它增加了死鎖的可能性，并且通常會(huì)對(duì)并發(fā)性和性能產(chǎn)生負(fù)面影響。

根據(jù)查詢(xún)條件創(chuàng)建合適的索引，最大化提升索引查找的效率，此優(yōu)化的一個(gè)目標(biāo)是使索引查找返回盡可能少的行，以最小化查詢(xún)的的成本。

③如果其他 SPID 當(dāng)前持有不兼容的表鎖，則不會(huì)發(fā)生鎖升級(jí)

鎖定升級(jí)始總是升級(jí)成表鎖，而不會(huì)升級(jí)到頁(yè)面鎖定。

如果另一個(gè) SPID 持有與升級(jí)的表鎖沖突的 IX(intent exclusive)鎖定，則它會(huì)獲取更細(xì)粒度的級(jí)別(行，key 或頁(yè)面)鎖定，定期進(jìn)行額外的升級(jí)嘗試。

表級(jí)別的 IX(intent exclusive)鎖定不會(huì)鎖定任何行或頁(yè)面，但它仍然與升級(jí)的 S(共享)或 X(獨(dú)占)TAB 鎖定不兼容。

如下所示，如果有個(gè)操作始終在不到一小時(shí)內(nèi)完成，您可以創(chuàng)建包含以下代碼的 SQL，并安排在操作的前執(zhí)行：

BEGIN TRANSELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0WAITFOR DELAY '1:00:00'COMMIT TRAN

此查詢(xún)?cè)?mytable 上獲取并保持 IX 鎖定一小時(shí)，這可防止在此期間對(duì)表進(jìn)行鎖定升級(jí)。

Happy Ending

好了，不說(shuō)了，小姐姐們因?yàn)椴幌腚x我開(kāi)又打起來(lái)了(死鎖)。

[[344495]]

 

作者：阿宇

編輯：陶家龍

出處：https://www.cnblogs.com/CoderAyu/