데드락(Deadlock): SQL에서 교착 상태를 방지하는 최적의 해결책

🔹 데드락(Deadlock)이란?

1. 데드락(Deadlock)의 정의

데드락(Deadlock)은 두 개 이상의 트랜잭션이 서로 상대방의 리소스(예: 데이터 행, 테이블 락)를 기다리며 영원히 진행되지 않는 상태를 의미합니다.

✅ 데드락이 발생하는 주요 원인:

• 트랜잭션 간 자원(락, Lock) 경쟁
• 락 순서가 꼬여 발생하는 상호 대기 상황
• 긴 트랜잭션 실행으로 인해 락 점유 시간 증가
• 동시성 처리를 고려하지 않은 쿼리 설계

📌 데드락이 발생하면 해당 트랜잭션은 무한 대기 상태에 빠지며, 데이터베이스 성능이 저하됨

---

🔹 데드락의 동작 방식

✅ 1. 데드락 발생 예제

✔️ Step 1: 두 개의 트랜잭션이 실행됨

-- 트랜잭션 1: A → B 순서로 락 획득
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;  -- A 테이블 락
WAITFOR DELAY '00:00:05';  -- 5초 대기
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;  -- B 테이블 락
COMMIT;

-- 트랜잭션 2: B → A 순서로 락 획득
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 2;  -- B 테이블 락
WAITFOR DELAY '00:00:05';  -- 5초 대기
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 1;  -- A 테이블 락
COMMIT;

📌 두 트랜잭션이 서로가 점유한 리소스를 기다리며 무한 대기 상태(Deadlock)에 빠짐

---

✅ 2. 데드락 감지 (Deadlock Detection)

대부분의 데이터베이스는 데드락을 감지하면 자동으로 하나의 트랜잭션을 강제 종료(Rollback) 합니다.

✔️ MySQL에서 데드락 감지 예제

SHOW ENGINE INNODB STATUS;

📌 현재 발생한 데드락과 관련된 정보를 확인 가능

✔️ SQL Server에서 데드락 감지 로그 확인

SELECT * FROM sys.dm_tran_locks;

📌 현재 트랜잭션의 락 상태를 모니터링 가능

---

🔹 데드락 방지 기법

✅ 1. 트랜잭션 실행 순서 조정

✅ 모든 트랜잭션이 동일한 순서로 자원을 잠그도록 설계
✔️ 해결 방법 예제 (A → B 순서로 고정)

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;  -- 항상 A부터 락 획득
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;

📌 모든 트랜잭션이 동일한 순서로 락을 요청하면 데드락 발생 확률이 감소

---

✅ 2. 락 범위 최소화 (Lock Granularity Reduction)

✅ 테이블 락(Table Lock) 대신 행 락(Row Lock) 사용
✔️ MySQL에서 행 수준 락 사용 예제

SELECT * FROM Accounts WHERE AccountID = 1 FOR UPDATE;

📌 불필요한 테이블 락을 방지하여 병렬 실행 성능 향상

---

✅ 3. 트랜잭션 타임아웃 설정

✅ 지정된 시간 안에 락이 해제되지 않으면 트랜잭션 강제 종료
✔️ MySQL에서 락 대기 시간 설정

SET innodb_lock_wait_timeout = 5;

✔️ SQL Server에서 락 타임아웃 설정

SET LOCK_TIMEOUT 5000;  -- 5초 후 강제 종료

📌 데드락이 발생해도 일정 시간 후 자동으로 해결됨

---

✅ 4. 인덱스 최적화로 락 충돌 방지

✅ 인덱스를 활용하여 필요한 데이터만 락을 획득
✔️ 비효율적인 인덱스 사용 예제

UPDATE Orders SET Status = 'Shipped' WHERE CustomerID = 10;

📌 CustomerID에 인덱스가 없으면 전체 테이블이 락에 걸릴 수 있음

✔️ 인덱스를 활용한 최적화 예제

CREATE INDEX idx_orders_customer ON Orders (CustomerID);
UPDATE Orders SET Status = 'Shipped' WHERE CustomerID = 10;

📌 인덱스를 사용하여 특정 행만 업데이트하면 데드락 발생 확률이 감소

---

✅ 5. 재시도 로직 적용 (Retry Mechanism)

✅ 트랜잭션이 데드락으로 인해 롤백되면 일정 횟수까지 재시도
✔️ Python + MySQL에서 재시도 로직 구현 예제

import mysql.connector
import time

def execute_transaction():
    for attempt in range(3):  # 최대 3회 재시도
        try:
            conn = mysql.connector.connect(user='user', password='pass', database='testdb')
            cursor = conn.cursor()
            cursor.execute("START TRANSACTION")
            cursor.execute("UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1")
            cursor.execute("UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2")
            conn.commit()
            break
        except mysql.connector.Error as err:
            conn.rollback()
            print(f"데드락 발생, 재시도 {attempt + 1}/3...")
            time.sleep(2)  # 2초 대기 후 재시도
        finally:
            cursor.close()
            conn.close()

execute_transaction()

📌 데드락이 발생하면 트랜잭션을 롤백하고 일정 횟수까지 자동으로 재시도

---

📌 결론

✅ 데드락(Deadlock)은 SQL에서 발생하는 대표적인 교착 상태 문제로, 트랜잭션이 서로의 리소스를 기다리며 무한 대기에 빠지는 현상이다.
✅ 트랜잭션 실행 순서를 조정하고, 락 범위를 최소화하면 데드락 발생 확률을 줄일 수 있다.
✅ 트랜잭션 타임아웃 설정 및 인덱스 최적화를 통해 불필요한 락 충돌을 방지할 수 있다.
✅ 재시도 로직을 적용하면 데드락이 발생해도 자동으로 해결할 수 있다.