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

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

이번에 진행할 강좌는 트랜잭션과 잠금처리 - 잠금(Lock)과 트랜잭션 격리 수준입니다.

TL;DR

트랜잭션 격리 수준과 설정 방법을 진행하며, 잠금과 트랜잭션 격리 수준이 데이터베이스 동시성 및 블로킹에 미치는 영향을 다룹니다. 또한, 블로킹 해결을 위한 모니터링 방법과 조치를 소개합니다.

트랜잭션 격리 수준(Transaction Isolation Level)

SQL Server에서 트랜잭션을 수행할 때 잠금 격리 수준을 설정합니다. 아래와 같은 SQL 구문으로 설정합니다.

내부적으로 잠금 모드가 다르게 구성되며, 트랜잭션 세션 설정에 따라 다른 세션의 조회/삽입/수정/삭제 등이 선별적으로 대기하게 됩니다. 각각 수준에 따라 Dirty Read, Repeatable Read, Phantom Read를 방지할 수 있습니다.

예를 들어, 게시판 로직에 조회수 + 1 루틴이 있습니다.

이런 패턴입니다. 

조금 극단적으로 예시로, 위와 같은 게시글 조회 수 처리 루틴으로 테이블에 일부 로우 단위 잠금이 발생할 경우 NOLOCK 힌트나 READ UNCOMMITTED 격리 수준으로 읽는다고 문제가 발생할까요? 잠금을 무시하고 읽어오지만 Dirty Read가 발생해도 조회수가 +1 되었는지 아닌지 문제라면 무시해도 되는 상황일 겁니다.

하지만, 계좌 이체는 어떨까요? 내 계좌에서 1억이 이체되는 중인데, ATM에서 내 계좌를 조회해 보고 이체받을 상대방에게 문의하니 계좌에서 돈은 나갔는데 아직 상대방 계좌로 입금 처리는 완료되지 않은 지연 상태라면? 여러 정황상 문제가 될 소지가 있습니다. 재화와 관련되면 잠시 동안 조회 지연이라도 문제가 심각해집니다. 

이런 문제를 방지하기 위해서 비즈니스의 요구조건과 트랜잭션 격리 수준을 잘 고려해 SQL 쿼리를 작성하시기 바랍니다.

블로킹(차단 - Blocking)을 유발하는 쿼리 처리

이제 잠금이 무엇이고, 차단 주체(블로킹을 유발하는 세션)가 무엇인지 조금 알 것 같습니다. 이렇게 차단이 일어날 경우를 블로킹(Blocking)이 발생한다라고 보통 말을 하게 되며 블로킹은 데이터베이스 리소스를 동시에 여러 명 또는 여러 프로세스가 사용하기 때문에 당연히 일어나는 디자인 특성입니다. - 특히 금융권 등에서 재화와 관련된 프로세스일 경우 거의 모든 과정에서 이런 트랜잭션을 이용한 처리를 하기 때문에 자주 블로킹이 발생합니다.

하지만 지나치게 응답속도가 늦거나 - CPU는 놀고 있는데도 불구하고 - 세션 Timeout이 발생해 트랜잭션이 ROLLBACK 되는 상황이 너무 자주 발생한다면, 여러 블로킹 / 교착상태(Deadlock) 처리를 살펴봐야 합니다. 이때 XEvents(확장 이벤트)나 SQL Server Profiler를 이용해 로깅을 수행하고 모니터링할 수 있으며, 블로킹을 유발하는 세션을 파악해 쿼리 패턴이나 트랜잭션을 다시 설계해 블로킹 문제를 해결해야 합니다.
 

SQL 강좌 책 구매

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

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