월요일

PostgreSQL deadlock detected 해결 — 두 트랜잭션이 서로 락을 기다리는 교착상태 원인 해결

PostgreSQL deadlock detected 에러 해결 — 교착상태 원인과 방지 전략

핵심 답변: PostgreSQL deadlock detected는 두 트랜잭션이 서로 상대방이 보유한 락을 기다리는 교착상태에서 발생한다. PostgreSQL은 이를 자동으로 감지해 하나를 강제 롤백한다. 방지법은 ① 일관된 락 순서 유지, ② SELECT FOR UPDATE 사용, ③ 배치 단위 COMMIT, ④ 외래키 인덱스 추가, ⑤ 재시도 로직 구현이다.

운영 중인 서비스 로그에 갑자기 이런 에러가 쏟아진다.

ERROR: deadlock detected

DETAIL: Process 12345 waits for ShareLock on transaction 678;

blocked by process 67890.

Process 67890 waits for ShareLock on transaction 456;

blocked by process 12345.

HINT: See server log for query details.

교착상태(Deadlock)는 두 트랜잭션이 서로 상대방이 보유한 락을 기다리는 상황이다. 양쪽 모두 진행할 수 없는 상태가 되면 PostgreSQL이 감지해 하나를 강제 롤백하고 ERROR 40P01을 반환한다.
트랜잭션 A: 행 1 잠금 → 행 2 잠금 시도 (대기)

트랜잭션 B: 행 2 잠금 → 행 1 잠금 시도 (대기)

→ 영원히 풀리지 않음 → PostgreSQL이 하나를 강제 종료


원인 1: UPDATE 순서가 트랜잭션마다 다른 경우

가장 흔한 원인이다. 같은 테이블의 여러 행을 업데이트할 때 트랜잭션마다 접근 순서가 다르면 교착상태가 생긴다.

-- 트랜잭션 A (id=1 → id=2 순서)

BEGIN;

UPDATE orders SET status = 'processing' WHERE id = 1;

UPDATE orders SET status = 'processing' WHERE id = 2;

COMMIT;

-- 트랜잭션 B (id=2 → id=1 역순 — 충돌!)

BEGIN;

UPDATE orders SET status = 'cancelled' WHERE id = 2;

UPDATE orders SET status = 'cancelled' WHERE id = 1; -- deadlock

COMMIT;

해결: 모든 트랜잭션에서 동일한 순서(id 오름차순)로 접근
-- SQL에서 ORDER BY로 순서 보장

UPDATE orders SET status = 'processing'

WHERE id IN (1, 2)

ORDER BY id;

# 애플리케이션에서 정렬 후 처리

def update_orders(session, order_ids, status):

for order_id in sorted(order_ids): # 항상 오름차순

session.query(Order).filter(Order.id == order_id).update({"status": status})

session.commit()


원인 2: SELECT FOR UPDATE 없이 나중에 UPDATE

조회 후 수정하는 패턴에서 자주 발생한다. 조회 시 락을 잡지 않으면 다른 트랜잭션이 끼어든다.

-- ❌ 락 없이 조회 후 수정 — 위험

BEGIN;

SELECT balance FROM accounts WHERE id = 100;

-- 이 사이에 다른 트랜잭션이 같은 행을 수정 가능

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

COMMIT;

-- ✅ SELECT FOR UPDATE로 조회 시 즉시 락 획득

BEGIN;

SELECT balance FROM accounts WHERE id = 100 FOR UPDATE;

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

COMMIT;

// Spring Data JPA — 비관적 락 사용

@Repository

public interface AccountRepository extends JpaRepository<Account, Long> {

@Lock(LockModeType.PESSIMISTIC_WRITE)

@Query("SELECT a FROM Account a WHERE a.id = :id")

Optional<Account> findByIdForUpdate(@Param("id") Long id);

}

@Transactional

public void transfer(Long fromId, Long toId, BigDecimal amount) {

// 작은 id부터 락 획득 — 순서 일관성 보장

Long firstId = Math.min(fromId, toId);

Long secondId = Math.max(fromId, toId);

Account first = accountRepository.findByIdForUpdate(firstId).orElseThrow();

Account second = accountRepository.findByIdForUpdate(secondId).orElseThrow();

}


원인 3: 인덱스 없이 대량 UPDATE/DELETE

인덱스 없는 컬럼으로 넓은 범위를 업데이트하면 테이블 전체에 락이 걸린다.

-- ❌ 인덱스 없는 컬럼으로 대량 업데이트 — 테이블 전체 잠금

UPDATE orders SET processed = true WHERE created_at < '2026-01-01';

-- ✅ 배치로 나눠서 처리

DO $$

DECLARE

batch_size INT := 1000;

last_id BIGINT := 0;

BEGIN

LOOP

UPDATE orders SET processed = true

WHERE id IN (

SELECT id FROM orders

WHERE created_at < '2026-01-01'

AND processed = false

AND id > last_id

ORDER BY id

LIMIT batch_size

)

RETURNING max(id) INTO last_id;

EXIT WHEN NOT FOUND OR last_id IS NULL;

COMMIT;

PERFORM pg_sleep(0.01); -- 다른 트랜잭션에 기회 부여

END LOOP;

END $$;


원인 4: 외래키 인덱스 누락

PostgreSQL은 외래키 참조 시 부모 테이블에 ShareLock을 건다. 외래키 컬럼에 인덱스가 없으면 잠금 범위가 커진다.

-- 외래키 컬럼 인덱스 누락 여부 확인

SELECT

tc.table_name,

kcu.column_name,

(SELECT 1 FROM pg_indexes

WHERE tablename = tc.table_name

AND indexdef LIKE '%' || kcu.column_name || '%') AS has_index

FROM information_schema.table_constraints tc

JOIN information_schema.key_column_usage kcu

��N��B��6�FR7G��S�&&6�w&�V�C�6ccc�FF��s�'�g��&�&FW"�&F�W3�7��f��B�f֖Ǔ�����76S�f��B�6��S��V�#�4UB��6��F��V�WB�sW2s��6�FS������ȹΫN��B� ��Y��Zȉ��莸�B���ࠣ�7G&��s��FVF��6���B� ��9��Y���B����N�K�i�8��B�9ޫ��)�ɩC���7G&��s����7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#���XN�����B��7Fw&U5���FVF��6�� �xȹ��Y��)��قث��������Y���BɘN�N���N� �Yθ�B�����N�K��B��ΫH�K�莸�B�8�9κ[����x�Yθ�B��N� � ����x^�x����ȹθ�N�Y���B� θ�B���ࠣƇ"7G��S�&&�&FW#����S�&�&FW"�F���6�ƖB6SSS��&v��3'�#ࠣƃ"7G��S�&f��B�6��S��VVӶ�&v��3'�'��FF��r�&�GF�ӣg��&�&FW"�&�GF�ӣ'�6�ƖB6SSS�6���#�3##"#�� ^�j����#ࠣ�7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#��7Fw&U5�FVF��6�� ��xnɹ˙����ࠣ�7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#���zι���h�ȉ�� Rȹ��7G&��s��Z��8�BɊN�hN� �ȉ���7G&��s�� �{�����7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#�"��٨�ٸBȉ�� ^���7G&��s�4T�T5Bd�"UDDS��7G&��s��ȹ�������7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#�2�������x^����Nث���B�7G&��s� ˙������C��7G&��s���)���4��ԕC����7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#�B�ɛ����*B˺ι���y� ��9�ȹ��7G&��s��ێ��ȪC��7G&��s��9��K����7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#�R�ث��������Y������^�Y��7G&��s��z~�(���7G&��s����x����7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#�b����ȹθ�B���x���7G&��s�W���V�F��&6��fc��7G&��s���Z�وC��ࠣ�7G��S�&�&v��'��Ɩ�RֆV�v�C��s�6���#�3332#���Bn��x�[��x�*N��B�Y� ��8�9�� ��9��ق�^�[��x���Bȉ��莸�B���ࠣƇ"7G��S�&&�&FW#����S�&�&FW"�F���6�ƖB6SSS��&v��3'�#ࠣ�F�b7G��S�&&6�w&�V�C�6S�cFfC�&�&FW"��VgC�G�6�ƖB3s6S��&�&FW"�&F�W3�G��FF��s�G�����&v��#��f��B�6��S��VVӶ6���#�3&S�Ɩ�RֆV�v�C��r#�H� ������&Vc�&�GG3���GV�GV�6�R�&��w7�B�6��"7G��S�&6���#�3s6S�#䆖�&�56���V7F������W��W7FVB�y����[N�+�����F�c���F�c

댓글 없음:

댓글 쓰기