[MSSQL/SQL SERVER] 격리수준, 락(lock)

격리수준

 

좋지 않은 읽기 방식의 예

Dirty Read, Unrepeatable Read, Phantom Read

 

Dirty Read : 메모리(데이터 캐시)에서는 변경되었지만 페이지(Disk)에선 변경되지 않은 데이터를 읽는것

ex)상대방이 입금했다하여 확인을 하였는데 확인 후 상대방이 롤백하여 실제로는 입금되지 않은 상황

-> 방지하기 위해선 READ COMMITTED 이상의 격리수준을 사용한다.

 

Unrepeatable Read(=Non repeatable read) : 트랜잭션 내에서 한 번 읽은 데이터가 트랜잭션이 끝나기 전에 변경되었다면, 다시 읽었을 때 새로운 값이 읽히는 것. 

Dirty Read 와의 차이점 : 행범위를 제한한다, 공유 잠금이 트랜잭션 끝까지 유지된다.

ex) 금액이 500원일 때 SELECT를 하였고 COMMIT 을 하기전에 금액 변경이 일어났을때 SELECT를 하면 500원이 아닌 변경된 돈으로 조회되는 경우. -> 기존 SELECT가 COMMIT이 되어야 SELECT를 새로 하였을때 변경된 데이터 값을 조회할 수 있어야한다.

-> 방지하기 위해선 격리수준을 REPEATABLE READ 이상으로 설정하여야한다.

 

Phantom Read : REPEATABLE READ 격리 수준에서는 트랜잭션이 진행 중인 데이터에 대해서 변경 작업을 할 수 없다. 하지만 

새로운 데이터의 입력 작업은 가능. 이것을 Phantom Read라 한다. 

반복되지 않는 읽기와의 차이점 : 트랜잭션 안에서 공유 잠금이 계속 유지된다, 해당 범위의 행에 대한 "범위 잠금"이 일어난다

 

좋지 않은 읽기를 방지하기 위해 상황에 따른 엔진격리 수준을 지정해야함.

 

 

커밋되지 않은 읽기

EX) UPDATE를 수행한 행이 트랜잭션이 끝나기 전에도 조회가 된다.

1. SELECT * FROM table WITH (READUNCOMMITTED) WHERE ...

2. SELECT * FROM table WITH (NOLOCK) WHERE...

3. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

커밋된 읽기

EX) 테이블의 같은 행에 UPDATE했을 때 해당 트랜잭션이 끝나기 전에는 SELECT 할 수 없다

(대기 상태가 되어 트랜잭션이 끝난 후 조회된다)

1. SELECT * FROM table WITH (READCOMMITTED) WHERE ...

2. SET TRANSACTION ISOLATION LEVEL READ COMMITTED

 

반복읽기 

EX) 특정 범위의 행들을 SELECT하면 해당 트랜잭션이 끝날 때까지 범위에 해당하는 행에 다른 세션에서 UPDATE를 할 수없다(INSERT는 범위 외로 지정되어 할 수있다)

1. SELECT * FROM table WITH (REPEATABLEREAD) WHERE ...

2. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 

순차 가능(serializable)

대부분 동작은 REAPEATABLE READ 와 비슷하지만 이 격리 수준은 쿼리필터의 대상이 되는 키 값들의 모든 범위에 대해 잠금을 유지한다. 읽기 작업에는 쿼리 필터의 대상이 되는 기존 행 뿐만 아니라, 앞으로 사용될 행에 대해서도 잠금을 설정한다. 그러므로 다른 트랜잭션에서 INSERT 하는 행도 읽기 작업의 쿼리 필터의 대상이 될 수 있기 때문에 차단된다.

 

1. SELECT * FROM table WITH (serializable) WHERE ...

2. SET TRANSACTION ISOLATION LEVEL serializable

 

SNAPSHOT

SQL Server의 행버전 관리의 특징상 tempDB에 커밋된 행들의 이전 버전을 기록한다.

이 특성을 이용한 격리수준으로 트랜잭션이 시작된 시점에 사용할 수 있는 최신의 커밋 데이터만 사용 가능하다.

SNAPSHOT은 트랜잭션이 진행 중인 테이블에 새 데이터를 추가하면 그 데이터를 실제 테이블에 적용하는 것이 아니라 우선 tempDB에 적용을 시켜놓고 원래 테이블의 트랜잭션이 종료되면 이 tempDB에 적용시켰던 데이터를 다시 원래 테이블로 입력하는 것이다.

S-LOCK 대신 행 버전 관리 기능을 이용한다는 다른 격리 수준과 차이점이 있다.

엔진격리 수준	문제점
READ UNCOMMITTED(동시성 높음 일관성 낮음)	D.R, N.R.R, P.R
READ COMMITTED	N.R.R, P.R
REPEATABLE READ	P.R
SNAPSHOT
SERIALIZABLE(동시성 낮음 일관성 높음)	없다

 

 

 

-Lock

 

S(공유)락 : 조회중인 데이터에 조회 외의 접근을 막는 락

 

X(배타)락 : 업데이트할 수 있는 리소스에 사용, 여러 사용자가 업데이트할 때 발생하는 교착 상태를 방지한다.

배타적잠금은 가장 높은 강도의 잠금으로서, Update가 행해진 시점부터 그 트랜잭션이 Commit될 때까지 배타적 잠금이 걸린다. 

배타적 잠금은 다른 모든 종류의 잠금과 호환되지 않는다. 

이 의미는 어떠한 약한 잠금이라도 걸려있는 레코드에 대해서는 Update가 불가능하며, 반대로 Update가 진행중인 레코드에 대해서는 Select를 포함한 어떠한 작업도 불가능하다는 의미가 된다.

 

U(업데이트)락 : 업데이트잠금은 공유잠금과 배타적잠금의 중간 강도의 잠금이다. 공유잠금과는 호환되지만 다른 업데이트잠금이나 배타적 잠금과는 호환되지 않는다. 일반적으로는 Update의 Filter(Where절)가 수행되는 단계에서 업데이트 잠금이 걸리며, Filter된 결과에 대해 실제로 Update를 시도할 때 업데이트잠금은 배타적 잠금으로 전환된다. (만약 테이블에 인덱스가 없거나 Where절이 인덱스를 탈 수 없게 되어 있다면, 테이블을 풀스캔하면서 모든 레코드에 업데이트잠금을 걸 것이다.)

업데이트잠금은 잠금힌트를 통해 업데이트문이 아닌 Select문에도 걸 수 있다. 보통 컨버젼 데드락을 방지하기 위해 Select문에 업데이트 잠금을 거는 경우가 많다.

 

intent락 : 시스템적으로 걸리는락.

 

※

트랜잭션은 세션단위로 이루어진다

sp_lock은 spid, dbid, objid, indid 등이 있고 ix와 ix는 서로 호환된다.