(SQLP SQLD 핵심 정리 7) LOCK과 트랜잭션 동시성 제어

LOCK에 의한 성능 저하를 최소화하는 방안

- 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션을 가능한 한 짧게 정의할 것

- 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계할 것

- 주간에 대용량 갱신 작업이 불가피하다면, 블로킹 현상에 의해 사용자가 무한정 기다리지 않도록 적절한 프로그래밍 기법을 도입할 것

- 트랜잭션 격리성 수준을 불필요하게 상향 조정하지 않을 것

- 트랜잭션을 잘 설계하고 대기 현상을 피하는 프로그래밍 기법을 적용하기에 앞서, SQL 문장이 가장 빠른 시간 내에 처리를 완료할 것

 

블로킹(Blocking)?

- 블로킹(Blocking)은 Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태를 말한다.

- 공유 Lock끼리는 호환되기 때문에 블로킹이 발생하지 않는다.

- 공유 Lock 과 배타적 Lock 은 호환되지 않아 블로킹이 발생할 수 있다.

 

SQL Server Lock 특징

- SQL Server 의 공유 Lock은 트랜잭션이나 쿼리 수행이 완료될 때까지 유지되는 것이 아니라 다음 레코드가 읽히면 곧바로 해제된다.

- 단, 기본 트랜잭션 격리성 수준(Read Committed)에서만 그렇다.

- 격리성 수준을 변경하지 않고도 트랜잭션 내에서 공유 Lock이 유지되도록 하려면 테이블 힌트로 holdlock을 지정하면 된다.

- 두 트랜잭션은 상대편 트랜잭션에 의한 공유 Lock이 해제되기 만을 기다리는 교착상태를 방지하려고,  SQL Server는 갱신(Update) Lock을 두게 되었고, 이 기능을 사용하려면 updlock 힌트를 지정하면 된다.

 

테이블 Lock

- Row Share(RS)

- Row Exclusive(RX)

- Share(S)

- Share Row Exclusive(SRX)

- Exclusive(X)

 

낮은 단계의 격리성 수준에서 발생할 수 있는 현상들

1. Dirty Read 

  : 다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것을 말한다.

2. Non-Repeatable Read

  : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상을 말한다.

3. Phantom Read

  : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상을 말한다.

 

트랜잭션 격리성 수준

- Read Uncommitted

  : 트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.

- Read Committed

  : 트랜잭션이 커밋되어 확정된 데이터만 다른 트랜잭션이 읽도록 허용함으로써 Dirty Read를 방지해준다.

- Repeatable Read

  : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지해 준다.

- Serializable Read

  : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않음은 물론, 새로운 레코드가 나타나지도 않는다.

 

다중버전 동시성 제어(Multiversion Concurrency Control, MVCC)

- 데이터를 변경할 때마다 그 변경 사항을 Undo 영역에 저장해 둔다.

- 데이터를 읽다가 쿼리(또는 트랜잭션) 시작 시점 이후에 변경된(변경이 진행 중이거나 이미 커밋된) 값을 발견하면, 

Undo 영역에 저장된 정보를 이용해 쿼리(또는 트랜잭션) 시작 시점의 일관성 있는 버전(CR Copy)을 생성하고 그것을 읽는다.