【ORACLEのロックについての検証１】ロック解除方法
データベースで、ロックが発生してしまい処理が停止したことはありませんか。
そんな時、ロックをしている原因となっているセッションは何なのかを調査します。
そのセッションを切断することにより、ロック待ちして止まっていたセッションが流れ出します。
■データベースロック状態確認SQL
SELECT A.SID SID,
A.USERNAME USERNAME,
A.SERIAL# SERIALNO,
A.SCHEMANAME SCHEMANAME,
A.MACHINE MACHINE,
B.TYPE,
B.BLOCK BLOCK,
A.PROGRAM PROGRAM,
A.PROCESS CLIENT,
TO_CHAR(B.CTIME/60,'999990.9') LOCK_TIME
FROM V$SESSION A,
V$LOCK B
WHERE A.SID = B.SID
AND B.TYPE IN ('TX')
ORDER BY LOCK_TIME DESC;
■ロック解除SQL
ALTER SYSTEM DISCONNECT SESSION 'SID,SERIALNO' IMMEDIATE;
■検証
●1.テスト用テーブル作成
SQL> CREATE TABLE TEST(A NUMBER);
表が作成されました。
SQL> INSERT INTO TEST VALUES(1);
1行が作成されました。
SQL> COMMIT;
コミットが完了しました。
●2.ロックをわざと発生させる
それぞれ別のコマンドプロンプトを起動して、DB接続を実施。
・セッション1(ロックの原因を作る)
SQL> UPDATE TEST SET A = 4 WHERE A = 1;
1行が更新されました。
・セッション2(セッション1の処理確定待ち)
SQL> UPDATE TEST SET A = 5 WHERE A = 1;
・セッション3(セッション1の処理確定待ち)
SQL> UPDATE TEST SET A = 6 WHERE A = 1;
・ロック状態確認SQLを実行
SQL> SET LIN 10000 PAGES 50000 TRIMSPOOL ON
SQL> SELECT A.SID SID,
2 A.USERNAME USERNAME,
3 A.SERIAL# SERIALNO,
4 A.SCHEMANAME SCHEMANAME,
5 A.MACHINE MACHINE,
6 B.TYPE,
7 B.BLOCK BLOCK,
8 A.PROGRAM PROGRAM,
9 A.PROCESS CLIENT,
10 TO_CHAR(B.CTIME/60,'999990.9') LOCK_TIME
11 FROM V$SESSION A,
12 V$LOCK B
13 WHERE A.SID = B.SID
14 AND B.TYPE IN ('TX')
15 ORDER BY LOCK_TIME DESC;
SID USERNAME SERIALNO SCHEMANAME MACHINE TY BLOCK PROGRAM CLIENT
LOCK_TIME
---------- ------------------------------ ---------- ------------------------------ ---------------------------------------------------------------- -- ---------- ------------------------------ ------------------------------ ---------
10 SYS 61653 SYS WORKGROUP\YDB TX 1 sqlplus.exe 3372:10892
9.5
243 SYS 56557 SYS WORKGROUP\YDB TX 0 sqlplus.exe 4076:3440
9.3
245 SYS 34310 SYS WORKGROUP\YDB TX 0 sqlplus.exe 10680:8012
9.0
LOCK_TIMEは、ロックが始まってからの時間。
BLOCK列が1のレコードがロックの原因を作ったセッション1です。
SID、SERIALNOを特定して切断します。
●3.ロックの原因になったセッション１を切断
SQL> ALTER SYSTEM DISCONNECT SESSION '10,61653' IMMEDIATE;
システムが変更されました。
●4.セッション1切断後、ロック状態確認SQLを実行
SID USERNAME SERIALNO SCHEMANAME MACHINE TY BLOCK PROGRAM CLIENT
LOCK_TIME
---------- ------------------------------ ---------- ------------------------------ ---------------------------------------------------------------- -- ---------- ------------------------------ ------------------------------ ---------
245 SYS 34310 SYS WORKGROUP\YDB TX 0 sqlplus.exe 10680:8012
0.9
243 SYS 56557 SYS WORKGROUP\YDB TX 1 sqlplus.exe 4076:3440
0.9
セッション1が切断され、セッション2がロック元に変わりました。(BLOCK=1)
・セッション1(切断され、何もできない)
SQL> UPDATE TEST SET A = 4 WHERE A = 1;
1行が更新されました。
SQL>
SQL> commit;
commit
*
行1でエラーが発生しました。:
ORA-03113: 通信チャネルでend-of-fileが検出されました
プロセスID: 2800
セッションID: 10、シリアル番号: 61653
・セッション2(処理確定待ち)
SQL> UPDATE TEST SET A = 4 WHERE A = 1;
・セッション3(セッション2の処理確定待ち)
SQL> UPDATE TEST SET A = 6 WHERE A = 1;
●5.ロックの原因になったセッション２を切断
SQL> ALTER SYSTEM DISCONNECT SESSION '243,56557' IMMEDIATE;
システムが変更されました。
●6.セッションBA切断後、ロック状態確認SQLを実行
SID USERNAME SERIALNO SCHEMANAME MACHINE TY BLOCK PROGRAM
CLIENT LOCK_TIME
---------- ------------------------------ ---------- ------------------------------ ---------------------------------------------------------------- -- ---------- ------------------------------ ------------------------------ ---------
245 SYS 34310 SYS WORKGROUP\YDB TX 0 sqlplus.exe
10680:8012 0.4
セッション2が切断され、セッション3の確定待ちになりました。
・セッション2(切断され、何もできない)
SQL> UPDATE TEST SET A = 5 WHERE A = 1;
1行が更新されました。
SQL>
SQL> rollback;
rollback
*
行1でエラーが発生しました。:
ORA-03113: 通信チャネルでend-of-fileが検出されました
プロセスID: 9960
セッションID: 243、シリアル番号: 56557
・セッション3(処理確定)
SQL> UPDATE TEST SET A = 6 WHERE A = 1;
SQL> COMMIT;
コミットが完了しました。
●7.セッション1,2切断、セッション3commit後、ロック状態確認SQLを実行
SQL> SELECT A.SID SID,
2 A.USERNAME USERNAME,
3 A.SERIAL# SERIALNO,
4 A.SCHEMANAME SCHEMANAME,
5 A.MACHINE MACHINE,
6 B.TYPE,
7 B.BLOCK BLOCK,
8 A.PROGRAM PROGRAM,
9 A.PROCESS CLIENT,
10 TO_CHAR(B.CTIME/60,'999990.9') LOCK_TIME
11 FROM V$SESSION A,
12 V$LOCK B
13 WHERE A.SID = B.SID
14 AND B.TYPE IN ('TX')
15 ORDER BY LOCK_TIME DESC;
レコードが選択されませんでした。
CLIENT列の値を特定し、タスクマネージャから切断することも可能です。
