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


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

 

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

 

 

 

TL;DR

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



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

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

 

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
 
트랜잭션 격리 수준
설명
READ UNCOMMITTED NOLOCK 힌트와 같으며, 트랜잭션이 완료되지 않은 데이터도 조회 가능
READ COMMITTED SQL Server 기본 설정. 다른 사용자의 COMMIT 된 데이터만 읽거나 수정 가능.
REPEATABLE READ 트랜잭션 내부에서 S Lock이 설정되어 다른 세션에서 읽기는 가능하지만, 수정할 경우는 대기하게 됨.
SNAPSHOT 트랜잭션 중에 발생한 데이터 수정은 다른 세션에서 변경 내용이 조회되지 않고, 대기도 없음. 오라클의 기본 설정과 유사함.
SERIALIZABLE 직렬화로 가장 높은 격리 수준. 데이터 수정과 삽입 처리 모두 커밋되기 전까지 대기가 발생함.


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

 

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

UPDATE 게시판 SET 조회수컬럼 = 조회수컬럼 + 1 WHERE 게시글키컬럼 = 조건

이런 패턴입니다. 


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


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


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

 

 

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

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


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

 

SQL 강좌 책 구매

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

 

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

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 27118
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 15310
2314 SQL강좌: 14-7. 트랜잭션과 잠금처리 - 교착상태(데드락-DeadLock) 관리 [1] 코난(김대우) 2023.08.18 165
2313 SQL강좌: 14-6. 트랜잭션과 잠금처리 - 잠금 관리 file 코난(김대우) 2023.08.18 48
» SQL강좌: 14-5. 트랜잭션과 잠금처리 - 잠금과 트랜잭션 격리 수준 코난(김대우) 2023.08.18 32
2311 SQL강좌: 14-4. 트랜잭션과 잠금처리 - 잠금(Lock)과 블로킹 코난(김대우) 2023.08.18 55
2310 SQL강좌: 14-3. 트랜잭션과 잠금처리 - 트랜잭션과 체크포인트 [1] file 코난(김대우) 2023.08.18 107
2309 SQL강좌: 14-2. 트랜잭션과 잠금처리 - 트랜잭션 종류 코난(김대우) 2023.08.18 73
2308 SQL강좌: 14-1. 트랜잭션과 잠금처리 - 트랜잭션 이해 코난(김대우) 2023.08.18 71
2307 SQL강좌: 13-5. 백업과 복원 - 로그 전달, Always On 고가용성과 재해 복구 구현 file 코난(김대우) 2023.08.18 36
2306 SQL강좌: 13-4. 백업과 복원 - 유지 관리 계획 수립 file 코난(김대우) 2023.08.18 51
2305 SQL강좌: 13-3. 백업과 복원 - 백업과 복원 전략 실행 file 코난(김대우) 2023.08.18 42
2304 SQL강좌: 13-2. 백업과 복원 - 백업과 복원 전략 file 코난(김대우) 2023.08.18 46
2303 SQL강좌: 13-1. 백업과 복원 - 백업과 복원 이해 file 코난(김대우) 2023.08.18 58
2302 SQL강좌: 12-9. 인덱스 생성과 관리 - DTA(데이터베이스 엔진 튜닝 관리자) file 코난(김대우) 2023.08.18 47
2301 SQL강좌: 12-8. 인덱스 생성과 관리 - 인덱스 재구성/재구축 코난(김대우) 2023.08.18 47
2300 SQL강좌: 12-7. 인덱스 생성과 관리 - 인덱스 옵션 코난(김대우) 2023.08.18 50
2299 SQL강좌: 12-6. 인덱스 생성과 관리 - 클러스터형 vs 비클러스터형 인덱스 file 코난(김대우) 2023.08.18 44
2298 SQL강좌: 12-5. 인덱스 생성과 관리 - 비클러스터형 인덱스 file 코난(김대우) 2023.08.18 39
2297 SQL강좌: 12-4. 인덱스 생성과 관리 - 클러스터형 인덱스 file 코난(김대우) 2023.08.18 44
2296 SQL강좌: 12-3. 인덱스 생성과 관리 - 인덱스 생성 file 코난(김대우) 2023.08.18 35
2295 SQL강좌: 12-2. 인덱스 생성과 관리 - 인덱스 종류 코난(김대우) 2023.08.18 70





XE Login