Tag Archives: Lock

透過 Sch-s 禁止修改物件定義

begin tran 

— 一定要有交易,才能呼叫系統預存程序 sys.sp_getschemalock(沒有文件的系統延伸預存程序),否則會有錯誤
/*
訊息 3918,層級 16,狀態 71,程序 sp_getschemalock,行 1
陳述式或函數必須於使用者交易的內容中執行。
*/
    declare @p1 int,@p2 bigint,@p3 int,@p4 bigint

    — 對 table 和預存程序下 sch-s 鎖定
    exec [sys].sp_getschemalock @p1 output,@p2 output,N'[northwind].[dbo].[customers]’
    exec [sys].sp_getschemalock @p3 output,@p4 output,N'[northwind].[dbo].[CustOrderHist]’
   
    select @p1,@p2,@p3,@p4

image
    
    exec [sys].sp_releaseschemalock 1  — 透過 Handle No 在交易中釋放掉先前對 Customers 資料表的 sch-s 鎖定

 

 

 

可以透過另外一條連接來驗證

sp_lock <spid>

image

從 objid 可以驗證是對 CustOrderHist 預存程序的鎖定,雖然 sp_lock 的 type 欄位呈現的是 tab

廣告

限定資料表僅能用 Table Lock

若一個資料表不常更新,卻有大量的 Key-Value 供查詢,一般稱為 Lookup table,這種 table 你查,我也可以查。但若都採用 SQL Server 預設的 Row/Key Lock,再因為 Lock 量過大而提升,則不如一開始就採用 table lock,以節省 Lock 所耗的 memory 和管理 lock 所耗的資源。

use tempdb
drop table t
create table t(c1 int identity,c2 uniqueidentifier default(newsequentialid()))

set nocount on
insert t default values
go 10000

create index idx on t(c1)

begin tran
    select * from t with(holdlock) where c1=1
–rollback

預設是 Row/Key lock,所以透過另外一個連接執行

exec sp_lock <前一條連接的 SPID>

得到結果如下:

image

可以透過下述設定,限制該 table 僅能用 Table Lock

SP_INDEXOPTION ‘t’, ‘AllowRowLocks’, false
GO
SP_INDEXOPTION ‘t’, ‘AllowPageLocks’, false
GO

則上述的查詢會得到

image

Lock 範例