자동 커밋 트랜잭션
각 쿼리마다 자동으로 트랜잭션이 붙는 것
보통 사용하는 쿼리는 자동 커밋 트랜잭션이 작동 됨
명시적 트랜잭션
직접 begin, commit 을 사용하는 것
암시적 트랜잭션
타 데이터베이스와 호환을 위해 사용
자동으로 begin tran이 붙지만 commit tran이나 rollback tran 은 직접 써줘야 하는 것
set implicit_transaction on 을 사용하면 암시적 트랜잭션이 시작 됨
잠금
sql server에서 잠금이 중요한 이유는 일관성 떄문
잠금은 동시에 여러 사용자가 하나의 데이터에 접근할 경우 즉 트랜잭션 상황에서 데이터의 격리 수준을 말함
↑약한 격리 수준
read uncommitted (커밋되지 않은 읽기)
동시성은 높지만 일관성이 낮음
read committed (커밋된 읽기) <- sql server 2005 기본값
데이터 변경이 있는 트랜잭션 중에는 읽을 수 없음
update나 insert로 배타적 잠금 (exclusive lock)이 발생되어 dirty read 문제 해결
select에 의한 공유잠금(shared lock)일 경우 데이터 변경이 가능한 unrepeatable read 발생
repeatable read (반복된 읽기)
공유잠금 상태에서 update를 통한 데이터 변경이 불가
하지만 insert를 통한 새로운 데이터 입력은 가능 (phantom read)
snapshot
트랜잭션중인 테이블에 데이터를 입력하면 tempdb에 임시로 저장하고 커밋 후에 원래 테이블에 입력함
serializable (직렬화 가능)
phantom read를 해결
일관성은 높지만 동시성이 낮음
↓강한 격리 수준
*여러 사용자가 동시에 하나의 데이터에 접근하면 일어나는 문제
-dirty read (커밋되지 않은 데이터 읽기)
메모리 (더티 페이지)에 변경된 데이터를 읽는 것
디스크에 저장되지 않고 데이터가 롤백된다면 잘못된 데이터를 읽은것이 됨
-unrepeatable read (반복되지 않은 읽기)
데이터 변경이 있는 트랜잭션 중에는 읽을 수 없음
배타적 잠금 (exclusive lock)이 발생되어 더티리드 문제 해결
select에 의한 공유잠금(shared lock)일 경우에도 데이터 변경이 가능
-phantom read (가상 읽기)
공유잠금 상태에서 insert를 통한 새로운 데이터 입력이 가능
잠금이 걸리는 리소스
행 단위 부터 데이터베이스 단위 까지 여러 잠금 범위가 있음
단위가 작을수록 동시성은 좋지만 관리 할 부분이 많아져 오버헤드가 증가됨
단위가 크다면 반대로 관리 할 잠금 수가 줄어 오버헤드는 줄지만 동시성이 나빠짐
잠금 리소스 종류
RID : 행 식별자는 단일 행을 잠그는 데 사용
KEY : 인덱스 내의 행 잠금은 serializable 격리 수준에서 사용
PAGE : 8KB 크기의 데이터 페이지나 인덱스 페이지
EXTENT : 8개의 페이지가 연속된 것
HORT : 클러스터형 인덱스가 없는 테이블에서 데이터 페이지나 인덱스를 보호하는 잠금
TABLE : 데이터와 인덱스가 포함된 전체 테이블
FILE : 데이터베이스 파일
APPLICATION : 응용프로그램이 지정한 리소스
METADATA
ALLOCATION_UNIT : 할당 단위
DATABASE : 전체 데이터베이스
잠금 모드 종류
서로 같이 작동할 수 없는 것들도 있고, 다른 모드를 수정하는 것도 있음
공유잠금 (S:shared lock) : select같은 데이터 읽기 작업에 사용, 공유잠금 끼리는 호환됨 (동시에 같은 데이터 select 가능)
배타잠금 (X:exclusive lock) : 데이터 수정작업 (insert, update, delete)에 사용, 다른잠금과 호환 안됨, 하나의 리소스에 여러 작업을 못하게 방지
업데이트잠금 (U:update lock) : 업데이트할 수 있는 리소스에 사용, 여러 사용자가 업데이트할 때 발생하는 교착상태 방지
의도잠금 (intent lock) : 잠금 계층 구조를 만드는 데 사용, 의도공유 의도배타 의도배타공유 등이 있다
스키마잠금 ( schema lock) : 테이블 스키마에 종속되는 작업이 실행될 때 사용, 스키마수정, 스키마안정성 잠금이 있음
대량 업데이트 잠금 (bulk updqte lock) : 데이터를 테이블로 대량 복사하는 경우와 TABLOCK힌트가 지정된 경우 사용
키 범위 잠금 : serializable 트랜잭션 격리 수준을 사용할 때 쿼리가 읽는 행 범위를 보호