Sql server08數(shù)據(jù)庫執(zhí)行什么操作的時候會加S鎖，IX鎖和SIX鎖

一. 為什么要引入鎖

成都創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供烏海網站建設、烏海做網站、烏海網站設計、烏海網站制作等企業(yè)網站建設、網頁設計與制作、烏海企業(yè)網站模板建站服務，十載烏海做網站經驗，不只是建網站，更提供有價值的思路和整體網絡服務。

多個用戶同時對數(shù)據(jù)庫的并發(fā)操作時會帶來以下數(shù)據(jù)不一致的問題:

丟失更新

A,B兩個用戶讀同一數(shù)據(jù)并進行修改,其中一個用戶的修改結果破壞了另一個修改的結果,比如訂票系統(tǒng)

臟讀

A用戶修改了數(shù)據(jù),隨后B用戶又讀出該數(shù)據(jù),但A用戶因為某些原因取消了對數(shù)據(jù)的修改,數(shù)據(jù)恢復原值,此時B得到的數(shù)據(jù)就與數(shù)據(jù)庫內的數(shù)據(jù)產生了不一致

不可重復讀

A用戶讀取數(shù)據(jù),隨后B用戶讀出該數(shù)據(jù)并修改,此時A用戶再讀取數(shù)據(jù)時發(fā)現(xiàn)前后兩次的值不一致

并發(fā)控制的主要方法是封鎖,鎖就是在一段時間內禁止用戶做某些操作以避免產生數(shù)據(jù)不一致

二 鎖的分類

鎖的類別有兩種分法：

1. 從數(shù)據(jù)庫系統(tǒng)的角度來看：分為獨占鎖（即排它鎖），共享鎖和更新鎖

MS-SQL Server 使用以下資源鎖模式。

鎖模式 描述

共享 (S) 用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如 SELECT 語句。

更新 (U) 用于可更新的資源中。防止當多個會話在讀取、鎖定以及隨后可能進行的資源更新時發(fā)生常見形式的死鎖。

排它 (X) 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進行多重更新。

意向鎖 用于建立鎖的層次結構。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

架構鎖 在執(zhí)行依賴于表架構的操作時使用。架構鎖的類型為：架構修改 (Sch-M) 和架構穩(wěn)定性 (Sch-S)。

大容量更新 (BU) 向表中大容量復制數(shù)據(jù)并指定了 TABLOCK 提示時使用。

共享鎖

共享 (S) 鎖允許并發(fā)事務讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時，任何其它事務都不能修改數(shù)據(jù)。一旦已經讀取數(shù)據(jù)，便立即釋放資源上的共享 (S) 鎖，除非將事務隔離級別設置為可重復讀或更高級別，或者在事務生存周期內用鎖定提示保留共享 (S) 鎖。

更新鎖

更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務組成，此事務讀取記錄，獲取資源（頁或行）的共享 (S) 鎖，然后修改行，此操作要求鎖轉換為排它 (X) 鎖。如果兩個事務獲得了資源上的共享模式鎖，然后試圖同時更新數(shù)據(jù)，則一個事務嘗試將鎖轉換為排它 (X) 鎖。共享模式到排它鎖的轉換必須等待一段時間，因為一個事務的排它鎖與其它事務的共享模式鎖不兼容；發(fā)生鎖等待。第二個事務試圖獲取排它 (X) 鎖以進行更新。由于兩個事務都要轉換為排它 (X) 鎖，并且每個事務都等待另一個事務釋放共享模式鎖，因此發(fā)生死鎖。

若要避免這種潛在的死鎖問題，請使用更新 (U) 鎖。一次只有一個事務可以獲得資源的更新 (U) 鎖。如果事務修改資源，則更新 (U) 鎖轉換為排它 (X) 鎖。否則，鎖轉換為共享鎖。

排它鎖

排它 (X) 鎖可以防止并發(fā)事務對資源進行訪問。其它事務不能讀取或修改排它 (X) 鎖鎖定的數(shù)據(jù)。

意向鎖

意向鎖表示 SQL Server 需要在層次結構中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級的共享意向鎖表示事務打算在表中的頁或行上放置共享 (S) 鎖。在表級設置意向鎖可防止另一個事務隨后在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能，因為 SQL Server 僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

鎖模式 描述

意向共享 (IS) 通過在各資源上放置 S 鎖，表明事務的意向是讀取層次結構中的部分（而不是全部）底層資源。

意向排它 (IX) 通過在各資源上放置 X 鎖，表明事務的意向是修改層次結構中的部分（而不是全部）底層資源。IX 是 IS 的超集。

與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖，表明事務的意向是讀取層次結構中的全部底層資源并修改部分（而不是全部）底層資源。允許頂層資源上的并發(fā) IS 鎖。例如，表的 SIX 鎖在表上放置一個 SIX 鎖（允許并發(fā) IS 鎖），在當前所修改頁上放置 IX 鎖（在已修改行上放置 X 鎖）。雖然每個資源在一段時間內只能有一個 SIX 鎖，以防止其它事務對資源進行更新，但是其它事務可以通過獲取表級的 IS 鎖來讀取層次結構中的底層資源。

獨占鎖：只允許進行鎖定操作的程序使用，其他任何對他的操作均不會被接受。執(zhí)行數(shù)據(jù)更新命令時，SQL Server會自動使用獨占鎖。當對象上有其他鎖存在時，無法對其加獨占鎖。

共享鎖：共享鎖鎖定的資源可以被其他用戶讀取，但其他用戶無法修改它，在執(zhí)行Select時，SQL Server會對對象加共享鎖。

更新鎖：當SQL Server準備更新數(shù)據(jù)時，它首先對數(shù)據(jù)對象作更新鎖鎖定，這樣數(shù)據(jù)將不能被修改，但可以讀取。等到SQL Server確定要進行更新數(shù)據(jù)操作時，他會自動將更新鎖換為獨占鎖，當對象上有其他鎖存在時，無法對其加更新鎖。

2. 從程序員的角度看：分為樂觀鎖和悲觀鎖。

樂觀鎖：完全依靠數(shù)據(jù)庫來管理鎖的工作。

悲觀鎖：程序員自己管理數(shù)據(jù)或對象上的鎖處理。

MS-SQLSERVER 使用鎖在多個同時在數(shù)據(jù)庫內執(zhí)行修改的用戶間實現(xiàn)悲觀并發(fā)控制

三 鎖的粒度

鎖粒度是被封鎖目標的大小,封鎖粒度小則并發(fā)性高,但開銷大,封鎖粒度大則并發(fā)性低但開銷小

SQL Server支持的鎖粒度可以分為為行、頁、鍵、鍵范圍、索引、表或數(shù)據(jù)庫獲取鎖

資源 描述

RID 行標識符。用于單獨鎖定表中的一行。

鍵 索引中的行鎖。用于保護可串行事務中的鍵范圍。

頁 8 千字節(jié) (KB) 的數(shù)據(jù)頁或索引頁。

擴展盤區(qū) 相鄰的八個數(shù)據(jù)頁或索引頁構成的一組。

表 包括所有數(shù)據(jù)和索引在內的整個表。

DB 數(shù)據(jù)庫。

四 鎖定時間的長短

鎖保持的時間長度為保護所請求級別上的資源所需的時間長度。

用于保護讀取操作的共享鎖的保持時間取決于事務隔離級別。采用 READ COMMITTED 的默認事務隔離級別時，只在讀取頁的期間內控制共享鎖。在掃描中，直到在掃描內的下一頁上獲取鎖時才釋放鎖。如果指定 HOLDLOCK 提示或者將事務隔離級別設置為 REPEATABLE READ 或 SERIALIZABLE，則直到事務結束才釋放鎖。

根據(jù)為游標設置的并發(fā)選項，游標可以獲取共享模式的滾動鎖以保護提取。當需要滾動鎖時，直到下一次提取或關閉游標（以先發(fā)生者為準）時才釋放滾動鎖。但是，如果指定 HOLDLOCK，則直到事務結束才釋放滾動鎖。

用于保護更新的排它鎖將直到事務結束才釋放。

如果一個連接試圖獲取一個鎖，而該鎖與另一個連接所控制的鎖沖突，則試圖獲取鎖的連接將一直阻塞到：

將沖突鎖釋放而且連接獲取了所請求的鎖。

連接的超時間隔已到期。默認情況下沒有超時間隔，但是一些應用程序設置超時間隔以防止無限期等待

五 SQL Server 中鎖的自定義

1 處理死鎖和設置死鎖優(yōu)先級

死鎖就是多個用戶申請不同封鎖,由于申請者均擁有一部分封鎖權而又等待其他用戶擁有的部分封鎖而引起的無休止的等待

可以使用SET DEADLOCK_PRIORITY控制在發(fā)生死鎖情況時會話的反應方式。如果兩個進程都鎖定數(shù)據(jù)，并且直到其它進程釋放自己的鎖時，每個進程才能釋放自己的鎖，即發(fā)生死鎖情況。

2 處理超時和設置鎖超時持續(xù)時間。

@@LOCK_TIMEOUT 返回當前會話的當前鎖超時設置，單位為毫秒

SET LOCK_TIMEOUT 設置允許應用程序設置語句等待阻塞資源的最長時間。當語句等待的時間大于 LOCK_TIMEOUT 設置時，系統(tǒng)將自動取消阻塞的語句，并給應用程序返回"已超過了鎖請求超時時段"的 1222 號錯誤信息

示例

下例將鎖超時期限設置為 1,800 毫秒。

SET LOCK_TIMEOUT 1800

3) 設置事務隔離級別。

4 ) 對 SELECT、INSERT、UPDATE 和 DELETE 語句使用表級鎖定提示。

5) 配置索引的鎖定粒度

可以使用 sp_indexoption 系統(tǒng)存儲過程來設置用于索引的鎖定粒度

六 查看鎖的信息

1 執(zhí)行 EXEC SP_LOCK 報告有關鎖的信息

2 查詢分析器中按Ctrl+2可以看到鎖的信息

七 使用注意事項

如何避免死鎖

1 使用事務時，盡量縮短事務的邏輯處理過程，及早提交或回滾事務；

2 設置死鎖超時參數(shù)為合理范圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免進程懸掛；

3 優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)；

4 .對所有的腳本和SP都要仔細測試，在正是版本之前。

5 所有的SP都要有錯誤處理（通過@error）

6 一般不要修改SQL SERVER事務的默認級別。不推薦強行加鎖

解決問題 如何對行 表 數(shù)據(jù)庫加鎖

八 幾個有關鎖的問題

1 如何鎖一個表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 鎖定數(shù)據(jù)庫的一個表

SELECT * FROM table WITH (HOLDLOCK)

加鎖語句：

sybase:

update 表 set col1=col1 where 1=0 ;

MSSQL:

select col1 from 表 (tablockx) where 1=0 ;

oracle:

LOCK TABLE 表 IN EXCLUSIVE MODE ；

加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖

幾個例子幫助大家加深印象

設table1(A,B,C)

A B C

a1 b1 c1

a2 b2 c2

a3 b3 c3

1）排它鎖

新建兩個連接

在第一個連接中執(zhí)行以下語句

begin tran

update table1

set A='aa'

where B='b2'

waitfor delay '00:00:30' --等待30秒

commit tran

在第二個連接中執(zhí)行以下語句

begin tran

select * from table1

where B='b2'

commit tran

若同時執(zhí)行上述兩個語句，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待30秒

2）共享鎖

在第一個連接中執(zhí)行以下語句

begin tran

select * from table1 holdlock -holdlock人為加鎖

where B='b2'

waitfor delay '00:00:30' --等待30秒

commit tran

在第二個連接中執(zhí)行以下語句

begin tran

select A,C from table1

where B='b2'

update table1

set A='aa'

where B='b2'

commit tran

若同時執(zhí)行上述兩個語句，則第二個連接中的select查詢可以執(zhí)行

而update必須等待第一個事務釋放共享鎖轉為排它鎖后才能執(zhí)行 即要等待30秒

3）死鎖

增設table2(D,E)

D E

d1 e1

d2 e2

在第一個連接中執(zhí)行以下語句

begin tran

update table1

set A='aa'

where B='b2'

waitfor delay '00:00:30'

update table2

set D='d5'

where E='e1'

commit tran

在第二個連接中執(zhí)行以下語句

begin tran

update table2

set D='d5'

where E='e1'

waitfor delay '00:00:10'

update table1

set A='aa'

where B='b2'

commit tran

同時執(zhí)行，系統(tǒng)會檢測出死鎖，并中止進程

補充一點:

Sql Server2000支持的表級鎖定提示

HOLDLOCK 持有共享鎖，直到整個事務完成，應該在被鎖對象不需要時立即釋放，等于SERIALIZABLE事務隔離級別

NOLOCK 語句執(zhí)行時不發(fā)出共享鎖，允許臟讀 ，等于 READ UNCOMMITTED事務隔離級別

PAGLOCK 在使用一個表鎖的地方用多個頁鎖

READPAST 讓sql server跳過任何鎖定行，執(zhí)行事務，適用于READ UNCOMMITTED事務隔離級別只跳過RID鎖，不跳過頁，區(qū)域和表鎖

ROWLOCK 強制使用行鎖

TABLOCKX 強制使用獨占表級鎖，這個鎖在事務期間阻止任何其他事務使用這個表

UPLOCK 強制在讀表時使用更新而不用共享鎖

應用程序鎖:

應用程序鎖就是客戶端代碼生成的鎖，而不是sql server本身生成的鎖

處理應用程序鎖的兩個過程

sp_getapplock 鎖定應用程序資源

sp_releaseapplock 為應用程序資源解鎖

注意: 鎖定數(shù)據(jù)庫的一個表的區(qū)別

SELECT * FROM table WITH (HOLDLOCK) 其他事務可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX) 其他事務不能讀取表,更新和刪除

Oracle創(chuàng)建悲觀鎖和樂觀鎖

為了得到最大的性能，一般數(shù)據(jù)庫都有并發(fā)機制，不過帶來的問題就是數(shù)據(jù)訪問的沖突。為了解決這個問題，大多數(shù)數(shù)據(jù)庫用的方法就是數(shù)據(jù)的鎖定。

數(shù)據(jù)的鎖定分為兩種方法，第一種叫做悲觀鎖，第二種叫做樂觀鎖。什么叫悲觀鎖呢，悲觀鎖顧名思義，就是對數(shù)據(jù)的沖突采取一種悲觀的態(tài)度，也就是說假設數(shù)據(jù)肯定會沖突，所以在數(shù)據(jù)開始讀取的時候就把數(shù)據(jù)鎖定住。而樂觀鎖就是認為數(shù)據(jù)一般情況下不會造成沖突，所以在數(shù)據(jù)進行提交更新的時候，才會正式對數(shù)據(jù)的沖突與否進行檢測，如果發(fā)現(xiàn)沖突了，則讓用戶返回錯誤的信息，讓用戶決定如何去做。

先從悲觀鎖開始說。在SqlServer等其余很多數(shù)據(jù)庫中，數(shù)據(jù)的鎖定通常采用頁級鎖的方式，也就是說對一張表內的數(shù)據(jù)是一種串行化的更新插入機制，在任何時間同一張表只會插1條數(shù)據(jù)，別的想插入的數(shù)據(jù)要等到這一條數(shù)據(jù)插完以后才能依次插入。帶來的后果就是性能的降低，在多用戶并發(fā)訪問的時候，當對一張表進行頻繁操作時，會發(fā)現(xiàn)響應效率很低，數(shù)據(jù)庫經常處于一種假死狀態(tài)。而Oracle用的是行級鎖，只是對想鎖定的數(shù)據(jù)才進行鎖定，其余的數(shù)據(jù)不相干，所以在對Oracle表中并發(fā)插數(shù)據(jù)的時候，基本上不會有任何影響。

注：對于悲觀鎖是針對并發(fā)的可能性比較大，而一般在我們的應用中用樂觀鎖足以。

Oracle的悲觀鎖需要利用一條現(xiàn)有的連接，分成兩種方式，從SQL語句的區(qū)別來看，就是一種是for update，一種是for update nowait的形式。比如我們看一個例子。首先建立測試用的數(shù)據(jù)庫表。

CREATE TABLE TEST(ID,NAME,LOCATION,VALUE,CONSTRAINT test_pk PRIMARY KEY(ID))AS SELECT deptno, dname, loc, 1 FROM scott.dept

這里我們利用了Oracle的Sample的scott用戶的表，把數(shù)據(jù)copy到我們的test表中。首先我們看一下for update鎖定方式。首先我們執(zhí)行如下的select for update語句。

select * from test where id = 10 for update

通過這條檢索語句鎖定以后，再開另外一個sql*plus窗口進行操作，再把上面這條sql語句執(zhí)行一便，你會發(fā)現(xiàn)sqlplus好像死在那里了，好像檢索不到數(shù)據(jù)的樣子，但是也不返回任何結果，就屬于卡在那里的感覺。這個時候是什么原因呢，就是一開始的第一個Session中的select for update語句把數(shù)據(jù)鎖定住了。由于這里鎖定的機制是wait的狀態(tài)(只要不表示nowait那就是wait)，所以第二個Session(也就是卡住的那個sql*plus)中當前這個檢索就處于等待狀態(tài)。當?shù)谝粋€session最后commit或者rollback之后，第二個session中的檢索結果就是自動跳出來，并且也把數(shù)據(jù)鎖定住。不過如果你第二個session中你的檢索語句如下所示。

select * from test where id = 10

也就是沒有for update這種鎖定數(shù)據(jù)的語句的話，就不會造成阻塞了。另外一種情況，就是當數(shù)據(jù)庫數(shù)據(jù)被鎖定的時候，也就是執(zhí)行剛才for update那條sql以后，我們在另外一個session中執(zhí)行for update nowait后又是什么樣呢。比如如下的sql語句。 由于這條語句中是制定采用nowait方式來進行檢索，所以當發(fā)現(xiàn)數(shù)據(jù)被別的session鎖定中的時候，就會迅速返回ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式獲取資源。所以在程序中我們可以采用nowait方式迅速判斷當前數(shù)據(jù)是否被鎖定中，如果鎖定中的話，就要采取相應的業(yè)務措施進行處理。

select * from test where id = 10 for update nowait

那這里另外一個問題，就是當我們鎖定住數(shù)據(jù)的時候，我們對數(shù)據(jù)進行更新和刪除的話會是什么樣呢。比如同樣，我們讓第一個Session鎖定住id=10的那條數(shù)據(jù)，我們在第二個session中執(zhí)行如下語句。

update test set value=2 where id = 10

這個時候我們發(fā)現(xiàn)update語句就好像select for update語句一樣也停住卡在這里，當你第一個session放開鎖定以后update才能正常運行。當你update運行后，數(shù)據(jù)又被你update語句鎖定住了，這個時候只要你update后還沒有commit，別的session照樣不能對數(shù)據(jù)進行鎖定更新等等。

總之，Oracle中的悲觀鎖就是利用Oracle的Connection對數(shù)據(jù)進行鎖定。在Oracle中，用這種行級鎖帶來的性能損失是很小的，只是要注意程序邏輯，不要給你一不小心搞成死鎖了就好。而且由于數(shù)據(jù)的及時鎖定，在數(shù)據(jù)提交時候就不呼出現(xiàn)沖突，可以省去很多惱人的數(shù)據(jù)沖突處理。缺點就是你必須要始終有一條數(shù)據(jù)庫連接，就是說在整個鎖定到最后放開鎖的過程中，你的數(shù)據(jù)庫聯(lián)接要始終保持住。與悲觀鎖相對的，我們有了樂觀鎖。樂觀鎖一開始也說了，就是一開始假設不會造成數(shù)據(jù)沖突，在最后提交的時候再進行數(shù)據(jù)沖突檢測。在樂觀鎖中，我們有3種

常用的做法來實現(xiàn)。

[1]第一種就是在數(shù)據(jù)取得的時候把整個數(shù)據(jù)都copy到應用中，在進行提交的時候比對當前數(shù)據(jù)庫中的數(shù)據(jù)和開始的時候更新前取得的數(shù)據(jù)。當發(fā)現(xiàn)兩個數(shù)據(jù)一模一樣以后，就表示沒有沖突可以提交，否則則是并發(fā)沖突，需要去用業(yè)務邏輯進行解決。

[2]第二種樂觀鎖的做法就是采用版本戳，這個在Hibernate中得到了使用。采用版本戳的話，首先需要在你有樂觀鎖的數(shù)據(jù)庫table上建立一個新的column，比如為number型，當你數(shù)據(jù)每更新一次的時候，版本數(shù)就會往上增加1。比如同樣有2個session同樣對某條數(shù)據(jù)進行操作。兩者都取到當前的數(shù)據(jù)的版本號為1，當?shù)谝粋€session進行數(shù)據(jù)更新后，在提交的時候查看到當前數(shù)據(jù)的版本還為1，和自己一開始取到的版本相同。就正式提交，然后把版本號增加1，這個時候當前數(shù)據(jù)的版本為2。當?shù)诙€session也更新了數(shù)據(jù)提交的時候，發(fā)現(xiàn)數(shù)據(jù)庫中版本為2，和一開始這個session取到的版本號不一致，就知道別人更新過此條數(shù)據(jù)，這個

時候再進行業(yè)務處理，比如整個Transaction都Rollback等等操作。在用版本戳的時候，可以在應用程序側使用版本戳的驗證，也可以在數(shù)據(jù)庫側采用Trigger(觸發(fā)器)來進行驗證。不過數(shù)據(jù)庫的Trigger的性能開銷還是比較的大，所以能在應用側進行驗證的話還是推薦不用Trigger。

[3]第三種做法和第二種做法有點類似，就是也新增一個Table的Column，不過這次這個column是采用timestamp型，存儲數(shù)據(jù)最后更新的時間。在Oracle9i以后可以采用新的數(shù)據(jù)類型，也就是timestamp with time zone類型來做時間戳。這種Timestamp的數(shù)據(jù)精度在Oracle的時間類型中是最高的，精確到微秒(還沒與到納秒的級別)，一般來說，加上數(shù)據(jù)庫處理時間和人的思考動作時間，微秒級別是非常非常夠了，其實只要精確到毫秒甚至秒都應該沒有什么問題。和剛才的版本戳類似，也是在更新提交的時候檢查當前數(shù)據(jù)庫中數(shù)據(jù)的時間戳和自己更新前取到的時間戳進行對比，如果一致則OK，否則就是版本沖突。如果不想把代碼寫在程序中或者由于別的原因無法把代碼寫在現(xiàn)有的程序中，也可以把這個時間戳樂觀鎖邏輯寫在Trigger或者存儲過程中。

不知道這個是不是你需要的...

如何在SQLServer中鎖定某行記錄

可以用悲觀鎖和樂觀鎖兩種訪求

（1）悲觀鎖

就像前面所說的那樣使用

例如：

select * from ta with(UPDLOCK)

這樣當記錄已經被鎖定后，

其他的人再試圖再次更新訪問的時候會失敗。

詳細內容不再細述了。

（2）樂觀鎖

類似16樓的說法，比如在表中增加一個字段以保存最后一次更新的時間戳，

更新的時候，用取得數(shù)據(jù)時的時間與數(shù)據(jù)庫中的更新時間進行比較，

如果比數(shù)據(jù)庫中的最后更新時間早的話，

則說明之前已經有其它人對于數(shù)據(jù)進行了更新。

則更新不能進行。

可以使用下面的語句進行更新操作。

updata 表名 set ..... where upddate = 上次取得記錄的更新時間

sqlserver 在高并發(fā)的情況下 用什么事務隔離級別比較好

隔離級別越高，越能保證數(shù)據(jù)的完整性和一致性，但是對并發(fā)性能的影響也越大。

對于多數(shù)應用程序，可以優(yōu)先考慮把數(shù)據(jù)庫系統(tǒng)的隔離級別設為Read Committed，它能夠避免臟讀取，而且具有較好的并發(fā)性能。

盡管它會導致不可重復讀、虛讀和第二類丟失更新這些并發(fā)問題，在可能出現(xiàn)這類問題的個別場合，可以由應用程序采用悲觀鎖或樂觀鎖來控制。

sql server 怎么保證查詢出的結果不能被其他電腦訪問，具體如下

這涉及到數(shù)據(jù)庫中,鎖的概念

首先,SQL Server更強調由系統(tǒng)來管理鎖。在用戶有SQL請求時，系統(tǒng)分析請求，自動在滿足鎖定條件和系統(tǒng)性能之間為數(shù)據(jù)庫加上適當?shù)逆i，同時系統(tǒng)在運行期間常常自動進行優(yōu)化處理，實行動態(tài)加鎖。也就是說,你的擔心是不必要的,SQLServer可以保證這種情況基本不會出現(xiàn).如果特殊情況下，需要你手動出來，請參考下面文檔。

一. 為什么要引入鎖

多個用戶同時對數(shù)據(jù)庫的并發(fā)操作時會帶來以下數(shù)據(jù)不一致的問題:

丟失更新

A,B兩個用戶讀同一數(shù)據(jù)并進行修改,其中一個用戶的修改結果破壞了另一個修改的結果,比如訂票系統(tǒng)

臟讀

A用戶修改了數(shù)據(jù),隨后B用戶又讀出該數(shù)據(jù),但A用戶因為某些原因取消了對數(shù)據(jù)的修改,數(shù)據(jù)恢復原值,此時B得到的數(shù)據(jù)就與數(shù)據(jù)庫內的數(shù)據(jù)產生了不一致

不可重復讀

A用戶讀取數(shù)據(jù),隨后B用戶讀出該數(shù)據(jù)并修改,此時A用戶再讀取數(shù)據(jù)時發(fā)現(xiàn)前后兩次的值不一致

并發(fā)控制的主要方法是封鎖,鎖就是在一段時間內禁止用戶做某些操作以避免產生數(shù)據(jù)不一致

二 鎖的分類

鎖的類別有兩種分法：

1. 從數(shù)據(jù)庫系統(tǒng)的角度來看：分為獨占鎖（即排它鎖），共享鎖和更新鎖

MS-SQL Server 使用以下資源鎖模式。

鎖模式 描述

共享 (S) 用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如 SELECT 語句。

更新 (U) 用于可更新的資源中。防止當多個會話在讀取、鎖定以及隨后可能進行的資源更新時發(fā)生常見形式的死鎖。

排它 (X) 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進行多重更新。

意向鎖 用于建立鎖的層次結構。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

架構鎖 在執(zhí)行依賴于表架構的操作時使用。架構鎖的類型為：架構修改 (Sch-M) 和架構穩(wěn)定性 (Sch-S)。

大容量更新 (BU) 向表中大容量復制數(shù)據(jù)并指定了 TABLOCK 提示時使用。

共享鎖

共享 (S) 鎖允許并發(fā)事務讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時，任何其它事務都不能修改數(shù)據(jù)。一旦已經讀取數(shù)據(jù)，便立即釋放資源上的共享 (S) 鎖，除非將事務隔離級別設置為可重復讀或更高級別，或者在事務生存周期內用鎖定提示保留共享 (S) 鎖。

更新鎖

更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務組成，此事務讀取記錄，獲取資源（頁或行）的共享 (S) 鎖，然后修改行，此操作要求鎖轉換為排它 (X) 鎖。如果兩個事務獲得了資源上的共享模式鎖，然后試圖同時更新數(shù)據(jù)，則一個事務嘗試將鎖轉換為排它 (X) 鎖。共享模式到排它鎖的轉換必須等待一段時間，因為一個事務的排它鎖與其它事務的共享模式鎖不兼容；發(fā)生鎖等待。第二個事務試圖獲取排它 (X) 鎖以進行更新。由于兩個事務都要轉換為排它 (X) 鎖，并且每個事務都等待另一個事務釋放共享模式鎖，因此發(fā)生死鎖。

若要避免這種潛在的死鎖問題，請使用更新 (U) 鎖。一次只有一個事務可以獲得資源的更新 (U) 鎖。如果事務修改資源，則更新 (U) 鎖轉換為排它 (X) 鎖。否則，鎖轉換為共享鎖。

排它鎖

排它 (X) 鎖可以防止并發(fā)事務對資源進行訪問。其它事務不能讀取或修改排它 (X) 鎖鎖定的數(shù)據(jù)。

意向鎖

意向鎖表示 SQL Server 需要在層次結構中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級的共享意向鎖表示事務打算在表中的頁或行上放置共享 (S) 鎖。在表級設置意向鎖可防止另一個事務隨后在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能，因為 SQL Server 僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

鎖模式 描述

意向共享 (IS) 通過在各資源上放置 S 鎖，表明事務的意向是讀取層次結構中的部分（而不是全部）底層資源。

意向排它 (IX) 通過在各資源上放置 X 鎖，表明事務的意向是修改層次結構中的部分（而不是全部）底層資源。IX 是 IS 的超集。

與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖，表明事務的意向是讀取層次結構中的全部底層資源并修改部分（而不是全部）底層資源。允許頂層資源上的并發(fā) IS 鎖。例如，表的 SIX 鎖在表上放置一個 SIX 鎖（允許并發(fā) IS 鎖），在當前所修改頁上放置 IX 鎖（在已修改行上放置 X 鎖）。雖然每個資源在一段時間內只能有一個 SIX 鎖，以防止其它事務對資源進行更新，但是其它事務可以通過獲取表級的 IS 鎖來讀取層次結構中的底層資源。

獨占鎖：只允許進行鎖定操作的程序使用，其他任何對他的操作均不會被接受。執(zhí)行數(shù)據(jù)更新命令時，SQL Server會自動使用獨占鎖。當對象上有其他鎖存在時，無法對其加獨占鎖。

共享鎖：共享鎖鎖定的資源可以被其他用戶讀取，但其他用戶無法修改它，在執(zhí)行Select時，SQL Server會對對象加共享鎖。

更新鎖：當SQL Server準備更新數(shù)據(jù)時，它首先對數(shù)據(jù)對象作更新鎖鎖定，這樣數(shù)據(jù)將不能被修改，但可以讀取。等到SQL Server確定要進行更新數(shù)據(jù)操作時，他會自動將更新鎖換為獨占鎖，當對象上有其他鎖存在時，無法對其加更新鎖。

2. 從程序員的角度看：分為樂觀鎖和悲觀鎖。

樂觀鎖：完全依靠數(shù)據(jù)庫來管理鎖的工作。

悲觀鎖：程序員自己管理數(shù)據(jù)或對象上的鎖處理。

MS-SQLSERVER 使用鎖在多個同時在數(shù)據(jù)庫內執(zhí)行修改的用戶間實現(xiàn)悲觀并發(fā)控制

三 鎖的粒度

鎖粒度是被封鎖目標的大小,封鎖粒度小則并發(fā)性高,但開銷大,封鎖粒度大則并發(fā)性低但開銷小

SQL Server支持的鎖粒度可以分為為行、頁、鍵、鍵范圍、索引、表或數(shù)據(jù)庫獲取鎖

資源 描述

RID 行標識符。用于單獨鎖定表中的一行。

鍵 索引中的行鎖。用于保護可串行事務中的鍵范圍。

頁 8 千字節(jié) (KB) 的數(shù)據(jù)頁或索引頁。

擴展盤區(qū) 相鄰的八個數(shù)據(jù)頁或索引頁構成的一組。

表 包括所有數(shù)據(jù)和索引在內的整個表。

DB 數(shù)據(jù)庫。

四 鎖定時間的長短

鎖保持的時間長度為保護所請求級別上的資源所需的時間長度。

用于保護讀取操作的共享鎖的保持時間取決于事務隔離級別。采用 READ COMMITTED 的默認事務隔離級別時，只在讀取頁的期間內控制共享鎖。在掃描中，直到在掃描內的下一頁上獲取鎖時才釋放鎖。如果指定 HOLDLOCK 提示或者將事務隔離級別設置為 REPEATABLE READ 或 SERIALIZABLE，則直到事務結束才釋放鎖。

根據(jù)為游標設置的并發(fā)選項，游標可以獲取共享模式的滾動鎖以保護提取。當需要滾動鎖時，直到下一次提取或關閉游標（以先發(fā)生者為準）時才釋放滾動鎖。但是，如果指定 HOLDLOCK，則直到事務結束才釋放滾動鎖。

用于保護更新的排它鎖將直到事務結束才釋放。

如果一個連接試圖獲取一個鎖，而該鎖與另一個連接所控制的鎖沖突，則試圖獲取鎖的連接將一直阻塞到：

將沖突鎖釋放而且連接獲取了所請求的鎖。

連接的超時間隔已到期。默認情況下沒有超時間隔，但是一些應用程序設置超時間隔以防止無限期等待

五 SQL Server 中鎖的自定義

1 處理死鎖和設置死鎖優(yōu)先級

死鎖就是多個用戶申請不同封鎖,由于申請者均擁有一部分封鎖權而又等待其他用戶擁有的部分封鎖而引起的無休止的等待

可以使用SET DEADLOCK_PRIORITY控制在發(fā)生死鎖情況時會話的反應方式。如果兩個進程都鎖定數(shù)據(jù)，并且直到其它進程釋放自己的鎖時，每個進程才能釋放自己的鎖，即發(fā)生死鎖情況。

2 處理超時和設置鎖超時持續(xù)時間。

@@LOCK_TIMEOUT 返回當前會話的當前鎖超時設置，單位為毫秒

SET LOCK_TIMEOUT 設置允許應用程序設置語句等待阻塞資源的最長時間。當語句等待的時間大于 LOCK_TIMEOUT 設置時，系統(tǒng)將自動取消阻塞的語句，并給應用程序返回"已超過了鎖請求超時時段"的 1222 號錯誤信息

示例

下例將鎖超時期限設置為 1,800 毫秒。

SET LOCK_TIMEOUT 1800

3) 設置事務隔離級別。

4 ) 對 SELECT、INSERT、UPDATE 和 DELETE 語句使用表級鎖定提示。

5) 配置索引的鎖定粒度

可以使用 sp_indexoption 系統(tǒng)存儲過程來設置用于索引的鎖定粒度

六 查看鎖的信息

1 執(zhí)行 EXEC SP_LOCK 報告有關鎖的信息

2 查詢分析器中按Ctrl+2可以看到鎖的信息

七 使用注意事項

如何避免死鎖

1 使用事務時，盡量縮短事務的邏輯處理過程，及早提交或回滾事務；

2 設置死鎖超時參數(shù)為合理范圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免進程懸掛；

3 優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)；

4 .對所有的腳本和SP都要仔細測試，在正是版本之前。

5 所有的SP都要有錯誤處理（通過@error）

6 一般不要修改SQL SERVER事務的默認級別。不推薦強行加鎖

解決問題 如何對行 表 數(shù)據(jù)庫加鎖

八 幾個有關鎖的問題

1 如何鎖一個表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 鎖定數(shù)據(jù)庫的一個表

SELECT * FROM table WITH (HOLDLOCK)

加鎖語句：

sybase:

update 表 set col1=col1 where 1=0 ;

MSSQL:

select col1 from 表 (tablockx) where 1=0 ;

oracle:

LOCK TABLE 表 IN EXCLUSIVE MODE ；

加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖

幾個例子幫助大家加深印象

設table1(A,B,C)

A B C

a1 b1 c1

a2 b2 c2

a3 b3 c3

1）排它鎖

新建兩個連接

在第一個連接中執(zhí)行以下語句

begin tran

update table1

set A='aa'

where B='b2'

waitfor delay '00:00:30' --等待30秒

commit tran

在第二個連接中執(zhí)行以下語句

begin tran

select * from table1

where B='b2'

commit tran

若同時執(zhí)行上述兩個語句，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待30秒

2）共享鎖

在第一個連接中執(zhí)行以下語句

begin tran

select * from table1 holdlock -holdlock人為加鎖

where B='b2'

waitfor delay '00:00:30' --等待30秒

commit tran

在第二個連接中執(zhí)行以下語句

begin tran

select A,C from table1

where B='b2'

update table1

set A='aa'

where B='b2'

commit tran

若同時執(zhí)行上述兩個語句，則第二個連接中的select查詢可以執(zhí)行

而update必須等待第一個事務釋放共享鎖轉為排它鎖后才能執(zhí)行 即要等待30秒

3）死鎖

增設table2(D,E)

D E

d1 e1

d2 e2

在第一個連接中執(zhí)行以下語句

begin tran

update table1

set A='aa'

where B='b2'

waitfor delay '00:00:30'

update table2

set D='d5'

where E='e1'

commit tran

在第二個連接中執(zhí)行以下語句

begin tran

update table2

set D='d5'

where E='e1'

waitfor delay '00:00:10'

update table1

set A='aa'

where B='b2'

commit tran

同時執(zhí)行，系統(tǒng)會檢測出死鎖，并中止進程

補充一點:

Sql Server2000支持的表級鎖定提示

HOLDLOCK 持有共享鎖，直到整個事務完成，應該在被鎖對象不需要時立即釋放，等于SERIALIZABLE事務隔離級別

NOLOCK 語句執(zhí)行時不發(fā)出共享鎖，允許臟讀 ，等于 READ UNCOMMITTED事務隔離級別

PAGLOCK 在使用一個表鎖的地方用多個頁鎖

READPAST 讓sql server跳過任何鎖定行，執(zhí)行事務，適用于READ UNCOMMITTED事務隔離級別只跳過RID鎖，不跳過頁，區(qū)域和表鎖

ROWLOCK 強制使用行鎖

TABLOCKX 強制使用獨占表級鎖，這個鎖在事務期間阻止任何其他事務使用這個表

UPLOCK 強制在讀表時使用更新而不用共享鎖

應用程序鎖:

應用程序鎖就是客戶端代碼生成的鎖，而不是sql server本身生成的鎖

處理應用程序鎖的兩個過程

sp_getapplock 鎖定應用程序資源

sp_releaseapplock 為應用程序資源解鎖

注意: 鎖定數(shù)據(jù)庫的一個表的區(qū)別

SELECT * FROM table WITH (HOLDLOCK) 其他事務可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX) 其他事務不能讀取表,更新和刪除

標題名稱：sqlserver悲觀鎖,sqlserver死鎖解決辦法
鏈接地址：http://chinadenli.net/article31/dsieepd.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供服務器托管、靜態(tài)網站、網站排名、自適應網站、軟件開發(fā)、微信公眾號

廣告

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