ORA-00055: maximum number of DML locks exceeded 완벽 해결 가이드
ORA-00055란?
ORA-00055는 Oracle 데이터베이스에서 DML(Data Manipulation Language) 락(Lock)의 최대 허용 개수를 초과했을 때 발생하는 에러입니다. Oracle은 INSERT, UPDATE, DELETE, MERGE 등의 DML 작업 시 내부적으로 테이블 수준의 락(enqueue)을 획득하는데, 이 락의 총 개수가 초기화 파라미터 DML_LOCKS에서 정의한 한계치를 넘어서면 이 에러가 발생합니다. 주로 대규모 트랜잭션이 동시에 다수 발생하거나, 수많은 테이블에 걸친 복잡한 배치 작업이 수행될 때 현업 환경에서 종종 마주치게 되는 에러입니다.
주요 발생 원인
1. DML_LOCKS 파라미터 값이 너무 낮게 설정된 경우
DML_LOCKS 파라미터는 데이터베이스 전체에서 동시에 보유할 수 있는 DML 락의 최대 수를 결정합니다. 기본값은 4 * TRANSACTIONS 수식으로 자동 계산되지만, 수동으로 낮게 설정하거나 TRANSACTIONS 파라미터 자체가 실제 동시 접속 수에 비해 과소 산정된 경우 쉽게 한계에 도달합니다. 특히 레거시 시스템을 현대화하거나 사용자 수가 급증한 환경에서 파라미터를 재검토하지 않으면 이 문제가 빈번하게 발생합니다.
2. 하나의 트랜잭션에서 지나치게 많은 테이블에 DML을 수행하는 경우
Oracle은 하나의 트랜잭션 내에서 DML이 수행되는 각 테이블마다 별도의 테이블 레벨 락(TM Lock)을 획득합니다. 수십~수백 개의 테이블을 한 트랜잭션 내에서 처리하는 대형 배치 프로그램이나, 복잡한 연쇄 트리거(Cascading Trigger)가 다수의 테이블을 연속으로 수정하는 구조에서는 단일 세션만으로도 락 자원을 대량으로 소모할 수 있습니다. 애플리케이션 설계 시 트랜잭션 범위를 명확히 정의하지 않으면 이러한 문제가 누적됩니다.
3. 트랜잭션이 COMMIT/ROLLBACK 없이 장시간 열려 있는 경우
트랜잭션이 종료되지 않으면 해당 트랜잭션이 보유한 DML 락도 해제되지 않습니다. 애플리케이션 로직 오류, 네트워크 단절 후 세션이 정리되지 않은 상태(Inactive 세션), 또는 명시적 COMMIT 누락 등으로 인해 다수의 열린 트랜잭션이 쌓이면 전체 시스템의 DML 락 자원이 고갈됩니다. 장시간 수행되는 리포팅 쿼리와 DML이 혼재하는 OLTP 환경에서 특히 주의해야 합니다.
해결 방법
해결 1: 현재 DML_LOCKS 상태 확인 및 파라미터 증가
먼저 현재 파라미터 값과 실제 락 사용 현황을 확인합니다.
-- 현재 DML_LOCKS 파라미터 값 확인
SHOW PARAMETER DML_LOCKS;
-- 또는 V$PARAMETER 뷰로 확인
SELECT name, value, description
FROM v$parameter
WHERE name IN ('dml_locks', 'transactions');
-- 현재 시스템에서 사용 중인 DML 락 현황 확인
SELECT COUNT(*) AS current_dml_lock_count
FROM v$lock
WHERE type = 'TM';
-- 어느 세션이 TM 락을 많이 보유하고 있는지 확인
SELECT s.sid,
s.serial#,
s.username,
s.status,
s.program,
COUNT(l.type) AS tm_lock_count
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
WHERE l.type = 'TM'
GROUP BY s.sid, s.serial#, s.username, s.status, s.program
ORDER BY tm_lock_count DESC;
문제가 확인되면 파라미터를 조정합니다. DML_LOCKS는 동적 파라미터가 아니므로 반드시 재시작이 필요합니다.
-- DML_LOCKS 파라미터 증가 (spfile 적용, 재시작 필요)
-- 권장: 현재 TRANSACTIONS 값의 4~6배로 설정
ALTER SYSTEM SET DML_LOCKS = 10000 SCOPE = SPFILE;
-- TRANSACTIONS 파라미터도 함께 검토
ALTER SYSTEM SET TRANSACTIONS = 2500 SCOPE = SPFILE;
-- 변경 후 데이터베이스 재시작
-- SHUTDOWN IMMEDIATE;
-- STARTUP;
-- 재시작 후 적용 확인
SHOW PARAMETER DML_LOCKS;
SHOW PARAMETER TRANSACTIONS;
⚠️ 주의:
DML_LOCKS = 0으로 설정하면 DML 락 기능 자체를 비활성화할 수 있지만, 이는 분산 트랜잭션 환경이나 특수한 경우에만 사용해야 하며 데이터 무결성 리스크가 있으므로 운영 환경에서는 권장하지 않습니다.
해결 2: 장시간 열린 트랜잭션(미종료 세션) 탐지 및 정리
-- 오랫동안 활성화된 트랜잭션 보유 세션 조회
SELECT s.sid,
s.serial#,
s.username,
s.status,
s.last_call_et AS elapsed_sec,
t.used_ublk AS undo_blocks_used,
t.start_time AS tx_start_time,
s.program,
s.machine,
s.sql_id
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
ORDER BY t.start_time ASC;
-- 특정 세션이 보유한 락 상세 조회
SELECT l.sid,
l.type,
l.lmode,
l.request,
o.object_name,
o.object_type
FROM v$lock l
JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.type = 'TM'
AND l.sid = :target_sid -- 문제 세션 SID 입력
ORDER BY o.object_name;
-- 문제 세션 강제 종료 (DBA 권한 필요)
-- 반드시 애플리케이션팀과 협의 후 수행할 것
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;
-- 예시: SID=245, SERIAL#=3921인 세션 종료
ALTER SYSTEM KILL SESSION '245,3921' IMMEDIATE;
해결 3: 대형 배치 트랜잭션을 분할하여 처리
하나의 트랜잭션에서 너무 많은 테이블을 처리하는 배치 작업을 분리합니다.
-- 나쁜 예: 수백 개 테이블을 하나의 트랜잭션으로 처리
BEGIN
UPDATE table_001 SET status = 'Y' WHERE batch_date = TRUNC(SYSDATE);
UPDATE table_002 SET status = 'Y' WHERE batch_date = TRUNC(SYSDATE);
-- ... table_003 ~ table_200 까지 계속 ...
UPDATE table_200 SET status = 'Y' WHERE batch_date = TRUNC(SYSDATE);
COMMIT; -- 한 번에 COMMIT → 200개 TM 락 동시 보유
END;
/
-- 좋은 예: 일정 단위로 COMMIT을 분산
DECLARE
v_commit_interval NUMBER := 10; -- 10개 테이블마다 COMMIT
v_counter NUMBER := 0;
BEGIN
FOR rec IN (
SELECT table_name
FROM user_tables
WHERE table_name LIKE 'TABLE_%'
ORDER BY table_name
) LOOP
EXECUTE IMMEDIATE
'UPDATE ' || rec.table_name ||
' SET status = ''Y'' WHERE batch_date = TRUNC(SYSDATE)';
v_counter := v_counter + 1;
IF MOD(v_counter, v_commit_interval) = 0 THEN
COMMIT; -- 주기적 COMMIT으로 TM 락 해제
END IF;
END LOOP;
COMMIT; -- 나머지 처리
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
해결 4: 트리거 내부의 불필요한 DML 최소화
-- 문제가 되는 트리거 패턴: 너무 많은 테이블에 연쇄 DML
CREATE OR REPLACE TRIGGER trg_order_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- 여러 테이블에 동시 DML → 각 테이블마다 TM 락 추가 발생
INSERT INTO order_log VALUES (:NEW.order_id, SYSDATE, 'INSERT');
UPDATE inventory SET qty = qty - :NEW.qty WHERE product_id = :NEW.product_id;
UPDATE customer_summary SET order_count = order_count + 1 WHERE cust_id = :NEW.cust_id;
UPDATE daily_stats SET total_orders = total_orders + 1 WHERE stat_date = TRUNC(SYSDATE);
-- ... 추가 DML이 많을수록 위험
END;
/
-- 개선 방안: 트리거에서는 최소한의 DML만 수행하고
-- 나머지는 별도 배치 또는 비동기 처리로 이관
CREATE OR REPLACE TRIGGER trg_order_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- 트리거에서는 핵심 로그만 기록
INSERT INTO order_log VALUES (:NEW.order_id, SYSDATE, 'INSERT');
-- inventory, stats 업데이트는 별도 배치 잡으로 분리
END;
/
예방 방법
1. DML_LOCKS 파라미터 정기 모니터링 및 용량 계획 수립
운영 환경에서 DML 락 사용량을 주기적으로 수집하여 임계치 도달 전에 선제적으로 파라미터를 조정하는 프로세스를 마련해야 합니다. 아래 쿼리를 일별 배치로 실행하여 락 사용 추세를 지속적으로 추적하세요.
-- DML 락 사용률 모니터링 쿼리 (일별 배치 또는 알림 스크립트로 활용)
SELECT p.value AS dml_locks_limit,
COUNT(l.type) AS current_usage,
ROUND(COUNT(l.type) / p.value * 100, 2) AS usage_pct,
CASE
WHEN ROUND(COUNT(l.type) / p.value * 100, 2) >= 80
THEN '⚠️ 경고: DML_LOCKS 80% 초과 - 즉시 검토 필요'
WHEN ROUND(COUNT(l.type) / p.value * 100, 2) >= 60
THEN '주의: DML_LOCKS 60% 초과'
ELSE '정상'
END AS status_msg
FROM v$parameter p,
v$lock l
WHERE p.name = 'dml_locks'
AND l.type(+) = 'TM'
GROUP BY p.value;
사용률이 60%를 넘으면 검토, 80%를 넘으면 즉각 파라미터 증가를 고려하는 운영 기준을 팀 내에 수립하길 권장합니다.
2. 애플리케이션 트랜잭션 설계 원칙 수립 및 코드 리뷰 프로세스 강화
ORA-00055의 근본적인 예방은 개발 단계에서 트랜잭션 범위를 적절히 제어하는 것입니다. 개발 팀에 다음 원칙을 가이드라인으로 배포하세요.
sql
-- 트랜잭션 범위 검증 쿼리 (개발/테스트 단계에서 실행)
-- 특정 세션이 현재 보유 중인 TM 락 수 확인
SELECT s.sid,
s.username,
s.program,
COUNT(*) AS tm_lock_count,
LISTAGG(o.object_name, ', ')
WITHIN GROUP (ORDER BY o
Top comments (0)