DBMS는 다수의 사용자를 가정한다. 따라서 동시에 작동하는 다중 트랜잭션의 상호 간섭 작용에서 데이터베이스를 보호할 수 있어야 하며, 이를 동시성 제어(Concurrency Control)라고 한다. 동시성을 제어할 수 있도록 하기 위해 모든 DBMS가 공통적으로 Lock 기능을 제공한다. 여러 사용자가 데이터를 동시에 액세스하는 것처럼 보이지만 내부적으로는 하나씩 실행되도록 트랜잭션을 직렬화하는 것이다. 또한 set transaction 명령어를 이용해 트랜잭션 격리성 수준을 조정할 수 있는 기능도 제공한다. DBMS마다 구현 방식이 다르지만 SQL Server를 예로 들면, 기본 트랜잭션 격리성 수준인 Read Committed 상태에선 레코드를 읽고 다음 레코드로 이동하자마자 공유 Lock을 해제하지만, Repeatable Read로 올리면 트랜잭션을 커밋될 때까지 공유 Lock을 유지한다. 동시성 제어가 어려운 이유가 바로 여기에 있는데, [그림 Ⅲ-2-3]처럼 동시성(Concurrency)과 일관성(Consistency)은 트레이드 오프(Trade-off) 관계인 것이다. 즉, 동시성을 높이려고 Lock의 사용을 최소화하면 일관성을 유지하기 어렵고, 일관성을 높이려고 Lock을 적극적으로 사용하면 동시성이 저하된다. 따라서 동시성 제어의 목표는, 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성이 유지되도록 하는 데에 있다.

데이터베이스 개발자들이 간과해선 안 되는 중요한 사실은, DBMS가 제공하는 set transaction 명령어로써 모든 동시성 제어 문제를 해결할 수 없다는 점이다. n-Tier 아키텍처가 지배적인 요즘 같은 애플리케이션 환경에서 특히 그렇다. 예를 들어, 사용자가 자신의 계좌에서 잔고를 확인하고 인출을 완료할 때까지의 논리적인 작업 단위를 하나의 트랜잭션으로 처리하고자 할 때, 잔고를 확인하는 SQL과 인출하는 SQL이 서로 다른 연결(Connection)을 통해 처리될 수 있기 때문이다. DB와 연결하기 위해 사용하는 라이브러리나 그리드(Grid) 컴포넌트가 동시성 제어 기능을 제공하기도 하지만, 많은 경우 트랜잭션의 동시성을 개발자가 직접 구현해야만 한다. 동시성 제어 기법에는 비관적 동시성 제어와 낙관적 동시성 제어, 두 가지가 있다.

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

가. 비관적 동시성 제어

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

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

SQL Server에서도 for update절을 사용할 수 있지만 커서를 명시적으로 선언할 때만 가능하다. 따라서 SQL Server에서 비관적 동시성 제어를 구현할 때는 holdlock이나 updlock 힌트를 사용하는 것이 편리하며, 이에 대한 구체적인 활용 사례는 1절에서 공유 Lock, 갱신 Lock과 함께 이미 설명하였다.

나. 낙관적 동시성 제어

낙관적 동시성 제어(Optimistic Concurrency Control)에선 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정한다. 따라서 데이터를 읽을 때는 Lock을 설정하지 않는다. 대신 수정 시점에, 다른 사용자에 의해 값이 변경됐는지를 반드시 검사해야 한다. 아래는 낙관적 동시성 제어의 구현 예시다.

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

2. 다중버전 동시성 제어

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

동시성 제어의 목표는, 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성이 유지되도록 하는 데에 있다고 했다. 그런데 읽기 작업에 공유 Lock을 사용하는 일반적인 Locking 메커니즘에서는 읽기 작업과 쓰기 작업이 서로 방해를 일으키기 때문에 종종 동시성에 문제가 생기곤 한다. 또한 데이터 일관성에 문제가 생기는 경우도 있어 이를 해결하려면 Lock을 더 오랫동안 유지하거나 테이블 레벨 Lock을 사용해야 하므로 동시성을 더 심각하게 떨어뜨리는 결과를 낳는다. 어떤 경우인지 예를 들어보자. 아래와 같이 10개의 계좌를 가진 계좌 테이블이 있고, 잔고는 각각 1,000원씩이다.

이 테이블에서 잔고 총합을 구하는 아래 쿼리가 TX1 트랜잭션에서 수행되기 시작했다.

잠시 후, 계좌이체를 처리하는 아래 TX2 트랜잭션도 작업을 시작했다고 가정하자.

1. TX1 : 2번 계좌까지 읽는다. 현재까지의 잔고 총합은 2,000원이다.
2. TX2 : ①번 update를 실행한다. 7번 계좌 잔고는 1,100원이 되었고, 아직 커밋되지 않은 상태다.
3. TX1 : 6번 계좌까지 읽어 내려간: ②번 update를 실행함으로써 3번 계좌는 900원, 7번 계좌는 1,100인 상태에서 커밋한다.
5. TX1 : 10번 계좌까지 읽어 내려간다. 7번 계좌 잔고를 1,100으로 바꾼 TX2 트랜잭션이 커밋되었으므로 이 값을 읽어서 구한 잔고 총합은 10,100이 된다.

어떤 일이 발생했는가? TX2 트랜잭션이 진행되기 직전의 잔고 총합은 10,000원이었고, TX2 트랜잭션이 완료된 직후의 잔고 총합도 10,000원이다. 어느 순간에도 잔고 총합이 10,100원인 순간은 없었으므로 방금 TX1의 쿼리 결과는 일관성 없게 구해진 값이다. 위와 같은 비일관성 읽기 문제를 해결하기 위한 일반적인 해법은 트랜잭션 격리성 수준을 상향 조정하는 것이다. 기본 트랜잭션 격리성 수준(Read Committed)에서는 값을 읽는 순간에만 공유 Lock을 걸었다가 다음 레코드로 이동할 때 Lock을 해제함으로 인해 위와 같은 현상이 발생했기 때문이다. 트랜잭션 격리성 수준을 Repeatable Read로 올리면 TX1 쿼리가 진행되는 동안 읽은 레코드는 공유 Lock이 계속 유지되며, 심지어 쿼리가 끝나고 다음 쿼리가 진행되는 동안에도 유지된다. 이처럼 트랜잭션 격리성 수준을 상향 조정하면 일관성이 높아지지만, Lock이 더 오래 유지됨으로 인해 동시성을 저하시키고 교착상태가 발생할 가능성도 커진다. 바로 위 사례가 대표적인 케이스다. TX2가 ①번 update를 통해 7번 레코드에 배타적 Lock을 설정하고 TX1은 3번 레코드에 공유 Lock을 설정한다. TX2는 ②번 update를 실행하는 단계에서 3번 레코드에 걸린 공유 Lock을 대기하게 되고, TX1이 7번 레코드를 읽으려는 순간 영원히 Lock이 풀릴 수 없는 교착상태에 빠진다. 이 때문에 테이블 레벨 Lock을 사용해야만 할 수도 있고, 이는 동시성을 더 심하게 저하시킨다.

나. 다중버전 동시성 제어

읽기 작업과 쓰기 작업이 서로 방해해 동시성을 떨어뜨리고, 공유 Lock을 사용함에도 불구하고 데이터 일관성이 훼손될 수 있는 문제를 해결하려고 Oracle은 버전 3부터 다중버전 동시성 제어(Multiversion Concurrency Control, 이하 MVCC) 메커니즘을 사용해 왔다. MS SQL Server도 2005 버전부터, IBM DB2도 9.7 버전부터 이 동시성 제어 메커니즘을 제공하기 시작했다. 이처럼 DBMS 벤더들이 MVCC 모델을 채택하는 이유는, 동시성과 일관성을 동시에 높이려는 노력의 일환이다. MVCC 메커니즘을 간단히 요약하면 다음과 같다.

• 데이터를 변경할 때마다 그 변경사항을 Undo 영역에 저장해 둔다
• 데이터를 읽다가 쿼리(또는 트랜잭션) 시작 시점 이후에 변경된(변경이 진행 중이거나 이미 커밋된) 값을 발견하면, Undo 영역에 저장된 정보를 이용해 쿼리(또는 트랜잭션) 시작 시점의 일관성 있는 버전(CR Copy)을 생성하고 그것을 읽는다.

쿼리 도중에 배타적 Lock이 걸린, 즉 변경이 진행 중인 레코드를 만나더라도 대기하지 않기 때문에 동시성 측면에서 매우 유리하다. 사용자에게 제공되는 데이터의 기준 시점이 쿼리(또는 트랜잭션) 시작 시점으로 고정되기 때문에 일관성 측면에서도 유리하다. MVCC에 장점만 있는 것은 아니다. Undo 블록 I/O, CR Copy 생성, CR 블록 캐싱 같은 부가적인 작업 때문에 생기는 오버헤드도 무시할 수 없다. 참고로, Oracle은 Undo 데이터를 Undo 세그먼트에 저장하고, SQL Server는 tempdb에 저장한다. MVCC를 이용한 읽기 일관성에는 문장수준과 트랜잭션 수준, 2가지가 있다.

다. 문장수준 읽기 일관성

문장수준 읽기 일관성(Statement-Level Read Consistency)은, 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것을 말한다. 일관성 기준 시점은 쿼리 시작 시점이 된다. [그림 Ⅲ-2-4]는 10023 시점에 시작된 쿼리가 10023 시점 이후에 변경된 데이터 블록을 만났을 때, Rollback(=Undo) 세그먼트에 저장된 정보를 이용해 10023 이전 시점으로 되돌리고서 값을 읽는 것을 표현하고 있다.

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

라. 트랜잭션 수준 읽기

트랜잭션 수준 읽기 일관성(Transaction-Level Read Consistency)은, 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것을 말한다. 기본 트랜잭션 격리성 수준(Read Committed)에서 완벽한 문장수준의 읽기 일관성을 보장하는 MVCC 메커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다. 물론 일반적인 Locking 메커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다. 트랜잭션 수준으로 완벽한 읽기 일관성을 보장받으려면 격리성 수준을 Serializable Read로 올려주어야 한다. 트랜잭션 격리성 수준을 Serializable Read로 상향 조정하면, 일관성 기준 시점은 트랜잭션 시작 시점이 된다. 물론 트랜잭션이 진행되는 동안 자신이 발생시킨 변경사항은 그대로 읽는다. SQL Server에서 트랜잭션 읽기 일관성 모드로 DB를 운영하려면 먼저 아래 명령을 수행해 주어야 한다.

그리고 트랜잭션을 시작하기 전에 트랜잭션 격리성 수준을 아래와 같이 ‘snapshot’으로 변경해 주면 된다.

마. Snapshot too old

세상에 공짜는 없는 법이다. Undo 데이터를 활용함으로써 높은 수준의 동시성과 읽기 일관성을 유지하는 대신, 일반적인 Locking 메커니즘에 없는 Snapshot too old 에러가 MVCC에서 발생한다. 대용량 데이터를 처리할 때 종종 개발자를 괴롭히는 것으로 악명 높은 이 에러는, Undo 영역에 저장된 Undo 정보가 다른 트랜잭션에 의해 재사용돼 필요한 CR Copy을 생성할 수 없을 때 발생한다.(좀 더 세부적인 메커니즘으로 들어가면 블록 클린아웃에 실패했을 때도 발생하는데, 이에 대한 설명은 생략하기로 한다.) 이 에러의 발생 가능성을 줄이기 위해 DBMS 벤더 측의 노력이 계속되고 있지만 아직 완벽한 해결책은 마련되지 못하고 있다. 따라서 이를 회피하기 위한 DBA 또는 개발자의 노력이 여전히 필요한 상태다. Snapshot too old 에러 발생 가능성을 줄이는 방법은 다음과 같다.

1. Undo 영역의 크기를 증가시킨다.
2. 불필요하게 커밋을 자주 수행하지 않는다.
3. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다. ANSI 표준에 따르면 커밋 이전에 열려 있던 커서는 더는 Fetch 하면 안 된다. 다른 방?.
4. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도?로 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다. Snapshot too old 발생 가능성을 줄일 뿐 아니라 문제가 발생했을 때 특정 부분부터 다시 시작할 수도 있어 유리하다. 물론 그렇게 해도 읽기 일관성에 문제가 없을 때에만 적용해야 한다.
6. 오랜 시간에 걸쳐 같은 블록을 여러 번 방문하는 Nested Loop 형태의 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고, 이를 회피할 수 있는 방법(조인 메소드 변경, Full Table Scan 등)을 찾는다.
7. 소트 부하를 감수하더라도 order by 등을 강제로 삽입해 소트연산이 발생하도록 한다.
8. 대량 업데이트 후에 곧바로 해당 테이블 또는 인덱스를 Full Scan 하도록 쿼리를 수행하는 것도 하나의 해결방법이 될 수 있다.