Error/Err_handling

ERROR[ DB: 데드락(Deadlock)문제와 해결 방법 ]

세모난 야구공 2025. 1. 14. 01:21

데이터베이스 데드락(Deadlock) 문제와 해결 방법

데이터베이스에서 여러 세션이 동시에 특정 테이블의 행을 갱신하려고 할 때 데드락(Deadlock) 문제가 발생할 수 있습니다. 

이 글은 여러 케이스와 해결 방법에 대해서 정리해보았습니다

상황 1: 동일 테이블의 특정 조건 Row 업데이트

상황

  1. 여러 세션에서 동일 테이블의 특정 조건에 해당하는 row를 update.
  2. 이로 인해 데드락이 발생.

원인

  1. index가 걸려있지 않은 특정 조건의 row를 탐색하기 위해 테이블에 shared lock을 먼저 획득하고 row를 찾음. 그 이후 update
  2. A 세션과 B 세션이 각각 테이블에 shared lock을 걸고 데이터를 조회.
  3. 이후 A 세션이 특정 row를 수정하려고 exclusive lock을 요청, B 세션의 shared lock 해제를 기다림.
  4. 반대로 B 세션도 exclusive lock을 요청하며 A 세션의 shared lock 해제를 기다림.
  5. 이러한 상호 대기가 발생하면서 데드락이 발생.

해결 방법

  1. 찾는 조건 열(Column)에 인덱스를 추가.
    • 인덱스가 없을 경우 테이블 전체에 shared lock이 걸리지만, 인덱스를 사용하면 특정 행에만 shared lock이 걸립니다.
    • 이렇게 하면 충돌 가능성을 줄이고 데드락을 방지할 수 있습니다.
  2. 행 간 충돌을 최소화할 수 있도록 적절한 인덱스 설계.

상황 2: Subquery를 이용한 Select-Update

상황

  1. 여러 세션에서 update 하기 위해서 특정 조건의 데이터를 Select로 조회한 뒤 Update 시도.
  2. 이로 인해 데드락이 발생.

원인

  1. 조건 열에 인덱스가 설정되어 있음
  2. select를 IN절로 Subquery를 사용.
    • Subquery로 인해 넓은 범위를 탐색하며 shared lock을 획득.
  3. A 세션과 B 세션이 각각 넓은 범위의 shared lock을 획득한 후, 특정 행을 수정하려고 exclusive lock을 요청.
  4. 서로의 shared lock 해제를 기다리며 데드락이 발생.

해결 방법

  1. in안에 Subquery를 사용하는 비효율적인 코드를 개선하여 효율적으로 작성.
    • 필요하지 않은 넓은 범위를 탐색하지 않도록 쿼리를 최적화.
  2. 인덱스를 활용하여 shared lock 범위를 최소화.
    • 인덱스를 설정하면 충돌을 줄이고 데드락 발생 가능성을 낮출 수 있습니다.

상황 3: Select 후 Update를 여러 세션에서 동시 실행

상황

  1. 특정 조건의 데이터를 조회.
  2. 해당 데이터를 업데이트.
  3. 위 과정을 여러 세션에서 동시에 실행하여 데드락이 발생.

원인

  1. A 세션이 특정 행을 조회하며 shared lock을 획득.
  2. B 세션도 동일한 행을 조회하며 shared lock을 획득.
  3. 이후 A, B 세션이 해당 행을 수정하려고 각각 exclusive lock을 요청하며 서로 대기.

해결 방법

  1. SELECT FOR UPDATE로 조회.
    • 데이터를 조회할 때 명시적으로 exclusive lock을 설정하여 다른 세션이 접근하지 못하도록 합니다.
    • 예를 들어, A 세션이 목적 row의 exclusive lock을 먼저 획득하면 B 세션은 row를 조회하지 못하고 대기 상태에 들어갑니다.
  2. SELECT FOR UPDATE NOWAIT 옵션 사용.
    • B 세션이 exclusive lock을 대기하지 않고 바로 다른 작업을 수행하도록 설정합니다.

요약

데드락은 다음과 같은 원인으로 발생합니다:

  • 인덱스가 없는 열을 조회하여 테이블 전체에 shared lock이 걸리는 경우.
  • 비효율적인 쿼리로 인해 넓은 범위의 shared lock이 발생하는 경우.
  • 여러 세션이 동일한 행을 동시에 조회 및 수정하려는 경우.

이를 해결하기 위해서는:

  1. 적절한 인덱스 추가.
  2. 효율적인 쿼리 작성.
  3. SELECT FOR UPDATE 및 NOWAIT 옵션 활용.
    1. 단 SELECT FOR UPDATE의 경우 무분별하게 사용할 경우 성능저하를 불러 올 수 있습니다

위 방법들을 적용하면 데드락 발생을 예방하고 데이터베이스 성능을 최적화할 수 있습니다.

 

++ 추가 내용

SELECT FOR UPDATE의 경우 무분별하게 사용할 경우 성능저하가 일어나는 이유

SELECT FOR UPDATE는 데이터베이스에서 특정 행을 잠그는 명령으로, 해당 행을 수정하려는 다른 트랜잭션은 잠금이 해제될 때까지 대기해야 합니다. 그래서 무분별하게 사용하면 다음과 같은 문제를 일으킬 수 있습니다:

  1. 성능 저하: 잠금이 많아지면 데이터베이스의 동시성 성능이 떨어지며, 대기 시간도 증가합니다.
  2. 낭비되는 리소스: 불필요한 잠금이 지속되면, 다른 트랜잭션들이 자원을 대기하면서 비효율적으로 리소스를 사용하게 됩니다.

따라서, SELECT FOR UPDATE는 필요한 경우에만 사용하고, 가능한 한 좁은 범위에서 빠르게 실행되도록 하는 것이 좋습니다.