안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 14-6. 트랜잭션과 잠금처리 - 잠금 관리를 진행 하겠습니다.

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

이번에 진행할 강좌는 트랜잭션과 잠금처리 - 잠금 관리입니다.

TL;DR

블로킹 상황 해결 방안을 진행합니다. 세션 관리를 통해 블로킹에 대응하고, 성능 모니터링 도구와 SQL Server Profiler를 활용한 블로킹 및 교착상태 분석 방법을 진행하며, 블로킹 관리와 성능 개선 전략을 살펴봅니다.

잠금과 블로킹 관리

지금까지 트랜잭션과 잠금에 대해서 논의했습니다. 그렇다면, 잠금으로 인해 블로킹을 유발하는 상태를 어떻게 관리해야 할까요?
지난 14-4. 트랜잭션과 잠금처리 - 잠금(Lock)과 블로킹 강좌에서 잠금과 블로킹을 확인하는 방법, 블로킹을 유발하는 세션의 상세 정보를 확인하는 방법을 안내해 드렸습니다.
sp_who2와 sys.dm_exec_input_buffer 시스템 카탈로그를 이용해 블로킹을 유발하는 프로세스 정보를 확인하였습니다. 이 프로세스를 어떤 형태로든 관리해야 합니다.

잠금과 블로킹을 유발하는 세션 관리 - kill <SPID>

kill <SPID>  명령으로 해당 세션을 kill 하면 됩니다. 예를 들어, sp_who2로 블로킹을 유발하는 세션을 확인했고, SPID가 71이라면, 아래처럼 실행합니다.

kill 71

현업에서 완벽한 해결책은 아닙니다. kill 명령으로 해당 세션을 죽여서 문제가 완전히 해결되는 경우는 없습니다. 수만 명이 동시에 접속하는 웹서비스가 약 100여 개의 Restful API 서비스로 제작되었고, 해당 API 마다 사용하는 SQL 구문이 다른데, 그중 1개가 잠금과 블로킹을 유발한다면, 아무리 kill 명령으로 죽여봐야 해당 애플리케이션 모듈 로직을 수정하고 다시 배포하지 않는 이상 계속 문제가 발생하기 때문입니다.

☑️ 챗GPT 활용: Restful API 서비스에 대해서 알려줘

잠금과 블로킹을 유발하는 세션 관리 - lock timeout 설정

해결책이라기보다는, 블로킹이 주기적으로 발생하는 초기에 어느 코드와 SQL 구문이 블로킹을 유발하는지 안다면, timeout 설정으로 코드와 SQL 구문 수정 전까지, 블로킹으로 인한 장애를 완화할 수 있습니다.

--락 타임아웃 시간 조사
select @@lock_timeout;

-- 결과가 -1 일 경우 타임아웃 없음. 무한 대기.

--락 타임아웃 설정 : 단위는 밀리초. 30초 설정
SET LOCK_TIMEOUT 30000;
GO

select @@lock_timeout;

이렇게 Timeout 설정으로 잠시 완화할 수 있습니다. 다시 말씀드리지만, 문제가 되는 로직을 찾고, 코드와 SQL 구문을 변경해 해결해야 합니다.

잠금과 블로킹을 유발하는 세션 관리 - 성능 모니터

잠금으로 인해 블로킹이 의심된다고 매번 SSMS에서 쿼리를 수행해 모니터링할 수 없습니다. 자동화 방식으로 메트릭(Metric)을 수집하고, 기록해야 합니다. 이때 성능 모니터 도구와 SQL Server Profiler를 이용할 수 있습니다.

SQL Server가 설치되어 있다면, Windows의 성능 모니터 도구에서 SQLServer: Locks 항목을 기록해 모니터링 및 기록을 저장할 수 있습니다.

이미지 - 성능 모니터 - 잠금 항목

특히, 교착상태(데드락-DeadLock)도 모니터링 / 기록할 수 있습니다. 

교착상태가 발생할 경우,

오류 메시지가 발생합니다. 교착상태 관리는 다음 강좌에서 조금 더 상세하게 다룹니다.

잠금과 블로킹을 유발하는 세션 관리 - SQL Server Profiler

이미지 SQL Server - Profiler 데드락 트레이스

SQL Server의 Profiler를 이용해, 잠금 상태 및 교착상태 쿼리를 모두 실시간 모니터링 / 기록 가능해 자주 블로킹이나 교착상태를 유발하는 SQL 구문을 확인할 수 있습니다.

SQL Server Profiler는 SQL 구문이나 저장 프로시저 쿼리 튜닝에도 자주 사용되는 도구입니다. SQL Server에서 실행되는 모든 처리가 기록되며, 다양한 필터를 적용해 원하는 실행만 따로 수집도 가능합니다.

성능 모니터 도구와 Profiler를 이용하면, 문제를 유발하는 SQL 구문과 애플리케이션을 판단할 수 있습니다.
그럼 다음 강좌에서 블로킹 및 교착상태 관리 방안에 대해 살펴보겠습니다.
 

SQL 강좌 책 구매

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

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