반응형
트랜잭션 고립화 수준 (Transaction Isolation Level)
- 잠금을 이해하기 전에 트랜잭션의 고립화 수준을 먼저 어느정도 알아야 한다.
- 고립화 수준 설정으로 인해 다른 세션에서 데이터를 조회 할 때 생기는 문제점들을 이해 할 수 있다.
Level 0 : READ UNCOMMITED
- 커밋되지 않은 트랜잭션의 데이터를 읽어 올 수 있음
- 만약 읽어온 데이터의 트랜잭션이 롤백을 하면 잘못된 데이터를 가져 올 수 있으므로 주의해야 함
- 이를 더티 리드(Dirty read) 혹은 더티 페이지(Dirty page)라 부름
- Dirty page : 커밋되지 않은 상태로 있는 디스크와 메모리가 일치하지 않는 페이지(데이터)
- Dirty read : 더티 페이지의 값을 읽어오는 것
- 이를 더티 리드(Dirty read) 혹은 더티 페이지(Dirty page)라 부름
Level 1 : READ COMMITTED
- 우리가 흔히 사용하는 트랜잭션 고립 수준이며 기본 값(default)
- 커밋되지 않은 트랜잭션에 대해 잠금이 걸리고 데이터를 읽어갈 수 없게 됨
Level 2 : REPEATABLE READ
- 트랜잭션이 SELECT로 문장 수행시 해당되는 데이터들을 트랜잭션이 끝날때까지 잠금
- 원래는 SELECT 문장 수행시에만 잠깐 잠금이 걸림
- 반복적으로 같은 값을 호출하였을때도 같은 값을 반환될 것을 보장
Level 3 : SERIALIZABLE
레벨 2 수준의 Repeatable Read는 해당되는 데이터만 잠금을 함
레벨 3 수준의 Serializable은 해당되는 데이터와 관련있는 데이터까지도 잠금
트랜잭션 잠금 (Lock)
잠금 정보 확인 (SP_LOCK)
-- 서버에 걸린 모든 잠금 정보 확인
EXEC sp_lock
-- 실행시킨 세션에서 걸고 있는 잠금 정보 확인
EXEC sp_lock @@spid
(사용 환경마다 id는 다를 수 있음)
- spid : 현재 사용자의 spid는 51
- dbid : dbid 5와 1 확인
- db_name() : dbid가 어떤 데이터베이스인지 확인 가능
select db_name(1) as dbid1
select db_name(5) as dbid5
잠금이 걸려 있는 이유
- 현재 세션에서 접속된 데이터베이스는 아무 트랜잭션이 없어도 기본적으로 잠금이 걸려있음
잠금된 트랜잭션 고립화 수준 확인 (DBCC USEROPTIONS)
-- 현재 접속되어 있는 세션의 설정 확인
DBCC USEROPTIONS
잠금 대기 시간 설정 (SET LOCK_TIMEOUT)
- 잠금이 걸린 데이터에 대해 처리를 할 때 지정된 시간만큼 대기 후 timeout 처리
- 세션 수준으로 설정
- 단위는 ms (1000 = 1초)
- 설정 값 (default : -1)
- -1 : 기본 값이며, 잠금이 풀릴 때까지 대기
- 0 : 잠금이 걸려있으면 즉시 작업 취소
- n(숫자) : n초 대기 후 잠금이 풀리지 않으면 해당 작업을 취소
-- 잠금이 걸려 있으면 5초를 기다린다.
-- 5초 뒤 잠금이 풀리지 않으면 작업을 취소한다.
-- 5초 뒤 잠금이 풀려있으면 작업을 실행한다.
SET LOCK_TIMEOUT 5000블록킹에 대한 정보 확인 (SP_WHO)
EXEC sp_who
참고
잠금에 대한 종류
종류에 대해 설명을 하면 머리만 아프고 어려워진다.
SQL 문서를 확인하면 잠금 종류에 대한 설명이 자세히 나와있다.
굳이 종류를 다 외우는 것보다는 잠금으로 인해 DB 작업이 어려울 경우,
SQL 문서를 확인하여 어떤 잠금인지 파악하면 문제 해결이 더욱 수월 할 것 같다.
참고된 사이트
- Microsoft - SQL 문서 - 트랜잭션 잠금 및 행 버전 관리 지침
트랜잭션 잠금 및 행 버전 관리 지침 - SQL Server
트랜잭션 잠금 및 행 버전 관리 지침
learn.microsoft.com
반응형