[DB] 📚 동시성 제어(Concurrency Control) with MySQL

본 포스팅은 학교 수업에서 진행하는 이론,실습에 대해 정리하는 포스팅 입니다. 공부용으로 작성하는 포스팅으로 오류가 있을 수 있습니다.

 

병행 제어(Concurrency Control)

데이터베이스 시스템의 주요 목표는 일관성과 공용성이다.

공용성은 여러 사용자가 데이터 베이스를 이용 가능하도록 하는 것을 의미하는데,

이렇게 되면, 충돌이 발생할 수 밖에 없다.

 

그럼에도 동시 공용(Concurrent Sharing)은 공용도 증가, 응답시간의 단축, 시스템 활용도 증대 등의 이점을 위해 필요하다. 

 

병행 제어는 다양한 기법이 존재한다.

• Locking: 공유항목에 대해 다른 트랜잭션에서는 read만 가능하도록 (Shared  Lock), 혹은 read/write 둘다 불가능하도록(Exclusive Lock)
• 2Phase Locking: 모든 트랜잭션들이 lock과 unlock을 확장단계와 수축단계로 구분하여 수행한다. 상호 배제 원리를 이용한 트랜잭션간의 직렬성 보장 기법으로 비교적 간단하며 데이터불일치를 예방할 수 있지만, lock대기시간과 deadlock이 발생할 수 있다.
• MVCC(Multi-Version Concurrency Control): 트랜잭션의 타임 스탬프와 접근 데이타의 여러 버전 타임스탬프를 비교하여 직렬가능성(serializable)이 보장되는 버전을 선택하는 기법으로 최근 데이터값을 선택하는 방식을 통해 동시성과 일관성을 동시에 해결할 수 있다. Undo 블록 I/O에 따른 오버헤드가 발생할 수 있다.

병행제어가 필요한 이유는 다음과 같다. 

	트랜잭션1	트랜잭션2	발생하는 문제	동시 접근
case1	read	read	문제없음	허용
case2	read	write	Dirty read(없어질걸 미리 읽어옴) Non-repeatable read(Read할 때 값이 다름) Phantom read(없던 투플 추가)	허용 또는 불가 선택
case3	write	write	Lost Update(Overwrite) Inconsistency Cascading rollback	lock을 이용하여 제한적으로 허용

 

위와 같이, 동시에 실행되는 트랜잭션이 각각 read와 write일 때, write write 생기는 문제가 존재한다.

이러한 문제를 해결하기 위해 병행제어가 필요

 

트랜잭션이 각각 read, write를 할 때 발생하는 문제에 대해 좀 더 자세히 다뤄보자

Dirty Read

커밋되지 않은 데이터를 읽어오는 문제이다.

T1에서 rollback을 하게 되면 변경사항들이 저장되지 않고 초기로 돌아가는데, T2에서는 rollback전의 데이터를 읽었다가, rollback 이후 sum을 하여 예상한 값과 다른 결과가 나올 수 있다.

 

Non-repeatable Read(Unrepeatable Read)

트랜잭션이 동일한 항목을 두번 읽을 때, 두번이 서로 다른 결과를 읽어오는 경우를 말한다.

일관성이 없는 결과를 받게 된다. 

 

Phantom Read

기존 read에는 없다가, 다른 트랜잭션에서 write 이후 read를 했을 때, 투플이 하나 더 생기는 문제이다.

 

 

이번에는 둘 다 Write를 할 때를 다뤄보자

 

• Lost Update (Overwrite):
  두 트랜잭션이 같은 데이터를 동시에 수정하여 한 트랜잭션의 업데이트가 다른 트랜잭션에 의해 덮어씌워지는 문제
• Inconsistency:
  다른 트랜잭션들이 특정 항목 값을 갱신하는 동안, 한 트랜잭션이 두 개의 항목 중 어떤 것은 갱신되기 전의 값을 읽고, 다른 것은 갱신된 후의 값을 읽게되어, 데이터의 불일치가 발생하는 상황
• Cascading Rollback:
  한 트랜잭션의 실패로 인해 의존 관계에 있는 다른 트랜잭션들도 연쇄적으로 롤백되는 문제

Cascading 예시

 

 

직렬 가능성과 스케줄

스케줄링은 트랜잭션 연산의 실행 순서를 결정하는 중요한 요소이다.

직렬 가능성은 이러한 스케줄이 데이터베이스의 일관성을 유지하면서 트랜잭션 간의 동시성을 허용하기 위한 기준이다.

• 스케줄 : 트랜잭션 연산들의 실행 순서 또는 트랜잭션 연산들의 조합
• 직렬 스케줄(serial schedule): 트랜잭션들이 하나씩 순차적으로 실행되는 스케줄이다. 즉 하나의 트랜잭션이 완료된 후에 다음 트랜잭션이 실행
  
  • ex) 트랜잭션 {T1, ... , Tn}이 순차적으로 실행됨
  • 교차실행(Interleaving)이 없음
  • 모든 직렬 스케줄은 정호가하며 데이터베이스의 일관성을 유지
  • n개의 트랜잭션에 대해, 가능한 모든 직렬 스케줄은 n!가지 존재
• 비직렬스케줄(nonserial schedule): 교차실행(Interleaving)된 스케줄로, 트랜잭션 {T1,...,Tn}이 병렬실행된다.
• 직렬 가능 스케줄(serializable schedule): 비직렬 스케줄이 직렬 스케줄과 동일한 결과를 보장하는 경우 직렬 가능 스케줄
  • 정의: n개의 트랜잭션 {T1,...,Tn}에 대한 스케줄 S가 특정 직렬 스케줄 S'와 동등하다면, S는 직렬 가능하다

스케줄 동등성(Schedule Equivalent)

스케줄 동등성이란 두 스케줄이 동일한 데이터 상태를 보장할 수 있는지 평가하는 기준이다.

즉, 두 스케줄이 논리적으로 동일한 결과를 낸다면, 이들 스케줄을 동등하다고 판단할 수 있다.

 

충돌 동등 (Conflict Equivalent)

명렁어들의 순서를 바꿔서 충돌 동등인 스케쥴

정의

• 스케줄 S와 S'가 충돌하지 않는 연산의 순서만 바꿔서 동일한 결과를 낼 경우, 두 스케줄은 충돌 동등
  • 이때 순서를 변경한다는 것은 서로 다른 트랜잭션의 작업이 교차하는 경우에 한해 서로간의 순서를 조정하는 것을 의미하고, 트랜잭션 내부 순서는 유지되어야한다.
• 충돌 동등한 스케줄은 동일한 Conflict Graph를 가짐
• 다만 이렇게 이상적으로 그리기가 쉽지 않아 선행 그래프(precedence grpah)로 그릴 수 밖에 없다.

이렇게 비직렬인 두 트랜잭션의 순서를 변경해서, 직렬인 스케줄과 충돌 동등한 경우를 충돌직렬가능스케줄(conflict serializable)이라고 한다.

 

뷰 동등(View Equivalent)

 

정의

스케줄 S와 S'가 다음 세 가지 조건을 만족하면 뷰 동등:

1. 모든 데이터 아이템에 대해 초기 읽기(Read)가 동일해야 함.
2. 각 데이터 아이템의 최종 쓰기(Write)가 동일해야 함.
3. 읽기 연산이 동일한 데이터를 읽어야 함(읽는 값이 같아야 함).

-> 동일한 데이터 x에 대해서 최초 읽기, 쓰기/읽기, 마지막쓰기 를 수행하는 트랜잭션의 순서가 동일

 

뷰 직렬 가능 스케줄(View Serializable Schedule)

• 어떤 직렬 스케줄과 뷰 동등한 스케줄
• 모든 충돌 직렬 가능 스케줄은 뷰 직렬 가능, 역은 성립하지 않는다

 

충돌 직렬 가능성 검사

선행 그래프를 통해 충돌 직렬 가능성을 검사할 수 있다.

선형 그래프는 방향그래프이며, 노드 Ti와 간선 Ti->Tj으로 표현할 수 있다.

선행 그래프를 그렸을 때, 사이클이 없는 경우 충돌 직렬 가능하다. (위 그림 예시는 사이클이 존재하여 직렬 불가능 스케줄)

선행 그래프에서 선형 순서로 위상 정렬된 모든 스케줄은 직렬 가능

 

이렇게, 직렬 가능성을 위해서는 실행 중인 트랜잭션들의 스케줄이 직렬 스케줄과 동일한 결과를 만들어내는지 확인해얗한다.

하지만 이 과정은 다음과 같은 어려움이 발생할 수 있다.

1. 트랜잭션 실행 후 직렬 가능성 검시 : 이미 실행된 스케줄이 직렬 가능하지 않다면, 해당 트랜잭션을 롤백하거나 취소해야함
2. 지속적으로 들어오는 트랜잭션 처리: 시스템에서 트랜잭션이 계속 들어오는 경우, 어떤 스케줄이 언제 시작되고 끝나는지를 결정하기 어렵다.

병행 제어 기법

위와 같은 어려움을 극복하기 위해, 트랜잭션을 실행하면서 직렬 가능성을 보장할 수 있는 다양한 병행 제어 기법이 사용된다.

 

•  2단계 잠금 기법(Two_Phase Locking, 2PL) : 트랜잭션이 데이터를 읽거나 쓰기 전에 Lock을 걸어 다른 트랜잭션의 접근을 차단, 두단계로 진행
  • 확장 단계: 새로운 잠금을 획득.
  • 축소 단계: 모든 잠금을 해제
  • 단점 : 교착 상태(deadlock)발생 가능성
• 타임스탬프 기반 기법
  • 각 트랜잭션에 타임스탬프를 부여하고 순서를 정하고, 충돌을 예방.
  • MVCC(Multi-Version Concurrency Control):
    • 데이터의 여러 버전을 유지하여 읽기/쓰기 연산의 충돌을 방지 (PostgreSQL등에서 사용)
• 낙관성 검증: 트랜잭션 실행 중에는 충돌을 허용하지만, 검증 단계에서 직렬 가능성을 확인.
• Snapshot Isolation: 트랜잭션이 시작될 때 데이터의 스냅샷을 생성하여 일관성을 유지

 

 

로킹(Locking)

정의: 상호 배제(독점 제어)를 통해 트랜잭션이 데이터에 동시에 접근하지 못하도록 하는 과정
다시 말해 잠금이 된 데이타 집합을 생성

데이터 객체의 배타적 접근을 보장한다.

 

성질

• 비공유성: 데이터 객체는 한번에 하나의 트랜잭션만 사용할 수 있다
• 부분효과의 배제: 트랜잭션이 완료되지 않은 상태에서 중간 결과가 다른 트랜잭션에 노출되지 않는다
• 단일 소유자: 하나의 데이터 객체는 한 트랜잭션에만 잠금(lock)된다
• 잠금 해제 제한: lock을 해제할 수 있는 권한은 해당 잠금을 건 트랜잭션에만 있다.

규약

1. Lock 연산 전제 조건: 트랜잭션 T는 read(x)또는 write(x) 연산을 수행하기 전에 반드시 lock(x) 연산을 실행해야 한다.
2. Lock과 Unlock의 순서: 트랜잭션 T는 모든 연산을 종료하기 전까지 unlock(x)연산을 실행할 수 없음.
3. 다중 Lock 방지: 트랜잭션 T는 다른 트랜잭션이 이미 x에 대해 잠금을 걸었을 경우, 해당 데이터에 대해 추가 잠금을 걸 수 없음
4. Unlock 제한: 트랜잭션 T는 자신이 걸지 않은 lock(x)를 해제할 수 없음

필요 조건

• 명세: 로킹의 구현은 완전하고 명확해야한다
• 최소성: 최소한의 자원으로 최대한의 자금 효과를 달성해야한다
• 충돌 검사: 충돌을 최소화 하고, 효율적으로 관리해야한다

 

로킹 모드 확장

• 공용 locking, shared-lock(s-lock): 공용된 접근 허용, read만 허용
• 전용 locking, exclusive-lock(x-lock): 배타적 접근, read/write 모두 불허

양립성

로크의 두 가지 모드가 동시에 데이터에 접근할 수 있는지를 결정

트랜잭션 모두 s-lock일때 접근이 허용됨

공용 로킹 규약

공용 및 전용 로크의 사용 방식을 정의하여 데이터 충돌을 방지 

1. 읽기/쓰기 전 lock 획득: T가 데이터 x를 읽으려면 lock-S(x)나 lock-X(x)을 먼저 획득해야함, 쓰려면 lock-X(x)연산을 먼저 획득해야 함
2. 양립성 검사: T가 xxx에 대해 Lock-S(x) 또는 Lock-X(x)를 요청할 때, 다른 트랜잭션이 x에 대한 충돌 상태의 로크를 보유 중이라면 대기해야 함.
3. 모든 로크 해제: T는 실행 종료 전에 획득한 모든 로크(lock)를 반드시 해제해야함
4. 자신이 걸지 않은 로크 해제 금지: T는 자신이 걸지 않은 데이터의 로크를 해제할 수 없음

2단계 로킹 규약(2PL)

확장 단계와 축소 단계라는 두 가지 단계를 통해 트랜잭션이 데이터에 접근하는 방식을 정의한다

 

1. 확장 단계(growing phase) - 트랜잭션이 필요한 모든 자원을 확보하는 단계

: 트랜잭션은 lock만 수행하고 unlock은 수행할 수 없는 단계 

2. 축소 단계(shrinking phase) - 트랜잭션이 더 이상 필요하지 않은 자원을 해제

: 트랜잭션은 unlock만 수행하고 lock은 수행할 수 없는 단계

 

스케줄 내의 모든 트랜잭션들이 2단계 로킹 규약을 준수한다면 그 스케줄은 직렬 가능

 

그림 자료와 같이, unlock 전까지는 locking만 계속 한다. 그리고 이후로는 unlock만 계속한다. 이렇게 locking을 하면 직렬이 된다.

첫번째 예시의 경우, T1이 lock을 두번 하고나서 unlock을 두번 한다. T2도 마찬가지로 T1이 언락을 하자마자 lock을 하고, unlock을 한다. 둘 다 2PLP를 만족하며 직렬 가능이다.

이 예시에서는 T1이 lock -> unlock -> lock ->unlock 으로 2PLP가 아니며, T2또한 lock->unlock->lock->unlock으로 2PLP가 아니다. 따라서 직렬 가능성을 보장하지 않는다. 

물론 2PL이 아니더라도 직렬일 수는 있다.(즉 필요조건은 아니다.)

 

실제 데이터베이스에서는 더 높은 안전성과 일관성을 보장하기 위해 엄밀2PLP(strict 2PLP)과 엄격 2PLP(rigorous 2PLP) 같은 변형된 규약을 사용한다. 대부분의 상용 DBMS는 아래의 두가지 규약을 사용한다.

 

• 엄밀 2단계 로킹 규약:
  • 모든 독점 잠금(lock-X)는 트랜잭션이 완료될 때까지 해제(unlock)되지 않음
  • 트랜잭션이 끝나기 전에 독점 잠금이 풀리지 않으므로, 다른 트랜잭션이 잠금된 데이터에 접근할 수 없다
  • 다른 트랜잭션이 해당 데이터를 읽거나 쓰는 것을 방지
  • 연쇄 복귀가 일어나지 않음
• 엄격 2단계 로킹 규약:
  • 모든 잠금은 트랜잭션이 완료될 때까지 해제되지 않음(이는 lock-X, lock-S 모두 해당)
  • 트랜잭션들이 완료하는 순서대로 직렬화 가능

다중 단위 로킹(Multiple Granularity Locking)

• 빈번한 잠금/해제 연산의 문제점:
  잠금을 설정하거나 해제하는 연산 자체가 오버헤드를 유발할 수 있음.
  따라서, 이를 해결하기 위해 다중 단위 로킹 메커니즘이 필요.
• 데이터 아이템의 크기에 따른 로킹 단위:
  • 큰 로킹 단위(Coarse Granularity):
    • 트랜잭션이 크고, 넓은 범위를 잠그는 경우 적합.
    • 장점: 잠금 연산 감소.
    • 단점: 병렬 처리 기회 감소.
  • 작은 로킹 단위(Fine Granularity):
    • 짧은 트랜잭션이나 세부적인 데이터 작업에 적합.
    • 장점: 세밀한 제어 가능.
    • 단점: 잠금 연산 증가로 오버헤드 발생.
• 다중 단위 로킹 계층 트리:
  데이터베이스, 구역, 파일, 레코드 등 계층적 구조를 기반으로 로킹 단위를 정의.
  트랜잭션의 작업 범위에 따라 적절한 수준에서 잠금.

 

MySQL의 Table-level locking vs Row-level lcoking

 

1. Table -level locking(테이블 수준 잠금)

 

특징

• 전체 테이블에 대해 읽기 또는 쓰기 작업 중 하나만 수행 가능.
• 동시성은 낮지만, 간단한 읽기 작업이 많은 경우 유리
• 주로 읽기와 쓰기 작업에 대해 전체 테이블을 잠그는 방식으로 작동 

LOCK TABLES student READ;
-- 작업 실행
UNLOCK TABLES;



LOCK TABLES student WRITE;
-- 작업 실행
UNLOCK TABLES;

장점: 단순한 구현, 쓰기 작업 시 충돌을 완전히 방지

단점: 동시성 처리 제한적, 대규모 데이터베이스 트랜잭션에서는 성능 저하

 

2. Row-level locking(행 수준 잠금)

 

특징

• 특정행에 대해서만 잠금이 걸리므로, 동시성을 높이고 성능을 최적화 한다
• 대규모 트랜잭션 처리에 적합하다

-- 갱신을 위한 잠금

START TRANSACTION;
SELECT * FROM student WHERE id=1234 FOR UPDATE;
-- 작업 실행
COMMIT;



--공유 잠금

START TRANSACTION;
SELECT * FROM student WHERE id=1234 LOCK IN SHARE MODE;
-- 작업 실행
COMMIT;

 

장점: 동시성 높은 작업에 유리하며, 대규모 데이터베이스 작업에서 성능 최적화

단점: 잠금 관리의 복잡성이 증가하고,  충돌 시 교착 상태(deadlock)가 될 수 있다.

 

대기 그래프 (T1이 T2의 unlock을, T2가 T1의 unlcok을 서로 기다리는중)의 사이클 발생 -> deadlock

 

* 교착 상태란 모든 트랜잭션이 실행을 전혀 진행하지 못하고, 서로 자원의 해제를 기다리는 무한 대기 상태를 의미한다.

Multi Version Concurrency Control(MVCC)

MVCC는 데이터베이스 내에서 데이터의 여러 버전을 유지하여, 트랜잭션이 과거의 데이터에 접근할 수 있도록 허용하는 동시성 제어 방식

어떤 트랜잭션에서 데이터에 접근시 데이터의 다중버전 상태중 직렬가능성이 보장되는 버전을 선택하여 처리하는 기법이다

특징

• 여러 버전의 데이터를 시스템에 저장.
  
  
• 일부 읽기 연산은 기존 방식에서 거절될 수 있지만, MVCC는 과거 버전의 데이터를 제공함으로써 성공적으로 처리.
  
  
• 직렬 가능성을 유지하면서도 성능을 향상.

타입별 MVCC 스키마

1. 타임스탬프 기반 (Timestamp Ordering):트랜잭션의 타임스탬프를 기준으로 데이터 버전을 관리.
2. 2단계 로킹 기반 (Two-Phase Locking): 기존 2단계 로킹을 확장하여 다중 버전을 지원.
   
   
3. 검증 및 스냅샷 격리 (Validation and Snapshot Isolation): 데이터를 격리된 스냅샷으로 관리하여 충돌을 방지.

MVCC의 문제점

1. 저장 공간의 증가: 데이터의 여러 버전을 저장해야 하므로 UNDO 로그와 같은 추가적인 저장소가 필요.
   
   
2. 스냅샷 오버헤드: 오래된 스냅샷이 필요 이상으로 유지될 경우, 성능 저하와 관리 문제가 발생할 수 있음.

MVCC의 주요 접근법

Approach 1: PostgreSQL, Firebird/Interbase, SQL Server

• 방법:
  • 데이터베이스에 데이터의 다중 버전을 별도의 레코드로 저장.
  • 불필요한 오래된 레코드를 정리(가비지 컬렉션)하여 저장 공간을 관리.
• 장점:
  • 데이터의 버전을 독립적으로 유지.
  • 읽기 연산의 충돌을 최소화.
• 단점:
  • 오래된 데이터를 주기적으로 정리해야 함.

Approach 2: Oracle, MySQL/InnoDB

• 방법:
  • 최신 데이터만 데이터베이스 내에 저장.
  • UNDO 로그를 활용하여 이전 버전의 데이터를 재구성.
• 장점:
  • 저장 공간 효율적 관리.
  • 필요할 때만 과거 데이터를 재구성.
• 단점:
  • UNDO 로그의 크기가 커질 경우 성능 저하 가능.

사진자료의 Undo Segement는 이전 데이터 버전을 저장하는 공간이며, SCN은 블록이 마지막으로 변경된 시점의 정보이다. select가 10023이고, 10024 이후 데이터가 변경되었더라도, SCN 10023 시점의 데이터 상태를 반환하기 위해 10006, 10021을 읽도록 한다.

이를 통해 읽기 일관성을 유지할 수 있다. 

 

SQL의 트랜잭션 격리 수준(Transaction Isolation Level)

트랜잭션 격리 수준은 데이터베이스의 트랜잭션이 서로 독립적으로 실행되도록 하는 정도를 조정하는 설정이다.

SQL-92 표준에 따르면 4가지 격리 수준이 정의된다:

1. READ UNCOMMITTED:
   • 트랜잭션이 커밋되지 않은 데이터까지 읽을 수 있음.
   • 가장 낮은 격리 수준이며, 동시성은 높지만 데이터 불일치 문제가 발생할 수 있음.
   • (mysql) Binary Log가 자동으로 Row Based에 기록됨 
2. READ COMMITTED:
   • 커밋된 데이터들을 읽을 수 있다. ex) T2에서 작업중에 T1의 커밋이 완료되면 그 데이터에 대해서는 읽어온다. -> 더티리드는 없지만 Nonrepeatable, 팬텀리드는 발생
   • 트랜잭션이 실행되는 동안, 다른 트랜잭션이 변경한 데이터는 볼 수 없음.
   • Oracle과 PostgreSQL의 기본 격리 수준.
   • (mysql) Binary Log가 자동으로 Row Based에 기록됨
   • 여러 세션이 동시 접속후에 commit이 빈번하게 일어나고 또는 트랜잭션이 수시로 변경된 내용을 참조해야 하는 경우 사용
3. REPEATABLE READ:
   • 트랜잭션이 시작된 시점의 데이터 상태를 유지. -> 본인 기준 commit이 끝나야 변경사항을 확인할 수 있고, 커밋 이전에는 옛날 버전 snapshot을 읽음
   • 동일 데이터를 여러 번 읽을 때, 항상 같은 값을 반환.
   • MySQL의 기본 격리 수준.
4. SERIALIZABLE:
   • 가장 높은 격리 수준.
   • 트랜잭션이 직렬적으로 실행되는 것처럼 보장.
   • 동시성은 가장 낮지만, 데이터 일관성을 완벽히 유지.

 

각 격리 수준에서의 문제 발생 여부

:아래 표는 각 격리 수준에서 어떤 문제가 발생할 수 있는지를 보여준다

레벨이 아래로 갈수록 느려지지만 문제점은 줄어든다.

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes (MySQL: No*)
SERIALIZABLE	No	No	No

(MySQL이 Repeatable read임에도 phantom read는 No인 이유는 MVCC때문이다.)

 

 

MySQL에선 아래와 같이 적용 가능하다

MySQL에서의 트랜잭션 설계

1. AutoCommit 설정 확인(기본값: true)

• AutoCommit 확인 방법: SELECT @@AUTOCOMMIT;
• AutoCommit 설정 변경:
  • 명령문으로 변경: SET autocommit=0;
  • 서버 설정 파일 변경: 
  • my.cnf 파일에 아래 내용을 추가하고 서버 재시작
    [mysqld]

    init_connect='SET autocommit=0'

    autocommit=OFF

     

2. Table Type이 InnoDB인지 확인 (InnoDB만 트랜잭션을 지원함)

• 확인 방법: SHOW TABLE STATUS;
• 변경 방법: ALTER TABLE 테이블명 ENGINE=INNODB;

3. Transaction Isolation Level(격리 수준)확인 (기본값: REPATABLE-READ)

• 격리 수준 확인 방법: SELECT @@GLOBAL.transaction_isolation; SELECT @@SESSION.transaction_isolation;
• 격리 수준 변경:
  • 명령문으로 변경: SET SESSION TRANSACTION ISOLATION LEVEL [원하는 레벨];
  • 서버 설정 파일 변경:
    1. my.cnf 파일에 아래 내용을 추가하고 서버 재시작  
    2.  
       transaction-isolation=READ-COMMITTED
    3. [mysqld]

 

트랜잭션 레벨에 따른 실행 결과 테스트(from. MySQL)

각 트랜잭션 레벨마다 동일한 쿼리를 실행하고 결과가 어떻게 다르게 나오는지 확인해보자

1. update & read(Dirty Read 테스트)

두개의 트랜잭션에서 각각 update와 select를 할 때, 트랜잭션 레벨과 커밋여부에 따라 어떤 값이 나오는지 확인하는 예제이다.

초기 테이블은 {A:1, B:10} , {A:2,B:0}으로 시작한다.

 

시작하기전 트랜잭션 T1, T2의 실행 환경에서 autocommit을 꺼준다.

REPEATABLE READ

먼저 MySQL 기본 레벨인 REPEATABLE READ에서 시작한다. 이론상 더티리드, 팬텀리드, 논리피터블 리드 모두 없어야한다.

LINE 1, 2 실행결과

실행결과 REPEATABLE READ 레벨에서 T1의 update는 T2의 select에 영향을 주지 않는다. 

라인3,4를 따라 원래대로 데이터를 복구시키고 커밋을 해주었다. 나머지 레벨에서도 동일하게 진행할것

 

READ COMMITTED

이론상 READ COMMITTED는 더티 리드는 없고 나머지 현상은 나타난다.

마찬가지로 더티리드가 없기 때문에, T1의 update는 T2에 영향이 없다.

만약 T1에서 B-10을 한 연산에 대해 Commit을 하고나서, T2가 읽는 예제였다면, sum(B)의 값은 0으로 나왔을 것이다.

READ UNCOMMITTED

마지막으로, UNCOMMITTED를 테스트해본다. 더티리드가 있는 레벨이라서 영향이 있을 것이다.

이처럼 UNCOMMITTED 레벨에서는 커밋을 하지 않은 데이터를 읽어오는 더티리드 현상이 나타난다.

 

2. insert&read(Phantom Read Test)

이번에는 PhantomRead를 테스트해본다. 원래대로면 REPATABLE READ레벨에서도 발생하는 현상이지만, MySQL은 MVCC이므로 발생하지 않는다. 이 사실 또한 직접 확인해보겠다.

이번 예시는 T2의 트랜잭션이 진행되는 동안 T1에서 새로운 값을 INSERT하고 커밋을 한 값에 대해 T2가 읽어오는지를 보는 예제다.

읽어온다면 팬텀리드가 발생한 것이고 읽어오지 않는다면 발생하지 않는것으로 볼 수 있다.

이번엔 역순으로 실행해보겠다.

 

READ UNCOMMITED

UNCOMMITED부터 살펴보자 (UNCOMMITTED는 더티리드부터 발생했으니 팬텀리드도 당연히 발생하겠지만.. )

우선 T2의 첫 select 결과이다.

 

2~4라인대로, T1에서는 트랜잭션 시작, 데이터 삽입, 커밋을 완료한상태이다.

우측 결과를 확인해보면 3,150 이 새로 삽입된것을 볼 수 있다. select를 연속으로 두번했는데 새로운 투플이 생기는 팬텀리드 현상이 발생

 

READ COMMITTED

COMMITTED도 같은 결과가 나올 것이다.

READ COMMITTED 기준 T2 2라인

예상대로 T1의 커밋된 값에 의하여 select 2번의 결과가 다르다. 

 

REPEATABLE READ

원래대로라면 앞의 두 레벨과 같은 결과가 나와야하지만 MySQL환경이므로 팬텀리드가 발생하지 않을 것이다.

아직 Insert하기전

 

T1의 Insert 및 커밋 완료하였으나 T2에서 select 안됨 ->팬텀리드가 발생하지않음

아직 T2의 커밋이 되지 않아서, T1의 커밋이 반영된 현재의 테이블이 아닌 커밋 이전의 스냅샷을 읽고 있기 때문에 팬텀리드 현상을 볼 수 없다.

T2커밋 이후 select를 해보면 이전 스냅샷이 아닌 현재 테이블을 읽어오므로 반영된 값을 읽어오는 것을 확인할 수 있다.

즉 , MySQL의 REPEATABLE READ 레벨에서는, 트랜잭션을 시작했으면 커밋을 완료해야 다른 트랜잭션에 의한 변경사항을 확인할 수 있다. 그 전까지는 MVCC에 의해 이전의 Snapshot만을 읽어온다.

 

거의 대부분의 상황에서는 이렇게 되지만, 스냅샷읽기가 아닌 현재 테이블 읽기로 조회를 하는 경우에는 커밋 이전에도 다른 트랜잭션에 의한 변동사항을 확인할 수도 있다고 한다.(SELECT FOR UPDATE..) 

 

자세한 것은 아래 블로그 내용을 참고

https://mangkyu.tistory.com/299