SQL Server 中鎖的自訂

1 處理死結和設定死結優先順序

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

可以使用SET DEADLOCK_PRIORITY控制在發生死結情況時會話的反應方式。如果兩個進程都鎖定資料，並且直到其它進程釋放自己的鎖時，每個進程才能釋放自己的鎖，即發生死結情況。

2 處理逾時和設定鎖逾時期間。

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

SET LOCK_TIMEOUT 設定允許應用程式設定語句等待阻塞資源的最長時間。當語句等待的時間大於 LOCK_TIMEOUT 設定時，系統將自動取消阻塞的語句，並給應用程式返回"已超過了鎖請求逾時時段"的 1222 號錯誤資訊

樣本

下例將鎖逾時期限設定為 1,800 毫秒。

SET LOCK_TIMEOUT 1800

3) 設定交易隔離等級。

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

5) 配置索引的鎖定粒度

可以使用 sp_indexoption 系統預存程序來設定用於索引的鎖定粒度

六 查看鎖的資訊

1 執行 EXEC SP_LOCK 報告有關鎖的資訊

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

七 使用注意事項

如何避免死結

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

2 設定死結逾時參數為合理範圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免進程懸掛；

3 最佳化程式，檢查並避免死結現象出現；

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

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

6 一般不要修改SQL SERVER事務的預設層級。不推薦強行加鎖

解決問題 如何對行 表 資料庫加鎖

八 幾個有關鎖的問題

1 如何鎖一個表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 鎖定資料庫的一個表

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）排它鎖

建立兩個串連

在第一個串連中執行以下語句

begin tran

update table1

set A='aa'

where B='b2'

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

commit tran

在第二個串連中執行以下語句

begin tran

select * from table1

where B='b2'

commit tran

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

2）共用鎖定

在第一個串連中執行以下語句

begin tran

select * from table1 holdlock -holdlock人為加鎖

where B='b2'

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

commit tran

在第二個串連中執行以下語句

begin tran

select A,C from table1

where B='b2'

update table1

set A='aa'

where B='b2'

commit tran

若同時執行上述兩個語句，則第二個串連中的select查詢可以執行

而update必須等待第一個事務釋放共用鎖定轉為排它鎖後才能執行 即要等待30秒

3）死結

增設table2(D,E)

D E

d1 e1

d2 e2

在第一個串連中執行以下語句

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

在第二個串連中執行以下語句

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

同時執行，系統會檢測出死結，並中止進程

補充一點:

Sql Server2000支援的表級鎖定提示

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

NOLOCK 語句執行時不發出共用鎖定，允許髒讀 ，等於 READ UNCOMMITTED交易隔離等級

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

READPAST 讓sql server跳過任何鎖定行，執行事務，適用於READ UNCOMMITTED交易隔離等級只跳過RID鎖，不跳過頁，地區和表鎖

ROWLOCK 強制使用行鎖

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

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

應用程式鎖:

應用程式鎖就是產生用戶端程式碼的鎖，而不是sql server本身產生的鎖

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

sp_getapplock 鎖定應用程式資源

sp_releaseapplock 為應用程式資源解鎖

注意: 鎖定資料庫的一個表的區別

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

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

SQL code

1 如何鎖一個表的某一行

A 串連中執行

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

begin tran

select * from tablename with (rowlock) where id=3

waitfor delay '00:00:05'

commit tran

B串連中如果執行

update tablename set colname='10' where id=3 --則要等待5秒

update tablename set colname='10' where id<>3 --可立即執行

2 鎖定資料庫的一個表

SELECT * FROM table WITH (HOLDLOCK)

注意: 鎖定資料庫的一個表的區別

SELECT * FROM table WITH (HOLDLOCK)

其他事務可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX)

其他事務不能讀取表,更新和刪除