手工鎖在ＳＱＬ ＳＥＲＶＥＲ中的應(yīng)用

摘要：本文主要介紹了手工鎖的常見(jiàn)類(lèi)型， NOLOCK、 HOLDLOCK 、UPDLOCK、 TABLOCKX并舉例說(shuō)明它們?cè)趯?shí)際應(yīng)用中的具體使用。

關(guān)鍵詞：手工鎖；SQL；應(yīng)用

中圖分類(lèi)號(hào)：TP311文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044(2008)36-2828-02

Lock Table Applied in SQL SERVER

TONG Li-ting

(Heilongjiang Agricultural Economy professional College， Mudanjiang 157041，China)

Abstract: The article introduces the general types of lockable such as NOLOCK、 HOLDLOCK 、UPDLOCK、 TABLOCKX and illustrates the practical application by setting examples.

Key words: Lock Table; SQL; Application

1 前言

鎖是網(wǎng)絡(luò)數(shù)據(jù)庫(kù)中的一個(gè)非常重要的概念，它主要用于多用戶(hù)環(huán)境下保證數(shù)據(jù)庫(kù)完整性和一致性。SQL Server作為一種中小型數(shù)據(jù)庫(kù)管理系統(tǒng)，該系統(tǒng)更強(qiáng)調(diào)由系統(tǒng)來(lái)管理鎖。能夠自動(dòng)在滿(mǎn)足鎖定條件和系統(tǒng)性能之間為數(shù)據(jù)庫(kù)加上適當(dāng)?shù)逆i，同時(shí)系統(tǒng)在運(yùn)行期間常常自動(dòng)進(jìn)行優(yōu)化處理，實(shí)行動(dòng)態(tài)加鎖。

在實(shí)際應(yīng)用中，有時(shí)為了應(yīng)用程序正確運(yùn)行和保持?jǐn)?shù)據(jù)的一致性，必須人為地給數(shù)據(jù)庫(kù)的某個(gè)表加鎖。比如，在某應(yīng)用程序的一個(gè)事務(wù)操作中，需要根據(jù)一編號(hào)對(duì)幾個(gè)數(shù)據(jù)表做統(tǒng)計(jì)操作，為保證統(tǒng)計(jì)數(shù)據(jù)時(shí)間的一致性和正確性，從統(tǒng)計(jì)第一個(gè)表開(kāi)始到全部表結(jié)束，其他應(yīng)用程序或事務(wù)不能再對(duì)這幾個(gè)表寫(xiě)入數(shù)據(jù)，這個(gè)時(shí)候，該應(yīng)用程序希望在從統(tǒng)計(jì)第一個(gè)數(shù)據(jù)表開(kāi)始或在整個(gè)事務(wù)開(kāi)始時(shí)能夠由程序人為地（顯式地）鎖定這幾個(gè)表，這就需要用到手工加鎖。

2 手工加鎖的類(lèi)型

在SQL Server 的SQL語(yǔ)句（SELECT、INSERT、DELETE、UPDATE）支持手工加鎖。所指定的鎖類(lèi)型有如下幾種：

HOLDLOCK: 在該表上保持共享鎖，直到整個(gè)事務(wù)結(jié)束，而不是在語(yǔ)句執(zhí)行完立即釋放。

NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個(gè)選項(xiàng)生效后，可能讀到未提交讀的數(shù)據(jù)或“。

PAGLOCK：指定添加頁(yè)面鎖（否則通常可能添加表鎖）。

READCOMMITTED：設(shè)置事務(wù)為讀提交隔離性級(jí)別。

READPAST: 跳過(guò)已經(jīng)加鎖的數(shù)據(jù)行，這個(gè)選項(xiàng)將使事務(wù)讀取數(shù)據(jù)時(shí)跳過(guò)那些已經(jīng)被其

他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，READPAST僅僅應(yīng)用于READ COMMITTED

READUNCOMMITTED：等同于NOLOCK。

REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級(jí)別。

ROWLOCK：指定使用行級(jí)鎖。

SERIALIZABLE：設(shè)置事務(wù)為可串行的隔離性級(jí)別。

TABLOCK：指定使用表級(jí)鎖，而不是使用行級(jí)或頁(yè)面級(jí)的鎖，SQL Server在該語(yǔ)句執(zhí)行完后釋放這個(gè)鎖，而如果同時(shí)指定了HOLDLOCK，該鎖一直保持到這個(gè)事務(wù)結(jié)束。

TABLOCKX：指定在表上使用排它鎖，這個(gè)鎖可以阻止其他事務(wù)讀或更新這個(gè)表的數(shù)據(jù)

UPDLOCK ：指定在讀表中數(shù)據(jù)時(shí)設(shè)置修改鎖（update lock）而不是設(shè)置共享鎖，該鎖一直保持到這個(gè)語(yǔ)句或整個(gè)事務(wù)結(jié)束，使用UPDLOCK的作用是允許用戶(hù)先讀取數(shù)據(jù)（而且不阻塞其他用戶(hù)讀數(shù)據(jù)），并且保證在后來(lái)再更新數(shù)據(jù)時(shí)，這一段時(shí)間內(nèi)這些數(shù)據(jù)沒(méi)有被其他程序更改。

3 常用手工鎖應(yīng)用舉例

3.1 NOLOCK

僅在SELECT語(yǔ)句中可以使用。執(zhí)行對(duì)應(yīng)的T-SQL語(yǔ)句時(shí)。不發(fā)出共享鎖，也不使用派它鎖。當(dāng)此項(xiàng)生效時(shí)，可能回讀取未提交的事務(wù)或一組在讀取中間回滾的頁(yè)面，即有可能發(fā)生臟讀。

例如

連接1代碼如下：

BEGIN TRAN

Select * From Table(NOLOCK)

COMMIT TRAN

連接2代碼如下：

BEGIN TRAN

Update table1 WITH(Tablock)

SET FieldB=’UserD’

WHERE FieldA=’5’

WAITFOR DELAY ‘0:00:5’--等待5秒

COMMIT TRAN

此示例中，同時(shí)執(zhí)行數(shù)據(jù)庫(kù)的兩個(gè)連接中的代碼。由于在連接1中使用了NOLOCK鎖，而使其不受連接2中一直保持的排它鎖的限制，可以立即執(zhí)行，并且讀取到了連接2中未提交的修改。但如果去掉NOLOCK鎖，則；連接1中的事務(wù)將受到連接2中事務(wù)的表級(jí)排它鎖的限制，必須等到連接2執(zhí)行完畢才能夠執(zhí)行。

3.2 HOLDLOCK

將共享鎖保留到事務(wù)完成，而不是在相應(yīng)的表、行或數(shù)據(jù)面不再需要時(shí)就立即釋放鎖。

例如：

BEGIN TRAN

SELECT * From Tablel (HOLDLOCK)

WAITFOR DELAY ‘0:00:5’

COMMIT TRAN

這將使共享鎖一直保持到事務(wù)結(jié)束。

3.3 UPDLOCK

讀取表時(shí)使用修改鎖，而不使用共享鎖，并將鎖一直保留到語(yǔ)句或事務(wù)的結(jié)束。UPDLOCK的優(yōu)點(diǎn)是允許讀取數(shù)據(jù)（不但塞其他事務(wù)）并在以后更新數(shù)據(jù)，同時(shí)確保自從賞賜讀取數(shù)據(jù)后數(shù)據(jù)沒(méi)有被更改。

例如

BEGIM TRAN

SELECT * From Table1 (UpdLock)

WAITFOR DELAY ‘0:00:5’

SELECT * FROM Table 1

COMMIT TRAN

連接2中的代碼如下：

BEGIN TRAN

Update table 1

SET FieldB=’UserD’

WHERE FieldA=’5’

COMMIT TRAN

此示例中，同時(shí)執(zhí)行數(shù)據(jù)庫(kù)的兩個(gè)連接中的代碼，由于連接1中的SELECT語(yǔ)句使用了修改鎖，所以前后量詞讀取操作得到的結(jié)果是一致的。如果將修改的鎖去掉，則會(huì)發(fā)生非重復(fù)讀的問(wèn)題。

3.4 TABLOCKX

使用表的排它鎖。該鎖可以防止其他事務(wù)讀取或更新表，并在語(yǔ)句或事務(wù)結(jié)束前一直持有。

例如：在并發(fā)的環(huán)境下，兩個(gè)用戶(hù)同時(shí)保存數(shù)據(jù)到一張表中，代碼如下：

cn.Begin Trans

cn.Execute “INSERT INTO table1 ……”

Set rs=cn.Execute(“SELECT COUNT(*)FROM table1 WHERE……”)

If rs.RecordCount>0 Then

‘表A的字段A不能重復(fù)

cn.RollbackTrans

Else

cn.CommitTrans

End If

下面分析一下上面程序的執(zhí)行過(guò)程。

在執(zhí)行INSERT命令時(shí)如果不添加任何參數(shù)，數(shù)據(jù)庫(kù)默認(rèn)申請(qǐng)一個(gè)IX鎖給表A 。當(dāng)?shù)谝粋€(gè)用戶(hù)執(zhí)行INSERT INTO 語(yǔ)句時(shí)，連接向數(shù)據(jù)庫(kù)申請(qǐng)了一個(gè)IX鎖給表A。同時(shí)第二個(gè)用戶(hù)執(zhí)行INSERT INTO語(yǔ)句，連接也向數(shù)據(jù)庫(kù)成功地申請(qǐng)了一個(gè)IX鎖給表A。但是當(dāng)執(zhí)行SELECT COUNT（*）一句的時(shí)候就會(huì)有問(wèn)題產(chǎn)生：假設(shè)第一個(gè)用戶(hù)先一步執(zhí)行，由于SELECT命令需要向數(shù)據(jù)庫(kù)申請(qǐng)一個(gè)S鎖給表A，而這時(shí)表A已經(jīng)存在一個(gè)IX并且屬于另外一個(gè)連接。因此它只好在此等候，緊接著第二個(gè)用戶(hù)也執(zhí)行SELECT COUNT（*）一句，它也會(huì)向數(shù)據(jù)庫(kù)申請(qǐng)一個(gè)S鎖給表A，這時(shí)數(shù)據(jù)就會(huì)自動(dòng)結(jié)束較晚申請(qǐng)IX鎖的連接同時(shí)回滾這個(gè)事務(wù)，這樣，對(duì)于應(yīng)用程序要實(shí)現(xiàn)的目標(biāo)來(lái)說(shuō)。就是一個(gè)失敗。

針對(duì)一個(gè)例子中的問(wèn)題，可以在INSERT命令中帶上參數(shù)with(tablock)來(lái)解決。

cn.Bengin Trans

cn.Execute “INSERT INTO tableA with (tablock)……”

Set rs =cn.Execute(“SELECTCOUNT(*)FROM tableA WHERE ……”)

If rs.RecordCount>0 Then

‘表A的字段A不能重復(fù)

cn.CommitTrans

End if

4 總結(jié)

由上可見(jiàn)，在SQL Server中可以靈活多樣地為SQL語(yǔ)句顯式加鎖，若適當(dāng)使用，我們完全可以完成一些程序的特殊要求，保證數(shù)據(jù)的一致性和完整性。對(duì)于一般使用者而言，了解鎖機(jī)制并不意味著必須使用它。事實(shí)上，SQL Server建議讓系統(tǒng)自動(dòng)管理數(shù)據(jù)庫(kù)中的鎖，而且一些關(guān)于鎖的設(shè)置選項(xiàng)也沒(méi)有提供給用戶(hù)和數(shù)據(jù)庫(kù)管理人員，對(duì)于特殊用戶(hù)，通過(guò)給數(shù)據(jù)庫(kù)中的資源顯式加鎖，可以滿(mǎn)足很高的數(shù)據(jù)一致性和可靠性要求，只是需要特別注意避免死鎖現(xiàn)象的出現(xiàn)。