テーブルをロックしているセッションを特定する方法

この記事ではテーブルをロックしているセッションを特定する方法について解説します。

 テーブルをロックしているセッションを特定するSQL

Oracle DatabaseではトランザクションやDDL操作によってテーブルがロックされ、他のセッションからの操作を受け付けなくなります。

通常は短時間でロックが解除されますが、ロックが長時間解除されないとトランザクションが一向に進まなくなってしまいます。

そういった場合はロックをかけているセッションを特定し、そのセッションをkillすることでロックを解除できます。

ロックをかけているセッションを特定するには以下のSQLを実行します。

SELECT
  object_name,
  oracle_username,
  s.sid,
  s.serial#,
  to_char(s.logon_time,'YYYY/MM/DD HH24:mi:SS DAY') as logon_time,
  s.program,
  l.locked_mode
FROM v$locked_object l,
  dba_objects o,
  v$session s
WHERE l.OBJECT_ID = o.OBJECT_ID
  AND l.SESSION_ID = s.SID
;

oracle_usernameはロックをかけているセッションのユーザが該当します。

locked_modeは以下の通り番号と対応します。

・0 - NONE: ロックが要求されたが、まだ取得されていない

・1 - NULL

・2 - ROWS_S (SS): 行共有ロック

・3 - ROW_X (SX): 行排他表ロック

・4 - SHARE (S): 共有表ロック

・5 - S/ROW-X (SSX): 共有行排他表ロック

・6 - Exclusive (X): 排他表ロック


 実行例

EMPテーブルに対してロックをかけ、そのセッションを特定し、killしてみます。

SYSユーザで接続し、EMP表に対してロックをかけます。

ロックはLOCK TABLEコマンドでかけることができます。

SQL> LOCK TABLE TEST.EMP IN EXCLUSIVE MODE;
表がロックされました。

別のセッションでSYSユーザで接続し、ロックの状況を確認します。

SQL> SELECT 
  object_name,
  oracle_username,
  s.sid,
  s.serial#,
  to_char(s.logon_time,'YYYY/MM/DD HH24:mi:SS DAY') as logon_time,
  s.program,
  l.locked_mode
FROM v$locked_object l,
  dba_objects o,
  v$session s
WHERE l.OBJECT_ID = o.OBJECT_ID
  AND l.SESSION_ID = s.SID
;
OBJECT_NAME ORACLE_USERNAME  SID  SERIAL# LOGON_TIME		     PROGRAM					LOCKED_MODE
----------- --------------- ---- -------- -------------------------- ----------------------------------------- ------------
EMP         SYS               38    38830 2024/11/07 00:44:25 木曜日 sqlplus@localhost.localdomain (TNS V1-V3)            6

SYSユーザによってEMP表に対してsqlplusからロックがかけられていることがわかります。

このSIDとSERIAL#を使用してセッションをkillします。

セッションのkillは以下のコマンドで行います。

ALTER SYSTEM kill session '<sid>, <serial#>';

SIDを38、SERIAL#を38830にしてコマンドを実行します。

SQL> ALTER SYSTEM kill session '38, 38830';
システムが変更されました。

ロックが解除されたか確認します。

SQL> SELECT 
  object_name,
  oracle_username,
  s.sid,
  s.serial#,
  to_char(s.logon_time,'YYYY/MM/DD HH24:mi:SS DAY') as logon_time,
  s.program,
  l.locked_mode
FROM v$locked_object l,
  dba_objects o,
  v$session s
WHERE l.OBJECT_ID = o.OBJECT_ID
  AND l.SESSION_ID = s.SID
;  2    3    4    5    6    7    8    9   10   11   12   13   14  
レコードが選択されませんでした。

ロックが解除されてレコードが無くなりました。

Labels: