Oracle Locking Mechanism(잠금 메커니즘) 분석 및 해결 방법

Oracle Locking Mechanism(잠금 메커니즘) 분석 및 해결 방법

데이터베이스 성능 최적화를 위한 필수 개념

1. Oracle Locking Mechanism 개요

Oracle 데이터베이스에서는 데이터의 일관성과 무결성을 유지하기 위해 다양한 잠금(Lock) 메커니즘을 제공합니다.

잠금은 트랜잭션이 동시에 실행될 때 충돌을 방지하고, 데이터의 정합성을 보장하는 중요한 기능입니다.

2. Oracle 잠금의 주요 유형

2.1 Row-Level Lock (행 수준 잠금)

특정 행(Row)에 대한 변경을 수행할 때 발생하는 잠금으로, 데이터의 동시성을 극대화하는 방식입니다.

UPDATE employees 
SET salary = salary * 1.1 
WHERE employee_id = 101;
        

이 SQL이 실행되면 employee_id가 101인 레코드에 대해 Row-Level Lock이 설정됩니다.

2.2 Table-Level Lock (테이블 수준 잠금)

테이블 전체에 대해 잠금을 거는 방식으로, 일반적으로 DDL(데이터 정의어) 작업에서 발생합니다.

LOCK TABLE employees IN EXCLUSIVE MODE;
        

이 명령어를 실행하면 다른 트랜잭션이 해당 테이블의 데이터를 수정할 수 없게 됩니다.

2.3 Shared Lock (공유 잠금)

읽기 작업(SELECT) 중에 다른 사용자가 데이터를 수정하지 못하도록 하는 잠금 방식입니다.

SELECT * FROM employees FOR UPDATE;
        

이 쿼리를 실행하면 employees 테이블에서 다른 사용자가 해당 데이터를 수정할 수 없습니다.

3. Oracle Deadlock(교착 상태) 분석

Deadlock(교착 상태)은 두 개 이상의 트랜잭션이 서로가 가진 자원을 기다리면서 영원히 종료되지 않는 상태를 말합니다.

3.1 Deadlock 발생 예제

-- 첫 번째 세션
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101;
UPDATE departments SET budget = budget - 5000 WHERE department_id = 10;

-- 두 번째 세션 (첫 번째 트랜잭션과 교착 상태 발생)
UPDATE departments SET budget = budget - 5000 WHERE department_id = 10;
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101;
        

위 예제에서는 두 개의 트랜잭션이 서로가 필요한 리소스를 가지고 있어 Deadlock이 발생합니다.

3.2 Deadlock 해결 방법

Deadlock을 해결하는 방법은 다음과 같습니다.

• 트랜잭션 실행 순서 표준화: 특정 순서로 트랜잭션을 실행하도록 규칙을 정합니다.
• 잠금 시간 제한: Oracle의 `WAIT` 옵션을 활용하여 일정 시간이 지나면 자동으로 트랜잭션을 종료합니다.
• Deadlock 로그 확인: Oracle의 `v$locked_object` 및 `v$session` 뷰를 활용하여 잠긴 객체를 확인합니다.

SELECT * FROM v$locked_object;
SELECT * FROM v$session WHERE blocking_session IS NOT NULL;
        

4. 잠금 해제 및 해결 방법

4.1 특정 세션의 잠금 해제

잠금이 걸린 세션을 확인한 후, 특정 세션을 종료하여 강제로 잠금을 해제할 수 있습니다.

SELECT sid, serial#, blocking_session 
FROM v$session 
WHERE blocking_session IS NOT NULL;
        

이제 특정 세션을 종료합니다.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
        

4.2 DDL을 통한 강제 잠금 해제

만약 특정 테이블이 계속 잠겨 있다면 `ALTER TABLE` 문을 이용하여 강제로 해제할 수 있습니다.

ALTER SYSTEM SET ddl_lock_timeout = 10;
        

5. 결론

Oracle 데이터베이스의 Locking Mechanism(잠금 메커니즘)은 데이터 일관성을 유지하는 데 필수적인 요소입니다.

하지만 과도한 잠금이나 Deadlock이 발생하면 시스템 성능이 저하될 수 있으므로 적절한 모니터링과 튜닝이 필요합니다.

이 글에서 소개한 방법을 활용하여 효율적인 데이터베이스 운영을 수행하시기 바랍니다.