selfup

selfupのトピックス-PR-

【初級】新人SEのためのSQLの基礎 第5回 トランザクションとカーソルにまつわる注意点

明示しなくてもコミットされる
製品ごとの挙動の違いに注意しよう

2006/04/03
出典:日経オープンシステム 2003年2月号142ページより
(記事は執筆時の情報に基づいており、現在では異なる場合があります)
図1●トランザクションの特性と注意点<BR>トランザクションには大きく2つの特性がある。トランザクションの開始と終了の間の処理は実行させるが確定は行わない(特性(1))。また,トランザクションは一連の処理の「確定」や「取り消し」をまとめて行う(特性(2))。このようなトランザクションの注意点には,(1)トランザクションの開始は製品によって異なる,(2)トランザクションに含まれるSQL文が製品によって異なる,(3)セーブポイント機能,(4)ロック――がある
図1●トランザクションの特性と注意点<BR>トランザクションには大きく2つの特性がある。トランザクションの開始と終了の間の処理は実行させるが確定は行わない(特性(1))。また,トランザクションは一連の処理の「確定」や「取り消し」をまとめて行う(特性(2))。このようなトランザクションの注意点には,(1)トランザクションの開始は製品によって異なる,(2)トランザクションに含まれるSQL文が製品によって異なる,(3)セーブポイント機能,(4)ロック――がある
[画像のクリックで拡大表示]
図2●トランザクションの開始と終了&lt;BR&gt;トランザクションの終了は「COMMIT(確定)」か「ROLLBACK(取り消し)」であるが,トランザクションの開始は製品によって異なる。Oracleの場合トランザクションの開始を明示する必要がなく,PostgreSQLの場合は「BEGIN」コマンドで明示しなければトランザクションとならない
図2●トランザクションの開始と終了<BR>トランザクションの終了は「COMMIT(確定)」か「ROLLBACK(取り消し)」であるが,トランザクションの開始は製品によって異なる。Oracleの場合トランザクションの開始を明示する必要がなく,PostgreSQLの場合は「BEGIN」コマンドで明示しなければトランザクションとならない
[画像のクリックで拡大表示]
図3●トランザクションに含まれるSQL文&lt;BR&gt;INSERT文,UPDATE文,DELETE文はトランザクションに含まれるが,CREATE TABLE文はトランザクションに含まれない(Oracleの場合)。CREATE TABLE文の直前でコミット処理が行われることに注意したい。PostgreSQLではCREATE TABLE文などもトランザクションに含む
図3●トランザクションに含まれるSQL文<BR>INSERT文,UPDATE文,DELETE文はトランザクションに含まれるが,CREATE TABLE文はトランザクションに含まれない(Oracleの場合)。CREATE TABLE文の直前でコミット処理が行われることに注意したい。PostgreSQLではCREATE TABLE文などもトランザクションに含む
[画像のクリックで拡大表示]
図4●セーブポイント機能の注意点&lt;BR&gt;セーブポイント機能とは,トランザクションを取り消す際にトランザクションの開始ポイントではなく途中のポイントまで部分的に取り消す機能。「SAVEPOINT」コマンドを利用し,1つのトランザクションに複数のセーブポイントが設定できる。取り消し範囲に含まれるセーブポイントは無効になる点に注意したい
図4●セーブポイント機能の注意点<BR>セーブポイント機能とは,トランザクションを取り消す際にトランザクションの開始ポイントではなく途中のポイントまで部分的に取り消す機能。「SAVEPOINT」コマンドを利用し,1つのトランザクションに複数のセーブポイントが設定できる。取り消し範囲に含まれるセーブポイントは無効になる点に注意したい
[画像のクリックで拡大表示]
図5●トランザクションの終了までロックは解放しない&lt;BR&gt;トランザクションの開始と終了の間の一連の処理は未確定処理となるため,トランザクションが終了するまでロックの解放は行われない。そのため,「デッドロック」や「ロックの未解放」などの問題が起きる可能性がある
図5●トランザクションの終了までロックは解放しない<BR>トランザクションの開始と終了の間の一連の処理は未確定処理となるため,トランザクションが終了するまでロックの解放は行われない。そのため,「デッドロック」や「ロックの未解放」などの問題が起きる可能性がある
[画像のクリックで拡大表示]
図6●カーソルの利用例&lt;BR&gt;カーソルとは,SQL文において特定のレコードを指し示すRDBMSの機能。明示的にカーソルを宣言する「明示カーソル」と,カーソルを宣言しない「暗黙カーソル」がある。明示カーソルの宣言は,「CURSOR(カーソル名)IS SELECT文」で行う
図6●カーソルの利用例<BR>カーソルとは,SQL文において特定のレコードを指し示すRDBMSの機能。明示的にカーソルを宣言する「明示カーソル」と,カーソルを宣言しない「暗黙カーソル」がある。明示カーソルの宣言は,「CURSOR(カーソル名)IS SELECT文」で行う
[画像のクリックで拡大表示]
図7●暗黙カーソルは対象レコードが複数あるとエラーになる&lt;BR&gt;暗黙カーソルは,対象レコードが1つの時に有効な機能。対象レコードが複数になる場合,明示カーソルを利用しなければならない
図7●暗黙カーソルは対象レコードが複数あるとエラーになる<BR>暗黙カーソルは,対象レコードが1つの時に有効な機能。対象レコードが複数になる場合,明示カーソルを利用しなければならない
[画像のクリックで拡大表示]

トランザクションの利用においては,いつトランザクションが開始し,どこで終了するのかを確実に把握しておきたい。Oracleの場合は常にトランザクションが有効であるため,明示しなくてもトランザクションは開始する。CREATE TABLE文などがあるとCOMMIT(確定)文が発行されなくてもコミットされるし,データベース接続が切断されてもコミットされてしまう。暗黙カーソルは,対象となるレコードが1レコードの場合でしか使えない点に注意したい。

 SQL文を利用する際に注意すべき基礎的なポイントを解説するセミナーの第5回である。前回は,副問い合わせ*ビュー*の利用における注意点について説明した。副問い合わせは実行エラーを起こしやすく,ビューは実行性能に配慮が必要なことなどを説明した。

 今回は,(1)複数のSQL文をまとめて処理する「トランザクション*」と(2)データベースの検索時に利用する「カーソル*」にまつわる注意点を説明する。どちらも,データベース・アプリケーションの開発ではよく利用する機能である。なおRDBMSは,筆者の利用経験の多いOracleをベースとし,必要に応じてPostgreSQLに関する情報を掲載する。

●トランザクション
製品による挙動の違いに注意しよう

 1つの処理が2つ以上のSQL文で表される場合にトランザクションを利用する。トランザクションは一連の処理をまとめて「確定」または「取り消し」を行うことができる機能で,これを利用すれば1つのSQL文が失敗した場合,同じ処理の別のSQL文をすべて取り消すことができる。データベース・アプリケーションでは欠かせない機能であるが,RDBMS製品による挙動の違いなどに注意しなければならない。

トランザクションの4つの注意点

 トランザクションには開始と終了があり,トランザクション内にはINSERT文(挿入)やDELETE文(削除),UPDATE文(更新)などが含まれる。トランザクション内のデータベースに対する更新処理は,実行されるが確定は行われず,未確定の処理が行われる。一連の処理の確定や取り消しは,トランザクション終了時にまとめて行う(図1[拡大表示]上)。トランザクションの終了は「COMMIT(コミット)」か「ROLLBACK(ロールバック)」で,コミットは一連の処理をまとめて確定し,ロールバックはまとめて処理を取り消すコマンドである。

 このようなトランザクションを利用する場合の注意点は,主なもので4つある(図1下)。1つ目は「トランザクションの開始」。1つのトランザクションはひとまとまりの処理と考えられるためトランザクションの開始ポイントは確実に理解しておきたいが,トランザクションの開始は製品によって異なるので注意が必要である。本セミナーではOracleとPostgreSQLについて比較しながら説明する。

 2つ目は「トランザクションに含まれるSQL文」である。これもトランザクションの開始と同様に,製品によって異なるので注意が必要である。3つ目はトランザクションにおいて部分的に取り消し処理などを行う「セーブポイント*機能」。最後の4つ目は「ロック*」である。トランザクション内の更新処理は未確定処理になるため,別のユーザーが同一データを更新できないようにしなければならない。そのためにトランザクションが終了するまでレコードやテーブルにロックがかかり,処理が継続できなくなる「デッドロック*」や「ロックの未解放」などに陥る可能性がある。

勝手にトランザクションが始まる

 Oracleでのトランザクションの開始は,明示的に行う必要が無く,勝手に始まる。Oracleでは常にトランザクションが有効なので,データベースへの接続が成功すればトランザクションが開始する。また,コミットかロールバックでトランザクションが終了すると,またそこから新たなトランザクションが開始する(図2[拡大表示]左)。従ってOracleでは,INSERT文などのデータベースに対する更新処理が行われても,コミットするまで確定しない。逆に言えばコミットやロールバックを忘れてしまうと未確定状態が延々と続くことになり,後述するロックの問題が起きる。

 PostgreSQLでは「BEGIN」コマンドを利用しなければトランザクションは開始しない(同右)。トランザクションは明示的に開始するためコミットやロールバックを忘れる事故は起きにくいが,トランザクションでは無いINSERT文などのデータベースに対する更新処理は即座に確定される。入力ミスなどをしても取り消し処理ができないため,対話型ツールでデータベースを操作する場合,慎重にSQL文を記述しなければならない*1

暗黙のうちにコミットされる

 トランザクションの終了はコミットやロールバックで行うのが基本であるが,例外がある。コミットやロールバックを発行しないでデータベースとの接続を切断してしまうと,トランザクションは終了する。その際コミットされるのか,ロールバックされるのかは,製品によって異なる。

 Oracleではコミット処理を行う。「COMMIT」を明示せずにコミット処理が行われるため,この処理を「暗黙コミット」と言う。Oracleの対話型ツール「SQL*Plus」を使っている場合に特に注意しなければならない点である。筆者は暗黙コミットで痛い経験をしたことがある。仕事が忙しく割り込み作業などを行っている際に,誤ってSQL*Plusを終了してしまい,データを壊してしまったことがある。Oracleでは常にトランザクションが有効なのでコミットさえしなければ処理を元に戻すことができるのだが,SQL*Plusを誤って終了させてしまい,処理の途中段階でコミットしてしまったことが原因である。

 PostgreSQLでは暗黙コミットは行わない。トランザクションの途中でデータベースとの接続が切断されてしまった場合,トランザクションはロールバックされる。

DDL文の前で暗黙コミットする

 また,トランザクションの対象にならないSQL文があると,そこでトランザクションが終了する。トランザクションの対象にならないSQL文はRDBMS製品によって異なり,OracleではCREATE TABLE文などのDDL(データ定義)文はトランザクションの対象にならない。PostgreSQLではDDL文もトランザクションの対象になる。

 トランザクションの対象になるSQL文が異なるため,同じSQL文の処理内容であってもRDBMS製品によって挙動が異なってくる。図3[拡大表示]のトランザクションの例ではUPDATE文などに混じってCREATE TABLE文がある。OracleではCRAETE TABLE文がトランザクションの対象にならないため,CREATE TABLE文の前でトランザクションが終了する。その際,COMMITが記述されていないにもかかわらず,コミット処理が行われる。また,CREATE TABLE文の後から新たなトランザクションが始まるため,一連の処理の最後のROLLBACKは,CREATE TABLE文の直後までしか戻らない(図3左)。

 一方PostgreSQLではCRATE TABLE文がトランザクションの対象となるため,図3の一連の処理は1つのトランザクションとなる。一連の処理の最後のROLLBACKによって,処理の最初まで戻る。

トランザクションの途中まで処理を取り消す

 ロールバックはトランザクションの開始処理まで戻るのが基本であるが,「セーブポイント」機能を利用すれば部分的な処理の取り消しが可能になる。図4[拡大表示]上に示したようにトランザクションの中に「SAVEPOINT(セーブポイント名)」を設定し,「ROLLBACK TO(セーブポイント名)」とすれば,ROLLBACKからSAVEPOINT間の処理を取り消すことができる。1つのトランザクションには複数のセーブポイントが設定可能である。セーブポイント機能はOracleにはあるがPostgreSQLには無い。

 利用上の注意点は,設定したセーブポイントもロールバック処理の対象になることである。例えば,図4下のようにSAVEPOINT AAAとSAVEPOINT BBBの2つを設定してROLLBACK TO AAAとすると,SAVEPOINT AAAまでの処理が取り消され,SAVEPOINT BBBのセーブポイントは無効になる。ROLLBACK TO AAAの後でROLLBACK TO BBBとするとエラーになる。飛び越されたセーブポイントが必要である場合,セーブポイントを再設定しなければならない。

ロック未解放で処理性能に悪影響

 トランザクションを利用する上で注意しなければならない最後のポイントは,レコードやテーブルに対する「ロック」である。データベースを更新する場合,該当するレコードやテーブルをロックし,別のトランザクションによる更新ができないようにする。トランザクション内の更新処理は,処理が確定するまで,つまりトランザクションの終了までロックを解放しない。別のユーザーが同じレコードを更新しようとした場合,ロックが解放されるまで待つことになる。ロックに関しては,「デッドロック」と「ロックの未解放」に注意しよう。

 デッドロックとは,2つ以上のトランザクションがそれぞれのロックの解放を待つ状態になり,処理が進めなくなってしまう状態になること(図5[拡大表示]上)。デッドロックになるといずれかの処理を中断せざるを得なくなる。テーブルなどのロックをかける順番を決めておけば,デッドロックの予防になる。

 トランザクションの途中でマシンがハングしたり,停電やネットワーク切断などがあると,データベース側のロックが残ってしまうことがある(同下)。ロックがかかった状態では該当データに対する更新処理は待たされることになり,実行性能を悪化させる。このようなロックの未解放の状態になってしまった場合,原因となるデータベース接続セッションのプロセスを見極めて,そのプロセスを強制終了させる。Oracleの場合は,データベース接続ミドルウエア「Net8*」の機能によって,無効接続を検出して該当プロセスを終了させることができる。

●カーソル
暗黙カーソルの利用に注意

 カーソルとは,データベースの検索において1レコードずつ指し示すためのRDBMSの機能である。ここではOracleのPL/SQLにおけるカーソルの使い方と利用上の注意点を見ていく。

 カーソルはRDBMSが提供するオブジェクトであり,宣言してから利用する「明示カーソル」と,宣言しなくても利用できる「暗黙カーソル」がある。図6左は明示カーソルの例で,「SELECT * FROM DEPT」の結果セットに対する「DEPT_CUR」というカーソルを宣言している。BEGIN以降でカーソルを利用し,図6[拡大表示]左の例ではカーソルを1つずつ移動させて対象レコードからデータを出力している。「DBMS_OUTPUT.PUT_LINE」はOracleが提供するパッケージ*であり,指定したデータを表示する。図6右は暗黙カーソルの例である。暗黙カーソルは「SELECT ~ INTO ~ FROM ~」という構文になる。カーソルの宣言は行っていないが,対象となるレコード(図6右の例ではDEPTテーブルにおけるEMPNOが7654のレコード)を指し示すカーソルが利用されている。

 暗黙カーソルは複数レコードを指し示す場合には利用できない点に注意したい。テスト環境と本番環境でデータが異なるような場合,安易に暗黙カーソルを利用していると本番時になってエラーが起きる可能性がある。複数レコードであるにもかかわらず暗黙カーソルを利用した場合,実行エラーとなる(図7[拡大表示]左)。エラーを回避するには明示カーソルを使えばよい(図7右)。必ず1レコードになる場合に限って,暗黙カーソルを利用するように徹底しよう。システム開発によっては暗黙カーソルの利用を禁止し,すべて明示カーソルを使う場合がある。パフォーマンス面においては,一般的には暗黙カーソルより明示カーソルの方が良い。暗黙カーソルでは検索データが複数レコードになっているかどうかのチェックのためにフェッチ(データの取り出し)を行うからである。


玉川 敏一
シーズ・ラボ ITソリューション部 セクションマネージャー
  • このエントリーをはてなブックマークに追加
  • Evernoteでクリップする
  • 印刷する

今週のトピックス-PR-

この記事に対するfacebookコメント

nikkeibpITpro

▲ ページトップ

CIO Computerworld

Twitterもチェック

執筆者一覧

イベントINFO -PR-

最新号

注目のセミナー

申込受付中!

演習で学ぶ!
実践RFP作成術
[開催迫る]

お申し込みは今すぐ!
大阪開催:9/ 4(木)