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
SQL> INSERT INTO TT_売上 (売上番号, 売上日, 得意先コード, 担当者コード)
2 VALUES(10, '2015-03-27', 1, 1);
1行が作成されました。
SQL> INSERT INTO TT_売上明細 (売上番号, 明細番号, 商品コード, 売上数量)
2 VALUES(10, 1, 1, 2);
1行が作成されました。
SQL> SELECT * FROM TT_売上 ORDER BY 売上番号;
売上番号 売上日 得意先コード 担当者コード
---------- -------- ------------ ------------
1 02-11-01 1 1
2 02-11-03 2 4
3 02-11-04 4 5
4 02-11-13 3 2
5 02-11-15 5 7
6 02-11-15 1 9
10 15-03-27 1 1
7行が選択されました。
SQL>
そこで、もう一つのSQLPlusにて「TT_売上」の中身をSELECT文で表示させます。
そうしますと、最初のSQLPlusでINSERTされた内容が表示されません。
■もう一つのSQLPlus
SQL> SELECT * FROM TT_売上 ORDER BY 売上番号;
売上番号 売上日 得意先コード 担当者コード
---------- -------- ------------ ------------
1 02-11-01 1 1
2 02-11-03 2 4
3 02-11-04 4 5
4 02-11-13 3 2
5 02-11-15 5 7
6 02-11-15 1 9
6行が選択されました。
SQL>
ここで、最初のSQLPlusの中でトランザクションの全ての処理を確定するコマンド「COMMIT」を行います。
再度、もう一つのSQLPlusにて「TT_売上」の中身をSELECT文で表示させると、追加されたデータが表示されます。
この時点で、他のユーザから見た場合、「TT_売上」への追加が公開された状態になります。
■最初のSQLPlus-Window
SQL> COMMIT; コミットが完了しました。 SQL>
もう一つのSQLPLusから「TT_売上」をSELECTしてみると、「売上番号：10」のデータが追加されていることが確認できます。
■もう一つのSQLPlus
SQL> SELECT * FROM TT_売上 ORDER BY 売上番号;
売上番号 売上日 得意先コード 担当者コード
---------- -------- ------------ ------------
1 02-11-01 1 1
2 02-11-03 2 4
3 02-11-04 4 5
4 02-11-13 3 2
5 02-11-15 5 7
6 02-11-15 1 9
10 15-03-27 1 1
7行が選択されました。
SQL>
尚、トランザクション処理を全て廃棄する場合には、「ROLLBACK」を行います。
SQL> ROLLBACK; ロールバックが完了しました。 SQL>
SQLPlusの注意点としては、ウインドウの右上の「X」で終了する場合は、ROLLBACKされる様ですが、
「EXIT」コマンドで終了すると、強制的に「COMMIT」されてしまう為、デバッグ中などは
確実に「ROLLBACK」を実行しないとデータベースに予期せぬデータが残ったりします。
SQLPlusでDML文１行毎にCOMMITを行いたい場合は、以下の「SET」コマンドで「AUTOCOMMIT」の値をONします。
通常はOFFになっていますので、暗黙のトランザクションが行われます。
但し、この状態でSQLPlusを使用するのは注意が必要です。
SQL> SHOW AUTOCOMMIT autocommit OFF SQL> SET AUTOCOMMIT ON SQL> SHOW AUTOCOMMIT autocommit IMMEDIATE SQL>
元に戻す場合は「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 ]
| OF |
表結合でSELECTする場合に使用し、表と列名を指定します。 OFで指定した表の行がロックされますが、OFで指定してない表はロックされません。 OFを指定しない場合は、SELECT対象の全ての表の行がロックされます。 |
| NOWAIT |
対象レコードにロックがかかっていた時に、解除を待たずに下記エラーで復帰します。 「ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました」 |
| WAIT |
対象行にロックが掛けられていた場合、ロックが解除されるまで待機します。(SELECT結果が返りません。) 待機時間が指定してある場合は、その時間までロックが解除を待機します。 待機時間を超えた場合は、下記エラーで復帰します。 「ORA-30006: リソース・ビジー; WAITタイムアウトの期限に達しました。」 WAITまたはNOWAITの両方していしてない場合は、ロックが解除されるまで待機します。 |
尚、DISTINCT演算子、集合演算子（UNION、INTERSECT、MINUS）、集計関数とともに指定できません。
（行の選択が確定的であるということですね。）
FOR UPDATE句のみの行ロック
ここで２個の
上記トランザクションの様に、SQLPlusを２個起動し、テストしてみます。
SELECT文にFOR UPDATE句のみを付加して実行します。
■最初のSQLPlus-Window
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE;
売上番号
----------
10
SQL>
SELECT結果が返り、売上番号が表示されます。
ここで、もう一つのSQLPlusで、同じSELECT文を実行してみます。
■もう一つのSQLPlus
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE;
結果が返らず、そのままの表示でバングアップした様な感じになります。
（SELECTが待たされています。）
そこで、最初のSQLPlusでROLLBACK命令を実行し、行ロックを解除してやります。
そうすると、もう一つのSQLPlusでも以下の表示がされます。
■もう一つのSQLPlus
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE;
売上番号
----------
10
SQL>
FOR UPDATE句とNOWAIT句を用いた行ロック
行ロックの最初でも説明しましたが、売上データの変更を行う場合には、最初に対象となる売上データにロックを掛けてから、
その売上データに対するUPDATE等の処理を行います。
１件の売上データにFOR UPDATE句とNOWAIT句を用いて行ロックを掛けてみます。
■最初のSQLPlus-Window
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT;
売上番号
----------
10
SQL>
最初のSQLPlusでは特に問題なく、行ロックが掛かり、売上番号が返されます。 そこで、もう一つのSQLPlusで同じSELECT文を実行してみます。
■もう一つのSQLPlus
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT; SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT * 行1でエラーが発生しました。: ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました SQL>
タイムアウトエラー（ORA-00054）が返されて、行ロックが失敗します。
そこで、最初のSQLPlusでROLLBACK命令を実行し、再度もう一つのSQLPlusで同じSELECT文を実行してみます。
■最初のSQLPlus-Window
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT;
売上番号
----------
10
SQL> ROLLBACK;
ロールバックが完了しました。
SQL>
■もう一つのSQLPlus
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT;
SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT;
売上番号
----------
10
SQL>
FOR UPDATE句とWAIT句を用いた行ロック
上記、NOWAIT句を用いた行ロックと同じSELECT文を最初のSQLPlusで実行し、行ロックを行います。
そこで、もう一つのSQLPlusで同じSELECT文でNOWITをWAIT 10に変更し実行してみます。
SELECT文実行開始から10秒後にエラー表示が返されます。
■もう一つのSQLPlus
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE WAIT 10;
SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE WAIT 10
*
行1でエラーが発生しました。:
ORA-30006: リソース・ビジー; WAITタイムアウトの期限に達しました。
SQL>
私が今まで売上伝票入力などで売上データに行ロックを掛ける場合は、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
SQL> LOCK TABLE TT_売上 IN EXCLUSIVE MODE NOWAIT; 表がロックされました。 SQL>
■もう一つのSQLPlus
SQL> SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT; SELECT 売上番号 FROM TT_売上 WHERE 売上番号 = 10 FOR UPDATE NOWAIT * 行1でエラーが発生しました。: ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました SQL>
