SQLのテーブルに共有ロックをかけたい

Question

実現したいこと

プロシージャAを実行中はテーブルBにデータが入らないようにしたい

CREATE PROCEDURE A
BEGIN

 insert into A_TBL
 select
  *
 from
  B_TBL

END

上記のようなプロシージャAがあったとします。

テーブルBのデータを、テーブルAにInsertするプロシージャです。

Excelで呼び出す想定で作りました。

BeginTrans

　exec A

CommitTrans

質問

・with(TABLOCKX)の使用方法
・テーブルに対して共有ロックをかける方法

を知りたいです。

上記プロシージャAを実行中にテーブルBのデータが増減してしまうと意図しないデータがテーブルAに挿入されてしまう可能性があります。
これを防ぐべく、テーブルロックを付与してあげる必要があるのかなと考えているのですがその必要はありますでしょうか？
VBA側でトランザクションを切っていれば、「ファントムリードは起こらない」で間違いないか気にしています。

そこで、テーブルAに共有ロックをかけることを検討しておりますが、共有ロックをかける方法は

CREATE PROCEDURE A
BEGIN

 insert into A_TBL
 select
  *
 from
  B_TBL WITH(TABLOCKX)

END

でいいのでしょうか？

しかし、

実行確認しているとロックがかかっていないように見えてしまって。。。。

下記のようなSQLを実行すると、普通にデータをInsertできてしまいます。

 select
  *
 from
  B_TBL WITH(TABLOCKX)

　insert into B_TBL value(データ)

END

with(TABLOCKX)の使い方が適切ではないからなのでしょうか？

知恵を貸していただけると嬉しいです。

どうぞよろしくお願いいたします。

補足

環境はSQL Server Management Studio 2008になります

Comments

[maisumakun]

> 下記のようなSQLを実行すると

WITH(TABLOCKX)とinsert intoは同じ接続から発行しているのですか？

[ypk]

ごめんなさい、同じ接続とはどういう意味ですか、、、？

自分のイメージだと、selectする人とinsertする人は別の人のイメージです。

Answer 1

上記プロシージャAを実行中にテーブルBのデータが増減してしまうと意図しないデータがテーブルAに挿入されてしまう可能性があります。

扱うテーブルが1つだけなら、意図しない事になる事はありません。

意図しない事になるのは、関係のある複数のテーブルに対して処理を行う場合に発生します。
これら一連の処理のトランザクションの競合の場合、最初に気にすべきなのはデッドロックです。

デッドロックにならないようにするには、一連の処理の順序が担保されている事が必要になります。

トランザクション内でのテーブルに関する実行順序を気にせずに行うような設計も出来ますので、もう少しトランザクションに関する理解を深める事が先決だと思います。

尚、

下記のようなSQLを実行すると、普通にデータをInsertできてしまいます。

同一のトランザクション内で連続して実行しているんだから、そうなるのは当然です。

Comments

[ypk]

ご回答ありがとうございます。
助かります。

トランザクションについて、もう少し勉強してみたいと思いました。

ひとつだけ、追加で教えてください。

>扱うテーブルが1つだけなら、意図しない事になる事はありません。

上記プロシージャAを実行中にテーブルBのデータが増減してしまうと意図しないデータがテーブルAに挿入されてしまう可能性は「ない」という理解でよろしいですか？もしそうから、その理由をもう少し詳しく知りたいです。

申し訳ございませんが、よろしくお願いいたします。

[sazi]

> 意図しないデータがテーブルAに挿入されてしまう可能性は「ない」
トランザクション分離レベル次第で、挿入されたとしても、その結果を処理側に伝播しない事は可能です。

> VBA側でトランザクションを切っていれば、「ファントムリードは起こらない」で間違いないか気にしています。

ファントムリードの有無は、トランザクション制御するかどうかではなく、先ずはトランザクション分離レベルの話です。

処理中にはテーブル操作がされないようにロックしたいという事ですが、何のためにそれをする必要があるのか分かりません。
質問では、insert into ですので、取得する際にデータが変化して対象/対象外が変化しても、取得する行為には影響ありません。

多分後続の処理に影響があるからだと思いますが、それならトランザクションの話になるので。

[ypk]

ご丁寧にありがとうございます。
また機会があればよろしくお願いいたします。

[ypk]

たびたび申し訳ないです、、、

WITH(TABLOCKX)

をつけて共有ロックをかけ、ファントムリードを防ぐことはできないという理解でよろしいでしょうか？

申し訳ないですがよろしくお願いいたします。

[sazi]

ファントムリードされるかどうかは、どのようにロックするかだけでなく、トランザクション分離レベルにもよります。
以下参考
【SQL server】トランザクション分離レベルについて
https://memorandom-nishi.hatenablog.jp/entry/2016/11/03/143533
上記だと、WITH(TABLOCKX)は悲観的ロック

[sazi]

そもそもファントムリードがどのような時にどのように生じるかを理解しない限り、抑制する方法も正しく選択できませんので、注意して下さい。