SQL 2008 SQLアプリケーション-ロックアプリケーション分析
6933 ワード
一、ロックの基本概念:
ロック(Locking)は、リレーショナル・データベース・システムの一般的かつ必要な部分であり、同じデータを同時更新したり、更新中にデータを表示したりすることを防止し、更新されたデータの整合性を保証します.また、変更中のデータの読み取りを防止することもできます.Sqlサーバはロックを動的に管理しますが、Transact-SQLクエリがSQLサーバのロックにどのように影響するかを理解する必要があります.ここでは、下錠の基本常識を簡単にご紹介します.
ロックは、同時発生の問題を防止するのに役立ちます.あるユーザーが別のユーザーが変更しているデータを読み込もうとしたり、別のユーザーが読み込んでいるデータを変更しようとしたり、別のトランザクションが変更しようとしているデータを変更しようとしたりすると、同時問題が発生します.
SQL Serverリソースはロックされます.リソースのロック方法はロックモード(lock mode)と呼ばれます.次の表に、SQL Server処理の主なロックモードを示します.
名前
説明
共有(S)
SELECT文など、データを変更または更新しない読み取り操作に使用します.
更新(U)
更新可能なリソースで使用します.複数のセッションが読み込み、ロック、およびその後に実行される可能性のあるリソース更新時に一般的なデッドロックが発生することを防止します.
排他(X)
INSERT、UPDATE、DELETEなどのデータ修正操作に使用します.同じリソースが同時に複数更新されないことを確認します.
意向
ロックを作成する階層.意向ロックには,意向共有(IS),意向排他(IX),意向排他共有(SIX)の3種類が含まれる.
スキーマ＃スキーマ＃
テーブル・アーキテクチャに依存する操作を実行するときに使用します.スキーマロックには、スキーマ修正(Sch-M)とスキーマ安定性(Sch-S)の2種類があります.
大容量更新(BU)
テーブルに大容量データコピーを行い、TABLOCKプロンプトを指定した場合に使用します.
キー範囲
シーケンス可能なトランザクション独立性レベルを使用すると、クエリがフェッチしたローの範囲が保護されます.クエリーを再実行すると、シーケンス可能なトランザクションに一致するクエリーのローが他のトランザクションに挿入されないことを確認します.
SQL Server内のさまざまなオブジェクトをロックできます.1つのローでも、テーブルまたはデータベースでも使用できます.ロック可能なリソースは、粒度(granularity)で大きく異なります.細い(行)から太い(データベース)まで.パーティクルロックを使用すると、ロックされていないローに対してクエリーを実行できるため、より大きなデータベースの同時実行が可能になります.ただし、SQLサーバによって生成されるロックごとにメモリが必要なため、数千行の独立した行レベルのロックもSQLサーバのパフォーマンスに影響します.粗粒度のロックは同時性を低下させるが,消費されるリソースも少ない.次の表では、SQL Serverがロックできるリソースについて説明します.
しげん
説明
KEY
インデックスでは、シーケンス可能なトランザクションのキー範囲を保護するロー・ロックが使用されます.
PAGE
データベース内の8 KBページ(データ・ページやインデックス・ページなど).
EXTENT
データ・ページやインデックス・ページなど、連続した8ページのセット.
HoBT
山やBの木.集約インデックスのないテーブルのBツリー(インデックス)またはスタック・データ・ページのロックを保護します.
TABLE
すべてのデータとインデックスを含むテーブル全体.
FILE
データベース・ファイル.
RID
スタック内の単一のローをロックするロー識別子.
APPLICATION
アプリケーション専用のリソース.
METADATA
メタデータ・ロック.
ALLOCATION_UNIT
ユニットを割り当てます.
DATABASE
データベース全体.
すべてのロックが互いに互換性があるわけではありません.たとえば、排他的ロックによってロックされたリソースに他のロックを追加することはできません.他のトランザクションは、排他ロックが解放されるまで待機またはタイムアウトする必要があります.更新ロックによってロックされたリソースは、他のトランザクションの共有ロックのみを受け入れることができます.共有ロックによってロックされたリソースは、他の共有ロックや更新ロックを受け入れることもできます.
SQL Serverは自動的にロックを割り当て、アップグレードします.アップグレードは、細粒度のロック(行またはページロック)が粗粒度のテーブルロックに変換されることを意味します.単一のT-SQL文が単一のテーブルまたはインデックスで5000個以上のロックを取得した場合、またはSQL Serverインスタンスのロック数が使用可能なメモリしきい値を超えた場合、SQL Serverはロックアップグレードを開始しようとします.ロックはシステムメモリを占有するため、多くのロックを大きなロックに変換してメモリリソースを解放します.ただし、メモリリソースを解放すると、同時性が低下します.
SQL Server 2008には、ロックアップグレードを無効にするか、パーティション(テーブルではなく)の範囲でロックアップグレードを有効にするための新しいテーブル・オプションが用意されています.
二、ロックの活動を見る
次にsysを使用する例を示す.dm_tran_locksダイナミックビューは、データベース内のロックのアクティビティを監視します.
次の文を実行するクエリー・ウィンドウを開きます.
別のクエリー・ウィンドウを開きます.
実行結果:
解析:この例では、まず新しいトランザクションを開始し、テーブルに排他ロックを配置するTABLOCKXロックプロンプトを使用してProductionを実行します.ProductDocumentテーブルはクエリーを実行します.クエリsys.dm_tran_locks動的管理視力は、現在のSQL Serverインスタンスで開いているロックを監視します.AdventureWorksデータベースのアクティブロックのリストを返します.結果の最後の行にProductDocumentテーブルの排他ロックが表示されます.
最初の3つの列は、セッション・ロック、リソース・タイプ、およびデータベースIDを定義します.4列目はObject_を使用していますName関数は、2つのパラメータ(オブジェクトIDとデータベースID)を使用して、どの名前にアクセスするかを指定することに注意します(2番目のパラメータは、SQL Server 2005 SP 2によって導入され、オブジェクト名を変換するためにどのデータベースを使用するかを指定するために使用されます).ロック要求モードとステータスもクエリーし、最後にFrom句はDMVを参照し、Where句で2つのリソースタイプを指定します.Resource_Typeは、DatabaseObjectFilePageKeyRIDExtentMetadataApplicationAllocation_UnitまたはHOBTタイプ.依存リソースタイプresource_associated_entity_ID、IDがobject ID、allocation unit ID、またはHobt IDであることを決定する.
■resource_associated_entity_id列はObject ID(リソースタイプがObject)を含むsysを用いることができる.objectsディレクトリビューで名前を変換します.
■resource_associated_entity_id列はallocation unit ID(リソースタイプはAllocation_Unit)を含むsysを参照できる.allocatiion_unitsとcontain_idはsysに結合する.partitionsでは、object IDを特定できます.
■resource_associated_entity_id列にはHobt ID(リソースタイプはKeypageRowまたはHOBT)が含まれ、sysを直接参照することができる.partitionsを検索し、対応するObject IDを検索します.
■Database、Extent、ApplicationまたはMetaDataのリソースタイプについてresource_associated_entity_id列は0になります.
sysを使用する.dm_tran_locksは、予期せぬ同時問題をデバッグできます.たとえば、クエリ・セッションがロックを占有する時間が予想より長くロックされるか、ロックのパーティクルまたはロック・モードが望ましくない(より小さなパーティクルではなくテーブル・ロックを使用することが望ましいロー・ロックまたはページ・ロック).ロックのロックレベルを理解することは、クエリの同時問題をより効果的にデバッグするのに役立ちます.
三、制御テーブルのロックアップグレード行為
SQLサーバで作成されたロックごとにメモリリソースが消費されます.ロックの数が増えるとメモリが減少します.ロックのメモリ使用率が特定のしきい値を超えている場合、SQL Serverは細粒ロック(ページまたは行)を粗粒ロック(テーブルロック)に変換します.このプロセスをロックアップグレードと呼びます.ロックアップグレードにより、SQL Serverインスタンスが占有するロックの数を減らし、ロックメモリの使用を減らすことができます.
細粒度はより多くのメモリを消費しますが、複数のクエリがロックされていないローにアクセスできるため、同時性も向上します.テーブル・ロックを導入すると、メモリの消費量が減少する可能性がありますが、クエリがテーブル全体をロックしているため、ブロックも発生します.データベースを使用するアプリケーションによっては、この動作は望ましくない可能性があります.また、SQLサーバがロックアップグレードを実施するときに、できるだけ多くの制御を望んでいる可能性があります.
SQL Server 2008では、ALter tableコマンドを使用してテーブルレベルでロックのアップグレードを制御する機能が導入されています.次の3つの設定から選択できます.
■Table SQL Server 2005で使用されるデフォルトの動作です.この値に設定すると、パーティション・テーブルであるかどうかにかかわらず、テーブル・レベルでロック・アップグレードが有効になります.
■Autoテーブルがパーティション化されている場合、パーティションレベル(ヒープまたはBツリー)でロックアップグレードを有効にします.テーブルがパーティション化されていない場合、ロックのアップグレードはテーブル・レベルで発生します.
■Disableはテーブルレベルでロックを削除してアップグレードする.なお、TABLOCKプロンプトを使用したり、シーケンス可能な独立性レベルのHeapを使用したクエリーでは、テーブル・ロックが表示される可能性があります.
次の例では、テーブルの新しい設定を変更します.
では、ロックアップグレードを無効にします.
説明:この構成を変更するとsysをクエリーできます.tablesディレクトリビューのlock_escalation_desc列でこのオプションを検証します.
注:テーブルがパーティション化されていない場合は、通常、テーブル・レベルがアップグレードされます.Disableオプションを指定した場合、テーブル・レベルのロック・アップグレードは表示されません.これにより、同時性が向上しますが、大量のローやページへのアクセスを要求すると、メモリの消費量が増加します.
月を誘うhttp://www.cnblogs.com/downmoon
ロック(Locking)は、リレーショナル・データベース・システムの一般的かつ必要な部分であり、同じデータを同時更新したり、更新中にデータを表示したりすることを防止し、更新されたデータの整合性を保証します.また、変更中のデータの読み取りを防止することもできます.Sqlサーバはロックを動的に管理しますが、Transact-SQLクエリがSQLサーバのロックにどのように影響するかを理解する必要があります.ここでは、下錠の基本常識を簡単にご紹介します.
ロックは、同時発生の問題を防止するのに役立ちます.あるユーザーが別のユーザーが変更しているデータを読み込もうとしたり、別のユーザーが読み込んでいるデータを変更しようとしたり、別のトランザクションが変更しようとしているデータを変更しようとしたりすると、同時問題が発生します.
SQL Serverリソースはロックされます.リソースのロック方法はロックモード(lock mode)と呼ばれます.次の表に、SQL Server処理の主なロックモードを示します.
名前
説明
共有(S)
SELECT文など、データを変更または更新しない読み取り操作に使用します.
更新(U)
更新可能なリソースで使用します.複数のセッションが読み込み、ロック、およびその後に実行される可能性のあるリソース更新時に一般的なデッドロックが発生することを防止します.
排他(X)
INSERT、UPDATE、DELETEなどのデータ修正操作に使用します.同じリソースが同時に複数更新されないことを確認します.
意向
ロックを作成する階層.意向ロックには,意向共有(IS),意向排他(IX),意向排他共有(SIX)の3種類が含まれる.
スキーマ＃スキーマ＃
テーブル・アーキテクチャに依存する操作を実行するときに使用します.スキーマロックには、スキーマ修正(Sch-M)とスキーマ安定性(Sch-S)の2種類があります.
大容量更新(BU)
テーブルに大容量データコピーを行い、TABLOCKプロンプトを指定した場合に使用します.
キー範囲
シーケンス可能なトランザクション独立性レベルを使用すると、クエリがフェッチしたローの範囲が保護されます.クエリーを再実行すると、シーケンス可能なトランザクションに一致するクエリーのローが他のトランザクションに挿入されないことを確認します.
SQL Server内のさまざまなオブジェクトをロックできます.1つのローでも、テーブルまたはデータベースでも使用できます.ロック可能なリソースは、粒度(granularity)で大きく異なります.細い(行)から太い(データベース)まで.パーティクルロックを使用すると、ロックされていないローに対してクエリーを実行できるため、より大きなデータベースの同時実行が可能になります.ただし、SQLサーバによって生成されるロックごとにメモリが必要なため、数千行の独立した行レベルのロックもSQLサーバのパフォーマンスに影響します.粗粒度のロックは同時性を低下させるが,消費されるリソースも少ない.次の表では、SQL Serverがロックできるリソースについて説明します.
しげん
説明
KEY
インデックスでは、シーケンス可能なトランザクションのキー範囲を保護するロー・ロックが使用されます.
PAGE
データベース内の8 KBページ(データ・ページやインデックス・ページなど).
EXTENT
データ・ページやインデックス・ページなど、連続した8ページのセット.
HoBT
山やBの木.集約インデックスのないテーブルのBツリー(インデックス)またはスタック・データ・ページのロックを保護します.
TABLE
すべてのデータとインデックスを含むテーブル全体.
FILE
データベース・ファイル.
RID
スタック内の単一のローをロックするロー識別子.
APPLICATION
アプリケーション専用のリソース.
METADATA
メタデータ・ロック.
ALLOCATION_UNIT
ユニットを割り当てます.
DATABASE
データベース全体.
すべてのロックが互いに互換性があるわけではありません.たとえば、排他的ロックによってロックされたリソースに他のロックを追加することはできません.他のトランザクションは、排他ロックが解放されるまで待機またはタイムアウトする必要があります.更新ロックによってロックされたリソースは、他のトランザクションの共有ロックのみを受け入れることができます.共有ロックによってロックされたリソースは、他の共有ロックや更新ロックを受け入れることもできます.
SQL Serverは自動的にロックを割り当て、アップグレードします.アップグレードは、細粒度のロック(行またはページロック)が粗粒度のテーブルロックに変換されることを意味します.単一のT-SQL文が単一のテーブルまたはインデックスで5000個以上のロックを取得した場合、またはSQL Serverインスタンスのロック数が使用可能なメモリしきい値を超えた場合、SQL Serverはロックアップグレードを開始しようとします.ロックはシステムメモリを占有するため、多くのロックを大きなロックに変換してメモリリソースを解放します.ただし、メモリリソースを解放すると、同時性が低下します.
SQL Server 2008には、ロックアップグレードを無効にするか、パーティション(テーブルではなく)の範囲でロックアップグレードを有効にするための新しいテーブル・オプションが用意されています.
二、ロックの活動を見る
次にsysを使用する例を示す.dm_tran_locksダイナミックビューは、データベース内のロックのアクティビティを監視します.
次の文を実行するクエリー・ウィンドウを開きます.
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を実行します.ProductDocumentテーブルはクエリーを実行します.クエリsys.dm_tran_locks動的管理視力は、現在のSQL Serverインスタンスで開いているロックを監視します.AdventureWorksデータベースのアクティブロックのリストを返します.結果の最後の行にProductDocumentテーブルの排他ロックが表示されます.
最初の3つの列は、セッション・ロック、リソース・タイプ、およびデータベースIDを定義します.4列目はObject_を使用していますName関数は、2つのパラメータ(オブジェクトIDとデータベースID)を使用して、どの名前にアクセスするかを指定することに注意します(2番目のパラメータは、SQL Server 2005 SP 2によって導入され、オブジェクト名を変換するためにどのデータベースを使用するかを指定するために使用されます).ロック要求モードとステータスもクエリーし、最後にFrom句はDMVを参照し、Where句で2つのリソースタイプを指定します.Resource_Typeは、DatabaseObjectFilePageKeyRIDExtentMetadataApplicationAllocation_UnitまたはHOBTタイプ.依存リソースタイプresource_associated_entity_ID、IDがobject ID、allocation unit ID、またはHobt IDであることを決定する.
■resource_associated_entity_id列はObject ID(リソースタイプがObject)を含むsysを用いることができる.objectsディレクトリビューで名前を変換します.
■resource_associated_entity_id列はallocation unit ID(リソースタイプはAllocation_Unit)を含むsysを参照できる.allocatiion_unitsとcontain_idはsysに結合する.partitionsでは、object IDを特定できます.
■resource_associated_entity_id列にはHobt ID(リソースタイプはKeypageRowまたはHOBT)が含まれ、sysを直接参照することができる.partitionsを検索し、対応するObject IDを検索します.
■Database、Extent、ApplicationまたはMetaDataのリソースタイプについてresource_associated_entity_id列は0になります.
sysを使用する.dm_tran_locksは、予期せぬ同時問題をデバッグできます.たとえば、クエリ・セッションがロックを占有する時間が予想より長くロックされるか、ロックのパーティクルまたはロック・モードが望ましくない(より小さなパーティクルではなくテーブル・ロックを使用することが望ましいロー・ロックまたはページ・ロック).ロックのロックレベルを理解することは、クエリの同時問題をより効果的にデバッグするのに役立ちます.
三、制御テーブルのロックアップグレード行為
SQLサーバで作成されたロックごとにメモリリソースが消費されます.ロックの数が増えるとメモリが減少します.ロックのメモリ使用率が特定のしきい値を超えている場合、SQL Serverは細粒ロック(ページまたは行)を粗粒ロック(テーブルロック)に変換します.このプロセスをロックアップグレードと呼びます.ロックアップグレードにより、SQL Serverインスタンスが占有するロックの数を減らし、ロックメモリの使用を減らすことができます.
細粒度はより多くのメモリを消費しますが、複数のクエリがロックされていないローにアクセスできるため、同時性も向上します.テーブル・ロックを導入すると、メモリの消費量が減少する可能性がありますが、クエリがテーブル全体をロックしているため、ブロックも発生します.データベースを使用するアプリケーションによっては、この動作は望ましくない可能性があります.また、SQLサーバがロックアップグレードを実施するときに、できるだけ多くの制御を望んでいる可能性があります.
SQL Server 2008では、ALter tableコマンドを使用してテーブルレベルでロックのアップグレードを制御する機能が導入されています.次の3つの設定から選択できます.
■Table SQL Server 2005で使用されるデフォルトの動作です.この値に設定すると、パーティション・テーブルであるかどうかにかかわらず、テーブル・レベルでロック・アップグレードが有効になります.
■Autoテーブルがパーティション化されている場合、パーティションレベル(ヒープまたはBツリー)でロックアップグレードを有効にします.テーブルがパーティション化されていない場合、ロックのアップグレードはテーブル・レベルで発生します.
■Disableはテーブルレベルでロックを削除してアップグレードする.なお、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
*/
説明:この構成を変更するとsysをクエリーできます.tablesディレクトリビューのlock_escalation_desc列でこのオプションを検証します.
注:テーブルがパーティション化されていない場合は、通常、テーブル・レベルがアップグレードされます.Disableオプションを指定した場合、テーブル・レベルのロック・アップグレードは表示されません.これにより、同時性が向上しますが、大量のローやページへのアクセスを要求すると、メモリの消費量が増加します.
月を誘うhttp://www.cnblogs.com/downmoon