[1] 동시성 제어

(1) 동시성 제어(Concurrency Control)란?

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

(2) 동시성 제어 기능

1. Lock 기능

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

2. 트랜잭션 격리성 수준 조정 기능

• SET TRANSACTION 명령어를 이용해 트랜잭션 격리성 수준을 조정할 수 있는 기능을 제공한다.
• SQL Server의 경우, 기본 트랜잭션 격리성 수준인 Read committed 상태에선 레코드를 읽고 다음 레코드로 이동하자 마자 공유 Lock을 해제한다.
• SQL Server의 경우, Repeatable Read로 올리면 트랜잭션을 커밋될 때까지 공유 Lock을 유지한다.

[2] 비관적 동시성 제어 vs. 낙관적 동시성 제어

(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을 던진다.

(2) 낙관적 동시성 제어

• Optimistic concurrency control
• 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정한다.
• 데이터를 읽을 때에는 Lock을 설정하지 않는다.
• 수정 시점에, ⭐️ 다른 사용자에 의해 값이 변경됐는지를 반드시 검사해야 한다.⭐️

[예시]

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 적립포인트, 방문횟수, 최근방문일시, 구매실적, 변경일시
  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;

[3] ⭐️ 다중버전 동시성 제어

(1) 일반적인 Locking 매커니즘의 문제점

1. 동시성 제어의 목표

동시에 실행되는 트랜잭션 수를 최대화함과 동시에 입력, 수정, 삭제, 검색 시 데이터 무결성이 유지하는것이다.

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

• 읽기 작업에 공유 Lock을 사용하는 일반적인 Locking 매커니즘에서는 읽기 작업과 쓰기 작업이 서로 방해를 일으키기 때문에 종종 동시성에 문제가 발생한다.
• 데이터 일관성에 문제가 생기는 경우 , 이를 해결하려면 Lock을 더 오랫동안 유지하거나
  테이블 레벨 lock을 사용해야 하므로 동시성 저하가 발생한다.

[예시]

• 순서

1. T1 : 2번 계좌까지 읽는다. 현재 잔고는 2,000
2. T2 : UPDATE를 수행한다. 7번 계좌 잔고는 1,100원이 되었고,아직 커밋되지 않았다.
3. T3 : 6번 계좌까지 읽는다. 현재까지의 잔고는 5,000원
4. T4 : UPDATE를 실행한다. 3번 계좌는 900원
5. T5 : COMMIT을 수행하였으므로 3번 계좌는 900원, 7번 계좌는 1,100인 상태에서 커밋한다.
6. T6 : 10번 계좌까지 읽어 내려 간다. 7번 계좌 잔고를 1,100으로 바꾼 TX2 트랜잭션이 커밋되었으므로 총 잔고는 10,100원이 된다.

3.일관성 읽기 문제를 해결하기 위한 일반적인 해법

• 위 예시와 같은 비일관성 읽기 문제를 해결하기 위한 일반적인 해법은 트랜잭션 격리성 수준을 상향하는 것이다.

기본 트랜잭션 격리성 수준(Read comitted)

• 값을 읽는 순간에만 공유 Lock을 걸었다가 다음 레코드로 이동할 때 Lock을 해제함으로써 위와 같은 현상이 발생한다.

Repeatable Read 트랜잭션 격리성 수준

• 트랜잭션 격리성 수준을 Repeatable Read로 올리면 TX1 쿼리가 진행되는 동안 읽은 레코드는 공유 Lock이 계속 유지된다.
• 심지어 쿼리가 끝나고 다음 쿼리가 진행되는 동안에도 유지된다.

트랜잭션 격리성 수준을 상향시 문제점

• 트랜잭션 격리성 수준을 올리면 일관성이 높아지지만, Lock이 더 오래 유지됨으로 인해 동시성을 저하 시키고 교착상태가 발생할 가능성도 커진다.
  1. TX2가 계좌번호 7번에 배타적 LOCK을 설정한다.
  2. TX1은 3번 레코드에 공유 Lock을 설정한다.
  3. TX1이 7번을 읽으려는 순간 Dead Lock이 발생한다.

(2) 다중버전 동시성 제어

• ORACLE은 버전 3부터 다중버전 동시성 제어(Multiversion Concurrency Control, MVCC)
  매커니즘을 사용한다.
• MS SQL Server 2005, IBM DB2 9.7버전 부터 동시성 매커니즘을 제공하기 시작했다.

1. ⭐️ MVCC란?

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

⭐️ 순서

1. 데이터를 읽기 시작
2. 도중에 쿼리(또는 트랜잭션)시작 시점 이후에 변경된(변경이 진행중이거나 이미 커밋된)값을 발견한다.
3. UNDO 영역에 저장된 정보를 이용해 쿼리(또는 트랜잭션)시작 시점의 일관성 있는 버전(CR Copy)를 생성한다.
4. 변경된 값 대신 3번에서 생성한 CR Copy를(일관성 있는 버전) 읽는다.

2. ⭐️ MVCC의 특징

• 쿼리 도중 배타적 Lock이 걸린, 즉 변경이 진행 중인 레코드를 만나더라도 대기하지 않기 때문에
  동시성 측면에 유리하다.
• UNDO 블록 I/O, CR Copy 생성, CR 블록 캐싱 같은 부가적인 작업의 오버헤드가 발생할 수 있다.
• Oracle은 UNDO 데이터를 UNDO 세그먼트에 저장한다.
• SQL Server는 UNDO 데이터를 tempdb에 저장한다.
• MVCC는 문장수준과 트랜잭션 수준의 읽기 일관성이 존재한다.

(3) ⭐️ 문장수준 읽기 일관성

다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 ⭐️단일 SQL문⭐️ 내에서 일관성 있게 값을 읽는 것이다.

[예시] 10023 시점에 시작된 쿼리가 10023 시점 이후에 변경된 데이터 블록을 만났을 때

• 일관성 기준 시점은 쿼리 시작 시점이 된다.
• 10023 시점에 시작된 쿼리가 10023 시점 이후에 변경된 데이터 블록을 만났을 때,
  Rollback(=UNDO) 세그먼트에 저장된 정보를 이용해 10023 이전 시점으로 되돌리고서
  값을 읽는다.
• SQL Server에서 문장수준 읽기 일관성 모드로 DB를 운영하려면 아래 명령어를 수행한다.

alter database <데이터베이스 이름> set read_committed_snapshot on;

(4) 트랜잭션 수준 읽기 일관성

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

1. 트랜잭션 수준의 읽기 일관성의 보장

• Read committed에서 완벽한 문장수준의 읽기 일관성을 보장하는 MVCC 매커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다.
• 일반적인 Locking 매커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다.
• 트랜잭션 수준으로 완벽한 읽기 일관성을 보장받으려면 격리성 수준을 Serializable Read로 올려주어야 한다.
• Isolation Level을 Serializable Read로 상향조정하면, 일관성 기준 시점은 트랜잭션 시작 시점이 된다.
• 트랜잭션이 진행되는 동안 자신이 발생시킨 변경사항은 그대로 읽는다.

[예시] Isolation Level을 Serializable Read로 상향 조정

alter database <데이터베이스 이름> set allow_snapshot_isolation on;

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

begin tran
 select ...;
 update....;
commit;

(5) ⭐️ Snapshot too old

1. Snapshot too old

• UNDO 영역에 저장된 UNDO 정보가 다른 트랜잭션에 의해 재사용돼 필요한 CR Copy을 생성할 수 없을 때 발생하는 현상이다.
• UNDO 데이터를 활용하면 높은 수준의 동시성과 읽기 일관성을 유지할 수 있지만 일반적인 Locking 매커니즘에 없는 SNAPSHOT TOO OLD 에러가 MVCC에서 발생한다.

2. SNAPSHOT TOO OLD 발생 가능성을 줄이는 방법

1. UNDO 영역의 크기를 증가시킨다.
2. 불필요한 커밋을 자주 수행하지 않는다.
3. FETCH ACROSS COMMIT 형태의 프로그램 작성을 피해 다른 방식으로 구현한다.
4. ANSI 표준에 따르면 커밋 이전에 열려 있던 커서는 더는 FETCH 하면 안된다.
5. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 조정한다.
6. 큰 테이블을 일정 범위로 나누어 읽고 단게적으로 실행할 수 있도록 코딩한다.
   • 이는 SNAPSHOT TOO OLD 발생 가능성을 줄일 뿐 아니라 문제가 발생시 특정 부분부터 다시 시작할 수 있다.
   • 읽기 일관성에 문제가 없을때만 적용하도록 한다.
7. 다음과 같은 경우가 있는지 체크한 뒤 이를 회피할 수 있는 방법을 찾는다.
   • 오랜 시간에 걸쳐 같은 블록을 여러번 방문하는 NL Join 형태의 조인문
     • 회피 방법 : 조인 메소드 변경
   • 인덱스를 경유한 테이블 액세스를 수반하는 프로그램
     • 회피 방법 : Full Table Scan
8. 소트 부하를 감수하더라도 order by 등을 강제로 삽입해 소트 연산이 발생하도록 한다.
9. 대량 업데이트 후에 곧바로 해당 테이블 또는 인덱스를 Full Scan 하도록 쿼리를 수행하는 것도 하나의 해결 방법이다.