[SQL] Deadlock(교착 상태) 문제 해결 방법

[SQL] Deadlock(교착 상태) 문제 해결 방법

📌 Deadlock(교착 상태)이란?

Deadlock(교착 상태)은 두 개 이상의 트랜잭션이 서로가 가진 자원의 잠금(Lock)이 해제되기를 기다리면서 **무한 대기 상태**에 빠지는 현상을 의미합니다.

즉, 트랜잭션 A는 트랜잭션 B가 잠근 자원을 기다리고, 트랜잭션 B는 트랜잭션 A가 잠근 자원을 기다리면서 교착 상태가 발생합니다.

---

📌 Deadlock 발생 원인과 해결 방법

1️⃣ 동일한 테이블에서 서로 다른 트랜잭션이 교차적으로 데이터를 수정할 때

설명: 두 개의 트랜잭션이 동일한 테이블의 서로 다른 행을 잠근 후, 상대방의 잠긴 데이터를 기다리는 경우 Deadlock이 발생할 수 있습니다.

오류 발생 예제:

-- 트랜잭션 A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 트랜잭션 B
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;

-- 트랜잭션 A가 트랜잭션 B의 데이터를 업데이트하려고 시도
UPDATE accounts SET balance = balance - 100 WHERE id = 2; -- Deadlock 발생

-- 트랜잭션 B가 트랜잭션 A의 데이터를 업데이트하려고 시도
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Deadlock 발생

해결 방법: 트랜잭션에서 일관된 잠금 순서를 유지해야 합니다.

-- 모든 트랜잭션에서 동일한 순서로 데이터를 업데이트하도록 변경
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
COMMIT;

---

2️⃣ 여러 트랜잭션이 동일한 테이블의 행을 동시에 업데이트할 때

설명: 같은 테이블을 동시에 업데이트하면서 교차 잠금이 발생하면 Deadlock이 발생할 수 있습니다.

오류 발생 예제:

-- 트랜잭션 A
BEGIN;
UPDATE orders SET status = 'SHIPPED' WHERE id = 100;

-- 트랜잭션 B
BEGIN;
UPDATE orders SET status = 'SHIPPED' WHERE id = 101;

-- 트랜잭션 A가 id=101을 업데이트 시도 (트랜잭션 B가 잠금 중)
UPDATE orders SET status = 'SHIPPED' WHERE id = 101;

-- 트랜잭션 B가 id=100을 업데이트 시도 (트랜잭션 A가 잠금 중)
UPDATE orders SET status = 'SHIPPED' WHERE id = 100;

해결 방법: 데이터베이스에서 잠금 대기 시간을 설정하거나, 특정 시간이 지나면 트랜잭션을 롤백하도록 설정합니다.

SET innodb_lock_wait_timeout = 5;  -- MySQL에서 Deadlock 대기 시간 설정
SET LOCK_TIMEOUT 5000;  -- PostgreSQL에서 5초 동안 대기 후 타임아웃

---

3️⃣ 트랜잭션이 너무 오래 지속될 때

설명: 하나의 트랜잭션이 너무 오래 실행되면, 다른 트랜잭션들이 해당 트랜잭션이 종료될 때까지 대기하면서 Deadlock이 발생할 가능성이 커집니다.

해결 방법:

• ✅ **트랜잭션을 가능한 한 짧게 유지**
• ✅ **트랜잭션 내부에서 불필요한 쿼리 실행을 최소화**
• ✅ **트랜잭션이 필요한 부분에서만 LOCK을 걸도록 조정**

-- 불필요한 SELECT 문을 줄이고, 트랜잭션을 빠르게 종료
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

---

4️⃣ 인덱스(Index) 미사용으로 인해 Deadlock 발생

설명: 인덱스가 없는 상태에서 WHERE 조건이 포함된 UPDATE 또는 DELETE 문을 실행하면 **테이블의 모든 행을 잠금(Full Table Lock)할 수 있어** Deadlock 위험이 커집니다.

해결 방법: WHERE 조건이 있는 쿼리에 적절한 인덱스를 추가하여 Deadlock을 방지합니다.

CREATE INDEX idx_accounts_id ON accounts(id);

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

---

📌 Deadlock 예방법 요약

• ✅ 트랜잭션 내에서 일관된 잠금 순서 유지
• ✅ 트랜잭션이 너무 오래 지속되지 않도록 유지
• ✅ 필요 시 잠금 대기 시간(lock timeout) 설정
• ✅ WHERE 조건을 사용하는 쿼리에 인덱스 적용

---

🔍 결론

Deadlock(교착 상태)은 **서로 다른 트랜잭션이 같은 자원을 기다리면서 발생하는 문제**입니다.
트랜잭션의 잠금 순서를 명확히 정하고, 인덱스를 활용하며, 트랜잭션 지속 시간을 줄이면 해결할 수 있습니다! 🚀

---

📌 다른 SQL 최적화 방법도 궁금하다면 함께 확인하세요!

📌 SQL 성능 최적화 방법 보러 가기