주요 관계형 데이터베이스(MySQL, PostgreSQL, Oracle, SQL Server)에서 데드락을 확인 방법

주요 관계형 데이터베이스(MySQL, PostgreSQL, Oracle, SQL Server)에서 데드락을 확인하고 해소하는 방법, 그리고 예방 전략에 대해 설명드리겠습니다.

데드락 발생 원리 (간단 예시)

1. 트랜잭션 1이 테이블 A의 행 1에 쓰기 락(Exclusive Lock)을 겁니다.
2. 트랜잭션 2가 테이블 B의 행 5에 쓰기 락을 겁니다.
3. 트랜잭션 1이 이어서 테이블 B의 행 5에 락을 걸려고 시도합니다. 하지만 트랜잭션 2가 이미 락을 점유하고 있으므로 대기합니다.
4. 트랜잭션 2가 이어서 테이블 A의 행 1에 락을 걸려고 시도합니다. 하지만 트랜잭션 1이 이미 락을 점유하고 있으므로 대기합니다.

결과적으로, 트랜잭션 1은 트랜잭션 2가 락을 놓기를 기다리고, 트랜잭션 2는 트랜잭션 1이 락을 놓기를 기다리는 순환 대기(Circular Wait) 상태가 되어 데드락이 발생합니다.

     (대기)         <----   테이블 B, 행 5  <---- (락 점유)
Txn 1           Txn 2
     (락 점유) ---->   테이블 A, 행 1  ---->         (대기)

주요 데이터베이스별 데드락 확인 및 해소 방법

대부분의 현대 데이터베이스는 데드락 감지 메커니즘을 내장하고 있으며, 데드락 발생 시 자동으로 관련 트랜잭션 중 하나를 '희생양(Victim)'으로 선택하여 롤백(Rollback)시키고 오류를 반환함으로써 데드락을 해소합니다. 하지만 개발자나 DBA가 상황을 파악하고 예방하기 위해 데드락 정보를 확인하는 방법을 아는 것이 중요합니다.

---

1. MySQL (InnoDB 스토리지 엔진 기준)

• 데드락 확인:
  • SHOW ENGINE INNODB STATUS; 명령: 가장 일반적인 방법입니다. 실행 결과 중 LATEST DETECTED DEADLOCK 섹션에서 가장 최근에 발생한 데드락의 상세 정보를 확인할 수 있습니다. 어떤 트랜잭션들이 어떤 락을 기다리고 있었는지, 어떤 쿼리가 데드락을 유발했는지, 어떤 트랜잭션이 롤백되었는지 보여줍니다.
  • MySQL 에러 로그: 데드락 발생 시 에러 로그 파일에도 관련 정보가 기록될 수 있습니다. (설정에 따라 다름)
  • Information Schema 테이블: 실시간 락 정보를 보려면 information_schema.INNODB_TRX, information_schema.INNODB_LOCKS, information_schema.INNODB_LOCK_WAITS 테이블을 조회하여 현재 트랜잭션과 락 대기 상태를 분석할 수 있습니다. (데드락 발생 후 확인보다는 현재 락 상황 분석에 유용)

예시 (SHOW ENGINE INNODB STATUS; 출력 일부):

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2025-04-30 13:40:00 0x70000a0a1000
    *** (1) TRANSACTION:
    TRANSACTION 12345, ACTIVE 5 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 50, OS thread handle 1231453234234, query id 100 localhost root updating
    UPDATE products SET stock = stock - 1 WHERE id = 10;
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `testdb`.`products` trx id 12345 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; ... (Record Data) ...

    *** (2) TRANSACTION:
    TRANSACTION 12348, ACTIVE 3 sec starting index read, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 55, OS thread handle 1231459876543, query id 105 localhost root updating
    UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10;
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `testdb`.`products` trx id 12348 lock_mode X locks rec but not gap
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; ... (Record Data) ...
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 59 page no 3 n bits 80 index PRIMARY of table `testdb`.`inventory` trx id 12348 lock_mode X locks rec but not gap waiting
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; ... (Record Data) ...

    *** WE ROLL BACK TRANSACTION (1)

(위 예시는 트랜잭션 1이 products 테이블의 행을 기다리고, 트랜잭션 2가 inventory 테이블의 행을 기다리며, 서로가 가진 락을 기다리는 상황을 보여줍니다. 결국 트랜잭션 1이 롤백되었습니다.)

• 데드락 해소:
  • 자동 해소: InnoDB는 데드락을 감지하면 자동으로 두 트랜잭션 중 하나(보통 변경량이 적거나 undo 로그가 적은 트랜잭션)를 롤백시키고, 해당 클라이언트에게 데드락 오류(Error Code: 1213)를 반환합니다.
  • 수동 해소: 매우 드물지만, 데드락이 자동으로 해소되지 않거나 특정 트랜잭션을 강제로 종료해야 할 경우, SHOW PROCESSLIST;로 트랜잭션의 프로세스 ID(Id 컬럼)를 확인하고 KILL <process_id>; 명령으로 종료할 수 있습니다.

---

2. PostgreSQL

• 데드락 확인:
  • PostgreSQL 서버 로그: 데드락 발생 시 가장 중요한 정보 소스입니다. postgresql.conf 파일에서 log_lock_waits가 활성화되어 있고 deadlock_timeout (기본값 1초)이 설정되어 있으면, 데드락 감지 시 관련 정보(데드락에 연루된 쿼리, 락 정보 등)가 서버 로그에 기록됩니다.
  • pg_locks 뷰: 현재 시스템의 모든 락 정보를 보여줍니다. granted 컬럼이 f(false)인 행은 락 대기 중임을 의미합니다. 이 뷰와 pg_stat_activity 뷰를 조인하여 어떤 세션이 어떤 락을 기다리는지 실시간으로 분석할 수 있습니다.
  • pg_stat_activity 뷰: 현재 실행 중인 모든 세션과 쿼리, 상태(예: active, idle in transaction), 대기 이벤트(wait_event_type, wait_event) 정보를 보여줍니다. 락 대기 중인 세션은 wait_event_type이 Lock으로 표시될 수 있습니다.

예시 (서버 로그):

    ERROR:  deadlock detected
    DETAIL:  Process 12345 waits for ShareLock on transaction 5678; blocked by process 9876.
            Process 9876 waits for ShareLock on transaction 5679; blocked by process 12345.
            Process 12345: UPDATE products SET stock = stock - 1 WHERE id = 10;
            Process 9876: UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10;
    HINT:  See server log for query details.
    CONTEXT:  while updating tuple (0,1) in relation "products"

• 데드락 해소:
  • 자동 해소: PostgreSQL은 deadlock_timeout 설정값 이후 데드락을 감지하면 관련 트랜잭션 중 하나를 자동으로 롤백하고 SQLSTATE '40P01' (deadlock_detected) 오류를 클라이언트에 반환합니다.
  • 수동 해소: 문제가 되는 특정 백엔드 프로세스를 종료해야 할 경우, pg_stat_activity에서 해당 프로세스의 PID(pid 컬럼)를 확인하고 다음 함수를 사용할 수 있습니다.
    • SELECT pg_cancel_backend(<pid>); : 해당 프로세스가 현재 실행 중인 쿼리를 안전하게 취소하려고 시도합니다. (권장)
    • SELECT pg_terminate_backend(<pid>); : 해당 프로세스를 강제로 종료합니다. (데이터 손실 위험은 없으나, 연결이 끊김)

---

3. Oracle

• 데드락 확인:
  • Alert Log (alert_<SID>.log): 데드락이 발생하면 Oracle은 ORA-00060: deadlock detected while waiting for resource 오류를 Alert Log에 기록하고, 상세 정보가 담긴 트레이스 파일(.trc)의 경로를 알려줍니다.
  • 트레이스 파일 (.trc): Alert Log에 명시된 트레이스 파일을 열면 데드락 그래프(Deadlock Graph), 관련 세션 정보, 실행 중이던 SQL 문장 등 매우 상세한 분석 정보를 얻을 수 있습니다.
  • 시스템 뷰:
    • V$LOCK: 현재 시스템의 락 정보를 보여줍니다. TYPE (락 유형), LMODE (보유 락 모드), REQUEST (요청 락 모드), BLOCK (락 차단 여부) 컬럼이 중요합니다.
    • V$SESSION: 현재 세션 정보를 보여줍니다. SID, SERIAL#, BLOCKING_SESSION (나를 막고 있는 세션 ID) 등을 확인할 수 있습니다.
    • DBA_WAITERS 와 DBA_BLOCKERS: 데드락 또는 블로킹(Blocking) 관계를 더 쉽게 파악할 수 있는 뷰입니다. (권한 필요)
  • Oracle Enterprise Manager (OEM): GUI 환경에서 데드락 발생 현황 및 상세 정보를 시각적으로 보여주는 기능을 제공합니다.

예시 (트레이스 파일 내용 일부 - 개념적 표현):

    Deadlock graph:
                       ---------Blocker(s)---------  ---------Waiter(s)----------
    Resource Name          process session holds waits  process session holds waits
    TX-000a001e-00012345        50     100     X             60     200           X
    TX-000b000f-00054321        60     200     X             50     100           X

    session 100: DID 0001-0032-00000001 session 200: DID 0001-003C-00000001
    session 200: DID 0001-003C-00000001 session 100: DID 0001-0032-00000001

    Rows waited on:
    Session 100: obj - 12345, file - 4, block - 100, row - 0
    Session 200: obj - 67890, file - 5, block - 200, row - 10
    ---------------------------------------------------

(세션 100이 보유한 TX 락을 세션 200이 기다리고, 세션 200이 보유한 TX 락을 세션 100이 기다리는 순환 대기 상태를 보여줍니다.)

• 데드락 해소:
  • 자동 해소: Oracle은 데드락을 감지하면 데드락을 유발한 SQL 문장을 실행한 트랜잭션 중 하나를 자동으로 롤백시키고, 해당 세션에 ORA-00060 오류를 반환합니다.
  • 수동 해소: 데드락 자체보다는 특정 블로킹 세션을 종료해야 할 때 사용합니다. V$SESSION 뷰 등에서 문제가 되는 세션의 SID와 SERIAL#를 확인하고 ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE; 명령으로 세션을 종료할 수 있습니다.

---

4. SQL Server

• 데드락 확인:
  • SQL Server Profiler / Extended Events: 데드락 정보를 가장 상세하게 얻는 방법입니다.
    • Profiler: Locks:Deadlock Graph 이벤트를 캡처하면 데드락 발생 시 관련 정보(참여한 프로세스, 리소스, SQL 문장)가 시각적인 XML 그래프 형태로 기록됩니다.
    • Extended Events: xml_deadlock_report 이벤트를 사용하여 데드락 정보를 XML 형식으로 수집할 수 있습니다. (Profiler보다 권장됨)
  • SQL Server 에러 로그: 데드락 발생 시 에러 로그에 오류 번호 1205와 함께 데드락 발생 사실이 기록됩니다.
  • 시스템 관리 뷰(DMV):
    • sys.dm_tran_locks: 현재 활성 락 정보를 보여줍니다.
    • sys.dm_os_waiting_tasks: 현재 대기 중인 작업과 대기 리소스 정보를 보여줍니다. blocking_session_id 컬럼으로 블로킹 관계를 파악할 수 있습니다.
    • sys.dm_exec_requests, sys.dm_exec_sessions: 현재 실행 중인 요청 및 세션 정보를 확인합니다.

예시 (Deadlock Graph XML - 주요 구조 개념):

    <deadlock>
      <victim-list>
        <victimProcess id="process1a2b3c4d" />
      </victim-list>
      <process-list>
        <process id="process1a2b3c4d" taskpriority="0" logused="100" waitresource="KEY: 5:72057594038321152 (a1b2c3d4e5f6)" waittime="1500" ownerId="12345" transactionname="UPDATE" ...>
          <inputbuf> UPDATE products SET stock = stock - 1 WHERE id = 10; </inputbuf>
        </process>
        <process id="process5e6f7g8h" taskpriority="0" logused="200" waitresource="KEY: 6:72057594038386688 (f6e5d4c3b2a1)" waittime="1500" ownerId="54321" transactionname="UPDATE" ...>
          <inputbuf> UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10; </inputbuf>
        </process>
      </process-list>
      <resource-list>
        <keylock hobtid="72057594038321152" dbid="5" objectname="testdb.dbo.products" indexname="PK__products__..." id="lockdeadbeef" mode="X" associatedObjectId="72057594038321152">
          <owner-list>
            <owner id="process5e6f7g8h" mode="X"/>
          </owner-list>
          <waiter-list>
            <waiter id="process1a2b3c4d" mode="X" requestType="wait"/>
          </waiter-list>
        </keylock>
        <keylock hobtid="72057594038386688" dbid="6" objectname="testdb.dbo.inventory" indexname="PK__inventory__..." id="lockcafebabe" mode="X" associatedObjectId="72057594038386688">
          <owner-list>
            <owner id="process1a2b3c4d" mode="X"/>
          </owner-list>
          <waiter-list>
            <waiter id="process5e6f7g8h" mode="X" requestType="wait"/>
          </waiter-list>
        </keylock>
      </resource-list>
    </deadlock>

(위 XML은 process1이 inventory의 키 락을 기다리고, process5가 products의 키 락을 기다리며, 서로가 가진 락을 기다리는 상황과 함께 희생양(victim) 정보, 실행된 쿼리 등을 보여줍니다.)

• 데드락 해소:
  • 자동 해소: SQL Server는 데드락을 감지하면 DEADLOCK_PRIORITY 설정(기본값은 롤백 비용이 가장 적은 트랜잭션)에 따라 희생양 트랜잭션을 선택하여 롤백하고, 클라이언트에 오류 1205를 반환합니다.
  • 수동 해소: 특정 세션(프로세스)을 종료해야 할 경우, 관련 DMV나 sp_who2 등으로 SPID(세션 ID)를 확인하고 KILL <spid>; 명령으로 종료할 수 있습니다.

---

데드락 예방 및 최소화 전략 (일반적)

데드락은 발생 후 해결하는 것보다 예방하는 것이 훨씬 중요합니다.

1. 트랜잭션 짧게 유지: 트랜잭션이 보유하는 락의 지속 시간을 줄여 충돌 가능성을 낮춥니다. (필요한 작업만 트랜잭션 내에서 수행)
2. 자원 접근 순서 일관성 유지: 여러 자원을 업데이트하는 경우, 모든 애플리케이션 로직에서 항상 동일한 순서로 자원에 접근하도록 규칙을 정합니다. (예: 항상 Table A -> Table B 순서로 락 획득) 이것이 가장 효과적인 방법 중 하나입니다.
3. 적절한 트랜잭션 격리 수준 사용: 필요 이상으로 높은 격리 수준(예: Serializable)은 락 범위를 넓혀 데드락 가능성을 높일 수 있습니다. READ COMMITTED가 많은 경우 기본이며, 상황에 맞게 조정합니다.
4. 인덱스 설계 최적화: 쿼리가 불필요하게 많은 데이터를 스캔하거나 테이블 전체를 잠그지 않도록 인덱스를 적절히 생성합니다. 특히 외래 키(Foreign Key)에는 인덱스를 생성하는 것이 좋습니다.
5. 락 타임아웃 설정: 락을 획득하기 위해 무한정 기다리지 않도록 적절한 락 타임아웃을 설정합니다. (데이터베이스 또는 쿼리 레벨에서 설정 가능)
6. 비관적 락 대신 낙관적 락 사용 고려: 충돌이 자주 발생하지 않는 환경이라면, @Version 등을 이용한 낙관적 락이 동시성을 높이고 데드락 가능성을 줄일 수 있습니다.
7. 애플리케이션 레벨에서 데드락 오류 처리: 데드락은 언제든 발생할 수 있으므로, 애플리케이션 코드에서 데드락 관련 오류(예: MySQL 1213, PostgreSQL 40P01, Oracle ORA-00060, SQL Server 1205)를 감지하고, 일정 시간 후 재시도(Retry)하는 로직을 구현하는 것이 중요합니다.

이러한 방법들을 통해 데드락 발생 빈도를 크게 줄이고, 발생하더라도 시스템에 미치는 영향을 최소화할 수 있습니다.