如何掌握SQLServer的鎖機制

SQL SERVER里的鎖機制：

十余年專注成都網(wǎng)站制作，企業(yè)網(wǎng)站制作，個人網(wǎng)站制作服務，為大家分享網(wǎng)站制作知識、方案，網(wǎng)站設計流程、步驟,成功服務上千家企業(yè)。為您提供網(wǎng)站建設,網(wǎng)站制作,網(wǎng)頁設計及定制高端網(wǎng)站建設服務,專注于企業(yè)網(wǎng)站制作,高端網(wǎng)頁制作,對假山制作等多個方面，擁有豐富的網(wǎng)站推廣經(jīng)驗。

NOLOCK（不加鎖）

此選項被選中時，SQL Server 在讀取或修改數(shù)據(jù)時不加任何鎖。 在這種情況下，用戶有可能讀取到未完成事務（Uncommited Transaction）或回滾(Roll Back)中的數(shù)據(jù), 即所謂的“臟數(shù)據(jù)”。

HOLDLOCK（保持鎖）

此選項被選中時，SQL Server 會將此共享鎖保持至整個事務結束，而不會在途中釋放。 例如，“ SELECT * FROM my_table HOLDLOCK”就要求在整個查詢過程中，保持對表的鎖定，直到查詢完成才釋放鎖定。

UPDLOCK（修改鎖）

此選項被選中時，SQL Server 在讀取數(shù)據(jù)時使用修改鎖來代替共享鎖，并將此鎖保持至整個事務或命令結束。使用此選項能夠保證多個進程能同時讀取數(shù)據(jù)但只有該進程能修改數(shù)據(jù)。

TABLOCK（表鎖）

此選項被選中時，SQL Server 將在整個表上置共享鎖直至該命令結束。 這個選項保證其他進程只能讀取而不能修改數(shù)據(jù)。

PAGLOCK（頁鎖）

此選項為默認選項， 當被選中時，SQL Server 使用共享頁鎖。

TABLOCKX（排它表鎖）

此選項被選中時，SQL Server 將在整個表上置排它鎖直至該命令或事務結束。這將防止其他進程讀取或修改表中的數(shù)據(jù)。

sqlserver事務中更新某張表是不是就開啟了排它鎖

有排他鎖，但是排他鎖生存時間非常的短，

1. 當開始更新時首先在表上放一個架構鎖，防止其他事務修改架構；

2. 在非序列化隔離級別下，整個表上會放一個意向共享鎖，允許其他事務進行讀取；

3. 然后事務開始更新這個表，更新是會逐行更新的，你可以把更新理解為一個游標；

4. 每一行上首先放上一個更新鎖，成功放置更新鎖以后，更新鎖會變?yōu)榕潘i；

5. 然后更新這一行數(shù)據(jù)，更新完畢后就會釋放這一行的排它鎖；

6. 整個表遍歷完畢后釋放架構鎖，釋放意向共享鎖。

sql server 2005 2709 出錯 是什么意思

鎖升級 禁止升級　

鎖定粒度是一個查詢或更新所鎖定的最小數(shù)據(jù)，粒度不同數(shù)據(jù)庫的性能和并發(fā)能力是此消彼長的，怎么來理解呢？鎖定的粒度越小并發(fā)的用戶數(shù)越多，這是顯而易的，如果這時發(fā)生一種情況，根據(jù)業(yè)務規(guī)律要鎖定大量的記錄行來進行更新，在保持并發(fā)用戶的前提下，我們鎖定的記錄的行鎖或鍵鎖就很多，我們知道鎖定不是免費的午餐，是要付出代價的，管理的鎖定多越多系統(tǒng)資源開銷就越大。還記得我們在前面介紹過鎖塊吧，鎖塊是一個64/128(128是64位操作系統(tǒng))字節(jié)的內(nèi)存塊，另外對每一個申請或正持有鎖塊的進程還要準備一個32/64(64是64位操作系統(tǒng))字節(jié)的內(nèi)存塊來描述這些進程，在這兒我們確定一個前提：不管鎖定粒度的大小，每一個鎖定都占用幾乎同樣的系統(tǒng)開銷。好，比如我們要進行10W行數(shù)據(jù)更新，為了并發(fā)我們都采用行鎖來鎖定，按照鎖塊的定義那么我們就得需要64B * 100000+N*32B= 6400000B +32NB(理論更新我們?nèi)=1相對于6400000可以忽略) 6.4M的RAM來管理這些行鎖，假設并發(fā)進程(當然是不同資源上的)數(shù)量是X，那么當前數(shù)據(jù)庫就得要X*6.4M的RAM用于管理鎖定，顯然這種對RAM需求的上升是系統(tǒng)無法忍受，不可能無限制的滿足的這種增長，那么SQLSERVER得用一種辦法來防止系統(tǒng)使用太多的內(nèi)存來追蹤鎖定并且提高鎖定的效率。這個任務交給了鎖管理器，它負責平衡資源的使用(當然還負責從特定操作的開始到結束保持連續(xù)、邏輯完整性)，這時管理器就采取鎖定升級這一明智造擇，從行鎖或鍵鎖或頁鎖升級為表級鎖定，比較6.4M和96B，顯然獲取一個表級鎖定比持有許多行或鍵鎖更有意義。

鎖升級的意義是顯而易見，使得鎖定開銷下降并避免系統(tǒng)資源耗盡。在結構引擎里我們提及鎖管理器，系統(tǒng)分配給鎖管理器的內(nèi)存是有限的，鎖的升級保證了鎖定占用內(nèi)存維持一個合理的限度。

鎖升級發(fā)生的時機：

1、 在一個對象上一個查詢或更新持有鎖的數(shù)量超過閥值。SQL2005缺省是5000個鎖(記得SQL6.0只有200個，但是我們要記住SQL6.0只有頁面鎖定哦)。

2、 鎖資源占用的內(nèi)存超過AWE或常規(guī)內(nèi)存的40%，40%是一個約數(shù)。

時機一滿足SQLSERVER就會嘗試鎖升級，當然升級不一定會成功，當失敗后在同一個對象上的鎖資源再次上升到一定程度時升級會再次發(fā)生，如果升級成功SQLSERVER會釋放對象上先前獲得的行、鍵、分頁鎖定。升級失敗發(fā)生當另外一個進程對表有行或頁有排它鎖定時。

鎖升級潛在的危險：

1、 鎖升級的結果一定是一個完全表級鎖定，也就是不可能出現(xiàn)行鎖升級為頁鎖的，最細的行級鎖升級的直接結果一定是表鎖定。

2、 鎖升級可能造成意外的阻塞(這個應該是很好理解的)

3、 鎖升級成功后無法降級

禁止升級 我們知道鎖升級是有潛在的危險，并且這種升級的結果是不可能現(xiàn)降級除非事務結束。所以升級不是對所有的應用都是一件好事，MS提供了兩個開關項：1211和1224，我們可以通過設置跟蹤標識來禁止升級。

7、行鎖、頁鎖

7.0之前的版本鎖定的最小粒度就是頁鎖，提醒大家一下那時的頁面最小單位是2K，如果細心部署一定程度上是可以滿足夠大的呑吐量和可以接受的響應時間。然后7.0后把分頁從2KB提升為8KB時(為什么要提升呢？嘿嘿，留一個疑問給大家)，這種頁面鎖定對并發(fā)能力是一種挑戰(zhàn)，也就是鎖定的范圍是7.0之前的4倍，這時并發(fā)及響應時間都成一個問題。SQL2005完全實現(xiàn)行級鎖定，顯然這對并發(fā)響應是可喜的，可是正如我在鎖升級里給大家算的一筆帳，在有限可利用的鎖定資源前提下，大量行級鎖定的代價還是讓人無法接受的，特別在極限的狀態(tài)下。

我們知道鎖定操作是一個密集型操作，一個鎖定不僅要看到內(nèi)存的損耗，還要看到SQLSERVER管理這些鎖定對其本身來也是一種負荷。雖然SQL內(nèi)部使用閂或自旋鎖來降低這種負荷，但我們很容易可以想像管理一個分頁鎖定比管理N個行級鎖定(假設頁面內(nèi)有N行記錄)更輕松、更有效率。

比較行鎖和頁鎖，行鎖降低了并發(fā)沖突但是資源的損耗也是顯然的，頁鎖減少必須存在鎖的數(shù)量及管理這些鎖定的資源損耗但是以并發(fā)能下降為代價的。到底哪個更合適，恐怕不是一句兩句能說完的，因為針對不同應用、不同行業(yè)、不同并發(fā)模型、不同隔離兩都各有各的優(yōu)勢。

在SQLSERVER2005可以用sp_indexoption來控制索引的鎖定單位。關于這個設置我們可以看看聯(lián)機幫助，但是一定要注意它只針對索引所以對堆表無法控制分頁鎖定。

8、動態(tài)管理鎖定

SQL造擇鎖定類型、粒度是基于行數(shù)、可能掃描的頁面數(shù)、分頁上的行數(shù)，隔離級別、進行的何種操作、可使用的系統(tǒng)資源等因素的影響 ，根據(jù)這些影響因素SQLSERVER選擇一種合適的鎖定模式這個過程稱動態(tài)鎖定策略(我發(fā)現(xiàn)策略在MS很流行)，數(shù)據(jù)庫引擎(還有印象我有引擎結構中介紹的存儲引擎吧)動態(tài)的管理粒度和鎖定模式，控制鎖定與系統(tǒng)資源的最佳成本效率。一個范圍內(nèi)的鎖定所要使用的系統(tǒng)資源肯定小，但是系統(tǒng)的并發(fā)性也就降低，如果選擇小范圍內(nèi)的鎖定，那管理鎖定所使用的系統(tǒng)資源上升，然而并發(fā)性能卻得到了淋漓發(fā)揮。

一般情況我們可使用系統(tǒng)缺省設置(行級鎖定是系統(tǒng)缺省的)，讓系統(tǒng)決定是否要進行鎖定的升級。這樣一來簡化我們對庫鎖定的管理，系統(tǒng)根據(jù)實際情況平衡負載。

9、死鎖

首先，我們得清楚死鎖與等待是兩回事。等待是當前進程所需要的資源讓另一個進程排它了，只要另外一個進程釋放，當時進程就可以繼續(xù)執(zhí)行(當然如果另外這個進程已經(jīng)死鎖那會進入無限期等待，但是這種情況一般不會發(fā)生，因為SQLSERVER會干預死鎖的。另外我們還有一個鎖定超時設置 ，這方面大家可以看聯(lián)機叢書)。而死鎖是發(fā)生在兩個進程間，在沒有人為干預兩個鎖定的進程是都無法繼續(xù)工作的一種困境。另外一個顯著的地方就是死鎖一旦發(fā)生，SQLSERVER就會干預進來，我們所能感知比如接收到1205號錯誤，健壯的應用系統(tǒng)會人工干預1205錯誤，恰當?shù)闹匦绿峤慌幚恚?205錯誤發(fā)生沒有終止的進程獲得相應的資源并處理自己的事務直至釋放資源，其實這種人為的干預潛在的又為死鎖提供一個外在環(huán)境。當然我們前面寫的一個過程也可以查詢到相應的鎖定信息。

接著，死鎖是無法完全避免的。在一個并發(fā)的多用戶系統(tǒng)，鎖定、線程、內(nèi)存、并行查詢、MARS中死鎖的發(fā)生是正常的、可以預見的，也是必然的。在我們能力范圍內(nèi)只能盡可能的在應用端或服務器上恰當?shù)奶幚硭梨i，使得這種無法完全避免的事件給系統(tǒng)帶來的影響降到最低。也就是我們應該明白：死鎖是無法完全避免，但是我可以降低發(fā)生的次數(shù)。

第三，死鎖是一種末日，沒有人為干預時永遠退不出這種狀態(tài)。一個并發(fā)的多用戶系統(tǒng)這種競爭資源的可能性是很大的，一有競爭就會有“矛盾”發(fā)生，雙方等待對方釋放自己所需要的資源，必然成了無限期等待，這種等待就是我們所說的死鎖。我們通過上面的介紹知道這時SQLSERVER鎖管理器會干預這個過程，試想如果沒有SQLSERVER鎖管理器的干預那么兩個進程一根筯的結果就是無限期等待，對于應用系統(tǒng)來說就是一個末日。SQLSERVER2005更是提供了豐富的鎖有關元數(shù)據(jù)，可以很方便的偵察出鎖定信息，SQLSERVER鎖管理器干預的結果就是根據(jù)犧牲品的優(yōu)先等級及回滾代價，把優(yōu)先級低和代價最小的進程當作犧牲品，殺掉這個進程并拋出1205錯誤。

第四，死鎖大體分為三類：cycle死鎖、conversion死鎖、應用級死鎖及不明死鎖。

如何處理SQL Server死鎖問題？

死鎖，簡而言之，兩個或者多個trans，同時請求對方正在請求的某個對象，導致雙方互相等待。簡單的例子如下：\x0d\x0a trans1 trans2\x0d\x0a ------------------------------------------------------------------------\x0d\x0a 1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction\x0d\x0a 2.update table A 2.update table B\x0d\x0a 3.update table B 3.update table A\x0d\x0a 4.IDBConnection.Commit 4.IDBConnection.Commit \x0d\x0a 那么，很容易看到，如果trans1和trans2，分別到達了step3，那么trans1會請求對于B的X鎖，trans2會請求對于A的X鎖，而二者的鎖在step2上已經(jīng)被對方分別持有了。由于得不到鎖，后面的Commit無法執(zhí)行，這樣雙方開始死鎖。\x0d\x0a 好，我們看一個簡單的例子，來解釋一下，應該如何解決死鎖問題。\x0d\x0a -- Batch #1\x0d\x0a CREATE DATABASE deadlocktest\x0d\x0a GO\x0d\x0a USE deadlocktest\x0d\x0a SET NOCOUNT ON\x0d\x0a DBCC TRACEON (1222, -1)\x0d\x0a -- 在SQL2005中，增加了一個新的dbcc參數(shù)，就是1222，原來在2000下，我們知道，可以執(zhí)行dbcc \x0d\x0a --traceon(1204,3605,-1)看到所有的死鎖信息。SqlServer 2005中，對于1204進行了增強，這就是1222。\x0d\x0a GO \x0d\x0a \x0d\x0a IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1\x0d\x0a IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1\x0d\x0a IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2\x0d\x0a GO\x0d\x0a CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000)) \x0d\x0a GO\x0d\x0a DECLARE @x int\x0d\x0a SET @x = 1\x0d\x0a WHILE (@x = [@p1] AND [t1].[c2] = [@p1] AND [deadlocktest].[dbo].[t1].[c2]

回答于?2022-11-16

sqlserver鎖表機制

這個問題要具體分析：

第一，事務隔離級別基本兩種模式，一種是阻塞式（read committed，repeatable read，serializable）

，一種是非阻塞式(read uncommitted,snapshot)。

默認是read committed，這種情況一般在更新表的時候，如果不使用hint 提示，基本是先對表添加IX鎖，級別不算高，基本和其他鎖兼容，但是repeatable read，serializable 事務隔離級別就會先對表添加IX鎖，然后向X鎖轉化，而X鎖和大多數(shù)鎖都不兼容，容易發(fā)生表阻塞。

第二種隔離級別不會有以上問題，但是又引入了其它的問題。

以上是一種情況。

另外一種就是 鎖升級，一個鎖是96B內(nèi)存，如果太多，sqlserver就會升級為表鎖，一般是5000以上行級鎖就升級為一個表X鎖。

所以適當?shù)奈募纸M和表分區(qū) 是有必要的。

其次就是資源互相引用導致事務長時間不能釋放，導致真正的死鎖，不過SQL2005以后，這種情況發(fā)生的概率很低。

留個問題你自己去想。

兩個SQL，兩個連接，同時執(zhí)行。

update A set A.NAME=xxx where A.id=55

update A set A.NAME=xxx where A.id=56, 如果 56 不存在你說會發(fā)生什么情況呢？

網(wǎng)頁題目：sqlserver鎖升級,sqlserver 解鎖
瀏覽路徑：http://chinadenli.net/article29/dsshgch.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設計、品牌網(wǎng)站設計、定制網(wǎng)站、網(wǎng)站排名、電子商務、網(wǎng)站營銷

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)