以前の記事では、ロック、ブロック、分離レベルについて話しました。 この記事では、SERIALIZABLE分離レベルで使用されるキー範囲ロックの詳細を説明し、sys.dm_tran_locks DMVを使用して既存のロックを表示する方法と、ロックが配置されているリ

キー範囲ロックはSERIALIZABLE分離レベルでのみ使用され、その目的は、他の三つの悲観的な同時実行分離レベル、すなわちread uncommitted、read committed、repeatable readで可能なファントム読み取りを停止することです。 ファントムリードとは、あるトランザクションがWHERE句を持つクエリを使用するなど、データの範囲に対して読み取りを実行し、別のトランザクションがWHERE句の条件を満たすデータを追加する場所です。 最初のトランザクションが同じ読み取りを実行すると、新しいデータが返され、同じトランザクション内の第二の読み取りにファントムデータが表示さ キー範囲ロックは、ファントムデータが追加されないようにするために使用され、データの取得に使用されるインデックスのキーに配置されます。 人を使用して、次の例。Adventureworks2012データベースのPersonテーブルでは、これらのキー範囲ロックがどのように使用されるかを示します。

の人。Personテーブルには、非クラスタ化インデックスIx_Person_Lastname_Firstname_Middlenameがあり、LastName列がインデックスキーの最初の列としてあります。 つまり、LastNameでフィルタリングしているクエリは、このインデックスを使用してデータを取得する必要があります。

Personからいくつかの行を返すクエリを実行すると。姓の範囲で人をフィルタリングすると、このインデックスが使用されていることがわかります。 たとえば、

SELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'ORDER BY LastName

は次の結果セットを返します。

実行計画を見ると、このインデックスが使用されていることがわかります。

分離レベルをSERIALIZABLEに設定し、トランザクションを開いて同じselectステートメントを実行すると、どのロックが保持されているかを確認できるはずです。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

別の接続からDMV sys.dm_tran_locksを照会して、現在のロックが保持されていることを確認できます。 私たちは重要なリソースのロックにのみ関心があるので、それに応じてフィルタリングします。

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

2つの異なるrequest_mode値で7つの行が再調整されます。 “S”request_modeは、返される3つの行に配置された共有ロックに対応します。 SERIALIZABLE分離レベルを使用しているため、これらの共有ロックは、トランザクションがコミットまたはロールバックするまで保持され、反復不可能な読み取り “RangeS-S”request_modeは、許可されているキー範囲ロックに対応します。 しかし、なぜ4つの行がありますか？ これに答えるために、sys.dm_tran_locksから返されたデータを使用し、これを使用して実際にどのリソースがロックされているかを調べることができます。興味のある列はresource_associated_entity_idとresource_descriptionです。 これらの列のデータは、resource_typeに基づいて変化する可能性があり、これの詳細については、TechNetを参照してください。 キーリソースの場合、resource_associated_entity_idはsysのpartition_idに対応します。間仕切り

SELECT *FROM sys.partitionsWHERE partition_id in (72057594057523200, 72057594045595648)

返されるobject_idはPersonに対応します。人物表

SELECT object_name(1765581328)

2つの異なるpartition_id値がPersonの異なるインデックスに対応していることがわかります。人

SELECT * FROM sys.indexes WHERE object_id = 1765581328

sys。パーティションビューでは、ロックされているパーティションがPk_Person_BusinessentityidおよびIx_Person_Lastname_Firstname_Middlenameインデックス用であることがわかります。

明確にするために、このすべてを一つのクエリに組み合わせることができます。

 SELECT L.resource_type ,L.resource_description ,L.resource_associated_entity_id ,L.request_mode ,L.request_type ,L.request_status ,OBJECT_NAME(I.object_id) AS TableName ,I.object_id ,I.index_id ,I.name AS IndexName ,I.type_desc FROM sys.dm_tran_locks L INNER JOIN sys.partitions P ON P.partition_id = L.resource_associated_entity_id INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id WHERE L.resource_type = 'KEY' ORDER BY L.request_mode

これは、3つの共有ロックが行自体(クラスター化インデックスのリーフレベル)に配置され、4つのキー範囲ロックが非クラスター化インデックス内のキーに配置されていることを示しています。 しかし、これは私たちが四つのロックを持っている理由の質問に答えていませんか？

その前に、元のクエリによって返された行に三つの共有ロックが配置されていることを証明し、それを使用して四つの”RangeS-S”ロックが配置されているリソー

まず、%%physloc%%と%%lockres%%の値をsysとともに使用します。fn_physlocformatter()関数は、元のクエリによって返された行のファイル、ページ、およびスロットを教えてくれます。 次に、この情報をDBCC PAGEコマンドで使用します。

元のクエリを次のように更新するだけです

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, %%lockres%% AS LockResource, *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

これで、Dennis Wareの行が物理的にデータファイル1、2806ページ、およびスロット3に配置されていることがわかります。 また、ロックリソースが（05c585e839d4）であることもわかります。

sys.dm_tran_locksから返されたデータを振り返ってみると、共有ロックの1つにresource_descriptionが(05c585e839d4)であることがわかりますので、これはそのロックがクラスター化インデックのその行に配置されていることを示しています。

これで、次のDBCC PAGEコマンドを実行して、ページ上の情報を実際に表示できますが、結果を返すには、トレースフラグ3604をオンにする必要があります。

DBCC TRACEON (3604);DBCC PAGE (10, 1, 2806, 3); -- DB_ID, FileId, PageId, Format

ここには多くのテキスト情報が表示されており、formatパラメータに異なる数値を指定して、異なる量のデータを返すことができます。 この場合、最初のビットはページヘッダーであり、その後は割り当てステータスがあります。 最初に使用されるスロットがスロット0であることがわかります。 ページ上のどこでこのスロットが開始されるかを示すオフセット値と、レコードの長さを示す長さの値があります。

スロット3が見つかるまで下にスクロールし、データブロックを過ぎてスクロールすると、各列の物理データが表示され、これがDennis Wareの行であることを確認でき

ここで、スロット3の最後に戻ると、sys.dm_tran_locksのresource_descriptionと%%lockres%%によって返された値と一致する

KeyHashValue = (05c585e839d4)

が表示されます。

しかし、どのように我々はキー範囲ロックのために同様の何かを行うのですか？ どのように我々は、彼らがロックされているリソースを見つけるのですか？ 一つの方法は、sys.dm_db_database_page_allocationsを使用して、どのページが非クラスタ化インデックスに属しているかを調べることです。 Ix_Person_Lastname_Firstname_Middlenameインデックスのdatabase_id、object_id、およびindex_idを渡すと、そのインデックス内で使用されるすべてのページの行が返されます。 次に、指定されたallocated_page_page_id値を使用してDBCC PAGEを実行し、Dennis Wareを含むページを見つけることができます。

SELECT allocated_page_page_id, *FROM sys.dm_db_database_page_allocations (DB_ID(N'AdventureWorks2012'), object_Id('AdventureWorks2012.Person.Person'), 4, null, 'DETAILED')WHERE is_allocated = 1

指定された値でDBCC PAGEを実行し、Dennis Wareを含むページを見つけることができます。便利なのは、この場合、DBCC PAGEは結果セットのページ内のデータを返すため、正しいページ、この場合はページ24745をすばやく見つけることができるということです。

ここで、sysの4つのキー範囲ロックにresource_description値を使用するとします。dm_tran_locks Dennis Ware、Victoria Ware、Clay Warten、Aaron Washingtonのデータを含む行には、”RangeS-S”ロックが実際に付与されていることがわかります。 しかし、Aaron Washingtonは結果セットに含まれていないので、この4番目のキーロックが存在するのはなぜですか？ これは、キーのキー範囲が取得されると、キー範囲ロック付きのキーの直前にデータが追加されないようにするためです。 換言すれば、例えば、Victoria WareとClay Warthen、例えばJennifer Warnerの間にデータを追加することはできない。

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (888888, 'VC', 'Jennifer', 'Warner')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

ここでは、待機中のキー範囲ロックを見ることができます。

これは、Timothy WardとAaron Washingtonの間にデータを追加できないことを意味し、キー範囲ロックは、開いているトランザクションでクエリを満たすことができるデータよりも実際に多くのデータをロックすることを意味します。 たとえば、Clay WarthenとAaron Washingtonの間に配置されるsimon Wartzの行を挿入することはできませんでしたが、openトランザクションの元のクエリでは返されませんでした。

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (999999, 'VC', 'Simon', 'Wartz')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

これで、キー範囲ロックがどのように使用されるかがわかったので、非クラスター化インデックスのページを検索する必要なく、どのリソースにキー範囲ロックが 必要なデータの一部を返すテーブルヒントを単純に含めることができます。

SELECT sys.fn_PhysLocFormatter(%%physloc%%), %%lockres%%, *FROM Person.Person WITH (INDEX = IX_Person_LastName_FirstName_MiddleName)WHERE LastName BETWEEN 'Ware' AND 'Warthen'

ご覧のとおり、これにより、クエリによって返される3つの行に対して、同じファイル、ページ、およびスロットデータと同じresource_descriptionが得られます。 これで、第四の”RangeS-S”ロックがClay Warthenのキーの直後のインデックス内のキー、すなわちスロット140に配置されていることがわかります。 ここでもDBCC PAGEを使用して25065ページのデータを表示できます。

今、私はちょうど実行したデモでは、我々は我々のWHERE句で使用しているものであるクラスタリングキーの最初の列を持つ非クラスタ化インデックスを持 しかし、クラスター化されていないインデックスを削除するとどうなりますか？ つまり、インデックスキーに含まれていない列に対して別のテーブルフィルターに対してクエリを実行した場合はどうなりますか？ これを簡単に示すために、非クラスタ化インデックスを削除するだけです。

DROP INDEX ON .BEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

期待どおりに同じ行が返されますが、今回はSQL Serverでクラスター化インデックスを使用してデータを取得する必要がありました。

だから、我々は何のロックを持っていますか？ 今回は適用できるキー範囲ロックがないため、共有ロックをテーブル全体に配置する必要があります。

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_modeSELECT object_name(1765581328)

この共有ロックはトランザクションがクローズされるまで保持されるため、トランザクションが完了するまでテーブル内のデータは更新できません。

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (777777, 'VC', 'Simon', 'ZZZZZ')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_mode

これは、SERIALIZABLE分離レベルを使用する場合に注意する必要があるものです。

概要

クエリ内のデータをフィルタリングするためにキー列を使用する場合、そのインデックス内のキーに対してキー範囲ロックを取ることができます。 これらは、クエリを満たすすべてのキーに加えて、クエリの外側にある次のキーに対して取得されます。 これにより、キー範囲ロック付きのキーの直前に新しいキーを挿入するデータが追加されなくなります。