SQLP - 동시성 제어

@markdown

# 동시성 제어

- DBMS는 다수의 사용자를 가정하며, 동시에 작동하는 다중 트랜잭션의 상호간섭 작용에서 데이터베이스를 보호 할 수 있어야 함

- 동시성을 제어할 수 있도록 하기 위해 모든 DBMS가 공통적으로 Lock 기능을 제공

- `SET TRANSACTION` 명령어를 이용해 트랜잭션 격리성 수준을 조정할 수 있는 기능도 제공

#### 동시성과 일관성의 상관관계


# 비관적 동시성 제어 vs 낙관적 동시성 제어

____

## 비관적 동시성 제어(Pessimistic Concurrency Control)

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

- 데이터를 읽는 시점에 Lock을 걸고 트랜잭션이 완료될 떄까지 이를 유지

<pre><code class="sql" style="font-size:15px">

SELECT 적립포인트, 방문횟수, 최근방문일시, 구매실적

FROM 고객

WHERE 고개번호 = :CUST_NUM FOR UPDATE;

새로운 적립포인트 계산

UPDATE 고객 SET 적립포인트 = :적립포인트 WHERE 고객번호 = :CUST_NUM;

</code></pre>

- SELECT 시점에 Lock을 거는 비관적 동시성 제어는 시스템 동시성을 심각하게 떨어뜨릴 우려가 있음

- 아래의 wait 또는 nowait 옵션을 함께 사용하는 것이 바람직

<pre><code class="sql" style="font-size:15px">for update nowait --> 대기 없이 Exception을 던짐

for update wait 3 --> 3초 대기 후 Exception을 던짐

</code></pre>

<br/>

## 낙관적 동시성 제어(Optimistic Concurrency Control)

____

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

- 데이터를 읽는 시점에 Lock을 걸지 않음

- 대신 수정 시점에 다른 사용자에 의해 값이 변경됐는지를 반드시 검사해야함

<pre><code class="sql" style="font-size:15px">SELECT 적립포인트, 방문횟수, 최근방문일시, 구매실적

INTO :a, :b, :c, :d

FROM 고객

WHERE 고객번호 = :cust_num;

새로운 적립포인트 계산

UPDATE 고객 SET 적립포인트 = :적립포인트

WHERE 고객번호 = :cust_num

AND 적립포인트 = :a

AND 방문횟수 = :b

AND 최근방문일시 = :c

AND 구매실적 = :d;\\

IF sql%rowcount = 0 then

alert('다른 사용자에 의해 변경되었습니다.');

END IF;

</code></pre>

<br/>

# 다중버전 동시성 제어

____

## 일반적인 Locking 매커니즘의 문제점

- 동시성 제어의 목표는 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성을 유지하는데 있다.

- 읽기 작업에 공유 Lock을 사용하는 일반적인 Locking 매커니즘에서는 읽기 작업과 쓰기 작업이 서로 방해를 일으키기 때문에 종종 동시성에 문제가 발생

- 데이터 일관성에 문제가 생기는 경우도 있어 이를 해결하려면 Lock을 더 오랫동안 유지하거나 테이블 레벨 Lock을 사용해야 하므로 동시성 저하 발생

## 다중버전 동시성 제어

____

- Oracle은 버전 3부터 다중버전 동시성 제어(Multiversion Concurrency Control, MVCC) 매커니즘을 사용

- 쿼리 도중 배타적 Lock이 걸린, 즉 변경이 진행 중인 레코드를 만나더라도 대기 하지 않기 때문에 동시성 측면에 유리

- UNDO 블록 I/O, CR Copy 생성, CR 블록 캐싱 같은 부가적인 작업의 오버헤드 발생

- Oracle은 UNDO 데이터를 UNDO 세그먼트에 저장

### MVCC란

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

- 데이터를 읽다가 쿼리 또는 트랜잭션 시작 시점 이후에 변경된 값을 발견하면, UNDO 영역에 저장된 정보를 이용해 쿼리 시작 시점의 일관성 있는 버전을 생성하고 읽음

<br/>

## 문장수준 읽기 일관성

____

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

- 일관성 기준 시점은 쿼리 시작 시점이 된다.


- 위의 그림은 10023 시점에 시작된 쿼리가 10023 시점 이후에 변경된 데이터 블록을 만났을 대, Rollback(=UNDO) 세그먼트에 저장된 정보를 이용해 10023 이전 시점으로 되돌리고서 값을 읽는 것을 표현

<br/>

## 트랜잭션 수준 읽기 일관성

____

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

- Read Committed에서 완벽한 문장수준의 읽기 일관성을 보장하는 MVCC 매커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않음

- 트랜잭션 수준으로 완벽한 읽기 일관성을 보장받으려면 격리성 수준을 Serializable Read로 올려주어야 함

- Isolation Level을 Serializable Read로 상향 조정하면, 일관성 기준 시점은 트랜잭션 시작 시점이 됨

- 물론 트랜잭션이 진행되는 동안 자신이 발생시킨 변경사항은 그대로 읽음

- 

<pre><code class="sql" style="font-size:15px">alter database <데이터베이스 이름> set allow_snapshot_isolation on;

-- 트랜잭션 시작하기 전 'snapshot'으로 변경

set tranaction isolation level snapshot

begin tran

select ...;

update ...;

commit;

</code></pre>

<br/>

## SNAPSHOT TOO OLD

____

- UNDO 데이터를 활용함으로써 높은 수준의 동시성과 읽기 일관성을 유지하는 대신, 일반적인 Locking 매커니즘에 없는 SNAPSHOT TOO OLD 에러가 MVCC에서 발생

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

### SNAPSHOT TOO OLD 줄이는 방법

- 1. UNDO 영역의 크기를 증가

- 2. 불필요한 커밋을 자주 수행하지 않음

- 3. FETCH ACROSS COMMIT 형태의 프로그램 작성을 피해 다른 방식으로 구현

- 4. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 조정

- 5. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩

- 6. 오랜 시간에 걸쳐 같은 블록을 여러번 방문하는 NL Join 형태의 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고, 이를 회피할 수 있는 방법을 찾음

- 7. Sort 부하를 감수하더라도 Order by 등을 강제로 삽입해 Sort 연산이 발생하도록 함

- 8. 대량 업데이트 후에 곧바로 해당 테이블 또는 인덱스를 Full Scan 하도록 쿼리를 수행하는 것도 하나의 해결 방법

> SQL 전문가 가이드 : 과목3. SQL 고급 활용 및 튜닝, 2장 Lock과 트랜잭션 동시성 제어 - 제3절 동시성 제어(p.547 ~ p.553)

> 사진 출처 : 구루비넷