Oracle SQL　トランザクション処理・ロック処理

• トランザクション処理
   
• 行ロック処理
  SELECT FOR UPDATE を使用した行ロック
• テーブルロック処理
  LOCK TABLE を使用したテーブルロック

おすすめ書籍

• 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
• Oracleの基本 ~データベース入門から設計/運用の初歩まで
• [改訂第4版]SQLポケットリファレンス

トランザクション処理

トランザクションとは「関連する複数の処理を一つの処理にまとめて管理する」ことをいいます。
「関連する複数の処理」とは何かと言いますと、INSERT,UPDATE,DELETEなどのDML文が集まった処理のことです。
例えば、売上伝票入力でのことを考えてみます。売上伝票入力には以下の様ないくつかのテーブルへの追加、更新などの処理が発生します。

・売上伝票ヘッダテーブルへの追加処理（得意先、売上日などの設定）
・売上伝票明細テーブルへの追加処理（商品、売上数量、単価などの設定）
・商品マスターへの在庫更新処理
（実際は納品時に払い出しを行っているのでここではしないかもしれない）
・そのほかのテーブルへの更新など・・・

これらの全ての追加・更新処理が成功して、初めて１件の売上伝票入力が完了します。 もし、上の処理で１件でも途中でエラーが発生した場合、それまでの全ての処理を無かったことにしないと整合性がとれません。
この一連の処理の「全ての成功」または「全ての失敗」を保障するものが、トランザクションの機能なのです。


これらの流れを、SQLの実行ツールであるSQLPlusで確認してみます。
SQLPlusではトランザクション開始としてのコマンドは、直接は実行するのではなくSQLPlusが起動し、
ユーザログインした時点で既に一つのトランザクションが開始している状態です。

トランザクションの処理の様子を、SQLPlusを２個起動し、行います。
DML文として売上データとして「TT_売上」「TT_売上明細」に１行ずつのデータを追加します。
（一般的には売上明細には複数行データが追加されるものです）
最初のSQLPlusでは「TT_売上」と「TT_売上明細」に対するINSERT文を発行し、 その時点での「TT_売上」の中身をSELECT文で表示させます。 最初のSQLPlusから「TT_売上」をみると、データが１件追加された様子が分かります。

Oracleでは暗黙的にトランザクションが開始されますので他のデータベースのように 「BEGIN TRANSACTION」文は実行しません。

■最初のSQLPlus-Window

そこで、もう一つのSQLPlusにて「TT_売上」の中身をSELECT文で表示させます。
そうしますと、最初のSQLPlusでINSERTされた内容が表示されません。

■もう一つのSQLPlus

ここで、最初のSQLPlusの中でトランザクションの全ての処理を確定するコマンド「COMMIT」を行います。
再度、もう一つのSQLPlusにて「TT_売上」の中身をSELECT文で表示させると、追加されたデータが表示されます。 この時点で、他のユーザから見た場合、「TT_売上」への追加が公開された状態になります。

■最初のSQLPlus-Window

もう一つのSQLPLusから「TT_売上」をSELECTしてみると、「売上番号：10」のデータが追加されていることが確認できます。

■もう一つのSQLPlus

尚、トランザクション処理を全て廃棄する場合には、「ROLLBACK」を行います。

SQLPlusの注意点としては、ウインドウの右上の「X」で終了する場合は、ROLLBACKされる様ですが、 「EXIT」コマンドで終了すると、強制的に「COMMIT」されてしまう為、デバッグ中などは 確実に「ROLLBACK」を実行しないとデータベースに予期せぬデータが残ったりします。

SQLPlusでDML文１行毎にCOMMITを行いたい場合は、以下の「SET」コマンドで「AUTOCOMMIT」の値をONします。 通常はOFFになっていますので、暗黙のトランザクションが行われます。 但し、この状態でSQLPlusを使用するのは注意が必要です。

元に戻す場合は「SET AUTOCOMMIT OFF」としてください。

行ロック処理(SELECT FOR UPDATE)

１件の売上データを更新することを考えてみますと、安全に更新するにはトランザクション処理で説明した様に、 最初に更新対象となる売上データにロックを掛けてから、その売上データに対するUPDATEを行い、 UPDATEが成功した場合にはCOMMITを行うことになります。尚、失敗した場合にはROLLBACKを行います。
（COMMIT及びROLLBACKを行うことでロックが解除されます）

１件の売上データにロックを掛ける機能として、SELECT文の最後尾にFOR UPDATE句を追加します。 SELECTした時にそのデータのみにロックを掛けて、他のユーザからはロックを掛けられなくする機能（排他行ロック）がFOR UPDATE句です。

SELECT <列名リスト> FROM <表名リスト> WHERE句
FOR UPDATE [OF [<表名>.]<列名> ... ] [ WAIT [ 待機時間 ] | NOWAIT ]

尚、DISTINCT演算子、集合演算子（UNION、INTERSECT、MINUS）、集計関数とともに指定できません。
（行の選択が確定的であるということですね。）

FOR UPDATE句のみの行ロック

ここで２個の 上記トランザクションの様に、SQLPlusを２個起動し、テストしてみます。
SELECT文にFOR UPDATE句のみを付加して実行します。

■最初のSQLPlus-Window

SELECT結果が返り、売上番号が表示されます。
ここで、もう一つのSQLPlusで、同じSELECT文を実行してみます。

■もう一つのSQLPlus

結果が返らず、そのままの表示でバングアップした様な感じになります。
（SELECTが待たされています。）
そこで、最初のSQLPlusでROLLBACK命令を実行し、行ロックを解除してやります。
そうすると、もう一つのSQLPlusでも以下の表示がされます。

■もう一つのSQLPlus

FOR UPDATE句とNOWAIT句を用いた行ロック

行ロックの最初でも説明しましたが、売上データの変更を行う場合には、最初に対象となる売上データにロックを掛けてから、 その売上データに対するUPDATE等の処理を行います。
１件の売上データにFOR UPDATE句とNOWAIT句を用いて行ロックを掛けてみます。

■最初のSQLPlus-Window

最初のSQLPlusでは特に問題なく、行ロックが掛かり、売上番号が返されます。 そこで、もう一つのSQLPlusで同じSELECT文を実行してみます。

■もう一つのSQLPlus

タイムアウトエラー（ORA-00054）が返されて、行ロックが失敗します。
そこで、最初のSQLPlusでROLLBACK命令を実行し、再度もう一つのSQLPlusで同じSELECT文を実行してみます。

■最初のSQLPlus-Window

■もう一つのSQLPlus

FOR UPDATE句とWAIT句を用いた行ロック

上記、NOWAIT句を用いた行ロックと同じSELECT文を最初のSQLPlusで実行し、行ロックを行います。
そこで、もう一つのSQLPlusで同じSELECT文でNOWITをWAIT 10に変更し実行してみます。
SELECT文実行開始から10秒後にエラー表示が返されます。

■もう一つのSQLPlus

私が今まで売上伝票入力などで売上データに行ロックを掛ける場合は、NOWAIT句を用いました。
売上伝票入力プログラムでは、対象となる売上データを読込む最初で、NOWAIT句のロックを掛け、
もし、エラーで返れば他の端末でその売上データを表示していることになり、
「他の端末で売上伝票を操作中です」などのメッセージを表示していました。

この方法が最善の策かは分かりませんが、確実ではないかと思います。
尚、この行ロックの注意点としては、ロック中に、プログラムエラーなどで ロックが解除されないままプログラムが予期せず終了しない様にしなければなりません。 行ロック中でのデータ処理は、try...catchなどでエラーを検知し、確実にROLLBACKを行う必要があります。

テーブルロック処理

月末の閉め処理などで、他からの更新・追加処理をストップしバッチ処理で月次閉め処理を行いたい場合があります。
そういった場合に対象となるテーブルそのものにロックを掛けることができます。

LOCK TABLE <表名> IN <ロックMODE> MODE [ WAIT [ 待機時間 ] | NOWAIT ]
 <ロックMODE>:=ROW SHARE|ROW EXCLUSIVE|SHARE UPDATE|SHARE|SHARE ROW EXCLUSIVE|EXCLUSIVE

テーブルロックのMODEとしては、私は「EXCLUSIVE」ぐらいしか使ったことがありません。

■最初のSQLPlus-Window

■もう一つのSQLPlus