오라클 LOCK 완벽 가이드: DML, DDL 잠금 유형부터 해결 방법까지

오라클 LOCK의 모든 것: 개념, 사용 방법, 예시까지

데이터베이스에서 다중 사용자가 동시에 접근해 데이터를 수정하는 경우, 데이터의 무결성과 안정성을 보장하는 것이 중요합니다. 오라클(Oracle)에서는 이러한 상황에서 LOCK을 사용하여 데이터를 보호합니다. 이번 포스팅에서는 오라클의 LOCK 개념부터 각 잠금 유형, 그리고 실무에서 활용할 수 있는 예시와 해결 방법까지 자세히 다뤄보겠습니다.

---

오라클 LOCK이란?

오라클 LOCK은 여러 사용자가 동시에 데이터를 수정하는 경우 발생할 수 있는 데이터 충돌을 방지하기 위해 사용됩니다. LOCK은 크게 DML LOCK과 DDL LOCK으로 구분되며, 각각의 역할이 다릅니다.

---

오라클의 주요 LOCK 유형

1) DML LOCK

• DML Lock은 다중 사용자가 동시에 데이터에 접근하더라도 데이터의 무결성을 유지하기 위해 사용됩니다. 예를 들어, INSERT, UPDATE, DELETE 문이 실행될 때 데이터에 LOCK이 걸립니다.
  • 테이블 레벨 잠금 (TM): 테이블을 대상으로 한 잠금으로, 다른 트랜잭션이 같은 테이블에서 DDL 작업을 수행하는 것을 방지합니다.
  • 행 레벨 잠금 (TX): 특정 행을 대상으로 한 잠금으로, 다른 사용자가 같은 행을 동시에 수정하지 못하도록 합니다.

예시:

-- 테이블 레벨 잠금 예시
SQL > UPDATE employees SET salary = salary * 1.1;
100 rows updated.

SQL > DROP TABLE employees;
-- 오류 발생: 테이블이 다른 트랜잭션에 의해 잠겨 있음.

2) DDL LOCK

• DDL Lock은 스키마 객체의 정의를 보호하기 위해 사용되며, 객체가 참조되고 있을 때 다른 트랜잭션이 수정하지 못하도록 합니다.

---

테이블 잠금 모드(Table Lock Mode)

오라클에는 테이블 잠금 모드로 RX, RS, SRX, X가 있습니다. 각 모드는 특정 상황에서 잠금의 정도를 설정해 다른 트랜잭션이 테이블에 접근할 수 있는 방법을 제한합니다.

• 행 독점 (RX): 다른 트랜잭션이 테이블의 특정 행을 동시에 읽거나 수정하는 것을 허용합니다.
• 행 공유 (RS): 테이블의 다른 행에 대한 SELECT...FOR UPDATE를 허용합니다.
• 공유 행 독점 (SRX): DML 작업을 차단하는 높은 수준의 잠금입니다.
• 독점 (X): 모든 다른 트랜잭션이 테이블에 접근하지 못하도록 제한합니다.

---

오라클 LOCK의 상태 확인 및 해제 방법

현재 LOCK 상태 확인

특정 테이블이나 트랜잭션의 상태를 조회할 수 있습니다. 예를 들어, V$LOCK 뷰를 사용해 시스템에서 LOCK의 상태를 확인할 수 있습니다.

SELECT s.username, s.sid, l.type, l.id1, l.id2, l.lmode, l.request, p.spid AS PID
FROM v$lock l, v$session s, v$process p
WHERE s.sid = l.sid 
  AND p.addr = s.paddr 
  AND s.username IS NOT NULL
ORDER BY id1, s.sid, request;

테이블에 걸린 락 해제 방법

LOCK 상태가 해결되지 않는 경우, 특정 트랜잭션을 종료하여 문제를 해결할 수 있습니다. 아래 쿼리를 사용하여 특정 세션의 잠금을 해제할 수 있습니다.

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

예시:

ALTER SYSTEM KILL SESSION '58, 6015';

 

이와 같이 오라클에서는 다양한 잠금 모드와 명령어를 통해 데이터베이스의 무결성을 보장하고, 데이터 충돌을 예방할 수 있습니다.

---

데드락(Deadlock) 처리

데드락은 두 개 이상의 트랜잭션이 서로의 리소스를 기다리면서 영원히 대기하게 되는 상태입니다. 오라클 서버는 데드락을 감지하면, 충돌을 일으킨 특정 문장만 롤백하고 트랜잭션 전체를 롤백하지는 않습니다.

데드락 예시:

-- 트랜잭션 1
UPDATE employees SET salary = salary * 1.1 WHERE emp_id = 24877;

-- 트랜잭션 2
UPDATE employees SET salary = salary * 1.1 WHERE emp_id = 24876;

두 트랜잭션이 서로의 리소스를 기다리는 상황이 발생하여 데드락이 발생하게 됩니다. 이 경우, 오라클 서버는 ORA-00060 오류 메시지를 반환하고 데드락을 해결합니다.

---

LOCK 관리 관련 SQL 예시

오라클에서 제공하는 다양한 LOCK 관리 SQL 문을 활용하면 현재 세션의 상태를 보다 쉽게 모니터링하고, 필요한 경우 잠금을 해제하여 시스템이 원활히 작동할 수 있도록 할 수 있습니다.

세션 락 조회

SELECT a.session_id, b.serial# AS serial_no, a.os_user_name, a.oracle_username, b.status
FROM v$locked_object a, v$session b
WHERE a.session_id = b.sid;

락 검사

SELECT a.sid,
  DECODE(a.type,
    'MR', 'Media Recovery',
    'TX', 'Transaction',
    'TM', 'DML') AS LOCK_TYPE,
  DECODE(a.lmode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive') AS MODE_HELD,
  DECODE(a.request,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive') AS MODE_REQUESTED,
  TO_CHAR(a.id1) AS LOCK_ID1,
  TO_CHAR(a.id2) AS LOCK_ID2
FROM v$lock a
WHERE (id1, id2) IN (SELECT b.id1, b.id2 FROM v$lock b WHERE b.request > 0);

 

오라클 데이터베이스의 다양한 잠금 유형과 관리 방법을 이해하면, 실무에서 더욱 안정적이고 효율적인 데이터 관리를 할 수 있습니다. 필요한 경우 SQL을 사용하여 LOCK 상태를 직접 관리하고, 필요 시 세션을 종료하여 성능과 무결성을 보장해보세요. :)