SQL 2008におけるSQL応用の－ロック応用分析
一、ロックの基本概念：
ロック（Locking）は、同じデータを作成することを防止する関係型データベースシステムの一般的かつ必要な一部である。 同時に更新する または更新中にデータを確認します。 更新されたデータの完全性を保証します。ユーザーが修正されたデータを読み込むのを防ぐこともできます。 。Sql Serverはダイナミックにロックを管理していますが、やはり必要です。 Transact-SQLクエリがSQL Serverのロックにどのように影響するかを知る。ここでは、簡単にロックの基本常識を紹介します。
ロックは合併問題の発生を防止するのに役立つ。あるユーザが他のユーザが修正しているデータを読もうとしたり、他のユーザが読み取っているデータを修正しようとしたり、他のトランザクションが修正しようとしているデータを修正しようとしたりすると、同時に問題が発生します。
SQL Serverリソースはロックされ、リソースのロック方式はロックモードと呼ばれ、以下の表にSQL Server処理の主なロックモードを一覧表示します。
名前
説明
共有(S)
SELECT文など、データを変更しない、または更新しない読取動作に使用します。
更新(U)
更新可能なリソースに使用します。複数のセッションが、読み取り、ロック、およびその後可能なリソース更新において共通の形式のデッドロックが発生することを防止する。
彼（X）の順番を決める
データ修正動作に使用されます。例えば、INSERT、UPDATE、DELETEなどです。同時に同じリソースを多重更新しないようにする。
意向
ロックの階層構造を作るために使用します。意向ロックには三つのタイプがあります。意向共有（IS）、意向排他（IX）、意向排他的共有（SIX）。
アーキテクチャ
テーブルアーキテクチャに依存する動作を実行するときに使用する。アーキテクチャロックは、2つのタイプを含む。アーキテクチャ修正（SchM）とアーキテクチャ安定性（Sch-S）。
大容量更新(BU)
テーブルに大容量のデータをコピーして、テーブルにTABLOCKメッセージを指定した場合に使用します。
結合範囲
順序付け可能なトランザクション分離レベルを使用すると、クエリの読み出し行の範囲が保護されます。クエリを再実行する際に、他のトランザクションは、シーケンス化されたトランザクションに該当するクエリの行を挿入できないことを確認します。
SQL Serverの様々なオブジェクトをロックすることができます。行でもテーブルでもデータベースでもいいです。ロックできる資源は粒度に大きな差があります。細い行から太いデータベースまで。細粒ロックは、ユーザーが特定の未ロック行に対してクエリを実行することができるので、より大きなデータベースの同時化を可能にする。しかし、SQL Serverによって生成される各ロックはメモリが必要であり、数千の独立したラインレベルのロックはSQL Serverの性能にも影響を及ぼします。粗粒度のロックは合併性を低下させたが、消費資源も少ない。以下の表では、SQL Serverがロックできるリソースを紹介します。
リソース
説明
KEY
索引では、プログレッシブトランザクション内のキー範囲を保護するための行ロックが使用されます。
PAGE
データベース内の8 KBページ、たとえばデータページや索引ページです。
EXTENT
一連の8ページ、たとえば、データページまたはインデックスページ。
HoBT
山またはBの木集められた索引がないテーブルのBツリー（索引）またはデータページを保護するための鍵です。
TABLE
すべてのデータと索引を含む表全体。
FILE
データベースファイル
RID
スタック内の単一の行をロックするためのライン識別子。
APPLICATION
アプリケーション専用のリソースです。
METADATA
メタデータロック
ALLOCATION_ユニット
割り当てユニット
DATABASE
データベース全体
すべてのロックが互換できるわけではない。例えば、ロックされたリソースには、他のロックが付けられません。彼がロック解除されるまで、他の仕事は待つか、タイムアウトしなければなりません。更新されたロックされたリソースは、他のトランザクションの共有ロックしか受け付けられません。共有ロックされたリソースは、他の共有ロックまたは更新ロックを受け入れることができる。
SQL Serverは自動的にロックを割り当て、アップグレードします。レベルアップは、細粒度のロック（行またはページ錠）が粗粒度のテーブルロックに変換されることを意味します。単一のT−SQL文が単一のテーブルまたはインデックス上で5000個以上のロックを取得した場合、またはSQL Server例のロック数が利用可能なメモリ閾値を超えた場合、SQL Serverはロックアップグレードを試みる。ロックはシステムメモリを占有するので、多くのロックを大きなロックに変えてメモリ資源を解放します。しかし、メモリ資源を放出すると同時に合併性が低下する。
SQL Server 2008は新しいテーブルオプションを持ってきました。ロックアップグレードを無効にしたり、パーティションの範囲でロックアップグレードを有効にしたりできます。
二、ロックの活動を確認する
次の例を示します。sys.dm_を使います。トレイロックダイナミックビューは、データベース内のロックの活動を監視します。
クエリーウィンドウを開き、次のような文を実行します。
解析：この例では、まず新しいトランザクションを開始し、TABLOCKXロックプロンプトを使用して、Production.Product Dcumentテーブルにクエリーを実行した。sys.dm_を調べますトレイlocks動的管理視力は現在のSQL Serverの例でどのロックが開かれているかを監視できます。それはAdvientureWorksデータベースのアクティブロックのリストを返します。結果の最後の行には、Product Dcumentの排他的なロックが見られます。
最初の3つの列は、セッションロック、リソースタイプ、およびデータベースIDを定義します。4列目はObject_を使用しています。Name関数は、2つのパラメータ（オブジェクトIDとデータベースID）を使用して、どの名前にアクセスするかを指定していることに注意してください（2番目のパラメータはSQL Server 2005 SP 2が導入したもので、オブジェクト名を変換するためにどのデータベースを使用するかを指定します）。ロック要求モードとステータスも調べて、最後にFromサブルーチンはDMVを参照して、Whereサブルーチンで二つのリソースタイプを指定しました。Resource_Typeは、Database\Object\File\Page\Key\RiD\Extent\Metadata\Appplication\Allocation_のようなロックされたリソースタイプを指定しています。ユニットまたはHOBTタイプ。資源に依存するタイプのレスキュー。assiociated_entity_IDはobject ID、allocation unit ID、またはHobt IDと確定します。
■レスポンスがよければassiociated_entity_id列はObject ID（リソースタイプはObject）を含み、sys.objectディレクトリビューを用いて名前を変換することができる。
■レスポンスがよければassiociated_entity_ID列はallocation unit ID（リソースタイプはAllocation_）を含みます。ユニット）は、sys.allocation_を参照することができます。unitsとcontain_idはsys.partitionsに連結すれば、Object IDを確定することができます。
■レスポンスがよければassiociated_entity_ID（リソースタイプはKey\page\RowまたはHOBT）を含み、sys.partitionsを直接参照して、対応するObject IDを検索することができる。
■Database、Extent、AppplicationまたはMetaDataのリソースタイプに対して、reourcuassiociated_entity_id列は0になります。
sys.dm_を使うトレイlocksは予想できない合併問題に対して故障デバッグを行うことができます。例えば、1つの照会セッションがロックされる時間が予想より長くなり、またはロックされた粒度またはロックパターンは、私たちが望むものではないかもしれない（より小さな粒径の行錠またはページ錠ではなく、テーブルロックを使用することが望ましいかもしれない）。ロックのあるロックレベルを理解することは、クエリの同時問題をより効果的にデバッグすることに役立つ。
三、制御表のロックアップ行為
各SQL Serverで作成されたロックはメモリリソースを消費します。ロックの数が増えるとメモリが減ります。ロックされたメモリの使用率が特定の閾値を超える場合、SQL Serverは、細粒度ロック（ページまたは行）を粗粒ロック（テーブルロック）に変換します。この過程をロックアップグレードと呼びます。ロックアップグレードはSQL Serverのインスタンス占有のロック数を減少させ、メモリロックの使用を減少させることができます。
細粒度はより多くのメモリを消費しますが、複数のクエリがロックされていない行にアクセスできるため、同時性も改善されます。時計のロックを導入するとメモリの消費が減るかもしれませんが、ブロックをもたらすかもしれません。データベースを使うアプリケーションによっては、この行動は発生したくないかもしれません。SQL Serverがロックアップグレードを実施する時に、できるだけ多くのコントロールを得ることを望むかもしれません。
SQL Server 2008は、ALter tableコマンドを使用してテーブルレベルでロックアップグレードを制御する機能を導入しています。以下の3つの設定から選択できます。
■Table SQL Server 2005で使用されるデフォルトの動作です。この値に設定すると、テーブルレベルでロックアップグレードが有効になります。
■Autoテーブルがパーティションされている場合、パーティションレベル（ヒープまたはBツリー）でロックアップグレードを有効にします。テーブルがパーティションされていない場合、ロックアップグレードはテーブルレベルに発生します。
■Displableはテーブルレベルでロックを削除してアップグレードします。注意してください。TABLOCKを使ったプログレッシブ隔离レベルのHeapのクエリを使用しても、テーブルロックが見られます。
以下の例では、修正テーブルの新しい設定を示しています。
ロック（Locking）は、同じデータを作成することを防止する関係型データベースシステムの一般的かつ必要な一部である。 同時に更新する または更新中にデータを確認します。 更新されたデータの完全性を保証します。ユーザーが修正されたデータを読み込むのを防ぐこともできます。 。Sql Serverはダイナミックにロックを管理していますが、やはり必要です。 Transact-SQLクエリがSQL Serverのロックにどのように影響するかを知る。ここでは、簡単にロックの基本常識を紹介します。
ロックは合併問題の発生を防止するのに役立つ。あるユーザが他のユーザが修正しているデータを読もうとしたり、他のユーザが読み取っているデータを修正しようとしたり、他のトランザクションが修正しようとしているデータを修正しようとしたりすると、同時に問題が発生します。
SQL Serverリソースはロックされ、リソースのロック方式はロックモードと呼ばれ、以下の表にSQL Server処理の主なロックモードを一覧表示します。
名前
説明
共有(S)
SELECT文など、データを変更しない、または更新しない読取動作に使用します。
更新(U)
更新可能なリソースに使用します。複数のセッションが、読み取り、ロック、およびその後可能なリソース更新において共通の形式のデッドロックが発生することを防止する。
彼（X）の順番を決める
データ修正動作に使用されます。例えば、INSERT、UPDATE、DELETEなどです。同時に同じリソースを多重更新しないようにする。
意向
ロックの階層構造を作るために使用します。意向ロックには三つのタイプがあります。意向共有（IS）、意向排他（IX）、意向排他的共有（SIX）。
アーキテクチャ
テーブルアーキテクチャに依存する動作を実行するときに使用する。アーキテクチャロックは、2つのタイプを含む。アーキテクチャ修正（SchM）とアーキテクチャ安定性（Sch-S）。
大容量更新(BU)
テーブルに大容量のデータをコピーして、テーブルにTABLOCKメッセージを指定した場合に使用します。
結合範囲
順序付け可能なトランザクション分離レベルを使用すると、クエリの読み出し行の範囲が保護されます。クエリを再実行する際に、他のトランザクションは、シーケンス化されたトランザクションに該当するクエリの行を挿入できないことを確認します。
SQL Serverの様々なオブジェクトをロックすることができます。行でもテーブルでもデータベースでもいいです。ロックできる資源は粒度に大きな差があります。細い行から太いデータベースまで。細粒ロックは、ユーザーが特定の未ロック行に対してクエリを実行することができるので、より大きなデータベースの同時化を可能にする。しかし、SQL Serverによって生成される各ロックはメモリが必要であり、数千の独立したラインレベルのロックはSQL Serverの性能にも影響を及ぼします。粗粒度のロックは合併性を低下させたが、消費資源も少ない。以下の表では、SQL Serverがロックできるリソースを紹介します。
リソース
説明
KEY
索引では、プログレッシブトランザクション内のキー範囲を保護するための行ロックが使用されます。
PAGE
データベース内の8 KBページ、たとえばデータページや索引ページです。
EXTENT
一連の8ページ、たとえば、データページまたはインデックスページ。
HoBT
山またはBの木集められた索引がないテーブルのBツリー（索引）またはデータページを保護するための鍵です。
TABLE
すべてのデータと索引を含む表全体。
FILE
データベースファイル
RID
スタック内の単一の行をロックするためのライン識別子。
APPLICATION
アプリケーション専用のリソースです。
METADATA
メタデータロック
ALLOCATION_ユニット
割り当てユニット
DATABASE
データベース全体
すべてのロックが互換できるわけではない。例えば、ロックされたリソースには、他のロックが付けられません。彼がロック解除されるまで、他の仕事は待つか、タイムアウトしなければなりません。更新されたロックされたリソースは、他のトランザクションの共有ロックしか受け付けられません。共有ロックされたリソースは、他の共有ロックまたは更新ロックを受け入れることができる。
SQL Serverは自動的にロックを割り当て、アップグレードします。レベルアップは、細粒度のロック（行またはページ錠）が粗粒度のテーブルロックに変換されることを意味します。単一のT−SQL文が単一のテーブルまたはインデックス上で5000個以上のロックを取得した場合、またはSQL Server例のロック数が利用可能なメモリ閾値を超えた場合、SQL Serverはロックアップグレードを試みる。ロックはシステムメモリを占有するので、多くのロックを大きなロックに変えてメモリ資源を解放します。しかし、メモリ資源を放出すると同時に合併性が低下する。
SQL Server 2008は新しいテーブルオプションを持ってきました。ロックアップグレードを無効にしたり、パーティションの範囲でロックアップグレードを有効にしたりできます。
二、ロックの活動を確認する
次の例を示します。sys.dm_を使います。トレイロックダイナミックビューは、データベース内のロックの活動を監視します。
クエリーウィンドウを開き、次のような文を実行します。
USE AdventureWorks
BEGIN TRAN
SELECT ProductID, ModifiedDate
FROM Production.ProductDocument
WITH (TABLOCKX)
SELECT request_session_id sessionid,
resource_type type,
resource_database_id dbid,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) objectname,
request_mode rmode,
request_status rstatus
FROM sys.dm_tran_locks
WHERE resource_type IN ('DATABASE', 'OBJECT')
/*
sessionid type dbid objectname rmode rstatus
51 DATABASE 4 NULL S GRANT
52 DATABASE 4 NULL S GRANT
53 DATABASE 8 NULL S GRANT
56 DATABASE 8 NULL S GRANT
53 OBJECT 8 ProductDocument X GRANT
*/
解析：この例では、まず新しいトランザクションを開始し、TABLOCKXロックプロンプトを使用して、Production.Product Dcumentテーブルにクエリーを実行した。sys.dm_を調べますトレイlocks動的管理視力は現在のSQL Serverの例でどのロックが開かれているかを監視できます。それはAdvientureWorksデータベースのアクティブロックのリストを返します。結果の最後の行には、Product Dcumentの排他的なロックが見られます。
最初の3つの列は、セッションロック、リソースタイプ、およびデータベースIDを定義します。4列目はObject_を使用しています。Name関数は、2つのパラメータ（オブジェクトIDとデータベースID）を使用して、どの名前にアクセスするかを指定していることに注意してください（2番目のパラメータはSQL Server 2005 SP 2が導入したもので、オブジェクト名を変換するためにどのデータベースを使用するかを指定します）。ロック要求モードとステータスも調べて、最後にFromサブルーチンはDMVを参照して、Whereサブルーチンで二つのリソースタイプを指定しました。Resource_Typeは、Database\Object\File\Page\Key\RiD\Extent\Metadata\Appplication\Allocation_のようなロックされたリソースタイプを指定しています。ユニットまたはHOBTタイプ。資源に依存するタイプのレスキュー。assiociated_entity_IDはobject ID、allocation unit ID、またはHobt IDと確定します。
■レスポンスがよければassiociated_entity_id列はObject ID（リソースタイプはObject）を含み、sys.objectディレクトリビューを用いて名前を変換することができる。
■レスポンスがよければassiociated_entity_ID列はallocation unit ID（リソースタイプはAllocation_）を含みます。ユニット）は、sys.allocation_を参照することができます。unitsとcontain_idはsys.partitionsに連結すれば、Object IDを確定することができます。
■レスポンスがよければassiociated_entity_ID（リソースタイプはKey\page\RowまたはHOBT）を含み、sys.partitionsを直接参照して、対応するObject IDを検索することができる。
■Database、Extent、AppplicationまたはMetaDataのリソースタイプに対して、reourcuassiociated_entity_id列は0になります。
sys.dm_を使うトレイlocksは予想できない合併問題に対して故障デバッグを行うことができます。例えば、1つの照会セッションがロックされる時間が予想より長くなり、またはロックされた粒度またはロックパターンは、私たちが望むものではないかもしれない（より小さな粒径の行錠またはページ錠ではなく、テーブルロックを使用することが望ましいかもしれない）。ロックのあるロックレベルを理解することは、クエリの同時問題をより効果的にデバッグすることに役立つ。
三、制御表のロックアップ行為
各SQL Serverで作成されたロックはメモリリソースを消費します。ロックの数が増えるとメモリが減ります。ロックされたメモリの使用率が特定の閾値を超える場合、SQL Serverは、細粒度ロック（ページまたは行）を粗粒ロック（テーブルロック）に変換します。この過程をロックアップグレードと呼びます。ロックアップグレードはSQL Serverのインスタンス占有のロック数を減少させ、メモリロックの使用を減少させることができます。
細粒度はより多くのメモリを消費しますが、複数のクエリがロックされていない行にアクセスできるため、同時性も改善されます。時計のロックを導入するとメモリの消費が減るかもしれませんが、ブロックをもたらすかもしれません。データベースを使うアプリケーションによっては、この行動は発生したくないかもしれません。SQL Serverがロックアップグレードを実施する時に、できるだけ多くのコントロールを得ることを望むかもしれません。
SQL Server 2008は、ALter tableコマンドを使用してテーブルレベルでロックアップグレードを制御する機能を導入しています。以下の3つの設定から選択できます。
■Table SQL Server 2005で使用されるデフォルトの動作です。この値に設定すると、テーブルレベルでロックアップグレードが有効になります。
■Autoテーブルがパーティションされている場合、パーティションレベル（ヒープまたはBツリー）でロックアップグレードを有効にします。テーブルがパーティションされていない場合、ロックアップグレードはテーブルレベルに発生します。
■Displableはテーブルレベルでロックを削除してアップグレードします。注意してください。TABLOCKを使ったプログレッシブ隔离レベルのHeapのクエリを使用しても、テーブルロックが見られます。
以下の例では、修正テーブルの新しい設定を示しています。
ALTER TABLE Person.Address
SET (LOCK_ESCALATION = AUTO)
-- SQL Server 2005
SELECT lock_escalation,lock_escalation_desc
FROM sys.tables
WHERE name='Address'
/*
lock_escalation lock_escalation_desc
2 AUTO
*/
ALTER TABLE Person.Address
SET ( LOCK_ESCALATION = DISABLE)
SELECT lock_escalation,lock_escalation_desc
FROM sys.tables
WHERE name='Address'
/*
lock_escalation lock_escalation_desc
1 DISABLE
*/