[트러블슈팅][Oracle] DB Transaction Lock 시 킬 세션 커맨드

안되는 이유는 모르겠고 되는 이유는 더더욱 모르겠을 때

---

 

문제 상황

개발 과정에서 DB 클라이언트나, 웹에서 POST 요청을 통해 DB 업데이트 DML문을 수행하던 중,
클라이언트 DB IO가 정상적으로 수행되지 않고, 웹 응답도 지연되는 문제가 발생했다.
즉, 사용자가 데이터를 전송했지만, 서버에서 처리가 멈춘 상태가 지속되었다.

 

원인 분석

조사 결과, 특정 트랜잭션이 락에 걸려 있었던 것이 문제였다.

그 중에서도, DB 행 단위 배타적 잠금(Row-X, LOCKED_MODE=3)을 잡고 있어

다른 트랜잭션이 대기 상태에 빠진 것이 원인이었다.

 

• 롱 트랜잭션 또는 커밋이 지연되는 트랜잭션이 Row-X 잠금을 장시간 점유
• 이로 인해 다른 세션의 UPDATE, DELETE, SELECT FOR UPDATE 요청이 무한 대기 상태가 됨

 

 

DB 트랜잭션 락이란?

DB 트랜잭션 락은 동시에 여러 트랜잭션이 동일 데이터에 접근할 때 무결성을 유지하기 위해 걸리는 잠금이다.

 

읽기/쓰기 충돌을 방지하고, 데이터 일관성을 확보하기 위한 장치이다.

잠금 방식과 범위에 따라 행·테이블 잠금· 공유/배타적 잠금 등 다양한 모드가 존재한다.

 

 

잠금 모드(Oracle 기준)

0	잠금 없음
1	Null – 공유 읽기 정도, 거의 영향 없음
2	Row-S (SS) – 다른 트랜잭션도 읽기 가능
3	Row-X (SX) – 행 단위 배타적 잠금, 다른 트랜잭션 수정 불가
4	TX – 트랜잭션 잠금, DML 수행 시 유지
5~6	Table 잠금 – DDL 수행 시 필요

 

일반적으로 UPDATE, DELETE, SELECT FOR UPDATE 같은 행 수정 시 Row-X 잠금이 걸리며, 트랜잭션이 오래 열려 있으면 다른 트랜잭션이 대기 상태가 되는 것이 핵심 원인이다.

 

 

그렇다면, 왜 롱 트랜잭션이 걸렸는가

JDBC 코드에서 디버거가 commit 이전에 라인 브레이크를 잡고 있었다.

 

회사 동료분이 해당 로우에 대한 업데이트 로직을 테스트하고 있었는데,

마침 라인브레이크를 걸어놓은 상태여서 해당 테이블, ROW에 접근이 안되는 것이었다... 

 

JDBC로 제어하는 트랜잭션의 특징

• 트랜잭션은 DB 커밋/롤백 시점까지 유지됨
  • JDBC에서 autoCommit=false로 트랜잭션 시작 시, commit() 또는 rollback() 호출 전까지 트랜잭션이 열려 있는 상태
  • 디버거에서 코드가 멈춰 있으면, 그 시점까지 트랜잭션과 Row-X 잠금이 그대로 유지
• 디버거가 실행 흐름을 멈춤
  • 디버그 브레이크포인트에서 코드가 멈춰 있으면, DB는 해당 트랜잭션이 완료되기를 기다림
  • 따라서 다른 세션에서 동일 행을 수정하려고 하면 pending 상태가 발생

 

 

대충 아래와 같은 소스 레벨에서의 문제였던 것이다.

conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(
    "UPDATE orders SET status=? WHERE id=?"
);
ps.setString(1, "PROCESSING");
ps.setInt(2, 123);
ps.executeUpdate();

// 디버거에서 여기서 브레이크포인트 걸면...
// commit() 호출 전까지 Row-X 잠금 유지
conn.commit();

 

 

해결 방법 (with kill session command)

-- For Oracle, CMD는 KILL SESSION 동적 쿼리
-- TABLE_NAME은 DB 락을 잡고 있는 단계

SELECT 
'ALTER SYSTEM KILL SESSION ' || '''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS CMD,
s.sid, s.serial#,
s.username, 
s.osuser, 
s.machine, 
l.locked_mode,
o.object_name 
FROM v$locked_object l 
JOIN v$session s 
ON l.session_id = s.sid 
JOIN all_objects o 
ON l.object_id = o.object_id 
WHERE o.object_name='TABLE_NAME';

 

위 SQL을 사용해 어떤 세션이 테이블 또는 행을 점유하고 있는지 확인한다.

CMD 절에 있는 KILL SESSION 절을 SQL ADMIN에서 실행해 세션을 강제종료한다.

단, 롱 트랜잭션을 강제 종료하면 해당 트랜잭션의 변경 내용이 롤백됨을 유의한다.

 

-- For Oracle, KILL SESSION DDL
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;