3-7-3. 동시성 제어

동시성 제어(Concurrency Control): 동시에 작동하는 다중 트랜잭션의 상호 간섭 작용에서 데이터베이스를 보호하는 것.

동시성 제어 기법에는 '비관적' 동시성 제어와 '낙관적' 동시성 제어 2가지가 있다.

 

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

가. 비관적 동시성 제어(Pessimistic Concurrency Control): 사용자들이 같은 데이터를 동시에 수정할 것이라고 가정해, 데이터를 읽는 시점에 Lock을 걸고, 트랜잭션이 완료될 때까지 이를 유지한다.

 

select 적립포인트, 방문횟수, 구매실적

from 고객

where 고객번호 =: cust_num for update;

 

-- 새로운 적립포인트 계산

update 고객 set 적립포인트 =: 적립포인트 where 고객번호 =: cust_num;

select 시점에 Lock을 거는 비관적 동시성 제어는 자칫 시스템 동시성을 심각하게 떨어뜨릴 우려가 있다. 그러므로 다음과 같이 wait, nowait 옵션을 함께 사용하는 것이 바람직하다.

 

for update nowait --> 대기없이 Exception을 던짐

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

SQL Server에서도 for update 절을 사용할 수는 있지만, 커서를 명시적으로 선언할 때만 가능하다. 따라서, SQL Server에서 비관적 동시성 제어를 구현할 때는 holdlock이나 updlock 힌트를 사용하는 것이 편리하다.

 

나. 낙관적 동시성 제어: Optimistic Concurrency Control

비관적과 반대로, 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정한다. 대신, 수정 시점에 다른 사용자에 의해 값이 변경되었는지는 반드시 검사해야 한다.

 

select 적립포인트, 방문횟수, 구매실적 into :a, :b, :c

from 고객

where 고객번호 =: cust_num;

 

-- 새로운 적립포인트 계산

update 고객 set 적립포인트 =: 적립포인트

where 고객번호 := cust_num

and 적립포인트 := a

and 방문횟수 := b

and 구매실적 := c;

 

if sql%rowcount = 0 then

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

end if;

최종 변경일시를 관리하는 칼럼이 있다면, 다음과 같이 좀 더 간단하게 구현할 수 있다.

 

select 적립포인트, 방문횟수, 구매실적, 변경일시

into :a, :b, :c, :mod_dt

from 고객

where 고객번호 =: cust_num;

 

-- 새로운 적립포인트 계산

update 고객 set 적립포인트 =: 적립포인트, 변경일시 = SYSDATE

where 고객번호 =: cust_num

and 변경일시 =: mod_dt;          -- 최종 변경일시가 앞서 읽은 값과 같은지 비교

 

2. 다중버전 동시성 제어

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

 - 읽기 작업에 공유 lock을 사용하는 일반적인 locking 메커니즘에서는 읽기 작업과 쓰기 작업이 서로 방해를 일으켜 동시성 문제가 생김

 - 데이터 일관성에 문제가 있어, 이를 해결하기 위해 lock을 더 오랫동안 유지하거나 테이블 레벨 lock을 사용해야 하므로 동시성을 더 심각하게 떨어뜨리는 결과를 발생시킴

 

나. 다중버전 동시성 제어

 - Oracle은 위를 해결하기 위해 버전 3부터 다중버전 동시성 제어(Multiversion Concurrency Control, MVCC) 메커니즘을 사용해왔다. 간단히 요약하면 아래와 같다.

• 데이터를 변경할 때마다 그 변경 사항을 undo 영역에 저장해 둔다.
• 데이터를 읽다가 쿼리 시작 시점 이후에 변경된 값을 발견 시, undo 영역에 저장된 정보를 이용해 쿼리 시작 시점의 일관성 있는 버전을 생성하고 그것을 읽는다.

 

다. 문장수준 읽기 일관성(Statement-Level Read Consistency): 다른 트랜잭션에 의해 데이터의 추가-변경-삭제가 발생하더라도, 단일 SQL 문에서 일관성 있게 값을 읽는 것을 말한다.

SQL Server에서 문장수준 읽기 일관성 모드로 DB를 운영하려면 다음 명령을 수행해 주면 된다.

 

alter database <database name> set read_committed_snapshot on;

라. 트랜잭션 수준 읽기 일관성(Transaction-Level Read Consistency): 다른 트랜잭션에 의해 데이터의 추가-변경-삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것을 의미한다.

물론 완벽하지는 않으므로, 완벽한 읽기 일관성을 보장받으려면 serializable read로 올려주어야 한다.

 

SQL Server에서 트랜잭션 읽기 일관성 모드로 DB를 운영하려면 다음 명령을 수행해 주면 된다.

 

alter database <database name> set allow_snapshot_isolation on;

그리고 트랜잭션을 시작하기 전, 격리성 수준을 다음과 같이 'snapshot'으로 변경해주면 된다.

 

set transaction isolation level snapshot

begin tran ....

 

마. Snapshot too old

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

위 에러를 해결하기 위한 방법은 아래와 같다.

1. Undo 영역의 크기를 증가시킨다.
2. 불필요한 커밋을 자주 수행하지 않는다.
3. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다.
4. 트랜잭션이 몰리는 시간대에, 오래 걸리는 쿼리가 같이 수행되지 않도록 시간을 조정한다.
5. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다.
6. 오랜 시간에 걸쳐 같은 블록을 여러 번 방문하는 Nested Loop 형태의 조인문 또는 인덱스를 경유한 테이블 엑세스를 수반하는 프로그램이 있는지 확인하고, 이를 회피할 조인 메소드 변경 및 Full Table Scan 등 방법들을 찾는다.
7. 소트 부하를 감수하더라고, order by 등을 강제로 삽입해 소트연산이 발생하도록 한다.
8. 대량 업데이트 후 곧바로 해당 테이블 또는 인덱스를 Full Scan하도록 쿼리를 수행한다.