限定資料表僅能用 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

5 Comments

  1. Van Hazard
    Posted 2011 年 05 月 18 日 at 08:33:58 | Permalink | 回覆

    可否限定僅能Row Lock…SQL Server 的 lock 爛死了…= =

    • Posted 2011 年 05 月 18 日 at 09:51:24 | Permalink | 回覆

      SQL Server 預設就是 Row Lock,若要強制不准自動提升 lock 層級,可以用 Table option 如 with(rowlock)…但可能耗費資源

      爛死了…相對於什麼是比較好的機制呢?

  2. ping
    Posted 2012 年 11 月 16 日 at 06:59:19 | Permalink | 回覆

    老師您好,請問一下我有一個查詢程式常發生DEADLOCK,這個程式執行時間大約10秒,同一時間可能會有多人查詢,程式裡只有寫select指令而已,而select指令對TABLE的LOCK是share lock, 為何還會發生deadlock呢?謝謝.

    • Posted 2012 年 11 月 16 日 at 07:07:16 | Permalink | 回覆

      Deadlock 一般發生有兩種可能,一是一般的 Lock 造成 Deadlock,另一是平行運算造成 Deadlock,若你只有 select 就發生 Deadlock,那應該是平行運算造成的。或許你可以透過在 select 語法最後加上 option(maxdop 1) 限制不准平行運算,或是嘗試改 index 或其他 hint,改掉原有平行運算的執行計畫。或是回報 bug(一般而言,sql 出現平行運算造成 deadlock,時有發生,但可視為 sql server 的 bug,當然,你要能開 case)

      • ping
        Posted 2012 年 11 月 19 日 at 03:07:19 | Permalink

        check一下查詢指令的確是有用到平行處理,謝謝老師的解答,

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: