3 データベースの概要
この章では、基本的なデータベースの概要と、トランザクション処理の実行方法について説明します。Oracleデータの変更内容の確定または取消しを制御する方法など、データベースの整合性を維持するための基本的な技術を学習します。
この章のトピックは、次のとおりです:
3.1 データベースへの接続
各項で、CONNECT文の詳細な構文について説明します。構文は次のとおりです。
EXEC SQL CONNECT { :user IDENTIFIED BY :oldpswd | :usr_psw }
[[ AT { dbname | :host_variable }] USING :connect_string ]
[ {ALTER AUTHORIZATION :newpswd | IN { SYSDBA | SYSOPER } MODE} ] ;
データの問合せまたは操作をする前に、Pro*C/C++プログラムをデータベースに接続する必要があります。ログインするには、単にCONNECT文を使用します。
EXEC SQL CONNECT :username IDENTIFIED BY :password ;
usernameおよびpasswordは、charまたはVARCHARホスト変数です。
または、この文は次のようにも指定できます。
EXEC SQL CONNECT :usr_pwd;
ホスト変数usr_pwdには、スラッシュ文字(/)で区切られたユーザー名とパスワードが含まれます。
これらは、単純化したCONNECT文のサブセットです。
CONNECT文は、プログラムが実行する最初のSQL文であることが必要です。つまり、プリコンパイル・ユニット内では、他のSQL文をCONNECT文の前に物理的に置くことはできますが、論理的に置くことはできません。
Oracleのユーザー名とパスワードを別々に入力するには、2つのホスト変数を文字列またはVARCHARとして定義します。(ユーザー名とパスワードの両方を含むユーザー名を入力する場合、必要なホスト変数は1つのみです。)
CONNECTを実行する前に、ユーザー名とパスワードの変数を設定する必要があります。設定しない場合、CONNECTは失敗します。プログラムのプロンプトで値の入力を求めることも、次のように値をハードコードすることもできます。
char *username = "SCOTT"; char *password = "TIGER"; ... EXEC SQL WHENEVER SQLERROR ... EXEC SQL CONNECT :username IDENTIFIED BY :password;
ただし、ユーザー名とパスワードは、CONNECT文にはハードコードできません。また、引用符で囲んだリテラルは使用できません。たとえば、次の2つの文はどちらも無効です。
EXEC SQL CONNECT SCOTT IDENTIFIED BY TIGER; EXEC SQL CONNECT 'SCOTT' IDENTIFIED BY 'TIGER';
ユーザー名およびパスワードをハードコードすることはお薦めしません。
3.1.1 ALTER AUTHORIZATION句を使用したパスワードの変更
Pro*C/C++のクライアント・アプリケーションでは、EXEC SQL CONNECT文を拡張し、実行時にユーザーのパスワードを変更できます。
この項では、様々なALTER AUTHORIZATION句の実行結果について説明します。
3.1.1.1 標準CONNECT
アプリケーションで次の文が発行されるとします。
EXEC SQL CONNECT ..; /* No ALTER AUTHORIZATION clause */
通常の接続が実行されます。予想される結果は次のとおりです。
-
アプリケーションが問題なく接続されます。
-
アプリケーションは接続できるが、パスワードに関する警告を受ける。この警告は、パスワードの期限は切れているが、まだログインできる猶予期間であることを示しています。この時点で、ユーザーは、アカウントがロックされる前にパスワードを変更するように求められます。
-
アプリケーションが接続に失敗します。次の原因が考えられます。
-
パスワードが間違っています。
-
アカウントが期限切れになっているか、またはロック状態です。
-
3.1.1.2 CONNECT文でのパスワードの変更
次のCONNECT文があるとします。
EXEC SQL CONNECT .. ALTER AUTHORIZATION :newpswd;
この文は、アプリケーションがアカウントのパスワードをnewpswdで指定した値に変更することを示します。変更後は、user/newpswdで接続試行が実行されます。次の結果が予想されます。
-
アプリケーションは問題なく接続できる。
-
アプリケーションが接続に失敗します。次のどちらかの原因が考えられます。
-
なんらかの理由でパスワードを認識できませんでした。パスワードは元のままです。
-
アカウントがロックされています。パスワードは変更できません。
-
3.1.2 Oracle Net Servicesを使用した接続
Oracle Net Servicesのドライバを使用して接続するには、tnsnames.ora構成ファイルまたはOracle Namesで定義されているサービス名を使用します。
Oracle Namesを使用する場合、ネーム・サーバーは、ネットワーク定義データベースからサービス名を取得します。
Oracle Net Servicesの詳細は、『Oracle Net Services管理者ガイド』を参照してください。
3.1.3 自動接続
CLUSTER$username
usernameは現行のオペレーティング・システムのユーザー名、CLUSTER$usernameは有効なOracleデータベース・ユーザー名です。(CLUSTER$の実際の値は、INIT.ORAパラメータ・ファイルに定義されています。)Pro*C/C++プリコンパイラに渡すのはスラッシュ文字のみです。次に例を示します。
... char *oracleid = "/"; ... EXEC SQL CONNECT :oracleid;
これにより、ユーザーCLUSTER$usernameで自動的に接続されます。たとえば、オペレーティング・システムのユーザー名がRHILLで、CLUSTER$RHILLが有効なOracleユーザー名の場合は、/を使用した接続により、ユーザーCLUSTER$RHILLでOracleに自動的にログインできます。
また、プリコンパイラには文字列内で/を渡すこともできます。ただし、その文字列の後続にブランクを入れないでください。たとえば、次のCONNECT文は失敗します。
... char oracleid[10] = "/ "; ... EXEC SQL CONNECT :oracleid;
3.1.3.1 AUTO_CONNECTプリコンパイラ・オプション
AUTO_CONNECT=YESで、最初の実行SQL文を処理するときにアプリケーションがまだデータベースに接続されていない場合、次のユーザーIDを使用して接続が試行されます。
CLUSTER$<username>
usernameは現行のオペレーティング・システムのユーザー名またはタスク名、CLUSTER$usernameは有効なOracleユーザーIDです。AUTO_CONNECTのデフォルト値はNOです。
AUTO_CONNECT=NOの場合、Oracleに接続するにはプログラムでCONNECT文を使用する必要があります。
3.1.3.2 SYSDBAまたはSYSOPERシステム権限
SYSDBAまたはSYSOPERシステム権限でログインするには、次のオプション文字列を他のすべての句の後に追加します。
[IN { SYSDBA | SYSOPER } MODE]
次に例を示します。
EXEC SQL CONNECT ... IN SYSDBA MODE ;
このオプションには次の制限があります。
-
プリコンパイラのAUTO_CONNECT=YESオプション設定を使用する場合、このオプションは使用できません。
-
CONNECT文にALTER AUTHORIZATIONキーワードを使用している場合、このオプションは使用できません。
3.2 高度な接続オプション
この項では、高度な接続で使用できるオプションについて説明します。
3.2.1 予備知識
ネットワーク上の通信ポイントは、ノードと呼ばれます。Oracle Netでは、ネットワーク上のノード間で情報(SQL文、データおよびステータス・コード)を送信できます。
プロトコルは、ネットワークへのアクセスに関する一連の規則です。この規則では、障害後のリカバリ手順、データの転送およびエラー検査のフォーマットなどが規定されます。
ローカル・ドメイン内のデフォルトのデータベースに接続するためにOracle Netの構文で使用するのは、そのデータベースのサービス名のみです。
サービス名がデフォルト(ローカル)・ドメイン内にない場合は、グローバル指定(すべてのドメインの指定)を使用する必要があります。次に例を示します。
HR.XX.ORACLE.COM
3.2.2 同時ログイン
Pro*C/C++では、Oracle Net経由で分散処理がサポートされます。アプリケーションは、ローカル・データベースおよびリモート・データベースの任意の組合せに同時にアクセスしたり、同じデータベースへの複数の接続を確立できます。図3-1では、アプリケーション・プログラムはOracleの1つのローカル・データベースおよび3つのリモート・データベースと接続しています。ORA2、ORA3およびORA4は、CONNECT文で使用される論理名です。
Oracle Netは、ネットワーク上の異なるマシン間およびオペレーティング・システム間に存在する境界を排除することによって、Oracleのツール製品に分散処理環境を提供します。この項では、Pro*C/C++によりOracle Net経由で分散処理がサポートされる方法について説明します。アプリケーションから可能な操作は、次のとおりです。
-
他のデータベースへの直接または間接アクセス
-
ローカルおよびリモート・データベースの任意の組合せへの同時アクセス
-
同一のデータベースへの複数接続
Oracle Netのインストール方法および使用可能なデータベースの識別方法は、データベースの識別とアクセスおよびシステム固有のOracleドキュメントを参照してください。
3.2.3 デフォルトのデータベースおよび接続
各ノードにはデフォルトのデータベースがあります。CONNECT文でデータベース名のみを指定し、ドメインを指定しない場合、指定したローカル・ノードまたはリモート・ノード上のデフォルトのデータベースに接続されます。
デフォルトの接続は、AT句のないCONNECT文によって行われます。ローカルまたはリモートの任意のノード上のデフォルトまたは非デフォルトの任意のデータベースに接続できます。AT句のないSQL文は、デフォルトの接続に対して実行されます。逆に、非デフォルトの接続は、AT句があるCONNECT文により行われます。AT句を持つSQL文は、非デフォルトの接続に対して実行されます。
データベース名は一意にする必要がありますが、2つ以上のデータベース名で同じ接続を指定できます。したがって、任意のノード上のデータベースに対して複数の接続を確立できます。
3.2.4 明示的接続
通常は、Oracleへの接続を次のように確立します。
EXEC SQL CONNECT :username IDENTIFIED BY :password;
また、次の文も使用できます。
EXEC SQL CONNECT :usr_pwd;
usr_pwdには、username/passwordが含まれます。
次のユーザーIDを使用してOracleに自動的に接続できます。
CLUSTER$username
usernameは現行のオペレーティング・システムのユーザー名またはタスク名、CLUSTER$usernameは有効なOracleユーザーIDです。プリコンパイラに渡すのはスラッシュ文字(/)のみです。次に例を示します。
char oracleid = '/'; ... EXEC SQL CONNECT :oracleid;
これにより、ユーザーCLUSTER$usernameで自動的に接続されます。
データベースおよびノードを指定しない場合、現在のノード上のデフォルトのデータベースに接続されます。別のデータベースに接続する場合は、そのデータベースを明示的に指定する必要があります。
明示的接続では、SQL文で参照される接続名を指定して、別のデータベースに直接接続します。同時に複数のデータベースに接続することも、同じデータベースに複数回接続することもできます。
3.2.4.1 単一の明示的接続
次の例では、リモート・ノードにある単一の非デフォルトのデータベースに接続します。
/* declare needed host variables */ char username[10] = "scott"; char password[10] = "tiger"; char db_string[20] = "NYNON"; /* give the database connection a unique name */ EXEC SQL DECLARE DB_NAME DATABASE; /* connect to the nondefault database */ EXEC SQL CONNECT :username IDENTIFIED BY :password AT DB_NAME USING :db_string;
この例の識別子は、次の目的で使用されています。
-
ホスト変数usernameおよびpasswordは、有効なユーザーを識別します。
-
ホスト変数db_stringには、リモート・ノードにある非デフォルトのデータベースに接続するためのOracle Net構文が含まれています。
-
未宣言の識別子DB_NAMEは、非デフォルト接続の名前を指定します。これは、Oracleで使用される識別子で、ホスト変数でもプログラム変数でもありません。
USING句では、DB_NAMEに対応付けるネットワーク、マシンおよびデータベースを指定します。その後、AT句(DB_NAME付き)を使用しているSQL文は、db_stringに指定したデータベースで実行されます。
もう1つの方法として、次の例に示すように、AT句で文字ホスト変数を使用できます。
/* declare needed host variables */ char username[10] = "scott"; char password[10] = "tiger"; char db_name[10] = "oracle1"; char db_string[20] = "NYNON"; /* connect to the nondefault database using db_name */ EXEC SQL CONNECT :username IDENTIFIED BY :password AT :db_name USING :db_string; ...
db_nameがホスト変数の場合、DECLARE DATABASE文は不要です。DB_NAMEが未宣言の識別子の場合にのみ、CONNECT ... AT DB_NAME文を実行する前にDECLARE DB_NAME DATABASE文を実行する必要があります。
3.2.4.1.1 SQL操作
権限を付与されている場合は、非デフォルトの接続で任意のSQL DML文を実行できます。たとえば、次のように入力します。
EXEC SQL AT DB_NAME SELECT ... EXEC SQL AT DB_NAME INSERT ... EXEC SQL AT DB_NAME UPDATE ...
次の例では、db_nameはホスト変数です。
EXEC SQL AT :db_name DELETE ...
db_nameがホスト変数の場合、SQL文で参照されるすべてのデータベース表を、DECLARE TABLE文で定義する必要があります。定義しないと、プリコンパイラで警告が発行されます。
3.2.4.1.3 カーソルの制御
OPEN、FETCHおよびCLOSEなどのカーソルの制御文は例外で、AT句は使用しません。カーソルを明示的に識別されたデータベースに対応付ける場合は、次のようにDECLARE CURSOR文でAT句を使用します。
EXEC SQL AT :db_name DECLARE emp_cursor CURSOR FOR ... EXEC SQL OPEN emp_cursor ... EXEC SQL FETCH emp_cursor ... EXEC SQL CLOSE emp_cursor;
db_nameがホスト変数の場合は、DECLAREされたカーソルを参照するすべてのSQL文の適用範囲内で宣言する必要があります。たとえば、あるサブプログラム内でカーソルをOPENし、別のサブプログラムでそのカーソルからFETCHする場合は、db_nameをグローバルに宣言する必要があります。
カーソルからOPEN、CLOSEおよびFETCHを実行する場合、AT句を使用しません。SQL文は、DECLARE CURSOR文のAT句で名前を付けられたデータベースか、カーソルの宣言でAT句が使用されていない場合はデフォルトのデータベースにおいて実行されます。
AT :host_variable句を使用すると、カーソルに対応付けられた接続を変更できます。ただし、カーソルがオープンされているときは対応付けを変更できません。次の例を考えてみます。
EXEC SQL AT :db_name DECLARE emp_cursor CURSOR FOR ... strcpy(db_name, "oracle1"); EXEC SQL OPEN emp_cursor; EXEC SQL FETCH emp_cursor INTO ... strcpy(db_name, "oracle2"); EXEC SQL OPEN emp_cursor; /* illegal, cursor still open */ EXEC SQL FETCH emp_cursor INTO ...
この例は、2番目のOPEN文を実行するときにemp_cursorがまだオープンされているため、無効となります。異なる接続に対して別々のカーソルが維持されることはありません。emp_cursorは1つのみ存在できます。別の接続のために再オープンするには、その前にクローズする必要があります。最後の例をデバッグするには、次のように、単にカーソルをクローズしてから再オープンします。
... EXEC SQL CLOSE emp_cursor; -- close cursor first strcpy(db_name, "oracle2"); EXEC SQL OPEN emp_cursor; EXEC SQL FETCH emp_cursor INTO ...
3.2.4.1.4 動的SQL
動的SQL文は、文中ではAT句が使用されないカーソル制御文に類似しています。
動的SQL方法1では、非デフォルトの接続で文を実行する場合は、AT句を使用する必要があります。次に例を示します。
EXEC SQL AT :db_name EXECUTE IMMEDIATE :sql_stmt;
方法2、3および4で非デフォルトの接続で文を実行する場合は、DECLARE STATEMENT文でのみAT句を使用します。PREPARE、DESCRIBE、OPEN、FETCHおよびCLOSEなど、その他の動的SQL文はAT句を使用しません。次の例に方法2を示します。
EXEC SQL AT :db_name DECLARE sql_stmt STATEMENT; EXEC SQL PREPARE sql_stmt FROM :sql_string; EXEC SQL EXECUTE sql_stmt;
次の例は方法3を示します。
EXEC SQL AT :db_name DECLARE sql_stmt STATEMENT; EXEC SQL PREPARE sql_stmt FROM :sql_string; EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; EXEC SQL OPEN emp_cursor ... EXEC SQL FETCH emp_cursor INTO ... EXEC SQL CLOSE emp_cursor;
3.2.4.2 複数の明示的接続
単一の明示的接続の場合と同様に、複数の明示的接続にはAT db_name句を使用できます。次の例では、2つの非デフォルトのデータベースに同時に接続しています。
/* declare needed host variables */ char username[10] = "scott"; char password[10] = "tiger"; char db_string1[20] = "NYNON1"; char db_string2[20] = "CHINON"; ... /* give each database connection a unique name */ EXEC SQL DECLARE DB_NAME1 DATABASE; EXEC SQL DECLARE DB_NAME2 DATABASE; /* connect to the two nondefault databases */ EXEC SQL CONNECT :username IDENTIFIED BY :password AT DB_NAME1 USING :db_string1; EXEC SQL CONNECT :username IDENTIFIED BY :password AT DB_NAME2 USING :db_string2;
識別子DB_NAME1およびDB_NAME2を宣言し、2つの非デフォルト・ノードのデフォルトのデータベースの名前を指定します。これにより、SQL文ではデータベースを名前で参照できます。
または、次の例のように、AT句でホスト変数を使用できます。
/* declare needed host variables */
char username[10] = "scott";
char password[10] = "tiger";
char db_name[20];
char db_string[20];
int n_defs = 3; /* number of connections to make */
...
for (i = 0; i < n_defs; i++)
{
/* get next database name and OracleNet string */
printf("Database name: ");
gets(db_name);
printf("OracleNet) string: ");
gets(db_string);
/* do the connect */
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT :db_name USING :db_string;
}
この方法を使用すれば、次の例のように、同じデータベースに複数の接続を行うこともできます。
strcpy(db_string, "NYNON");
for (i = 0; i < ndefs; i++)
{
/* connect to the nondefault database */
printf("Database name: ");
gets(db_name);
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT :db_name USING :db_string;
}
...
複数の接続に同じOracle Net文字列を使用する場合も、接続ごとに異なるデータベース名を使用する必要があります。ただし、データベース名ではデフォルトおよび非デフォルトのデータベースの両方が識別されるため、1つのデータベース名で同じデータベースに2回接続できます。
3.2.4.3 データの整合性の確認
アプリケーション・プログラムでは、複数のリモート・データベースにあるデータを操作するトランザクションの整合性を確認する必要があります。つまり、プログラムはトランザクションのすべてのSQL文をコミットまたはロールバックする必要があります。これは、ネットワーク障害が発生した場合や、システムの1つがクラッシュした場合は不可能です。
たとえば、2つの会計データベースで作業しているとします。一方のデータベースで勘定の借方に記帳し、他方のデータベースで勘定の貸方に記帳してから、それぞれのデータベースでCOMMITを発行します。両方のトランザクションがコミットまたはロールバックされたかどうかは、プログラム側で確認する必要があります。
3.2.5 暗黙的接続
暗黙的接続は、Oracleの分散問合せ機能を通じてサポートされます。この機能には明示的接続は不要ですが、サポートされるのはSELECT文のみです。分散問合せを使用すると、単一のSELECT文で1つ以上の非デフォルトのデータベースにあるデータにアクセスできます。
分散問合せ機能はデータベース・リンクに依存しており、リンクにより接続事態ではなく、CONNECT文に名前が割り当てられます。 実行時には、指定したOracleサーバーにより埋込みSELECT文が実行され、非デフォルトのデータベースに暗黙的に接続されて、必要なデータが取得されます。
3.2.5.1 単一の暗黙的接続
次の例では、1つの非デフォルト・データベースに接続します。最初に、プログラムでは次の文が実行され、データベース・リンクが定義されます(通常、データベース・リンクは、DBAまたはユーザーが対話形式で確立します)。
EXEC SQL CREATE DATABASE LINK db_link CONNECT TO username IDENTIFIED BY password USING 'NYNON';
プログラムでは、次のようにデータベース・リンクを使用して非デフォルトのEMP表を問い合せできます。
EXEC SQL SELECT ENAME, JOB INTO :emp_name, :job_title FROM emp@db_link WHERE DEPTNO = :dept_number;
データベース・リンクは、埋込みSQL文のAT句に使用されるデータベース名とは無関係です。単に、非デフォルトのデータベースの位置、データベースへのパス、使用するOracleユーザー名およびパスワードをOracleに対して指示します。データベース・リンクは、明示的に削除されるまで、データベース・ディクショナリに格納されます。
前述の例で、デフォルトのOracleサーバーは、データベース・リンクdb_linkを使用して、Oracle Net経由で非デフォルトのデータベースにログインします。問合せはデフォルトのサーバーに送られますが、非デフォルトのデータベースに転送されて実行されます。
データベース・リンクを簡単に参照できるように、次のようにシノニムを対話形式で作成できます。
EXEC SQL CREATE SYNONYM emp FOR emp@db_link;
その結果、プログラムで、次のように非デフォルトのEMP表に問合せができるようになります。
EXEC SQL SELECT ENAME, JOB INTO :emp_name, :job_title FROM emp WHERE DEPTNO = :dept_number;
3.2.5.2 複数の暗黙的接続
次の例では、2つの非デフォルト・データベースに同時に接続します。まず次の一連の文を実行し、2つのデータベース・リンクを定義して2つのシノニムを作成します。
EXEC SQL CREATE DATABASE LINK db_link1 CONNECT TO username1 IDENTIFIED BY password1 USING 'NYNON'; EXEC SQL CREATE DATABASE LINK db_link2 CONNECT TO username2 IDENTIFIED BY password2 USING 'CHINON'; EXEC SQL CREATE SYNONYM emp FOR emp@db_link1; EXEC SQL CREATE SYNONYM dept FOR dept@db_link2;
その結果、プログラムで、次のように非デフォルトのEMP表とDEPT表に問合せができるようになります。
EXEC SQL SELECT ENAME, JOB, SAL, LOC FROM emp, dept WHERE emp.DEPTNO = dept.DEPTNO AND DEPTNO = :dept_number;
Oracleでは、db_link1にある非デフォルトのEMP表とdb_link2にある非デフォルトのDEPT表を結合することにより、問合せが実行されます。
3.3 トランザクション用語の定義
トランザクションの説明に入る前に、この項で定義されている用語に慣れる必要があります。
Oracleが管理するジョブまたはタスクは、セッションと呼ばれます。アプリケーション・プログラムまたはSQL*Formsなどのツールを実行してデータベースに接続すると、ユーザー・セッションが開始されます。
Oracleでは、ユーザー・セッションを同時に機能させ、コンピュータ・リソースを共有できます。そのために、Oracleは同時実行性、つまり多数のユーザーによる同一データへのアクセスを制御する必要があります。同時実行性の制御が十分でないと、データの整合性が失われる可能性があります。つまり、データまたは構造への変更が誤った順序で行われるおそれがあります。
Oracleでは、ロック(エンキューとも呼ばれます)を使用してデータへの同時アクセスを制御します。ロックにより、データの表や行などのデータベース・リソースのユーザーに一時的な所有権が与えられます。そのため、ロックを使用しているユーザーが変更を終了するまで、他のユーザーはデータを変更できません。
デフォルトのロック機能がOracleのデータおよび構造を保護するため、明示的にリソースをロックする必要はありません。ただし、デフォルトのロックをオーバーライドした方が有利な場合は、表または行に対するデータ・ロックを要求できます。行の共有や排他など、数種類のロック・モードから選択できます。
複数のユーザーが同じデータベース・オブジェクトへのアクセスを試みると、デッドロックが発生する可能性があります。たとえば、同じ表を更新するユーザーが2人いる場合、それぞれ相手が現在ロックしている行の更新を試みると待機状態になります。それぞれのユーザーが、相手が使用中のリソースを待つことになるため、Oracleによりデッドロックが解除されるまで、どちらも処理を続行できません。Oracleでは、最低作業量を完了した関連するトランザクションにエラー信号が送られ、「リソース待機の間にデッドロックが検出されました。」というOracleエラー・コードがSQLCAのsqlcodeに戻されます。
1人のユーザーによって問合せが行われている表を、同時に別のユーザーが更新すると、Oracleでは問合せ用の表データの読取り一貫性ビューが生成されます。つまり、ある問合せが開始され、進行していく間、その問合せによって読み込まれたデータは変更されません。更新アクティビティが続行している間、Oracleでは、表データのスナップショットを取り、変更内容をロールバック・セグメントに記録します。Oracleでは、ロールバック・セグメント内の情報に基づいて、読取り一貫性のある問合せ結果が作成され、必要に応じて変更内容が取り消されます。
3.4 トランザクションがデータベースを保護する方法
Oracleはトランザクション指向です。つまり、トランザクションを使用してデータの整合性が確保されます。トランザクションとは、あるタスクを完了するために定義する1つ以上の論理的に関連付けられたSQL文です。Oracleでは、一連のSQL文を一単位として扱い、それらの文によって実行されたすべての変更が、同時にコミット(確定)されるか、ロールバック(取消し)されます。トランザクションの途中でアプリケーション・プログラムに障害が発生すると、データベースは自動的にトランザクション前の状態にリストアされます。
次項では、トランザクションの設計および制御方法について説明します。特に、次の操作方法を学習します。
-
データベースへの接続。
-
同時接続
-
トランザクションの開始および終了
-
COMMIT文を使用したトランザクションの確定
-
ROLLBACK TO文とともにSAVEPOINT文を使用したトランザクションの部分的な取消し
-
ROLLBACK文を使用したトランザクション全体の取消し
-
RELEASEオプションの指定によるリソースの解放とデータベースのログオフ
-
SET TRANSACTION文を使用した読取り専用トランザクションの設定
-
FOR UPDATE句またはLOCK TABLE文を使用したデフォルト・ロックの上書き
この章で説明するSQL文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
3.5 トランザクションの開始および終了方法
プログラムの最初の実行SQL文(CONNECT以外)によりトランザクションを開始します。1つのトランザクションが終了すると、次の実行SQL文により別のトランザクションが自動的に開始します。このように、すべての実行文はトランザクションの一部です。宣言SQL文はロールバックされません。また、コミットする必要もないため、トランザクションの一部とみなされません。
-
COMMITまたはROLLBACK文を記述します。RELEASEオプションは、付けても付けなくてもかまいません。これにより、データベースへの変更を明示的に確定または取り消します。
-
実行の前と後に自動コミットを発行するデータ定義文(ALTER、CREATEまたはGRANTなど)を記述します。これにより、データベースへの変更を暗黙的に確定します。
システム障害が発生した場合や、ソフトウェアの問題、ハードウェアの問題または強制割込みなどが原因で、予期しないユーザー・セッション停止が発生した場合にも、トランザクションは終了します。そのトランザクションはOracleによりロールバックされます。
トランザクションの途中でプログラムに障害が発生すると、Oracleは発生したエラーを検出し、そのトランザクションをロールバックします。オペレーティング・システムに障害が発生すると、データベースがトランザクション前の状態にリストアされます。
3.6 COMMIT文の使用
プログラムをCOMMIT文またはROLLBACK文で分割しなければ、Oracleではそのプログラム全体が1つのトランザクションとみなされます(ただし、そのプログラムにデータ定義文が含まれている場合は、自動COMMITが発行されます)。
COMMIT文を使用すると、データベースへの変更を確定できます。変更をCOMMITするまで、他のユーザーは変更されたデータにアクセスできず、トランザクションの開始前の状態のデータが表示されます。特に、COMMIT文では次の処理が実行されます。
-
現行のトランザクション中にデータベースに対して行った変更をすべて確定します。
-
これらの変更を他のユーザーが参照できるようにします。
-
すべてのセーブポイントを消去します(次の項を参照)。
-
解析ロック以外の行および表のロックをすべて解除します。
-
CURRENT OF句で参照されているカーソルをクローズします。MODE=ANSIの場合は、COMMIT文に指定されている接続の明示カーソルをすべてクローズします。
-
トランザクションを終了します。
COMMIT文は、ホスト変数の値にも、プログラムの制御フローにも影響しません。
MODE=ORACLEの場合、CURRENT OF句で参照されていない明示カーソルは、COMMITの前後もオープンしたままです。これによってパフォーマンスが向上します。
これらの処理は通常の処理の一部分であるため、COMMIT文はプログラムのメイン・パスにインラインで設定する必要があります。プログラムの終了前に、保留中の変更を明示的にCOMMITする必要があります。コミットしない場合、保留中の変更はロールバックされます。次の例では、トランザクションをコミットしてOracleから切断します。
EXEC SQL COMMIT WORK RELEASE;
オプションのキーワードWORKには、ANSI互換性があります。RELEASEオプションを指定すると、プログラムで使用されているOracleリソース(ロックとカーソル)がすべて解放され、データベースからログオフされます。
データ定義文では、実行の前後に自動COMMITが発行されるため、データ定義文の後にCOMMIT文を記述する必要はありません。したがってデータ定義文が正常終了しても異常終了しても、その前のトランザクションがコミットされます。
関連項目
3.6.1 DECLARE CURSOR文でのWITH HOLD句の使用
CURSORの後にWITH HOLD句を付けて宣言されているカーソルは、COMMIT後もオープン状態となります。この句の使用方法は、次の例のとおりです。
EXEC SQL
DECLARE C1 CURSOR WITH HOLD
FOR SELECT ENAME FROM EMP
WHERE EMPNO BETWEEN 7600 AND 7700
END-EXEC.
UPDATEの場合は、カーソルを宣言しないでください。DB2では、デフォルト(コミット時に全カーソルをクローズする)を変更するためにWITH HOLD句が使用されます。Pro*COBOLでは、DB2からOracleへのアプリケーションの移行を簡単に行えるようにするために、この句が用意されています。MODE=ANSIと指定されているとき、OracleではDB2のデフォルトが使用されますが、ホスト変数はすべて宣言部で宣言する必要があります。宣言部を省略するには、次の項で説明するプリコンパイラ・オプションCLOSE_ON_COMMITを使用します。
3.6.2 CLOSE_ON_COMMITプリコンパイラ・オプション
プリコンパイラ・オプションCLOSE_ON_COMMITを使用すると、MODE=ANSIのデフォルト動作をオーバーライドできます(コマンドラインにMODE=ANSIを指定した場合、WITH HOLD句で宣言されていないカーソルはコミット時にクローズされます)。
CLOSE_ON_COMMIT = {YES | NO}
デフォルトはNOです。このオプションは、コマンドラインまたは構成ファイルで入力する必要があります。
注意:
このオプションは注意して使用してください。カーソルのオープン/クローズを何度も行うと、各OPEN文で再解析を行う必要があるためアプリケーションの処理速度が低下する可能性があります。
3.7 SAVEPOINT文の使用
SAVEPOINT文を使用すると、トランザクション処理中にカレント・ポイントにマークを付けて名前を指定できます。マークを設定したそれぞれの点をセーブポイントと呼びます。たとえば、次の文によりstart_deleteというセーブポイントを設定します。
EXEC SQL SAVEPOINT start_delete;
セーブポイントによってロング・トランザクションを分割できるため、より複雑なプロシージャを制御できるようになります。たとえば、単一のトランザクションが複数のファンクションを実行しているときに、それぞれのファンクションの前にセーブポイントを設定できます。これにより、ある関数が失敗した場合も、Oracleデータを前の状態に簡単にリストアし、リカバリしてから、その関数を再実行できます。
トランザクションの一部を取り消すには、ROLLBACK文とそのTO SAVEPOINT句を使用してセーブポイントを指定します。次の例は、MAIL_LIST表にアクセスして、新しいリストの挿入、古いリストの更新、(少数の)使用されていないリストの削除を行います。削除後に、SQLCA内のsqlerrdの3番目の要素で、削除された行数を調べます。削除された行数が予想以上に多い場合は、セーブポイントstart_deleteまでロールバックして、その削除のみを取り消します。
...
for (;;)
{
printf("Customer number? ");
gets(temp);
cust_number = atoi(temp);
printf("Customer name? ");
gets(cust_name);
EXEC SQL INSERT INTO mail_list (custno, cname, stat)
VALUES (:cust_number, :cust_name, 'ACTIVE');
...
}
for (;;)
{
printf("Customer number? ");
gets(temp);
cust_number = atoi(temp);
printf("New status? ");
gets(new_status);
EXEC SQL UPDATE mail_list
SET stat = :new_status
WHERE custno = :cust_number;
}
/* mark savepoint */
EXEC SQL SAVEPOINT start_delete;
EXEC SQL DELETE FROM mail_list
WHERE stat = 'INACTIVE';
if (sqlca.sqlerrd[2] < 25) /* check number of rows deleted */
printf("Number of rows deleted is %d\n", sqlca.sqlerrd[2]);
else
{
printf("Undoing deletion of %d rows\n", sqlca.sqlerrd[2]);
EXEC SQL WHENEVER SQLERROR GOTO sql_error;
EXEC SQL ROLLBACK TO SAVEPOINT start_delete;
}
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
sql_error:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("Processing error\n");
exit(1);
あるセーブポイントまでロールバックすると、そのセーブポイント以降のすべてのセーブポイントが消去されます。ただし、ロールバックしたセーブポイントはそのまま残ります。たとえば、セーブポイントを5つマークし、3番目のセーブポイントまでロールバックすると、4番目と5番目のセーブポイントのみが消去されます。
2つのセーブポイントに同じ名前を付けると、最初のセーブポイントが消去されます。COMMIT文またはROLLBACK文では、すべてのセーブポイントが消去されます。
3.8 ROLLBACK文
ROLLBACK文を使用すると、保留状態のデータベースへの変更を取り消せます。たとえば表から行を誤って削除したときなどは、ROLLBACK文を使用して元のデータをリストアできます。TO SAVEPOINT句を使用すると、現行のトランザクションの途中の文までロールバックできます。したがって、変更をすべて取り消す必要はありません。
未完成のトランザクションを開始した場合(たとえば、SQL文が正常に実行されないなど)、ROLLBACKを使用すると起点まで戻ることができるため、データベースの整合性が維持されます。特に、ROLLBACK文では次の処理が実行されます。
-
カレント・トランザクションで実行されたデータベースの変更を取り消します。
-
すべてのセーブポイントを消去します。
-
トランザクションを終了します。
-
解析ロック以外の行および表のロックをすべて解除します。
-
CURRENT OF句で参照されているカーソルをクローズします。MODE=ANSIの場合は、すべての明示カーソルをクローズします。
ROLLBACK文は、ホスト変数の値やプログラム内の制御の流れには影響を与えません。
MODE=ORACLEの場合、CURRENT OF句で参照されていない明示カーソルは、ROLLBACKの前後もオープンしたままです。
特に、ROLLBACK TO SAVEPOINT文では次の処理が実行されます。
-
指定したセーブポイントがマークされた以降のデータベースへの変更を取り消します。
-
指定したセーブポイント以降のセーブポイントをすべて消去します。
-
指定したセーブポイントがマークされた以降に取得された行および表のロックをすべて解除します。
ROLLBACK文は例外処理の一部になっているため、プログラムのメイン・パスではなくエラー処理ルーチン内に指定する必要があります。次の例では、トランザクションをロールバックして、Oracleとの接続を切断します。
EXEC SQL ROLLBACK WORK RELEASE;
オプションのキーワードWORKには、ANSI互換性があります。RELEASEオプションを指定すると、プログラムで使用されているリソースがすべて解放され、データベースから切断されます。
WHENEVER SQLERROR GOTO文からROLLBACK文が記述されているエラー処理ルーチンに分岐したときに、ROLLBACKでエラーが発生すると、プログラムが無限にループする可能性があります。無限ループを回避するには、次に示すようにROLLBACK文の前にWHENEVER SQLERROR CONTINUEを記述します。
EXEC SQL WHENEVER SQLERROR GOTO sql_error;
for (;;)
{
printf("Employee number? ");
gets(temp);
emp_number = atoi(temp);
printf("Employee name? ");
gets(emp_name);
EXEC SQL INSERT INTO emp (empno, ename)
VALUES (:emp_number, :emp_name);
...
}
...
sql_error:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("Processing error\n");
exit(1);
プログラムが異常終了すると、Oracleによりトランザクションが自動的にロールバックされます。
関連項目
3.8.1 文レベルのロールバック
Oracleは、SQL文を実行する前に、暗黙的なセーブポイント(ユーザーは操作できません)を設定します。したがって、この文が失敗すると、自動的にロールバックされ、SQLCA内のsqlcodeに適切なエラー・コードが戻されます。たとえば、INSERT文が一意の索引内に同じ値を挿入しようとしたためエラーが発生すると、この文はロールバックの対象になります。
Oracleは、デッドロックを解除するために単一のSQL文をロールバックすることもあります。Oracleは関係しているトランザクションの1つにエラーを通知し、そのトランザクション中の現在の文をロールバックします。
失われるのは失敗したSQL文で開始された作業のみです。つまり、現行のトランザクション内でこの文より前に行われた作業は保存されます。したがって、データ定義文が失敗しても、それ以前の自動コミットは取り消されません。
SQL文は実行前に必ず解析され、構文規則に従っているか、有効なデータベース・オブジェクトを参照しているかが検証されます。SQL文の実行中にエラーが検出されると、ロールバックが発生しますが、解析中にエラーが検出されても、ロールバックは発生しません。
3.9 RELEASEオプション
プログラムが異常終了すると、Oracleにより変更が自動的にロールバックされます。異常終了が発生するのは、プログラムが作業を明示的にコミットもロールバックもせずに、RELEASEオプションを使用してOracleから切断する場合です。正常終了が発生するのは、プログラムが正常に実行され、オープン状態のカーソルがクローズされ、作業が明示的にコミットまたはロールバックされ、Oracleから切断され、制御がユーザーに戻された場合です。
最後に実行されるSQL文が次のどちらかの場合、プログラムは正常終了します。
EXEC SQL COMMIT WORK RELEASE;
または
EXEC SQL ROLLBACK WORK RELEASE;
トークンWORKはオプションです。最後のSQL文が上のどちらでもない場合は、そのユーザー・セッションで取得したロックおよびカーソルはプログラムの終了後も解放されず、ユーザー・セッションがアクティブでなくなったことをOracleが認識するまで保持されます。この結果、マルチユーザー環境では、他のユーザーはロックされたリソースへのアクセスを必要以上に長く待たされる場合があります。
3.10 SET TRANSACTION文
SET TRANSACTION文を使用すると、読取り専用トランザクションを開始できます。読取り専用トランザクションでは反復可能読取りが行えるため、他のユーザーが更新中の1つ以上の表に対して、複数の問合せを実行する場合に便利です。次にSET TRANSACTION文の例を示します。
EXEC SQL SET TRANSACTION READ ONLY;
SET TRANSACTION文は、読取り専用トランザクションの最初のSQL文である必要があり、1つのトランザクションで1回しか使用できません。READ ONLYパラメータは必須です。これを使用しても、他のトランザクションには影響がありません。
読取り専用トランザクションに使用できるのは、SELECT文、COMMIT文およびROLLBACK文のみです。たとえば、INSERT文、DELETE文またはSELECT FOR UPDATE OF文を使用するとエラーが発生します。
読取り専用トランザクション中、複数の表と複数の問合せで構成された読取り一貫性ビューが作成され、すべての問合せがデータベースの同じスナップショットを参照します。他のユーザーは、通常の方法でデータの問合せや更新ができます。
読取り専用トランザクションは、COMMIT文またはROLLBACK文、データ定義文によって終了します。(データ定義文では暗黙的COMMITが発行されることを思い出してください。)
次の例では、店の管理者が読取り専用トランザクションを使用して、当日、先週および先月の売上を調べ、要約レポートを生成しています。このレポートは、このトランザクションの実行中にデータベースを更新する他のユーザーによる影響を受けません。
EXEC SQL SET TRANSACTION READ ONLY; EXEC SQL SELECT sum(saleamt) INTO :daily FROM sales WHERE saledate = SYSDATE; EXEC SQL SELECT sum(saleamt) INTO :weekly FROM sales WHERE saledate > SYSDATE - 7; EXEC SQL SELECT sum(saleamt) INTO :monthly FROM sales WHERE saledate > SYSDATE - 30; EXEC SQL COMMIT WORK; /* simply ends the transaction since there are no changes to make permanent */ /* format and print report */
3.11 デフォルト・ロックの上書き
デフォルトでは、多数のデータ構造がOracleにより自動的にロックされます。ただし、デフォルトのロックをオーバーライドして、別のロックを有効にする場合は、行または表を特定して、そこにデータ・ロックを要求できます。明示的なロックにより、トランザクション中に表に対するアクセスを共有または制限したり、複数の表および複数の問合せの読取り一貫性を保持したりできます。
SELECT FOR UPDATE OF文を使用すると、表の特定行を明示的にロックすることで、UPDATEまたはDELETEが実行されるまで、その行が変更されないようにできます。ただし、OracleではUPDATE時またはDELETE時に行レベルのロックが自動的に取得されます。したがって、UPDATEまたはDELETEの前に行をロックする場合にのみ、FOR UPDATE OF句を使用してください。
LOCK TABLE文を使用すると、表全体を明示的にロックできます。
3.11.1 FOR UPDATE OFの使用
UPDATE文またはDELETE文のCURRENT OF句で参照されるカーソルをDECLAREする場合は、FOR UPDATE OF句を使用すると行の排他ロックを取得できます。SELECT FOR UPDATE OF文では、更新または削除の対象となる行が識別され、アクティブ・セット内の各行がロックされます。これは、ある行内の既存の値に基づいて更新処理を行う場合に便利です。更新前に、その行が他のユーザーにより変更されないようにする必要があります。
FOR UPDATE OF句はオプションです。たとえば、次のようなコードがあるとします。
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, job, sal FROM emp WHERE deptno = 20 FOR UPDATE OF sal;
ここでFOR UPDATE OF句を削除すると、次のようにコードが単純になります。
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, job, sal FROM emp WHERE deptno = 20;
CURRENT OF句は、必要に応じてFOR UPDATE句を追加するようにプリコンパイラに指示します。CURRENT OF句を使用して、カーソルから最後にFETCHされた行を参照します。
関連項目
3.11.2 LOCK TABLEの使用
LOCK TABLE文を使用すると、指定したロック・モードで1つ以上の表をロックできます。たとえば、次の文は行共有モードでEMP表をロックします。行共有ロックでは、表への同時アクセスが可能です。他のユーザーが表全体をロックして排他使用することはできません。
EXEC SQL LOCK TABLE EMP IN ROW SHARE MODE NOWAIT;
ロック・モードによって、その表に設定できる他のロックが決定されます。たとえば、同時に多数のユーザーが1つの表に対して行共有ロックを取得できる一方で、排他ロックを取得できるのは一度に1ユーザーのみです。あるユーザーが表を排他ロックしている間は、他のユーザーはその表の行をINSERT、UPDATEまたはDELETEできません。
オプションのキーワードNOWAITを指定すると、他のユーザーが表をロックしている場合は、その表の解放を待機しないようにOracleに対して指示できます。制御はただちにプログラムに戻されるため、プログラムではロックの取得を再度試みるまでの間に別の作業ができます。(SQLCA内のsqlcodeをチェックすると、LOCK TABLEが失敗したか確認できます。)NOWAITを省略すると、表が利用可能になるまで、Oracleは待機します。待機の時間制限は設定されていません。
表がロックされていても、他のユーザーは表に対して問い合せできますが、問合せを実行しても表のロックを取得できません。したがって、問合せが他の問合せや更新を妨げることはなく、更新が問合せを妨げることもありません。2つの異なるトランザクションで同じ行の更新が試みられる場合にのみ、一方のトランザクションが他方のトランザクションの完了まで待機の状態になります。
関連項目
3.12 コミットにまたがるフェッチ
COMMITとFETCHを併用する場合は、CURRENT OF句を使用しないでください。かわりに、各行のROWIDをSELECTしてから、その値を使用して更新または削除中の現在行を識別します。次に例を示します。
...
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, sal, ROWID FROM emp WHERE job = 'CLERK';
...
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND GOTO ...
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary, :row_id;
...
EXEC SQL UPDATE emp SET sal = :new_salary
WHERE ROWID = :row_id;
EXEC SQL COMMIT;
...
}
ただし、FETCHされた行はロックされません。つまり、ある行を読み取っても、その行を更新または削除する前に別のユーザーがその行を変更すると、結果が一貫性のないものになる可能性があります。
3.13 分散トランザクションの処理
分散データベースとは、異なるノード上の複数の物理データベースで構成される単一の論理データベースです。分散型の文とは、データベース・リンクによってリモート・ノードにアクセスする任意のSQL文です。分散トランザクションには、分散データベースの複数のノードでデータを更新するための分散型の文が、1つ以上設定されています。その更新が1つのノードのみに影響するときは、そのトランザクションは分散型ではありません。
COMMITを発行すると、分散トランザクションによる影響を受ける各データベースの変更が確定されます。COMMITのかわりにROLLBACKを発行すると、すべての変更が取り消されます。ただし、コミットまたはロールバック中にネットワークやマシンで障害が発生すると、分散トランザクションの状態は不明またはインダウトになることがあります。そのような場合、FORCE TRANSACTIONシステム権限があれば、FORCE句を使用して、ローカル・データベースでトランザクションを手動でコミットまたはロールバックできます。このトランザクションは、データ・ディクショナリ・ビューDBA_2PC_PENDINGにあるトランザクションIDを引用符で囲んだリテラルで指定する必要があります。次に例を示します。
EXEC SQL COMMIT FORCE '22.31.83'; ... EXEC SQL ROLLBACK FORCE '25.33.86';
FORCEは指定されたトランザクションのみコミットまたはロールバックするため、カレント・トランザクションには影響しません。インダウト・トランザクションは、手動でセーブポイントまでロールバックできません。
COMMIT文中のCOMMENT句を使用すると、分散トランザクションと対応付けるためのコメントを指定できます。トランザクションがインダウトになると、COMMENTで指定したテキストが、OracleによりトランザクションIDとともにデータ・ディクショナリ・ビューDBA_2PC_PENDINGに格納されます。長さ50文字以内の引用符付きリテラルを指定する必要があります。次に例を示します。
EXEC SQL COMMIT COMMENT 'In-doubt trans; notify Order Entry';
注意:
COMMENT句は、将来のリリースでは廃止になる予定です。COMMENT句ではなくトランザクション名を使用することをお薦めします。
関連項目
3.14 ガイドライン
次のガイドラインに従うと、いくつかの問題を回避できます。
3.14.1 アプリケーションの設計
アプリケーションを設計するときは、論理的に関連する処理を1つのトランザクション内にグループ化してください。正しく設計されたトランザクションには、与えられた作業を完了するために必要なステップが、すべて過不足なく含まれています。
表を参照するデータは一貫している必要があります。したがって、トランザクション内のSQL文は、一貫した方法でデータを変更する必要があります。 たとえば、2つの銀行口座間の資金振替には、一方の口座の借方勘定と他方の口座の貸方勘定が含まれています。どちらの処理も、正常終了または失敗が同時であることが必要です。一方の口座への新規預金など、関連のない更新はトランザクションに含めないでください。