none
複合キーの行ロックについて

    質問

  • SQL SERVER2014 初心者です。

    お分かりになるかた助けて下さい。

    テーブル:TABLE1
    ------------------------------
    COLUMN1  |  COLUMN2
    ------------------------------
          1  |         1
          1  |         2
          1  |         3
          2  |         1
          2  |         2
          2  |         3

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

    インデックス:COLUMN1,COLUMN2 (一意)


    上記のテーブルに対してCOLUMN1のみを指定し該当するレコードに行ロックをかけたいです。

    下記のように複合キー COLUMN1,COLUMN2の値すべてに対してSELECT文を発行すれば、
    該当するレコードに対して行ロックがかけられたことは検証できました。
    (COLUMN1=1のものすべてに行ロックをかける場合)
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=1 AND COLUMN2=1
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=1 AND COLUMN2=2
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=1 AND COLUMN2=3

    COLUMN2の値が分からない(0より大きい整数)状況でCOLUMN1=1のすべてのレコードに
    行ロックをかけることは出来ないでしょうか?

    具体的には、
    -------------------------------------------------------------------------
    [トランザクションA]
    BEGIN TRAN
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=1 AND COLUMN2 > 0
    --------------------------------------------------------------------------

    --------------------------------------------------------------------------
    [トランザクションB]
    BEGIN TRAN
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=2 AND COLUMN2 > 0
    ---------------------------------------------------------------------------
    トランザクションAのあとにトランザクションBを実行するとロック待ちになります。

    そこでCOLUMN1とCOLUMN2を指定して

    -------------------------------------------------------------------------
    [トランザクションA]
    BEGIN TRAN
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=1 AND COLUMN2=1
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=1 AND COLUMN2=2
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=1 AND COLUMN2=3
    --------------------------------------------------------------------------

    --------------------------------------------------------------------------
    [トランザクションB]
    BEGIN TRAN
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=2 AND COLUMN2=1
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=2 AND COLUMN2=2
    SELECT * FROM TABLE1 WITH(UPDLOCK,ROWLOCK) WHERE COLUMN1=2 AND COLUMN2=3
    ---------------------------------------------------------------------------
    とすれば トランザクションBはロック待ちにならないのですが、
    COLUMN2を指定しなくてもロック待ちにならない方法が分かりません。

    おわかりになるかたアドバイスをお願い致します。


    2017年12月21日 11:44

すべての返信

  • インデックスの正確な定義はどのような内容になっていますでしょうか?

    もし COLUMN1, COLUMN2 ではなく COLUMN2, COLUMN1 の順になっていると質問のような挙動になります。

    2017年12月21日 22:51
  • ご返信ありがとうございます。

    順序としてはCOLUMN1(昇順),COLUMN2(昇順)

    種類は 一意 非クラスタ化 になります。

    2017年12月21日 23:53
  • 検索時に単純にインデックスが使われてなかったりしないですかね?(オプティマイザの判断)

    ヒントでインデックス指定したらうまくいったりとかしないでしょうか?


    ※ロックの問題ではなく、検索側の問題という意味です。
    • 編集済み なちゃ 2017年12月22日 1:06
    2017年12月22日 1:06
  • 非クラスタ化インデックスとのことですが、他にクラスタ化インデックスは存在しますでしょうか? それとも存在しないヒープテーブルでしょうか?

    なぜかというと質問文では SELECT * と書かれています。これにより全てのカラムを読み出すことになるため、検索に使用したインデックスだけでなく行全体に対してもロックを行う必要が生じます。

    2017年12月22日 1:21
  • 度々ありがとうございます。

    知識不足で関係がないと自己判断し、記載しておりませんでした。
    すみません。正確には下記の通りです。

    CREATE TABLE [dbo].[TABLE1](
     [COLUMNNO] [int] IDENTITY(1,1) NOT NULL,
     [COLUMN1] [int] NOT NULL CONSTRAINT [DF_TABLE1_COLUMN1]  DEFAULT ((0)),
     [COLUMN2] [int] NOT NULL CONSTRAINT [DF_TABLE1_COLUMN2]  DEFAULT ((0)),
     CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED
    (
     [COLUMNNO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    ALTER TABLE [dbo].[TABLE1] ADD  CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED
    (
     [COLUMNNO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    GO

    2017年12月22日 2:04
  • 返信ありがとうございます。

    ヒント文でインデックスを指定して検証してみます。

    ありがとうございます。

    2017年12月22日 2:07
  • 佐祐理様、なちゃ様 色々アドバイスいただきまして

    ありがとうございました。やりたいことが出来ました。

    もっと勉強します。

    2017年12月22日 7:54
  • 以下が参考になりませんでしょうか? 私の方でCOLUMN1、COLUMN2にクラスタ化インデックスを作成みたところ、ロック待ちになりませんでした。

    行ロックを行うとテーブルのインテント排他も出来てしまう
    https://social.msdn.microsoft.com/Forums/sqlserver/ja-JP/ef62a791-2002-4aee-8a37-1612b37588af?forum=sqlserverja


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年12月22日 7:57
  • 今後このページを閲覧する人のためにもどのように解決したのかを記述してください。でなければこのページが無意味なものになり、回答する意義もなくなってしまいます。
    2017年12月22日 8:21