BUG | MsSql Deadlock 교착상태 문제 해결

문제를 발견하게 된 과정

QA 시스템 운영 중 오류 로그를 발견했습니다.

트랜잭션(프로세스 ID 1696)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다.
트랜잭션을 다시 실행하십시오.

문제 코드 예시

SELECT 문을 수행할 때 데드락이 발생했다고 로그가 남겨졌습니다.

SELECT 
    customer.id,
    customer.name,
    menu.category,
    product.date
    
FROM 
    customer
   JOIN 
    menu
    	ON customer.id = menu.id
   JOIN
    product
    	ON menu.id = product.id;

문제의 원인

해당 테이블에 데이터를 INSERT 또는 UPDATE를 할 때, SELECT를 수행하지 못하고 있었습니다.

DB 격리 수준(Isolation_level)이 ReadCommitted로 되어 있어 다른 트랜잭션이 테이블을 변경 중이면 SELECT 작업을 할 수 없었습니다.

 

MsSql Isolation level 조회 방법

SELECT
    CASE transaction_isolation_level -- 트랜잭션 격리 수준을 나타내는 컬럼
       WHEN 0 THEN 'Unspecified'
       WHEN 1 THEN 'ReadUncommitted'
       WHEN 2 THEN 'RadCommitted'
       WHEN 3 THEN 'Repeatable'
       WHEN 4 THEN 'Serializable'
       WHEN 5 THEN 'Snapshot'
    END AS transaction_isolation_level
FROM sys.dm_exec_sessions -- 현재 실행중인 세션 정보를 담고 있는 테이블
WHERE session_id = @@SPID -- 세션 아이디가 현재 연결된 세션 ID인 정보만 조회

 

 

transaction_isolation_level	격리 수준	설명
0	Unspecified (미지정)
1	Read Uncommitted (읽기 미확정)	세션1에서 데이터를 변경중이어도 세션2에서 SELECT할 수 있습니다. 세션1의 데이터 변경이 끝나지 않더라도 세션2에서 변경사항을 읽을 수 있습니다.
2	Read Committed (읽기 확정)	다른 사용자가 INSERT, UPDATE중이라면 READ할 수 없습니다. 기본 격리 수준입니다.
3	Repeatable Read (반복 가능 읽기)	세션1에서 SELECT수행중일 때, 세션2에서는 UPDATE, DELETE는 대기해야 하고, INSERT는 가능합니다.
4	Serializable (직렬화 가능)	세션1에서 SELECT수행중일 때, 세션2에서는 UPDATE, DELETE, INSERT는 대기가 발생합니다.
5	Snapshot (스냅샷 읽기)	세션1에서 변경, 삭제된 데이터를 세션2에서는 원본 데이터로 읽을 수 있습니다. 하지만 이 데이터를 변경하려고 하면 오류가 발생합니다.

 

문제를 해결한 방법

SELECT 문 FROM 절에 WITH(NOLOCK) 을 추가했습니다.

SELECT 
    customer.id,
    customer.name,
    menu.category,
    product.date
    
FROM 
    customer WITH (NOLOCK) 
   JOIN 
    menu WITH (NOLOCK) 
    	ON customer.id = menu.id
   JOIN
    product WITH (NOLOCK) 
    	ON menu.id = product.id;

 

 

WITH (NOLOCK) 알아보기

SELECT 할 때 세션 1에서 데이터를 INSERT 또는 UPDATE 하더라도 세션 2에서 테이블을 조회할 수 있습니다. (공유잠금을 걸지 않음.)

쓰기 잠금(Exclusive Lock)이 걸려있다면, 세션1에서 수정 중인 데이터는 세션 2에서 조회되지 않습니다.

만약, 쓰기 잠금도 해제돼있다면, 세션 1에서 수정 중인 데이터도 세션 2에서 조회됩니다.

 

 

단점

Committed 되지 않은 데이터가 Rollback될 경우 잘못된 데이터를 읽게 될 수 있습니다. 그러므로 정확성이 필요한 경우에는 사용하지 말아야 합니다.

 

 

단점에도 불구하고 WITH (NOLOCK) 설정한 이유

데드락이 발생했던 테이블은 테이블 잠금 상태였기 때문에 각 트랜잭션이 다른 행의 자원을 필요로 했어도 자원 경합이 발생했습니다.

상품 가입 정보를 DB에 저장 후, 가입 완료 정보를 다시 조회할 때 데드락이 발생했습니다.

 

트랜젝션 1에서 고객1의 Insert, 또는 Update 정보가 Rollback 되어도,

트랜젝션 2에서 고객2가 저장했던 가입 완료 정보는 정확할 것이라고 생각했습니다.

 

잠금 유발 테스트를 통한 테이블 잠금 상태 확인

-- 1. 트랜잭션 시작
BEGIN TRAN;

-- 2. 실제 일어나지 않을 update문 실행
UPDATE 
    'table'
SET 
    name = 'name'
WHERE 
    id = 1
     AND
    1=0; -- 실행 불가능한 조건

-- 3. 데이터베이스 활성 상태 잠금 조회
SELECT 
    request_session_id,
    resource_type, -- OBJECT: 테이블 잠금, KEY or RID: 행 잠금 
    resource_database_id,
    resource_associated_entity_id,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id = OBJECT_ID('table');


-- 4. 트랜잭션 종료
ROLLBACK TRAN;

-- 기타. 특정 테이블 잠금 설정 확인 (행 잠금, 페이지 잠금 가능 여부 확인)
SELECT 
    name,
    allow_row_locks, -- 행 잠금 가능 여부
    allow_page_locks -- 페이지 잠금 가능 여부
FROM sys.indexes
WHERE object_id = OBJECT_ID('테이블명');

 

위 작업을 수행했을 때 3. 데이터베이스 활성 상태 잠금 조회 쿼리문에서 resource_type = OBJECT가 나왔습니다.

따라서 이 테이블에는 테이블 잠금이 걸려있음을 확인했습니다.

 

---

참고자료

SQL SERVER Transaction Isolation Level