DBでは複数の処理が同時に実行されており、OLTP処理の場合、
SQLの応答速度(レスポンス)はミリ秒で処理されているのが当たり前です。
同時に複数の処理が実行される為、同じレコードに対する更新処理も行われる可能性があります。このときOracleではどのような動作となるか確認していきます。
ロックのメカニズム
Oracleでは同じレコードが同時に更新されないよう行をロックし、その他の更新処理が行われないようにしています。 その為、Oracleは同じレコードに対し、更新処理が実行されると、後続の処理が待機します。最初のトランザクションが終了すると、ロックを開放し、後続の処理が再開されます。
この処理はOracleが自動で実施するため、ユーザは特に意識しなくてもロック機能により順番に更新処理を行うことができます。
ロックされた行を更新しようとするとロックが解放するまで待ちます。このメカニズムをエンキューといいます。複数ユーザが同時に更新処理を行おうとしてもエンキューにより、すべて順番待ちとなり、順々に処理されます。
またロックは対象レコードのみとなるので、その他レコードに対してはロックを取得しません。その為、同じ表に対しても別のレコードであれば同時に更新が可能です。
またロック待ちとなるとトランザクションが終了するまで待機となってしまいますが、
SQL実行時に待機時間を設定することができます。
では実行例を見てみましょう。
セッション①
SQL> select * from emp; EMP_ID FIRST_NAME LAST_NAME TEL ADDRESS -------- ---------- ---------- ---------------- ----------- 2 Danble Door 0120-000-000 Porland 3 Harry Potter 042-491-9652 Tokyo SQL> UPDATE emp SET tel = '090-aaaa-1111' WHERE emp_id = 2; 1 row updated.
このままcommitせずに次に別のセッションで同じレコードを更新してみましょう。
セッション②
SQL> SELECT * FROM emp; EMP_ID FIRST_NAME LAST_NAME TEL ADDRESS -------- ---------- ---------- ---------------- ----------- 2 Danble Door 0120-000-000 Porland 3 Harry Potter 042-491-9652 Tokyo SQL> UPDATE emp SET TEL = '090-bbbb-2222' WHERE emp_id = 2;
→ 待機
待機状態となるとプロンプトが返ってこなくなります。これがロック待ちの状態です。
この状態はセッション①がトランザクションを終了するまで永遠に待機します。
セッション①がずっとcommitしなかったら、どうなるでしょう。
セッション②は明日になってもupdateが実行できません。
セッション①がすぐにcommitすれば良いですが、これはあまりよくないですね。
この場合、待機時間を設定することが可能です。
[構文] ロック待機時間の指定
SQL> SELECT …
FOR UPDATE [ WAIT [ 秒 ] | NOWAIT ];
SQL> SELECT …
FOR UPDATE [ WAIT [ 秒 ] | NOWAIT ];
上記のSELECT FOR UPDATE文を使用すると対象行に対しロックを取得することができます。またWAIT句を使用することで何秒間待機することができます。
NOWAITは待機せずにすぐにエラーを返します。
SQL> select * from emp where emp_id = 2 for update wait 5;
select * from emp where emp_id = 2 for update wait 5
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
UPDATEを実行する前にSELECT FOR UPDATE文を使用することでロックを取得できるかどうか試すことが可能です。
デッドロック
次にデッドロックについてご紹介していきます。デッドロックとはお互いが互いのロックを取得できるのを待ち続けてしまう現象のことを言います。
Oracleの場合このデッドロックが発生した場合、片方のロック待ちを解除することにより、デットロックの状態を解消します。
この解消の処理は自動的にOracleが検知からロック解除まで実施してくれます。
またこのデッドロック情報はアラートログファイルに記載され、トレースファイルにも出力されます。
こちらも確認してきます。
セッション①
SQL> SELECT * FROM emp; EMP_ID FIRST_NAME LAST_NAME TEL ADDRESS -------- ---------- ---------- ---------------- ----------- 2 Danble Door 0120-000-000 Porland 3 Harry Potter 042-491-9652 Tokyo SQL> update emp set TEL = '090-aaaa-1111' where emp_id = 2; 1 row updated.
セッション②
SQL> SELECT * FROM emp; EMP_ID FIRST_NAME LAST_NAME TEL ADDRESS -------- ---------- ---------- ---------------- ----------- 2 Danble Door 0120-000-000 Porland 3 Harry Potter 042-491-9652 Tokyo SQL> UPDATE emp SET tel = '090-bbbb-2222' WHERE emp_id = 3; 1 row updated.
セッション①はemp_idが2、セッション②はemp_idが3であり別々のレコードである為、こちらのupdateは同時に実行することができます。続いてみていきましょう。
セッション①
SQL> UPDATE emp SET tel = '090-bbbb-2222' WHERE emp_id = 3;
→ ロック待ち
ここでセッション①がemp_idが3のレコードに対しupdateを実行しますが、
セッション②でemp_idが3のレコードのロックを取得しているのでここでセッション①は待機します。
セッション②
SQL> UPDATE emp SET tel = '090-aaaa-1111' WHERE emp_id = 2;
→ ロック待ち
上記でセッション②はemp_idが2のロックを取得しようとしますが、emp_idが2のレコードはセッション①が取得中である為、セッション②が待機します。
ここでお互いがロック待ちの状態となり、どちらも操作が不能になります。この状況をデッドロックと言います。この場合はデッドロックを検知しセッション①がエラーとなります。
セッション①
SQL> UPDATE emp SET tel = '090-bbbb-2222' WHERE emp_id = 3;
UPDATE emp SET = '090-bbbb-2222' WHERE emp_id = 3
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Oracleは自動的にデットロックを検知し、片方のセッションのロック待ちを解除します。またデットロックの情報はアラートログに記載されています。
altert.logの抜粋
XXX XX XX XX:XX:XX 2016 ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace /orcl_ora_22736.trc.
さらに細かい情報はトレースファイルに記載されています。
アラートログの場所はdiagnostic_destパラメータに記載されています。
実際のパス：$diagnostic_dest/rdbms/DB_NAME/INSTANCE_NAME/trace
SQL> show parameter diagno NAME TYPE VALUE
---------------------- ----------- ---------------------- diagnostic_dest string /u01/app/oracle
以上がデータ同時実行性の内容となります。お疲れ様でした。