동시성 제어(Concurrency Control)란 동시에 작동하는 다중 트랜잭션의 상호 간섭 작용에서 데이터베이스를 보호할 수 있어야 함을 의미한다.
동시성을 높이려고 Lock의 사용을 최소화하면 일관성을 유지하기 어렵고, 일관성을 높이려고 Lock을 적극적으로 사용하면 동시성이 저하된다.
-> 동시성 제어의 목표는, 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성이 유지되도록 하는 데에 있다.
동시성 제어 기법
비관적 동시성 제어
사용자들이 같은 데이터를 동시에 수정할 것이라고 가정한다. 따라서 한 사용자가 데이터를 읽는 시점에 Lock을 걸고 조회 또는 갱신 처리가 완료될 때까지 이를 유지한다.
for update nowait : Lock을 얻지 못한 경우 대기 없이 Exception 던짐
for udpate wait 3 : 3초동안 Lock을 얻지 못한 경우 대기 없이 Exception 던짐
wait 또는 nowait 옵션을 사용하여 트랜잭션을 종료하면 동시성을 증가시킬 수 있다.
낙관적 동시성 제어
사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정한다. 따라서 데이터를 읽을 때 Lock을 걸지 않는다. 대신 수정 시점에, 다른 사용자에 의해 값이 변경됐는지를 반드시 검사해야 한다.
sql%rowcount : 가장 최근에 수행된 SQL문에 의해 영향을 받은 행의 개수
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;select절에서 읽은 컬럼의 개수가 많다면 update 시 where절에 적어야 할 조건이 많아진다. 만약 update 대상 테이블에 최종변경일시 컬럼이 있다면 더 간단하게 레코드의 갱신 여부를 판단할 수 있다.
select 적립포인트, 방문횟수, 최근방문일시, 구매실적, 변경일시 into :a, :b, :c, :d, :mod_dt
from 고객
where 고객번호 = :cust_num;
update 고객 set 적립포인트 = :적립포인트, 변경일시 = SYSDATE
where 고객번호 = :cust_num
and 변경일시 = :mod_dt;
if sql%rowcount = 0 then
alert('다른 사용자에 의해 변경되었습니다.');
end if;다중버전 동시성 제어
동시성 제어의 목표는, 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성이 유지되도록 하는 것이다. 하지만, 일반적인 Locking 메커니즘에서 읽기 작업과 쓰기 작업이 서로 방해를 일으키기 때문에 종종 동시성에 문제가 생기곤 한다. 또한, 데이터 일관성에 문제가 생기는 경우도 있어 이를 해결하려면 Lock을 더 오랫동안 유지하거나 테이블 레벨 Lock을 사용해야 하므로 동시성을 더 심각하게 떨어뜨리는 결과를 낳는다.
이러한 문제를 해결하기 위해 Oracle은 버전 3부터 다중버전 동시성 제어(Multiversion Concurrency Control, MVCC) 메커니즘을 사용해 왔다. 이는 스냅샷 격리성 수준이라고도 하며 동시성과 일관성을 동시에 높이려는 노력의 일환이다.
- 데이터를 변경할 때마다 그 변경사항을 Undo 영역에 저장해둔다.
- 데이터를 읽다가 쿼리(또는 트랜잭션) 시작 시점 이후에 변경된 값을 발견하면, Undo 영역에 저장된 정보를 이용해 쿼리(또는 트랜잭션) 시작 시점의 일관성 있는 버전을 생성하고 그것을 읽는다.
쿼리 도중 배타적 Lock이 걸린 레코드를 만나더라도 대기하지 않기 때문에 동시성 측면에서 매우 유리하며 사용자에게 제공되는 데이터의 기준 시점이 쿼리(또는 트랜잭션) 시작 시점으로 고정되기 때문에 일관성 측면에도 유리하다. 하지만, Undo 블록 I/O, CR Copy 생성, CR 블록 캐싱같은 부가적인 작업 때문에 생기는 오버헤드도 무시할 수 없다.
MVCC를 이용한 읽기 일관성
문장수준 읽기 일관성
다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것을 말한다.
일관성 기준 시점 = 쿼리 시작 시점
SCN(System Change Number) : 블록이 마지막으로 변경된 시점의 정보
SELECT절의 SCN은 10023이다. 즉, 현재 데이터블록 내의 SCN이 10023보다 작으면 이전의 데이터, 크면 변경된 데이터라고 보면 된다.
데이터 블록들을 쭉 읽다가 변경된 블록을 발견하면 그 블록으로부터 CR 블록을 생성하고 Undo 레코드를 읽어 한 단계 이전 상태로 롤백한 후 읽는다.
(위 그림에서 10024(Current) -> 10008(CR), 10024(Current) -> 10021(CR)로 롤백 후 읽는다고 보면 된다.)
트랜잭션 수준 읽기 일관성
다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것을 말한다.
일관성 기준 시점 = 트랜잭션 시작 시점
물론 트랜잭션이 진행되는 동안 본인이 발생시킨 변경사항은 그대로 읽는다.
Snapshot too old
Undo 영역에 저장된 Undo 정보가 다른 트랜잭션에 의해 재사용돼 필요한 CR Copy를 생성할 수 없을 때 발생한다.
Snapshow too old 에러 발생 가능성을 줄이는 방법
1. Undo 영역의 크기를 증가시킨다.
2. 불필요하게 커밋을 자주 수행하지 않는다.
3. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다. ANSI 표준에 따르면 커밋 이전에 열려있던 커서는 더이상 Fetch하면 안된다.
4, 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다.
5. 오랜 시간에 걸쳐 같은 블록을 여러 번 방문하는 Nested Loop 형태의 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고, 이를 이를 회피할 수 있는 방법(조인 메소드 변경, Full Table Scan 등)을 찾는다.
6. 소트 부하를 감수하더라도 order by 등을 강제로 삽입해 소트연산이 발생하도록 한다.
7. 대량 업데이트 후에 곧바로 해당 테이블 또는 인덱스를 Full Scan 하도록 쿼리를 수행하는 것도 하나의 해결방법이 될 수 있다.
