하나의 DBMS는 다수의 사용자가 사용합니다. 따라서 동시에 작동하는 다중 트랜잭션의 상호간섭작용에서 데이터베이스를 보호해야 합니다. 이를 동시성 제어 라고 합니다.
DBMS는 LOCK 기능을 통해 동시성을 제어합니다. 여러 사용자가 데이터를 동시에 엑세스하는 것처럼 보이지만 LOCK을 통해 내부적으로 직렬화해 처리합니다.
또한 SET TRANSACTION 기능을 이용해 트랜잭션의 격리성 수준을 조정할 수 있는 기능을 제공합니다.
동시성 제어는 동시성과 일관성이 서로 TRADE OFF 관계입니다. 동시성을 높이기 위해 LOCK을 최소화하면 일관성을 유지하기가 어렵고, 일관성을 높이기 위해 LOCK을 적극적으로 사용하면 동시성이 저하됩니다. 따라서 동시성 제어의 목표는 동시에 실행되는 트랜잭션의 수를 최대화하면서 데이터 CRUD 시, 무결성이 유지되도록 하는데 있습니다.
비관적 동시성 제어 vs 낙관적 동시성 제어
비관적 동시성 제어
사용자들이 데이터를 동시에 수정할 것이라고 가정한 상태에서 동시성을 제어합니다. 따라서 데이터를 읽는 시점에 LOCK을 걸고 트랜잭션이 완료될때 까지 이를 유지합니다. SELECT 시점에 LOCK을 거는 비관적 동시성 제어는 동시성을 크게 떨어뜨립니다. 그러므로 다음과 같이 WAIT, NOWAIT 옵션을 함께 사용하는 것이 바람직합니다.
select 적립포인트, 방문횟수, 최근방문일시, 구매실적
from 고객
where 고객번호 = :cust_num for update;
-- 새로운 적립포인트 계산
update 고객 set 적립포인트 = :적립포인트 where 고객번호 = :cust_num;
-- 조회 대상 레코드에 LOCK이 걸려있으면 대기없이 예외처리
for update nowait
-- 3초 대기 후 여전히 LOCK이 걸려있으면 예외처리
for update wait 3
낙관적 동시성 제어
사용자들이 동시에 같은 데이터를 수정하지 않을 것이라 가정합니다. 따라서 조회시점에 LOCK을 설정하지 않습니다. 수정 시점에 다른 사용자에 의해 값이 변경됐는지 반드시 검사해야합니다.
select 적립포인트, 방문횟수, 최근방문일시, 구매실적 into :a, :b, :c, :d
from 고객
where 고객번호 = :cust_num
-- 새로운 적립포인트 계산
update 고객 set 적립포인트 = :적립포인트
where 고객번호 = :cust_num
and 적립포인트 = :a
and 방문횟수 = :b
and 최근방문일시 = :c
and 구매실적 = :d;
-- 이 트랜잭션을 통한 update 결과가 있는지 확인
if sql%rowcount = 0 then
alert('다른 사용자에 의해 변경됐습니다.');
end if
변경일시를 관리하는 칼럼이 있다면, 좀 더 간단하게 구현할 수 있습니다.
select 적립포인트, 방문횟수, 최근방문일시, 구매실적 into :a, :b, :c, :d, :mod_dt
from 고객
where 고객번호 = :cust_num
update 고객 set 적립포인트 = :적립포인트, 변경일시 = sysdate
where 고객번호 = :cust_num
and 변경일시 = :mod_dt; -- 최종 변경일시가 앞서 읽은 값과 같은지 비교다중버전 동시성 제어
일반적인 LOCKING 메커니즘의 문제점
읽기 작업에 공유 LOCK을 사용하는 일반적인 LOKING 메커니즘에서는 읽기작업과 쓰기작업이 서로 방해를 일으킵니다. 데이터 일관성에 문제가 생기는 경우 이를 해결하려면 LOCKING을 더 오래 유지하거나 테이블 LOCK을 이용해야하는데 이는 동시성을 더 심각하게 떨어뜨립니다.
다중버전 동시성 제어
읽기 작업과 쓰기 작업은 서로를 방해해 동시성을 떨어뜨립니다. 공유 LOCK을 사용해도 데이터 일관성이 훼손되는 문제를 해결하기 위해 ORACLE은 다중버전 동시성 제어 메커니즘 (MVCC) 을 사용합니다.
- 데이터를 변경할 때마다 그 변경사항을 UNDO 영역에 저장합니다.
- 데이터를 읽다가 쿼리 시작 시점 이후에 발견하면 UNDO 영역에 저장된 정보를 이용해 쿼리 시작점의 버전을 생성해 읽습니다.
쿼리 도중 배타적 LOCK이 걸린, 즉 변경이 진행중인 레코드를 만나더라도 대기하지 않고 UNDO 데이터를 활용하기 때문에 동시성 측면에서 매우 유리합니다. 사용자에게 제공되는 데이터 기준 시점이 쿼리 시작 시점으로 고정되기 때문에 일관성 측면에서도 유용합니다.
단점은 UNDO 블록, CR COPY, CR 블록 캐싱 같은 부가적인 작업 때문에 생기는 오버헤드 입니다.
MVCC를 이용한 읽기 일관성에는 문장수준과 트랜잭션 수준 2가지가 있습니다.
문장수준 읽기 일관성
다른 트랜잭션에 의해 데이터의 추가/변경/삭제가 발생하더라도 단일 SQL 내에서 일관성 있게 값을 읽습니다. 기준 시점은 쿼리 시작 시점입니다.
트랜잭션 수준 읽기 일관성
다른 트랜잭션에 의해 데이터의 추가/변경/삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것을 말합니다. 기본 트랜잭션 격리성 수준에서 완벽한 문장 수준의 읽기 일관성을 보장하는 MVCC 메커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않습니다. 트랜잭션 수준으로 완벽한 읽기 일관성을 보장받기 위해선 격리성 수준을 SERIALIZABLE READ로 올려줘야 합니다.
SNAPSHOT TOO OLD
UNDO 데이터를 활용해 높은 수준의 동시성과 일관성을 유지하는 대신, 일반적인 LOCKING 메커니즘에선 발생하지 않는 SNAPSHOT TOO OLD 에러가 MVCC 에선 발생합니다. UNDO 영역에 저장된 정보가 다른 트랜잭션에 의해 재사용되어 필요한 CR COPY를 생성할 수 없을 때 발생합니다. SNAPSHOT TOO OLD 에러 발생 가능성을 줄이는 방법은 다음과 같습니다.
- UNDO 영역의 크기를 증가시킵니다.
- 불필요한 커밋을 자주 수행하지 않습니다.
- fetch accross commit 형태의 프로그램 작성을 지양합니다. fetch across commit은 프로그램 내에서 cursor를 선언하고 loop를 수행하면서 fetch하고 데이타를 변경할 때 롤백 세그먼트의 사용량을 줄이기 위해서 매 loop시마다 commit을 하는 것을 말합니다.
- 트랜잭션이 몰리는 시간대에는 오래 걸리는 쿼리가 같이 수행되지 않도록 시간을 조정합니다.
- 큰 테이블을 일정 범위로 나눠 읽고 단계적으로 실행할 수 있도록 코딩합니다.
- 오랜 시간동안 같은 블록을 여러번 반복하는 프로그램을 지양합니다.
- 소트 부하를 감소하더라도 order by 를 삽입해 소트연산이 발생하도록합니다.
- 대량 업데이트 후에 곧바로 테이블 또는 인덱스 FULL SCAN 하도록 쿼리를 수행하는 것도 해결방법이 될 수 있습니다.