若一個資料表不常更新,卻有大量的 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>
得到結果如下:
可以透過下述設定,限制該 table 僅能用 Table Lock
SP_INDEXOPTION ‘t’, ‘AllowRowLocks’, false
GO
SP_INDEXOPTION ‘t’, ‘AllowPageLocks’, false
GO
則上述的查詢會得到
5 Comments
可否限定僅能Row Lock…SQL Server 的 lock 爛死了…= =
SQL Server 預設就是 Row Lock,若要強制不准自動提升 lock 層級,可以用 Table option 如 with(rowlock)…但可能耗費資源
爛死了…相對於什麼是比較好的機制呢?
老師您好,請問一下我有一個查詢程式常發生DEADLOCK,這個程式執行時間大約10秒,同一時間可能會有多人查詢,程式裡只有寫select指令而已,而select指令對TABLE的LOCK是share lock, 為何還會發生deadlock呢?謝謝.
Deadlock 一般發生有兩種可能,一是一般的 Lock 造成 Deadlock,另一是平行運算造成 Deadlock,若你只有 select 就發生 Deadlock,那應該是平行運算造成的。或許你可以透過在 select 語法最後加上 option(maxdop 1) 限制不准平行運算,或是嘗試改 index 或其他 hint,改掉原有平行運算的執行計畫。或是回報 bug(一般而言,sql 出現平行運算造成 deadlock,時有發生,但可視為 sql server 的 bug,當然,你要能開 case)
check一下查詢指令的確是有用到平行處理,謝謝老師的解答,