聊一聊數(shù)據(jù)庫中的鎖
點(diǎn)擊上方藍(lán)色“程序猿DD”，選擇“設(shè)為星標(biāo)”
回復(fù)“資源”獲取獨(dú)家整理的學(xué)習(xí)資料！
背景
數(shù)據(jù)庫中有一張叫后宮佳麗的表,每天都有幾百萬新的小姐姐插到表中,光陰荏苒,夜以繼日,日久生情,時(shí)間長了,表中就有了幾十億的小姐姐數(shù)據(jù),看到幾十億的小姐姐,每到晚上,我可愁死了,這么多小姐姐,我翻張牌呢?
辦法當(dāng)然是精兵簡政,刪除那些age>18的,給年輕的小姐姐們留位置...
于是我在數(shù)據(jù)庫中添加了一個(gè)定時(shí)執(zhí)行的小程序,每到周日,就自動(dòng)運(yùn)行如下的腳本
Copydelete from `后宮佳麗` where age>18
一開始還自我感覺良好,后面我就發(fā)現(xiàn)不對(duì)了,每到周日,這個(gè)腳本一執(zhí)行就是一整天,運(yùn)行的時(shí)間有點(diǎn)長是小事,重點(diǎn)是這大好周日,我再想讀這張表的數(shù)據(jù),怎么也讀不出來了,怎是一句空虛了得,我好難啊!
為什么
編不下去了,真實(shí)背景是公司中遇到的一張有海量數(shù)據(jù)表,每次一旦執(zhí)行歷史數(shù)據(jù)的清理,我們的程序就因?yàn)樽x不到這張表的數(shù)據(jù),瘋狂地報(bào)錯(cuò),后面一查了解到,原來是因?yàn)槎〞r(shí)刪除的語句設(shè)計(jì)不合理,導(dǎo)致數(shù)據(jù)庫中數(shù)據(jù)由行鎖(Row lock)升級(jí)為表鎖(Table lock)了?.
解決這個(gè)問題的過程中把數(shù)據(jù)庫鎖相關(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í)
在我開始解釋SQL Server鎖定體系結(jié)構(gòu)之前，讓我們花點(diǎn)時(shí)間來描述ACID（原子性，一致性，隔離性和持久性）是什么。ACID是指數(shù)據(jù)庫管理系統(tǒng)（DBMS）在寫入或更新資料的過程中，為保證事務(wù)（transaction）是正確可靠的，所必須具備的四個(gè)特性：原子性（atomicity，或稱不可分割性）、一致性（consistency）、隔離性（isolation，又稱獨(dú)立性）、持久性（durability）。
ACID
原子性(Atomicity)
一個(gè)事務(wù)（transaction）中的所有操作，或者全部完成，或者全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣。即，事務(wù)不可分割、不可約簡。
一致性(Consistency)
在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)約束、觸發(fā)器、級(jí)聯(lián)回滾等。
隔離性(Isolation)
數(shù)據(jù)庫允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(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ì)丟失。
來源:維基百科?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)程類型和隔離級(jí)別更改此鎖定模式。這些鎖定模式是：
鎖定層次結(jié)構(gòu)
SQL Server具有鎖定層次結(jié)構(gòu)，用于獲取此層次結(jié)構(gòu)中的鎖定對(duì)象。數(shù)據(jù)庫位于層次結(jié)構(gòu)的頂部，行位于底部。下圖說明了SQL Server的鎖層次結(jié)構(gòu)。
共享（S）鎖 (Shared (S) Locks)
當(dāng)需要讀取對(duì)象時(shí)，會(huì)發(fā)生此鎖定類型。這種鎖定類型不會(huì)造成太大問題。
獨(dú)占（X）鎖定 (Exclusive (X) Locks)
發(fā)生此鎖定類型時(shí)，會(huì)發(fā)生以防止其他事務(wù)修改或訪問鎖定對(duì)象。
更新（U）鎖 (Update (U) Locks)
此鎖類型與獨(dú)占鎖類似，但它有一些差異。我們可以將更新操作劃分為不同的階段：讀取階段和寫入階段。在讀取階段，SQL Server不希望其他事務(wù)有權(quá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獲取頁面或行上的鎖時(shí)，表中需要設(shè)置意圖鎖。
SQL Server locking
了解了這些背景知識(shí)后，我們嘗試再SQL Server找到這些鎖。SQL Server提供了許多動(dòng)態(tài)管理視圖來訪問指標(biāo)。要識(shí)別SQL Server鎖，我們可以使用sys.dm_tran_locks視圖。在此視圖中，我們可以找到有關(guān)當(dāng)前活動(dòng)鎖管理的大量信息。
在第一個(gè)示例中，我們將創(chuàng)建一個(gè)不包含任何索引的演示表，并嘗試更新此演示表。
CopyCREATE 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后，我們來看看sys.dm_tran_lock視圖里有什么。
Copyselect * from sys.dm_tran_locks WHERE request_session_id=74
此視圖返回有關(guān)活動(dòng)鎖資源的大量信息,但是是一些我們難以理解的一些數(shù)據(jù)。因此，我們必須將sys.dm_tran_locks?join 一些其他表。
CopySELECT 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獲取頁中的獨(dú)占鎖和TestBlock表意向鎖。這意味著在SQL Server釋放鎖之前，任何其他進(jìn)程都無法讀取此資源,這是SQL Server中的基本鎖定機(jī)制。
現(xiàn)在，我們將在測試表上填充一些合成數(shù)據(jù)。
CopyTRUNCATE 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
在上面的查詢中，SQL Server獲取每一行的獨(dú)占鎖?，F(xiàn)在，我們將運(yùn)行另一個(gè)查詢。
CopyBEGIN TRAN
UPDATE TestBlock set Nm ='New_Value' where Id<7000
在上面的查詢中，SQL Server在表上創(chuàng)建了獨(dú)占鎖，因?yàn)镾QL Server嘗試為這些將要更新的行獲取大量RID鎖,這種情況會(huì)導(dǎo)致數(shù)據(jù)庫引擎中的大量資源消耗,因此，SQL Server會(huì)自動(dòng)將此獨(dú)占鎖定移動(dòng)到鎖定層次結(jié)構(gòu)中的上級(jí)對(duì)象(Table)。我們將此機(jī)制定義為Lock Escalation, 這就是我開篇所說的鎖升級(jí),它由行鎖升級(jí)成了表鎖。
根據(jù)官方文檔的描述存在以下任一條件，則會(huì)觸發(fā)鎖定升級(jí)：
單個(gè)Transact-SQL語句在單個(gè)非分區(qū)表或索引上獲取至少5,000個(gè)鎖。
單個(gè)Transact-SQL語句在分區(qū)表的單個(gè)分區(qū)上獲取至少5,000個(gè)鎖，并且ALTER TABLE SET LOCK_ESCALATION選項(xiàng)設(shè)置為AUTO。
數(shù)據(jù)庫引擎實(shí)例中的鎖數(shù)超過了內(nèi)存或配置閾值。
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms184286(v=sql.105)
如何避免鎖升級(jí)
防止鎖升級(jí)的最簡單，最安全的方法是保持事務(wù)的簡短，并減少昂貴查詢的鎖占用空間，以便不超過鎖升級(jí)閾值,有幾種方法可以實(shí)現(xiàn)這一目標(biāo).
將大批量操作分解為幾個(gè)較小的操作
例如，在我開篇所說的在幾十億條數(shù)據(jù)中刪除小姐姐的數(shù)據(jù)：
Copydelete from `后宮佳麗` where age>18
我們可以不要這么心急,一次只刪除500個(gè)，可以顯著減少每個(gè)事務(wù)累積的鎖定數(shù)量并防止鎖定升級(jí)。例如：
CopySET ROWCOUNT 500
delete_more:
delete from `后宮佳麗` where age>18
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
創(chuàng)建索引使查詢盡可能高效來減少查詢的鎖定占用空間
如果沒有索引會(huì)造成表掃描可能會(huì)增加鎖定升級(jí)的可能性, 更可怕的是，它增加了死鎖的可能性，并且通常會(huì)對(duì)并發(fā)性和性能產(chǎn)生負(fù)面影響。
根據(jù)查詢條件創(chuàng)建合適的索引,最大化提升索引查找的效率,此優(yōu)化的一個(gè)目標(biāo)是使索引查找返回盡可能少的行，以最小化查詢的的成本。
如果其他SPID當(dāng)前持有不兼容的表鎖，則不會(huì)發(fā)生鎖升級(jí)
鎖定升級(jí)始總是升級(jí)成表鎖，而不會(huì)升級(jí)到頁面鎖定。如果另一個(gè)SPID持有與升級(jí)的表鎖沖突的IX（intent exclusive）鎖定，則它會(huì)獲取更細(xì)粒度的級(jí)別（行，key或頁面）鎖定，定期進(jìn)行額外的升級(jí)嘗試。表級(jí)別的IX（intent exclusive）鎖定不會(huì)鎖定任何行或頁面，但它仍然與升級(jí)的S（共享）或X（獨(dú)占）TAB鎖定不兼容。
如下所示,如果有個(gè)操作始終在不到一小時(shí)內(nèi)完成，您可以創(chuàng)建包含以下代碼的sql，并安排在操作的前執(zhí)行
CopyBEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN
此查詢?cè)趍ytable上獲取并保持IX鎖定一小時(shí)，這可防止在此期間對(duì)表進(jìn)行鎖定升級(jí)。
Happy Ending
好了,不說了,小姐姐們因?yàn)椴幌腚x我開又打起來了(死鎖).
參考文獻(xiàn):
SQL Server Transaction Locking and Row Versioning Guide?https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-guides/jj856598(v=sql.110)
SQL Server, Locks Object?https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-locks-object?view=sql-server-2017
How to resolve blocking problems that are caused by lock escalation in SQL Server?https://support.microsoft.com/es-ve/help/323630/how-to-resolve-blocking-problems-that-are-caused-by-lock-escalation-in
Main concept of SQL Server locking?https://codingsight.com/main-concept-of-sql-server-locking/
往期推薦
我的星球是否適合你？
點(diǎn)擊閱讀原文看看我們都聊過啥？