データの並行性の管理
※用語はなるべく、Oracle® Databaseリファレンス 12c リリース1 (12.1) より抜粋しております。
■公式チェックリストより出題範囲
データの並行性の管理
・ロック・メカニズムとOracleによるデータ並行性の管理方法について説明する
・ロック競合を監視および解消する
白本11ｇだと9章が該当する項目だが、9章のDMLを使用したデータ管理、PL/SQLオブジェクトの管理が出題範囲チェックリストには載っていない・・・消えてしまったのだろうか？ Oracle公式の傾向と対策動画の試験から削除されたトピックにもこの2項目はない。
Bronze合格者ならDMLに関しては学習済みなので記事には取り上げない。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
要点
■ロック・メカニズムとOracleによるデータ並行性の管理方法について説明する
■ロック競合を監視および解消する
＠IT記事より
「データの並行性の管理 出題数は多くはありませんが、ロックメカニズムやロック競合の監視、ロック競合の解消が出題されることがあります。DML時に取得されるロックの種類や、どのようなケースでロック待ちになるのか、といったことを一通り軽く確認しておくといいでしょう。」
問題にするにはパターンが決まりきっている所なので仕組みを一連の流れとして覚えてしまえばいいと思う。
PL/SQLオブジェクトに関する問題は出題されたが1問程度だったと思う
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
PL/SQLオブジェクトの管理
■代表的なオブジェクト
・ファンクション ：関数
・プロシージャ ：特定のアクションを実行する場合に使用するPL/SQLプログラム
・パッケージ ：ファンクションとプロシージャをグループ化したもの
・トリガー ：特定の事象が発生した場合に自動的に実行されるPL/SQLオブジェクト
【特に重要なもの】
パッケージ
パッケージは「パッケージ仕様部」「パッケージ本体」の2つのオブジェクトから構成されている
パッケージ仕様部：ファンクション、プロシージャ、および変数の定義が含まれる
パッケージ本体 ：パッケージ仕様部で定義されたサブプログラムの実コードが含まれる
特徴
・パッケージ本体はパッケージ仕様部から分離されているため、パッケージ本体のコードのみを変更・再コンパイルできる
・パッケージ内のサブプログラムをコールする方法はパッケージ仕様部に、コードはパッケージ本体に記載する
・パッケージ本体はパッケージ仕様部がコンパイル済でなければコンパイルできない
・パッケージ本体なしでも、パッケージ仕様部を作成できるが、パッケージ仕様部なしでパッケージ本体を作成することはできない
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
ロック競合の監視および解消
ロックとは、同じデータが複数のセッションで同時に変更されないようにするための仕組みです。
セッションでは、データの変更を実行する前に変更対象のデータが自動的にロックされ、解除されるまで別のトランザクションはロックされたデータを変更できなくなります。
ロックの種類
・排他ロック：関連リソースが共有されないようにするためのロック
・共有ロック：排他ロックを必要とする書込みユーザーの同時アクセスを防ぐためのロック
DMLロック
DML文を実行すると、以下の2つのロックが自動的に設定されます
・変更対象の行に対する、行排他ロック
→行排他ロックは、他のトランザクションによって同時に同じ行に対する変更が行われないようにするために設定されます。
・変更対象の表に対する、表共有ロック
→表共有ロックは、他のトランザクションいよって同時に表を削除、切り捨てされたりして、表全体がロックされないように設定されます。
手動ロック
構文のみ押さえていればいい
LOCK TABLE 表名 IN ロックモード MODE [NOWAIT];
NOWAIT句の指定を省略した場合は、ロックを取得するまで処理が待機する。
指定した場合はエラーになり、ロック解除待ちにならずに済みます。
ロックモード
・ROW SHARE 行レベルの共有ロック
・ROW EXCLUSIVE 行レベルの排他ロック。DML文でも自動的に設定される。
・SHARE 表レベルの共有ロック。索引を作成する場合も自動的に設定される
・SHARE ROW EXCLUSIVE 表レベルの共有ロック、および行レベルの排他ロック。ほとんどのDDL文で自動的に設定される。
－INSERT,UPDATE,MERGE文を実行した時に取得されます
・EXCLUSIVE 表レベルの排他ロック。ほとんどのDDL文で自動的に設定される。
－**ALTER文などの実行した時に取得されます。
【ポイント】ロックモード同士の競合を覚えるのは面倒なので基本的なことだけでも押さえておく
・EXCLUSIVEロックモードが取得されている時は、いずれも共有ロック、排他ロックを取得することはできない。
・行共有ロックが取得されている時は、行,表共有ロックを取得することはできる。ただし表レベルの排他ロックは取得できない。
・表共有ロックが取得されている時は、行,表共有ロックを取得することはできる。ただし、行、表レベルの排他ロックは取得できない。
Tips
SELECT文にFOR UPDATE句を指定すると対象の行に対して排他ロックを取得することが出来る。
指定された文に対しUPDATE文を新たに発行するとロックを検出し、セッションの反応が停止します。
ロック競合
他のトランザクションがロックを取得しているために、実行しているセッションがアイドル状態で待機していること。
ロック競合が起きると、ロック解除待ちのリクエストはキューイングされます。その後、先にロックを取得していたトランザクションが終了すると、ロック解除待ちのリクエストをしていたセッションが自動的にロックを取得します。
【原因】頻出なので必ず覚えておくこと。
・コミットされていないトランザクション
・長時間実行するトランザクション
・必要以上に高いロックレベルの設定
ロック競合の解消
ロック競合を解消するには、ロックが設定されているトランザクションをＣＯＭＭＩＴまたはＲＯＬＬＢＡＣＫして終了させる。
●コマンドによるセッションの強制終了
構文：ブロックしているセッションの検出
SELECT sid, serial#
FROM v$session
WHERE sid IN(SELECT bloking_session FROM v$session);
構文：ブロックしているセッションの強制終了
ALTER SYSTEM KILL SESSION 'sid, serial#';
●v$sessionビューには、接続されているすべてのセッションの詳細が含まれている
●BLOKING_SESSIONの値はブロックしているセッションのID
●SIDがセッションIDと一致している場合に強制終了コマンドを実行する。
デッドロック
デッドロックとは2つ以上のセッションがお互いにロックされているデータのロック解除を待っている状態です。
それぞれのセッションが他のもう1つのデータのロック解除を待っているため、いずれのセッションもトランザクションを終了できず、ロック競合を解消できない。
アラート・ログ・ファイルに記録され、ユーザートレースファイルから詳細を確認することができます。
※赤字の部分が出題される。
Oracleデータベースでは、デッドロックが発生すると自動的にデッドロックが検出され、デッドロックの原因となっている1文がロールバックします。
ロールバックされた文を実行していたセッションにはORA-00060エラーが返され、制御が戻るので、そのトランザクション全体をコミットまたはロールバックすることによって、トランザクションを終了し、ロック競合を解消できます。
※自分でコミット、ロールバック操作を行わなければいけない点に注意
DDL_LOCK_TIMEOUT初期化パラメータ
DDL文がDML文によって取得されているロックの解除を待機する時間を設定する。
デフォルトは0秒なので、解除の待機はしない。