안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 14-7. 트랜잭션과 잠금처리 - 교착상태(데드락-DeadLock) 관리를 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 트랜잭션과 잠금처리 - 교착상태(데드락 - DeadLock) 관리입니다.

TL;DR

교착상태의 정의와 발생 이유를 설명하며, 교착상태를 유발하는 예제로 통해 실제 상황을 살펴봅니다. 또한, 교착상태를 예방하고 관리하는 방법을 소개하며, 해결책을 제시합니다.

교착상태 쿼리 수행

교착상태는 무엇이고 어떻게 발생하나요? SQL 구문으로 확인해 보겠습니다. 간단한 2개 테이블이고, 데이터가 1개씩 존재합니다. 여기까지만 봐도 교착상태 쿼리 느낌이 올 겁니다.

USE AdventureWorks;
GO

-- 2개 테이블 생성 후 샘플 데이터 추가
CREATE TABLE deadlock_test1(
idx INT,
cust_num INT
);
GO

CREATE TABLE deadlock_test2(
idx INT,
cust_num INT
);
GO

INSERT INTO deadlock_test1 VALUES(1,1);
INSERT INTO deadlock_test2 VALUES(2,2);
GO

SELECT * FROM deadlock_test1;
SELECT * FROM deadlock_test2;
GO

SSMS에서 새 쿼리로 2개 세션을 새로 열고 각각 다음 단계를 실행합니다.
 
1번 세션에서 실행

USE AdventureWorks;
GO

-- 세션 1-1 실행
BEGIN TRANSACTION; 
UPDATE deadlock_test1 SET cust_num = cust_num * 2;

2번 세션에서 실행

USE AdventureWorks;
GO

-- 세션 2-1 실행
BEGIN TRANSACTION; 
UPDATE deadlock_test2 SET cust_num = cust_num * 2;

1번 세션에서 실행

-- 세션 1-2 실행 - 블로킹 발생
UPDATE deadlock_test2 SET cust_num = cust_num * 2;
 

2번 세션에서 실행

-- 세션 2-2 실행 - 블로킹 발생
UPDATE deadlock_test1 SET cust_num = cust_num * 2;

두 개의 세션이 서로 다른 테이블을 노려보면서 사용 가능해지기를 기다리고 있습니다.

잠시 후, 아래와 같은 deadlock 오류 메시지가 발생하고 둘 중 하나의 세션이 종료됩니다.

1205 교착상태 오류입니다.

교착상태가 발생하는 이유

SQL 구문에서 보신 그대로입니다. 하나의 애플리케이션에 함수 루틴 1과 함수 루틴 2가 있다고 가정합니다. 그리고, 테이블 A와 테이블 B가 있다고 가정해 보지요..

함수 1은 트랜잭션 처리를

함수 2는 트랜잭션 처리를

로 처리합니다.

애플리케이션 로직이 위와 같을 경우, 함수 1이 테이블 A를 잡을 때, 동시에 함수 2가 테이블 B를 잡았다고 가정하면, 함수는 모두 다음 리소스를 사용하려고 서로 노려만 보고 있는 것입니다. 이런 상황이 교착상태입니다.

교착상태가 발생하면 SQL Server가 중재를 해서 하나의 프로세스를 Victim(희생양)으로 처리합니다. 한번 1205 교착상태 오류가 발생했다면, 이제 시작입니다. 사용자가 많아지고 동시성이 높아질수록, 교착상태 오류는 더 빈번하게 자주 발생하게 됩니다.

블로킹이나 교착상태를 해결하는 근본적인 방법

블로킹이나 교착상태를 피하는 방법은 잘 구성된 애플리케이션 개발 계획입니다. 예를 들어, 애플리케이션 내부에 사용자 데이터 삭제나 제품 삭제 루틴 프로세스가 있다면 이런 패턴입니다.

제품 삭제 루틴

사용자 삭제 루틴

당연한 루틴이며, 늘 해왔던(사용자가 많지 않을 때부터) 패턴입니다.

하지만, 위와 같이 프로세스 흐름이 서로 엇갈리는, 블로킹이나 교착상태 발생 가능성이 높은 프로세스 흐름이 있습니다. 이럴 경우, 프로세스 플로우를 변경하면 됩니다.

예를 들어, 사용자 삭제 루틴을 이렇게 변경합니다. 삭제할 데이터는 하위 쿼리나 임시 테이블을 이용해 흐름을 잘 제어하고, X 잠금(Exclusive Lock)을 가능한 짧게 가져갑니다.

이렇게 순서를 바꾼다면? 해당 루틴을 수행하면서 블로킹이나 교착상태가 발생될 가능성이 낮아집니다. 이렇게 루틴을 보완하는 과정을 현업에서는 리소스 처리를 시리얼(Serialize)하게 바꾼다라고 이야기합니다.

해보신 분은 아실 겁니다. 이렇게 프로세스 흐름을 바꾸기란 절대 쉬운 일이 아닙니다. 기술적인 어려움도 있지만, 소위 어른들의 사정으로, 오류로 인한 비즈니스 임팩트에 대한 책임 소재 등 여러 어려움이 있습니다.

말 그대로, 모든 프로세스에서 다 참조하는 member 테이블의 한 계정을 지우려면 이곳저곳에 참조하는 리소스들을 먼저 삭제하고 member 테이블을 지워야 합니다. 개인적으로 30개 정도의 참조하는 테이블에서 삭제 후 member 테이블 로우를 지우던 기억이 납니다. 로직을 변경하고 코드로 적용하기 어렵습니다. 하지만, 블로킹이나 데드락이 한번 발생했을 때 빠르게 이슈를 해결하지 않으면 더 큰 장애가 따라오게 되니, 이 강좌를 참고하셔서 잘 조치하시길 바랍니다.

근본적인 해결 방법

프로젝트 기획 단계부터 디자인 프로세스나 구현 단계에서 해당하는 테이블 접근 순서를 문서화하는 것입니다. 그리고 트랜잭션 처리 루틴은 따로 관리를 하세요. 트랜잭션 처리 순서 루틴을 문서화해두고 X저장 프로시저가 A-> B -> C 순서로 처리한다면, Y저장 프로시저를 생성할 때도 A -> B -> C 순서로 리소스에 접근하도록, 문서화를 해 두시면 큰 규모의 프로젝트에서 여러 팀이 나누어 개발을 할 경우에도 교착상태나 블로킹을 최소화하실 수 있습니다.

너무 무서운 이야기만 많이 드린 것 같아요. 블로킹이나 교착상태 문제를 접하면서 이 강좌에서 설명드린 내용 이상의 이슈는 거의 없습니다. 너무 두려워하실 필요 없습니다. 블로킹이나 교착상태가 발생한다면 이전 강좌 내용대로 모니터링하면서 블로킹 유발 쿼리들을 확인하고, 이어서 조치를 취하면 됩니다.
 

수고하셨습니다.

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022