【Oracle】ORA-02049の原因と解決方法｜timeout: distributed transaction waiting for lock・分散トランザクションのロック待ち

SHOW PARAMETER distributed_lock_timeout;

SELECT name,
       value,
       isdefault,
       issys_modifiable
FROM v$parameter
WHERE name = 'distributed_lock_timeout';

SELECT db_link,
       owner_id,
       logged_on,
       heterogeneous,
       protocol,
       open_cursors,
       in_transaction
FROM v$dblink
ORDER BY db_link;

SELECT owner,
       db_link,
       username,
       host,
       created
FROM dba_db_links
WHERE db_link LIKE UPPER('%TARGET%')
ORDER BY owner, db_link;

SELECT sid,
       serial#,
       username,
       machine,
       program,
       status,
       event,
       blocking_session,
       seconds_in_wait,
       sql_id
FROM v$session
WHERE username IS NOT NULL
ORDER BY seconds_in_wait DESC;

SELECT s.sid,
       s.serial#,
       s.username,
       s.sql_id,
       q.sql_text
FROM v$session s
JOIN v$sql q
  ON q.sql_id = s.sql_id
WHERE q.sql_text LIKE '%@%'
ORDER BY s.sid;

SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       l.type,
       l.id1,
       l.id2,
       l.lmode,
       l.request,
       l.block
FROM v$lock l
JOIN v$session s
  ON s.sid = l.sid
WHERE l.request > 0 OR l.block > 0
ORDER BY l.block DESC, s.sid;

-- DBリンク先DBで実行する
SELECT sid,
       serial#,
       username,
       machine,
       program,
       module,
       status,
       event,
       blocking_session,
       last_call_et,
       sql_id
FROM v$session
WHERE username IS NOT NULL
ORDER BY last_call_et DESC;

-- DBリンク先DBで実行する
SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       o.owner,
       o.object_name,
       o.object_type,
       lo.locked_mode
FROM v$locked_object lo
JOIN dba_objects o
  ON o.object_id = lo.object_id
JOIN v$session s
  ON s.sid = lo.session_id
ORDER BY s.last_call_et DESC;

SELECT local_tran_id,
       global_tran_id,
       state,
       mixed,
       advice,
       tran_comment,
       fail_time,
       force_time,
       retry_time
FROM dba_2pc_pending
ORDER BY fail_time DESC;

SELECT local_tran_id,
       in_out,
       database,
       dbuser_owner,
       interface
FROM dba_2pc_neighbors
ORDER BY local_tran_id, in_out;

SELECT p.local_tran_id,
       p.state,
       p.mixed,
       p.fail_time,
       n.in_out,
       n.database,
       n.dbuser_owner,
       n.interface
FROM dba_2pc_pending p
LEFT JOIN dba_2pc_neighbors n
  ON n.local_tran_id = p.local_tran_id
ORDER BY p.fail_time DESC, n.in_out;

-- 例: 運用手順で明確に判断できる場合だけ実行する
-- COMMIT FORCE 'local_tran_id';
-- ROLLBACK FORCE 'local_tran_id';

-- 実行後はDBA_2PC_PENDINGで状態を再確認する
SELECT local_tran_id, state, mixed, force_time
FROM dba_2pc_pending
ORDER BY fail_time DESC;

ORA-02049発生時の考え方

1. 現在のトランザクションをROLLBACKする
2. 短い待機を入れる
3. 同じ処理を再試行する
4. 再試行回数を超えたら業務エラーとして扱う
5. ログにローカルDB、DBリンク名、SQL ID、対象キーを残す

悪い例:
  処理A: ローカル表 -> リモート表@DBLINK
  処理B: リモート表@DBLINK -> ローカル表

改善例:
  処理A: ローカル表 -> リモート表@DBLINK
  処理B: ローカル表 -> リモート表@DBLINK

同じ順序でロックを取ることで、相互待ちやタイムアウトを減らす。