[SQLP] 2장. Lock과 트랜잭션 동시성 제어 - 동시성 제어

동시성 제어 (Concurrency Control)

1. 비관적 동시성 제어 vs 낙관적 동시성 제어

가. 비관적 동시성 제어

• 사용자들이 같은 데이터를 동시에 수정할 것이라고 가정

• Select 시점에 Lock을 거는 비관적 동시성 제어는 자칫 시스템 동시성을 심각하게 떨어뜨릴 우려가 있음 -> no wait 옵션 사용

• holdlock, updlock 힌트 사용 권장

나. 낙관적 동시성 제어

• 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정

• Select 시에는 Lock을 설정하지 않음 -> 수정 시점에 다른 사용자에 의해 값이 변경됐는지 검사 필요

2. 다중버전 동시성 제어

가. 일반적인 Locking 메커니즘의 문제점

• 값을 읽는 순간에만 공유 Lock을 걸고 다음 레코드로 이동할 때 Lock을 해제함

• 트랜잭션 격리성 수준을 상향 조정하면 일관성이 높아지지만, Lock이 더 오래 유지됨으로 인해 동시성 저하 및 교착상태 발생 가능성 증가

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

• 데이터를 변경할 때마다 변경사항을 Undo 영역에 저장해 둠

• 데이터를 읽다가 트랜잭션 시작 시점 이후에 변경된 값을 발견하면, Undo 영역에 저장된 정보를 이용해 트랜잭션 시작 시점의 일관성 있는 버전(CR Copy)을 생성하고 그것을 읽음

• Undo 블록 I/O, CR Copy 생성, CR 블록 캐싱 등의 부가적인 작업 때문에 오버헤드 생길 우려 있음

• MVCC를 이용한 읽기 일관성에는 문장수준과 트랜잭션 수준, 2가지가 있음

다. 문장수준 읽기 일관성 (Statement-Level Read Consistency)

• 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것

• 일관성 기준 시점은 쿼리 시작 시점

사용 예시

alter database <데이터베이스 이름> set read_committed_snapshot on;

라. 트랜잭션 수준 읽기

• 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것

• 트랜잭션 격리성 수준을 Serializable Read로 상향 조정하면, 일관성 기준 시점은 트랜잭션 시작 시점이 됨

마. Snapshot too old

• Undo 데이터를 활용함으로써 일반적인 Locking 메커니즘에 없는 Snapshot too old 에러가 MVCC에서 발생

• Undo 영역에 저장된 Undo 정보가 다른 트랜잭션에 의해 재사용돼 필요한 CR Copy을 생성할 수 없을 때 발생

• Snapshot too old 에러 발생 가능성 줄이는 방법

  • Undo 영역의 크기를 증가시킨다.

  • 불필요한 커밋을 자주 수행하지 않는다.

  • 커밋 이전에 열려 있던 커서는 더는 Fetch 하지 않는다.

  • 소트 부하를 감수하더라도 Order by 등을 강제로 삽입해 소트 연산이 발생하도록 한다.

  • 같은 블록을 여러번 반복하는 쿼리는 회피할 수 있는 방법(조인 메소드 수행, Full Table Scan)을 찾는다.

  • 대량 업데이트 후에 곧바로 해당 테이블 또는 인덱스를 Full Scan 하도록 한다.