|
|
掲載地 2016/07/20 12:37:53
|
|
|
閘門の概要
1. なぜロックを導入するのか
複数のユーザーが同時にデータベース上で操作を行うと、以下のデータ不整合が生じます。
欠落した更新
2人のユーザー、AとBが同じデータを読み取り改変し、一方のユーザーの改変の結果がもう一方の変更結果(例えばチケット予約システム)を破壊します
エッチリーディング
ユーザーAがデータを修正し、その後ユーザーBがデータを読み上げますが、ユーザーAが何らかの理由でデータの変更をキャンセルし、データは元の値に戻ります
繰り返し読まないでください
ユーザーAがデータを読み、その後ユーザーBがデータを読み取り修正します
並行制御の主な方法はブロッキングであり、これはデータ不整合を避けるために一定期間ユーザーが特定の操作を行うことを禁止することです
2. 閘門の分類
閘門には2つのカテゴリーに分けられます。
1 . データベースシステムの観点から見ると、排他的ロック(排他的ロック)、共有ロック、更新ロックに分けられます
MS - SQL Serverは以下のリソースロックパターンを使用します。
ロックモードの説明
Share(s)は、データを変更・更新しない操作(読み取り専用操作)に使われます。例えばSELECT文などです。
更新可能なリソースではUpdate(U)が使われます。 複数のセッションが読み込まれ、ロックされ、リソース更新が発生する可能性のあるデッドロックを防ぐことができます。
Excluative(X)はINSERT、UPDATE、DELETEなどのデータ改正操作に使用されます。 同じリソースに対して同時に複数の更新が行われないように注意してください。
意図ロックはロックの階層を確立するために使われます。 インテントロックの種類は、Intent Shared(IS)、Intent Exclusive(IX)、Intent Exclusive(SIX)です。
スキーマロックは、テーブルスキーマに依存する操作を行う際に使われます。 スキーマロックの種類は、スキーマ修正(Sch -M)とスキーマ安定性(Sch -S)です。
大量更新(BU)は、大量のデータがテーブルにコピーされ、TABLOCKヒントを指定する際に使われます。
共有ロック
共有ロックは、同時トランザクションがリソースを読み取る(SELECT)を可能にします。 共有(S)ロックがリソースに存在する場合、他のトランザクションはデータを変更できません。 データが読み込まれたらすぐにリソースの共有(S)ロックを解除します。ただし、トランザクションの隔離レベルが繰り返し可能またはそれ以上に設定されている場合や、共有(S)ロックがトランザクションの存続期間中ロックヒントで保持されている場合を除きます。
更新ロック
アップデート(U)ロックは通常のデッドロックを防ぎます。 典型的な更新パターンは、レコードを読み込み、リソース(ページまたは行)の共有(S)ロックを取得し、その後行を修正するというもので、そのロックを排他的な(X)ロックに変換する必要があります。 もし2つのトランザクションがリソースに対して共有モードロックを取得し、同時にデータを更新しようとした場合、1つのトランザクションがそのロックを排他的(X)ロックに変換しようとします。 共有モードから排他的ロックへの移行はしばらく待たなければなりません。なぜなら、あるトランザクションの排他的ロックは別のトランザクションの共有モードロックと互換性がないからです。 ロック待ちが発生します。 2つ目のトランザクションは、更新のための排他的な(X)ロックを取得しようとします。 デッドロックは、両方のトランザクションが排他的(X)ロックに変換され、それぞれのトランザクションが共有モードロックを解放するのを待つために発生します。
この潜在的なデッドロック問題を避けるために、更新された(U)ロックを使用してください。 リソースに対して更新された(U)ロックを付与できるのは一度に1つのトランザクションだけです。 トランザクションがリソースを変更した場合、更新(U)ロックは排他的(X)ロックに変換されます。 それ以外の場合、ロックは共有ロックに変換されます。
排他的ロック
排他的(X)ロックは同時トランザクションがリソースにアクセスすることを防止します。 他のトランザクションは排他的な(X)ロックでロックされたデータを読み取ったり変更したりできません。
意図ロック
インテントロックは、SQL Serverが階層内の基盤リソースの一部に対して共有(S)ロックまたは排他的(X)ロックを取得する必要があることを示します。 例えば、テーブルレベルでshare-intentロックが設置されている場合、取引がテーブルのページや行にshare(S)ロックを付与する意図があることを示します。 テーブルレベルでインテントロックを設定することで、他のトランザクションがそのページを含むテーブルに対して排他的(X)ロックを取得するのを防ぎます。 意図ロックはパフォーマンスを向上させることができます。なぜなら、SQL Serverはテーブルレベルでのみ意図ロックをチェックし、トランザクションがそのテーブルのロックを安全に取得できるかどうかを判断するためです。 テーブルの各行やページのロックを確認して、トランザクションがテーブル全体をロックできるかどうかを判断する代わりに、
インテントロックには、インテントシェアリング(IS)、インテントエクスクルーシブ(IX)、インテント排他的シェアリング(SIX)が含まれます。
ロックモードの説明
意図共有(IS)は、トランザクションの意図が読み取り階層の一部(すべてではなく)の基盤となるリソースに対して、各リソースにSロックを付与することを示します。
意図排他(Intent Exxive、IX)は、トランザクションの意図が階層内の基盤となるリソースの一部を各リソースにXロックをかけることで変更することを示します。 IXはISの超集合です。
意図付き排他共有(SIX)は、トランザクションの意図が階層内のすべての基盤リソースを読み取り、各リソースにIXロックをかけて一部(すべてではない)を修正することを示します。 トップレベルのリソースに対して同時ISロックを許可してください。 例えば、テーブルのSIXロックはテーブルにSIXロックを配置し(ISロックを同時実行可能)、現在修正されたページにはIXロック(修正された行にXロック)を配置します。 各リソースは一定期間、他のトランザクションによるリソース更新を防ぐために1つのSIXロックしか持てませんが、他のトランザクションはテーブルレベルのISロックを取得することで階層内の基礎リソースを読み取ることができます。
排他的ロック:ロック操作を実行するプログラムのみが使用でき、それ以外の操作は認められません。 データ更新コマンドを実行すると、SQL Serverは自動的に排他的ロックを使用します。 オブジェクトに他のロックが存在する場合、排他ロックを追加することはできません。
共有ロック:共有ロックによってロックされたリソースは他のユーザーが読み取ることができますが、他のユーザーはそれを変更できません。
更新ロック:SQL Serverがデータを更新する準備ができたら、まずデータオブジェクトをロックし、データが変更できず読み取れるようにします。 SQL Serverがデータを更新したいと判断すると、自動的に更新ロックを排他的ロックに置き換え、オブジェクトに他のロックが存在する場合は更新ロックを追加できません。
2 . プログラマーの視点から見ると、楽観ロックと悲観ロックに分けられます。
楽観ロック:ロックの管理は完全にデータベースに依存しています。
悲観的なロック:プログラマーはデータやオブジェクト自体のロック管理を管理します。
MS - SQLSERVERは、複数のユーザー間で同時にデータベース内で修正を行う悲観的な並行制御を実装するためにロックを使用します
3. 錠前の粒子サイズ
ロックの粒度はブロック対象のサイズであり、小さなブロッキング粒度は高い同時実行性ですがオーバーヘッドは大きく、大きなブロッキング粒度は低並行性ですがオーバーヘッドは小さいです
SQL Serverは、行、ページ、キー、キーレンジ、インデックス、テーブル、またはデータベースに対してロックの粒度をサポートしています
資源の説明
RID行識別子。 テーブルの行を個別にロックしていました。
インデックスのキーロウロック。 シリアライズ可能なトランザクションの鍵の範囲を保護するために使用されます。
8キロバイト(KB)のデータページまたはインデックスページ。
拡張ディスク:8つの隣接したデータページまたはインデックスページのセットです。
表 すべてのデータとインデックスを含む全体のテーブル。
データベース。
4. ロックアップ時間の長さ
ロックが保持される時間とは、要求されたレベルでリソースを保護するために必要な時間のことです。
読み取り操作を保護するために使用される共有ロックのホールド時間は、トランザクションの隔離レベルによって異なります。 デフォルトのトランザクション隔離レベルであるREAD COMMITTEDでは、共有ロックは読み取りページの間のみ制御されます。 スキャンでは、ロックはスキャン内の次のページで入手されるまで解除されません。 HOLDLOCKプロンプトを指定するか、トランザクションの隔離レベルをREPEATABLE READまたはSERIALIZABLEに設定すると、トランザクションが終了するまでロックは解除されません。
カーソルに設定された並行性オプションに応じて、共有モードで抽出を保護するためにスクロールロックを取得することができます。 スクロールロックが必要な場合、カーソルが次に抽出または閉じられるまで解除されません。 しかし、HOLDLOCKを指定すると、スクロールロックはトランザクション終了まで解除されません。
アップデートを保護するために使われる排他ロックは、取引終了まで解放されません。
もし接続が他の接続が制御するロックと競合するロックを取得しようとした場合、その接続は以下までブロックされます:
競合するロックが解放され、接続は要求されたロックを取得します。
接続タイムアウトは終了しました。 デフォルトではタイムアウト間隔はありませんが、一部のアプリは無期限の待ち時間を防ぐためにタイムアウト間隔を設定しています
SQL Serverにおけるロックの5つのカスタマイズ
1 デッドロックの処理とデッドロックの優先順位設定
デッドロックとは、複数のユーザーが異なるブロックを申請することで、申請者がブロック権の一部を持ち、他のユーザーが所有する部分的なブロックを待つために、無限に待つ状態を指します
SET DEADLOCK_PRIORITYを使って、デッドロック状態時のセッションの反応を制御できます。 両プロセスがデータをロックし、それぞれのプロセスが自分のロックを解除できないと、相手のプロセスがロックを解除するまでその状態が起こると、デッドロック状態が生じます。
2 タイムアウトを処理し、ロックタイムアウトの時間を設定します。
@@LOCK_TIMEOUT 現在のセッションのロックタイムアウト設定をミリ秒単位で返します
SET LOCK_TIMEOUT設定により、アプリケーションは文がリソースをブロックする最大待ち時間を設定できます。 文の待ち時間がLOCK_TIMEOUT設定より長くなると、システムは自動的にブロッキング文をキャンセルし、ロック要求のタイムアウト期間が超過したというエラーメッセージ1222をアプリケーションに返します
例
以下の例では、ロックタイムアウトの時間が1,800ミリ秒に設定されています。
セットLOCK_TIMEOUT1800
3) トランザクションの隔離レベルを設定する。
4) SELECT、INSERT、UPDATE、DELETE文に対してテーブルレベルのロックヒントを使うこと。
5) インデックスのロッキング粒度を設定する
sp_indexoptionシステムのストアドプロシージャを使ってロックのインデックスの粒度を設定できます
6. 錠前の情報を見る
1 執行SP_LOCKロックに関する情報を報告する
2 クエリアナライザーでCtrl+2を押してロック情報を見る
7. 使用上の注意事項
行き詰まりを避ける方法
1. トランザクションを使用する際は、トランザクションの論理処理プロセスを短縮し、トランザクションを早めに提出またはロールバックする。
2 デッドロックのタイムアウトパラメータを合理的な範囲に設定します。例えば:3分から10分; 時間が過ぎると、プロセスのハングを避けるために自動的に作業が中断されます。
3. プログラムの最適化、デッドロック現象のチェックおよび回避;
4. 正確なバージョンを出す前に、すべてのスクリプトとSPを慎重にテストすること。
5 すべてのSPはエラー処理(@error経由)を備えなければなりません
6 SQL SERVERトランザクションのデフォルトレベルを変更しないでください。 強制ロックは推奨されません
問題の解決方法行テーブルデータベースのロック方法
8. 錠前に関するいくつかの質問
1 テーブルの列をロックする方法
トランザクションアイソレーションレベルを設定し READUNCOMMITTED(未コミット)
SELECT *FROM table ROWLOCK(id = 1)
2 データベース内のテーブルをロックする
*テーブルから WITH( HOLDLOCK )
ロックステートメント:
sybase:
テーブルセット col1 = col1 ここで1=0 ;
MSSQL:
テーブル(tablockx)からcol1を選択し、ここで1=0;
oracle:
排他モードのロックテーブル;
ロックがロックされた後は、ロックされたユーザーが解除するまで他の誰も操作できず、コミットまたはロールバックで解除されます
いくつかの例を挙げると、印象を深めるのに役立ちます
セットテーブル1(A、B、C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
1) 排他的ロック
2つの新しい接続を作成する
最初の接続で次の文を実行します
トランを始めろ
更新表1
集合A= ' aa '
ここで B= ' b2 '
遅延待ち」 00:00:30' --30秒待って
コミットトラン
2回目の接続で次の文を実行します
トランを始めろ
*表1から選択
ここで B= ' b2 '
コミットトラン
上記の2つの文が同時に実行された場合、選択クエリは更新の実行を待つ必要があり、すなわち30秒待つ必要があります
2) 共有ロック
最初の接続で次の文を実行します
トランを始めろ
select *table1から holdlock - ホールドロックが人工的に追加される
ここで B= ' b2 '
遅延待ち」 00:00:30' --30秒待って
コミットトラン
2回目の接続で次の文を実行します
トランを始めろ
表1からA,Cを選択します
ここで B= ' b2 '
更新表1
集合A= ' aa '
ここで B= ' b2 '
コミットトラン
上記の2つの文が同時に実行されると、2回目の接続で選択クエリを実行できます
アップデートは、最初のトランザクションが共有ロックを解放し、排他ロックに変換するのを待ってから実行される必要があり、つまり30秒待つ必要があります
3) デッドロック
追加表2(D,E)
D E
D1 E1
d2 e2
最初の接続で次の文を実行します
トランを始めろ
更新表1
集合A= ' aa '
ここで B= ' b2 '
遅れを待つ 00:00:30
更新表2
集合D=' d5 '
ここで E= ' e1 '
コミットトラン
2回目の接続で次の文を実行します
トランを始めろ
更新表2
集合D=' d5 '
ここで E= ' e1 '
遅れを待つ 00:00:10
更新表1
集合A= ' aa '
ここで B= ' b2 '
コミットトラン
同時に、システムはデッドロックを検知し、プロセスを中止します
付け加えると:
SQL Server 2000がサポートするテーブルレベルのロックヒント
HOLDLOCKはトランザクション全体が完了するまで共有ロックを保持し、ロックされたオブジェクトが不要になった時点で、SERIALIZABLEトランザクション分離レベルに等しい状態でリリースされます
NOLOCK文は共有ロックを発行せずに実行されるため、READ UNCOMMITDトランザクション隔離レベルに等しいダーティリードが許されます
PAGLOCKは複数のページロックを使用し、1つのテーブルロックを使用します
READPASTは、ロックされた行をスキップしてトランザクションを実行させ、READの未コミットトランザクション隔離レベルでは、ページ、ゾーン、テーブルのロックはスキップせず、RIDロックのみをスキップします
ROWLOCKはrowlockの使用を強制します
TABLOCKXは排他的テーブルレベルのロックの使用を強制し、トランザクション中に他のトランザクションがテーブルを使用できないようにします
UPLOCKは共有ロックなしでテーブルを読み取る際に更新の使用を強制します
アプリロック:
アプリケーションロックとは、SQL Server自体によって生成されるロックではなく、クライアントコードによって生成されるロックのことです
アプリケーションロックの処理を行う2つのプロセス
sp_getapplock Lock アプリケーションリソース
sp_releaseapplock アプリケーションリソースのアンロック
注:データベース内のテーブルをロックすることの違い
SELECT *FROM table WITH( HOLDLOCK ) 他のトランザクションはテーブルを読み取ることができますが、更新や削除はできません
SELECT *FROM table WITH(TABLOCKX) 他のトランザクションはテーブルの読み取り、更新、削除ができません
|
先の：あのc...http://localhost:111/xxx.svc に聞くエンドポイントはなかった。次に：SQLロック NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
