sqlserver 排他鎖鎖定某行怎么寫

鎖一個(gè)表的某一行?

創(chuàng)新互聯(lián)公司是一家專業(yè)提供鄂倫春企業(yè)網(wǎng)站建設(shè),專注與成都做網(wǎng)站、網(wǎng)站建設(shè)、外貿(mào)營銷網(wǎng)站建設(shè)、HTML5、小程序制作等業(yè)務(wù)。10年已為鄂倫春眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)絡(luò)公司優(yōu)惠進(jìn)行中。

SET?TRANSACTION?ISOLATION?LEVEL?READ?UNCOMMITTED?

SELECT?*?FROM?table?ROWLOCK?WHERE?id?=?1

實(shí)例：

--排它鎖?

--新建兩個(gè)連接?

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

begin?tran?

update?table1?

set?A='aa'?

where?B='b2'?

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

commit?tran?

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

begin?tran?

select?*?from?table1?

where?B='b2'?

commit?tran?

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

.net的sqlserver事務(wù)里，delete語句如何只鎖行，不鎖表

你理解錯(cuò)了！

默認(rèn)sqlserver都是行數(shù)據(jù)鎖定，隔離級別是 read commited 也就是讀取可 提交數(shù)據(jù)。

我給你舉個(gè)例子！

SELECT TOP 1000

[ID]

,[DeleteBy]

,[DelDate]

FROM [dbo].[DeleteLog]

顯示結(jié)果

-----------------------------------------------

ID DeleteBy DelDate

1 admin 2008-04-13 00:00:00.000

2 admin 2008-05-04 00:00:00.000

-------------------------------------------------

表數(shù)據(jù)就兩行

然后我做如下操作：

打開 SQL Server Management Studio

輸入：

begin transaction

DELETE FROM [HMS].[dbo].[DeleteLog]

where ID='1'

在另一個(gè)窗口中：

SELECT

[ID]

,[DeleteBy]

,[DelDate]

FROM [dbo].[DeleteLog]

where ID=1

你發(fā)現(xiàn) 這一個(gè)窗口被阻塞了，

但是查詢

SELECT

[ID]

,[DeleteBy]

,[DelDate]

FROM [dbo].[DeleteLog]

where ID=2

可以正確返回結(jié)果。 這充分證明了，sqlserver默認(rèn)隔離級別是行數(shù)據(jù)鎖定。

然后你此時(shí)在第一個(gè)刪除窗口 中輸入

rollback

，記住前面的刪除不執(zhí)行，只執(zhí)行rollback。

此時(shí)看一下查詢

SELECT

[ID]

,[DeleteBy]

,[DelDate]

FROM [dbo].[DeleteLog]

where ID=1

那個(gè)窗口的結(jié)果已經(jīng)出來了，阻塞被解除了。

========================================

當(dāng)然了！你執(zhí)行了全表檢索肯定也是被阻塞的，因?yàn)閯h除操作還沒提交啊，檢索數(shù)據(jù)中又包含了你要刪除的數(shù)據(jù)，當(dāng)然被阻塞了。

你的問題出現(xiàn)在哪里了，你應(yīng)該明白了吧！

解決這個(gè)問題其實(shí)很簡單，不要長事務(wù)占用。檢索的時(shí)候避開要刪除的數(shù)據(jù)。

當(dāng)然也可以改變隔離級別，sqlserver分為兩類隔離級別，改成非阻塞類就可以。

但是我個(gè)人不推薦這么做。改變隔離級別可以如下方式：

set transaction isolation level read uncommitted

begin transaction

DELETE FROM [HMS].[dbo].[DeleteLog]

where ID='1'

這個(gè)刪除沒有提交

檢索的時(shí)候

set transaction isolation level read uncommitted

SELECT

[ID]

,[DeleteBy]

,[DelDate]

FROM [dbo].[DeleteLog]

where ID=1

根本不會阻塞。 比較順利，刪除更新也一樣。

這種方式 適合 數(shù)據(jù)量龐大的社交，天文數(shù)據(jù)庫，企業(yè)管理不適合。

可以從側(cè)面看出，你的程序并不優(yōu)良，明白了否？

SQLSERVER 怎么實(shí)現(xiàn)select行級鎖

如何在SQLServer中鎖定某行記錄 SELECT au_lname FROM authors WITH (ROWLOCK ) 鎖定提示 描述 HOLDLOCK 將共享鎖保留到事務(wù)完成，而不是在相應(yīng)的表、行或數(shù)據(jù)頁不再需要時(shí)就立即釋放鎖。HOLDLOCK等同于SERIALIZABLE。

sqlserver怎么實(shí)現(xiàn)一個(gè)行鎖

1?如何鎖一個(gè)表的某一行?

SET?TRANSACTION?ISOLATION?LEVEL?READ?UNCOMMITTED?

SELECT?*?FROM?table?ROWLOCK?WHERE?id?=?1?

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

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解鎖

SQL SERVER 中如何使用行鎖? 為什么我寫的行鎖鎖不住啊?高手快來吧

給你個(gè)最詳細(xì)的吧 可能有你要的內(nèi)容

鎖的概述

一. 為什么要引入鎖

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

丟失更新

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

臟讀

A用戶修改了數(shù)據(jù),隨后B用戶又讀出該數(shù)據(jù),但A用戶因?yàn)槟承┰蛉∠藢?shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時(shí)B得到的數(shù)據(jù)就與數(shù)據(jù)庫內(nèi)的數(shù)據(jù)產(chǎn)生了不一致

不可重復(fù)讀

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

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

二 鎖的分類

鎖的類別有兩種分法：

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

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

鎖模式 描述

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

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

排它 (X) 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會同時(shí)同一資源進(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) 向表中大容量復(fù)制數(shù)據(jù)并指定了 TABLOCK 提示時(shí)使用。

共享鎖

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

更新鎖

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

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

排它鎖

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

意向鎖

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

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

鎖模式 描述

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

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

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

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

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

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

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

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

悲觀鎖：程序員自己管理數(shù)據(jù)或?qū)ο笊系逆i處理。

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

三 鎖的粒度

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

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

資源 描述

RID 行標(biāo)識符。用于單獨(dú)鎖定表中的一行。

鍵 索引中的行鎖。用于保護(hù)可串行事務(wù)中的鍵范圍。

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

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

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

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

四 鎖定時(shí)間的長短

鎖保持的時(shí)間長度為保護(hù)所請求級別上的資源所需的時(shí)間長度。

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

根據(jù)為游標(biāo)設(shè)置的并發(fā)選項(xiàng)，游標(biāo)可以獲取共享模式的滾動鎖以保護(hù)提取。當(dāng)需要滾動鎖時(shí)，直到下一次提取或關(guān)閉游標(biāo)（以先發(fā)生者為準(zhǔn)）時(shí)才釋放滾動鎖。但是，如果指定 HOLDLOCK，則直到事務(wù)結(jié)束才釋放滾動鎖。

用于保護(hù)更新的排它鎖將直到事務(wù)結(jié)束才釋放。

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

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

連接的超時(shí)間隔已到期。默認(rèn)情況下沒有超時(shí)間隔，但是一些應(yīng)用程序設(shè)置超時(shí)間隔以防止無限期等待

五 SQL Server 中鎖的自定義

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

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

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

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

@@LOCK_TIMEOUT 返回當(dāng)前會話的當(dāng)前鎖超時(shí)設(shè)置，單位為毫秒

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

示例

下例將鎖超時(shí)期限設(shè)置為 1,800 毫秒。

SET LOCK_TIMEOUT 1800

3) 設(shè)置事務(wù)隔離級別。

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

5) 配置索引的鎖定粒度

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

六 查看鎖的信息

1 執(zhí)行 EXEC SP_LOCK 報(bào)告有關(guān)鎖的信息

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

七 使用注意事項(xiàng)

如何避免死鎖

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

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

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

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

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

6 一般不要修改SQL SERVER事務(wù)的默認(rèn)級別。不推薦強(qiáng)行加鎖

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

八 幾個(gè)有關(guān)鎖的問題

1 如何鎖一個(gè)表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

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

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解鎖

幾個(gè)例子幫助大家加深印象

設(shè)table1(A,B,C)

A B C

a1 b1 c1

a2 b2 c2

a3 b3 c3

1）排它鎖

新建兩個(gè)連接

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

begin tran

update table1

set A='aa'

where B='b2'

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

commit tran

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

begin tran

select * from table1

where B='b2'

commit tran

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

2）共享鎖

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

begin tran

select * from table1 holdlock -holdlock人為加鎖

where B='b2'

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

commit tran

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

begin tran

select A,C from table1

where B='b2'

update table1

set A='aa'

where B='b2'

commit tran

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

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

3）死鎖

增設(shè)table2(D,E)

D E

d1 e1

d2 e2

在第一個(gè)連接中執(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

在第二個(gè)連接中執(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

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

補(bǔ)充一點(diǎn):

Sql Server2000支持的表級鎖定提示

HOLDLOCK 持有共享鎖，直到整個(gè)事務(wù)完成，應(yīng)該在被鎖對象不需要時(shí)立即釋放，等于SERIALIZABLE事務(wù)隔離級別

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

PAGLOCK 在使用一個(gè)表鎖的地方用多個(gè)頁鎖

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

ROWLOCK 強(qiáng)制使用行鎖

TABLOCKX 強(qiáng)制使用獨(dú)占表級鎖，這個(gè)鎖在事務(wù)期間阻止任何其他事務(wù)使用這個(gè)表

UPLOCK 強(qiáng)制在讀表時(shí)使用更新而不用共享鎖

應(yīng)用程序鎖:

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

處理應(yīng)用程序鎖的兩個(gè)過程

sp_getapplock 鎖定應(yīng)用程序資源

sp_releaseapplock 為應(yīng)用程序資源解鎖

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

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

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

參考資料：

網(wǎng)頁題目：sqlserver行鎖,sqlserver鎖表語句
轉(zhuǎn)載來源：http://aaarwkj.com/article26/dsisejg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、營銷型網(wǎng)站建設(shè)、企業(yè)建站、品牌網(wǎng)站設(shè)計(jì)、網(wǎng)站收錄、外貿(mào)網(wǎng)站建設(shè)

廣告

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