テーブルスキャンによるロック待ちを発生させてみた

概要

テーブルスキャンによるロック待ちってどんなんかいな?SSMS 上で検証してみました。

簡単な説明

「テーブルスキャンによるロック待ち」とは、2つのトランザクションが発行された際に、1つ目のトランザクションと2つ目のトランザクションが操作するレコードが全く違うものでも、テーブルスキャンの場合には2つ目のトランザクションが1つ目のトランザクションによってロックされるという現象です。

詳しくはSQL Server 2005 Tips and Tipsを参照。

準備

テスト DB・テーブルの準備

CREATE DATABASE TABLE_LOCK_SCAN_TEST
DBCC USEROPTIONS

USE TABLE_LOCK_SCAN_TEST;
CREATE TABLE TABLE_A(
  DATA1 INT,
  DATA2 INT);

CREATE TABLE TABLE_B(
  DATA1 INT,
  DATA2 INT);

テストデータの作成

INSERT INTO TABLE_A (DATA1, DATA2) VALUES (0,0);
INSERT INTO TABLE_A (DATA1, DATA2) VALUES (1,1);
INSERT INTO TABLE_A (DATA1, DATA2) VALUES (2,2);
INSERT INTO TABLE_B
  SELECT * FROM TABLE_A;

ロック待ちを発生させてみる

ロックするほうのトランザクションを実行する

BEGIN TRANSACTION
  SELECT * FROM TABLE_A WITH(XLOCK, ROWLOCK, HOLDLOCK) WHERE DATA1=0;

実行計画をみると、テーブルスキャンになっています。

こちらのトランザクションはこのまま放置します。

ロック待ちになるトランザクションを実行する

別のタブでもう一個トランザクションを切ってほぼ同じクエリを流してみます。

BEGIN TRANSACTION
  SELECT * FROM TABLE_A WHERE DATA1=2;

はい、このまま固まりました。これがテーブルスキャンによるロック待ちです。

SQLServer2008にて確認しました。