1. ホーム
  2. データベース
  3. 神託

Oracleデッドロック検出クエリとその処理

2022-01-07 19:01:41

デッドロックのクエリ文です。

-- Deadlock query statement
SELECT bs.username "Blocking User", bs.username "DB User",
          ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
          bs.serial# "Serial#", bs.sql_address "address",
          bs.sql_hash_value "Sql hash", bs.program "Blocking App",
          ws.program "Waiting App", bs.machine "Blocking Machine",
          ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
          ws.osuser "Waiting OS User", bs.serial# "Serial#",
          ws.serial# "WSerial#",
          DECODE (wk.TYPE,
                  'MR', 'Media Recovery',
                  'RT', 'Redo Thread',
                  'UN', 'USER Name',
                  'TX', 'Transaction',
                  'TM', 'DML',
                  'UL', 'PL/SQL USER LOCK',
                  'DX', 'Distributed Xaction',
                  'CF', 'Control FILE',
                  'IS', 'Instance State',
                  'FS', 'FILE SET',
                  'IR', 'Instance Recovery',
                  'ST', 'Disk SPACE Transaction',
                  'TS', 'Temp Segment',
                  'IV', 'Library Cache Invalidation',
                  'LS', 'LOG START OR Switch',
                  'RW', 'ROW Wait',
                  'SQ', 'Sequence Number',
                  'TE', 'Extend TABLE',
                  'TT', 'Temp TABLE',
                  wk.TYPE
                 ) lock_type,
          DECODE (hk.lmode,
                  0, 'None',
                  1, 'NULL',
                  2, 'ROW-S (SS)',
                  3, 'ROW-X (SX)',
                  4, 'SHARE',
                  5, 'S/ROW-X (SSX)',
                  6, 'EXCLUSIVE',
                  TO_CHAR (hk.lmode)
                 ) mode_held,
          DECODE (wk.request,
                  0, 'None',
                  1, 'NULL',
                  2, 'ROW-S (SS)',
                  3, 'ROW-X (SX)',
                  4, 'SHARE',
                  5, 'S/ROW-X (SSX)',
                  6, 'EXCLUSIVE',
                  TO_CHAR (wk.request)
                 ) mode_requested,
          TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
          DECODE
             (hk.BLOCK,
              0, 'NOT Blocking', /**//* Not blocking any other processes */
              1, 'Blocking', /**/ /* This lock blocks other processes */
              2, 'Global', /**/ /* This lock is global, so we can't tell */
              TO_CHAR (hk.BLOCK)
             ) blocking_others
     FROM v$lock hk, v$session bs, v$lock wk, v$session ws
    WHERE hk.BLOCK = 1
      AND hk.lmode ! = 0
      AND hk.lmode ! = 1
      AND wk.request ! = 0
      AND wk.TYPE(+) = hk.TYPE
      AND wk.id1(+) = hk.id1
      AND wk.id2(+) = hk.id2
      AND hk.SID = bs.SID(+)
      AND wk.SID = ws.SID(+)
      AND (bs.username IS NOT NULL)
      AND (bs.username <> 'SYSTEM')
      AND (bs.username <> 'SYS')
ORDER BY 1;




デッドロックが発生したselect文のクエリ

select sql_text from v$sql where hash_value in (
    select sql_hash_value from v$session where sid in (select session_id from v$locked_object)
)
 



データベースのデッドロックのチェック方法

1、データベースのデッドロックの現象

実行中にOKボタンや保存ボタンをクリックしても、プログラムが応答せず、エラーも報告されない。

2. デッドロックの仕組み

ときに、列のテーブルのデータベースは、ステートメントの実行をコミットされない後に更新または削除操作を行うには、データのこの列の別のステートメントの実行で更新操作を行うには待機状態になります、現象は、ステートメントが実行されているが、正常に実行されていない、とエラーが報告されません。

3. デッドロックの場所を特定する方法

データベースのテーブルを調べることで、どのステートメントがデッドロックしているか、どのマシンがデッドロックを発生させているかを確認することができます。

 1) dbaユーザーで次のステートメントを実行します。

select username,lockwait,status,machine,program from v$session where sid 
in (select session_id from v$locked_object)




出力がある場合は、デッドロックが発生しており、どのマシンでデッドロックが発生しているかがわかります。フィールドの説明です。
Username. デッドロック声明に使用されたデータベースユーザー。
Lockwait. デッドロックの状態。コンテンツがある場合はデッドロック状態であることを意味する。
Status : ステータス、アクティブはデッドロックの意味
Machine : デッドロック文があるマシン。

Program

2) dbaユーザーで以下の文を実行し、どの文がデッドロックしているかを確認します。

select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))



4. デッドロックの解消

     通常は、デッドロックが発生したステートメントをコミットすれば良いのですが、実際の実行プロセスでは。どのステートメントがデッドロックを発生させているのか、ユーザーにはわからない場合があります。一旦プログラムを終了し、再起動することができます。私もOracleを使っている過程でよくこの問題に遭遇するので、ちょっとした解決策もまとめてみました。

 1) デッドロックが発生しているプロセスを見つける。

sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S 
WHERE l.SESSION_ID=S.SID;




2) デッドロックしたプロセスを終了させる。

alter system kill session 'sid,serial#'; (where sid=l.session_id)



 3)それでも直らない場合

select pro.spid from v$session ses, v$process pro where ses.sid=XX and ses.paddr=pro.addr;



ここで、sid はデッドロックの sid と置き換えられる。

       exit
       ps -ef|grep spid



 spid がこのプロセスのプロセス番号の場合、これを kill します。 Oracle プロセスです。

今回の記事は、Oracle Deadlock Detection and Processingについてです。Oracle Deadlock Detection and Processingの詳細については、Script Houseの過去記事を検索するか、引き続き以下の関連記事を参照してください。