SQL SERVER のロックエスカレーション

SQLSERVERで、大量データを更新する場合に、
行ロックのはずなのにテーブルロックになる場合がある。

BOOK　ONLINEによると、
ロックのエスカレーションとは、粒度の小さい多数のロックを変換して、
粒度が大きい少数のロックにする処理です。
この処理により、同時実行の競合が発生する確率は高くなりますが、システムのオーバーヘッドは減少します。

ロックのエスカレーションは、次のいずれかの時点で開始されます。
* 1 つの Transact-SQL ステートメントが単独のテーブルまたはインデックスに対して、少なくとも 5,000 個のロックを獲得したとき。
* データベース エンジンのインスタンスのロック数がメモリまたは構成のしきい値を超えたとき。
ということみたいです。

5000件以上の更新があるトランザクションは5000件単位でトランザクションを分けろってことか・・・
SQLSERVER2005で実験してみます。

      • ロックエスカレーション

USE hoge
GO

    • テーブルの削除

truncate TABLE TESTLOCK

    • テーブルの作成

CREATE TABLE TESTLOCK
(ID int primary key,
data1 varchar(10),
data2 varchar(10))

    • データを追加

DECLARE @i integer
SET @i=0

WHILE(@i < 100000)
BEGIN

INSERT INTO TESTLOCK Values(@i,'NODATA','NODATA')
SET @i = @i + 1

END

    • 件数を取得

select count(*) from TESTLOCK

BEGIN TRAN
UPDATE TESTLOCK SET data1 = N'dataA'
WHERE ID BETWEEN '0' AND '6222';

    • ①

SELECT COUNT(*) FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE';


ROLLBACK

    • ②

BEGIN TRAN
UPDATE TESTLOCK SET data1 = N'dataA'
WHERE ID BETWEEN '0' AND '6220';

SELECT COUNT(*) FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE';

ROLLBACK

①の件数は6246件
②の件数は1件

更新件数が6220件では１０回繰り返してもロックエスカレーションは発生しなかった。
更新件数が6221〜6223件では何回かに一回ロックエスカレーションが発生した。
更新件数が6224件では必ずロックエスカレーションが発生した。

環境やテーブルによって変化するようですが、大体このくらいなのかなと思います。


    • ロック詳細確認用SQL

SELECT
resource_type as オブジェクトの種類,
resource_associated_entity_id as エンティティ,
request_mode as ロックの種類,
request_type as 要求の種類,
request_status as 状態
FROM sys.dm_tran_locks
WHERE [resource_type]<>'DATABASE';

    • オブジェクトの確認

SELECT OBJECT_NAME('53575229')

    • オブジェクトの確認

SELECT OBJECT_NAME(Object_id) FROM sys.partitions WHERE hobt_id='72057594038452224'


これを発生させない為には、

別のロックを先に行うといいそうです。

    • 一時間更新ロック

BEGIN TRAN
SELECT * FROM TESTLOCK WITH (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '0:10:00'--10分
COMMIT TRAN

その後以下SQLを実行すれば行ロックになります。

BEGIN TRAN
UPDATE TESTLOCK SET data1 = N'dataA'
WHERE ID BETWEEN '0' AND '16224';

SELECT COUNT(*) FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE';

ROLLBACK

ロック件数が16290件になり行ロックになっています。