熱門標(biāo)簽：服務(wù)外包 AI電銷 網(wǎng)站排名優(yōu)化 Linux服務(wù)器 百度競(jìng)價(jià)排名 鐵路電話系統(tǒng) 呼叫中心市場(chǎng)需求 地方門戶網(wǎng)站

一、鎖的基本概念：

鎖定(Locking)是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)的常規(guī)和必要的一部分，它防止對(duì)相同數(shù)據(jù)作 并發(fā)更新 或在更新過(guò)程中查看數(shù)據(jù)， 從而保證被更新數(shù)據(jù)的完整性。它也能防止用戶讀取正在被修改的數(shù)據(jù) 。Sql Server動(dòng)態(tài)地管理鎖定，然而，還是很有必要 了解Transact- SQL查詢如何影響SQL Server中的鎖定。在此，簡(jiǎn)單介紹下鎖的基本常識(shí)。

鎖定有助于防止并發(fā)問(wèn)題的發(fā)生。當(dāng)一個(gè)用戶試圖讀取另一個(gè)用戶正在修改的數(shù)據(jù)，或者修改另一個(gè)用戶正在讀取的數(shù)據(jù)時(shí)，或者嘗試修改另一個(gè)事務(wù)正在嘗試修改的數(shù)據(jù)時(shí)，就會(huì)出現(xiàn)并發(fā)問(wèn)題。

SQL Server資源會(huì)被鎖定，資源的鎖定方式稱作它的鎖定模式(lock mode)，下表列出SQL Server處理的主要鎖定模式：

名稱	描述
共享 (S)	用于不更改或不更新數(shù)據(jù)的讀取操作，如 SELECT 語(yǔ)句。
更新 (U)	用于可更新的資源中。 防止當(dāng)多個(gè)會(huì)話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見(jiàn)形式的死鎖。
排他 (X)	用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。 確保不會(huì)同時(shí)對(duì)同一資源進(jìn)行多重更新。
意向	用于建立鎖的層次結(jié)構(gòu)。 意向鎖包含三種類型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。
架構(gòu)	在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。 架構(gòu)鎖包含兩種類型：架構(gòu)修改 (Sch-M) 和架構(gòu)穩(wěn)定性 (Sch-S)。
大容量更新 (BU)	在向表進(jìn)行大容量數(shù)據(jù)復(fù)制且指定了 TABLOCK 提示時(shí)使用。
鍵范圍	當(dāng)使用可序列化事務(wù)隔離級(jí)別時(shí)保護(hù)查詢讀取的行的范圍。 確保再次運(yùn)行查詢時(shí)其他事務(wù)無(wú)法插入符合可序列化事務(wù)的查詢的行。

可以鎖定SQL Server中的各種對(duì)象，既可以是一個(gè)行，也可以是一個(gè)表或數(shù)據(jù)庫(kù)。可以鎖定的資源在粒度（granularity）上差異很大。從細(xì)（行）到粗（數(shù)據(jù)庫(kù)）。細(xì)粒度鎖允許更大的數(shù)據(jù)庫(kù)并發(fā)，因?yàn)橛脩裟軐?duì)某些未鎖定的行執(zhí)行查詢。然而，每個(gè)由SQL Server產(chǎn)生的鎖都需要內(nèi)存，所以數(shù)以千計(jì)獨(dú)立的行級(jí)別的鎖也會(huì)影響SQL Server的性能。粗粒度的鎖降低了并發(fā)性，但消耗的資源也較少。下表介紹SQL Server可以鎖定的資源：

資源	說(shuō)明
KEY	索引中用于保護(hù)可序列化事務(wù)中的鍵范圍的行鎖。
PAGE	數(shù)據(jù)庫(kù)中的 8 KB 頁(yè)，例如數(shù)據(jù)頁(yè)或索引頁(yè)。
EXTENT	一組連續(xù)的八頁(yè)，例如數(shù)據(jù)頁(yè)或索引頁(yè)。
HoBT	堆或 B 樹(shù)。 用于保護(hù)沒(méi)有聚集索引的表中的 B 樹(shù)（索引）或堆數(shù)據(jù)頁(yè)的鎖。
TABLE	包括所有數(shù)據(jù)和索引的整個(gè)表。
FILE	數(shù)據(jù)庫(kù)文件。
RID	用于鎖定堆中的單個(gè)行的行標(biāo)識(shí)符。
APPLICATION	應(yīng)用程序?qū)Ｓ玫馁Y源。
METADATA	元數(shù)據(jù)鎖。
ALLOCATION_UNIT	分配單元。
DATABASE	整個(gè)數(shù)據(jù)庫(kù)。

不是所有的鎖都能彼此兼容。例如，一個(gè)被排他鎖鎖定的資源不能被再加其他鎖。其他事務(wù)必須等待或超時(shí)，直到排他鎖被釋放。被更新鎖鎖定的資源只能接受其他事務(wù)的共享鎖。被共享鎖鎖定的資源還能接受其他的共享鎖或更新鎖。

SQL Server自動(dòng)分配和升級(jí)鎖。升級(jí)意味著細(xì)粒度的鎖（行或頁(yè)鎖）被轉(zhuǎn)化為粗粒度的表鎖。當(dāng)單個(gè)T－SQL語(yǔ)句在單個(gè)表或索引上獲取5000多個(gè)鎖,或者SQL Server實(shí)例中的鎖數(shù)量超過(guò)可用內(nèi)存閾值時(shí)，SQL Server會(huì)嘗試啟動(dòng)鎖升級(jí)。鎖占用系統(tǒng)內(nèi)存，因此把很多鎖轉(zhuǎn)化為一個(gè)較大的鎖能釋放內(nèi)存資源。然而，在釋放內(nèi)存資源的同時(shí)會(huì)降低并發(fā)性。

SQL Server 2008帶來(lái)了新的表選項(xiàng)，可以禁用鎖升級(jí)或在分區(qū)（而不是表）范圍啟用鎖升級(jí)。

二、查看鎖的活動(dòng)

下面演示一個(gè)實(shí)例，它使用sys.dm_tran_locks動(dòng)態(tài)視圖監(jiān)視數(shù)據(jù)庫(kù)中鎖的活動(dòng)。

打開(kāi)一個(gè)查詢窗口，執(zhí)行如下語(yǔ)句：

打開(kāi)另一個(gè)查詢窗口，執(zhí)行：

執(zhí)行結(jié)果：

解析：本示例中，我們首先啟動(dòng)了一個(gè)新事務(wù)，并使用TABLOCKX鎖提示（這個(gè)提示對(duì)表放置了排他鎖），對(duì)Production.ProductDocument表執(zhí)行了一個(gè)查詢。查詢sys.dm_tran_locks動(dòng)態(tài)管理視力可以監(jiān)視當(dāng)前SQL Server實(shí)例中打開(kāi)了哪些鎖。它返回了AdventureWorks數(shù)據(jù)庫(kù)中活動(dòng)鎖的列表?？梢栽诮Y(jié)果中的最后一行看到ProductDocument表上的排他鎖。

前三列定義了會(huì)話鎖、資源類型和數(shù)據(jù)庫(kù)ID。第四列使用了Object_Name函數(shù)，注意它使用了兩個(gè)參數(shù)（對(duì)象ID和數(shù)據(jù)庫(kù)ID）來(lái)指定訪問(wèn)哪個(gè)名稱（第二個(gè)參數(shù)是SQL Server 2005 SP2引入的，它用來(lái)指定為了轉(zhuǎn)換對(duì)象名稱而使用哪個(gè)數(shù)據(jù)庫(kù)）。同時(shí)也查詢鎖定請(qǐng)求模式和狀態(tài)，最后，F(xiàn)rom子句引用DMV，用Where子句指定了兩個(gè)資源類型。Resource_Type指定了鎖定的資源類型，如Database\Object\File\Page\Key\RID\Extent\Metadata\Application\Allocation_Unit或HOBT類型。依賴資源類型的resource_associated_entity_id，確定ID是object ID, allocation unit ID, 或Hobt ID。

■如果resource_associated_entity_id列包含Object ID(資源類型為Object),可以使用sys.objects目錄視圖來(lái)轉(zhuǎn)換名稱。

■如果resource_associated_entity_id列包含allocation unit ID(資源類型為Allocation_Unit),可以引用sys.allocatiion_units和contain_id聯(lián)結(jié)到sys.partitions上，就可以確定object ID。

■如果resource_associated_entity_id列包含Hobt ID(資源類型為Key\page\Row或HOBT),可以直接引用sys.partitions,然后查找相應(yīng)的Object ID。

■對(duì)于Database、Extent、 Application或MetaData的資源類型，resource_associated_entity_id列將為0。

使用sys.dm_tran_locks能對(duì)無(wú)法預(yù)料的并發(fā)問(wèn)題進(jìn)行故障調(diào)試。例如，一個(gè)查詢會(huì)話占用鎖的時(shí)間可能比預(yù)期時(shí)間長(zhǎng)而被鎖，或者鎖的粒度或鎖模式不是我們所期望的（可能是希望使用表鎖而不是更小粒度的行鎖或頁(yè)鎖）。理解鎖處于的鎖定級(jí)別有助于我們更有效地對(duì)查詢的并發(fā)問(wèn)題進(jìn)行故障調(diào)試。

三、控制表的鎖升級(jí)行為

每個(gè)在SQL Server中創(chuàng)建的鎖都會(huì)消耗內(nèi)存資源。當(dāng)鎖的數(shù)量增加時(shí)，內(nèi)存就會(huì)減少。如果鎖的內(nèi)存使用百分比超過(guò)一個(gè)特定閾值，SQL Server會(huì)將細(xì)粒度鎖（頁(yè)或行）轉(zhuǎn)換為粗粒度鎖（表鎖）。這個(gè)過(guò)程稱為鎖升級(jí)。鎖升級(jí)可以減少SQL Server實(shí)例占有的鎖數(shù)量，減少鎖內(nèi)存的使用。

雖然細(xì)粒度會(huì)消耗更多的內(nèi)存，但由于多個(gè)查詢可以訪問(wèn)未鎖定的行，因此也會(huì)改善并發(fā)性。引入表鎖可能會(huì)減少內(nèi)存的消耗，但也會(huì)帶來(lái)阻塞，這是因?yàn)橐粭l查詢鎖住了整個(gè)表。根據(jù)使用數(shù)據(jù)庫(kù)的應(yīng)用程序，這個(gè)行為可能是不希望發(fā)生的，而且你可能希望當(dāng)SQL Server實(shí)施鎖升級(jí)時(shí)盡量獲得更多的控制。

SQL Server 2008引入了使用ALter table命令在表級(jí)別控制鎖升級(jí)的功能?，F(xiàn)在可以從如下3個(gè)設(shè)置中選擇：

■Table 這是SQL Server 2005中使用的默認(rèn)行為。當(dāng)設(shè)置為該值時(shí)，在表級(jí)別啟用了鎖升級(jí)，不論是否為分區(qū)表。

■Auto 如果表已分區(qū)，則在分區(qū)級(jí)別（堆或B樹(shù)）啟用鎖升級(jí)。如果表未分區(qū)，鎖升級(jí)將發(fā)生在表級(jí)別上。

■Disable 在表級(jí)別刪除鎖升級(jí)。注意，對(duì)于用了TABLOCK 提示或使用可序列化隔離級(jí)別下Heap的查詢時(shí)，你仍然可能看到表鎖。

下面示例演示了修改表的新設(shè)置：

下來(lái)，我們禁用鎖升級(jí)：

說(shuō)明：在更改了這個(gè)配置后，可以通過(guò)查詢sys.tables目錄視圖的lock_escalation_desc列來(lái)驗(yàn)證這個(gè)選項(xiàng)。

注意：如果表未分區(qū)，通常情況為表級(jí)別升級(jí)。如果你指定了Disable選項(xiàng)，將不會(huì)出現(xiàn)表級(jí)別的鎖升級(jí)。這會(huì)提高并發(fā)性，但如果你請(qǐng)求訪問(wèn)大量的行或頁(yè)，會(huì)增加內(nèi)存的消耗。
邀月 來(lái)自 http://www.cnblogs.com/downmoon