RealMySQL 8.0 | 5장 트랜잭션과 잠금

RealMySQL 8.0 을 읽고 정리했습니다.

목차

1. 트랜잭션
2. MySQL 엔진의 잠금
3. InnoDB 스토리지 엔진 잠금
4. MySQL의 격리 수준

---

트랜잭션(Transaction)

작업의 완전성을 보장해 주는 것

1. 100% : 모두 완벽하게 처리하거나
2. 0% : 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)을 방지

잠금(Lock)

🤔 잠금 없이 여러 커넥션이 하나의 데이터를 동시에 변경한다면?
그 데이터의 값은 예측할 수 없게 됩니다.

즉, 여러 커넥션에서 동시에 동일한 자원을 요청한 경우
순서대로 한 시점에는 하나의 커넥션만 변경할 수 있도록 동시성을 제어하는 역할

• 트랜잭션 : 데이터의 정합성을 보장하기 위한 기능
• 잠금 : 동시성을 제어하기 위한 기능

격리 수준(Isolation level)

하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지 결정하는 레벨

1. 트랜잭션

• 단일한 논리적인 작업 단위
• 논리적인 이유로 여러 SQL문들을 단일 작업으로 묶어서 나누어지지 않게 만든 것
• 쿼리가 일부만 성공해서 DB에 반영되는 일이 일어나지 않게 만든 것

🤔 트랜잭션이 보장해주는 것은?

작업 셋을 100% (COMMIT) 또는 0% (ROLLBACK)로 만들어 작업 셋의 정합성을 보장합니다.

💡 트랜잭션 관점에서 MyISAM과 InnoDB의 처리 방식 차이 살펴보기

• MyISAM, MEMORY 스토리지 엔진 : 트랜잭션 지원 X
  → 부분 업데이트 발생
  → 데이터 정합성을 맞추기 어려워짐
  → 분기 처리 + 데이터 클렌징 코드까지 필요하게 됨
• InnoDB : 트랜잭션 지원 O
  → 간단한 코드로 구현 가능

⚠️ 주의사항

• 커넥션은 필요한 시점에 생성하기
  • 데이터베이스의 커넥션 개수가 제한되어있어서 불필요한 작업이 데이터베이스 커넥션을 소유하고 있지 않도록 필요한 시점에 생성해야합니다.
• 트랜잭션은 적절한 분리가 중요하다
  • 알맞는 논리단위로 작업 셋을 정해, 그 부분만 트랜잭션 범위를 지정하는 것이 좋습니다.
  • 트랜잭션 중에 문제 발생 시 Rollback을 하게 되는데, 트랜잭션의 범위가 커지면, DB와 관련 없는 문제가 발생해도 Rollback이 될 수도 있고, 같은 작업 셋이 아닌데 같이 Rollback 될 수 있습니다.
  • 외부 서버를 이용한 메일 전송이 트랜잭션에 포함될 경우, 메일 서버가 불안정하면, DBMS도 같이 문제가 생길 수 있기 때문에 트랜잭션을 분리해야합니다.

2. MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 MySQL 엔진 레벨 과 스토리지 엔진 레벨 로 나눌 수 있습니다.

• MySQL 엔진 레벨의 잠금 : 모든 스토리지 엔진에 영향을 미침
  • 글로벌 락
  • 테이블 락
  • 메타데이터 락
  • 네임드 락 (유저 레벨 락)
• 스토리지 엔진 레벨의 잠금 : 스토리지 엔진 간 상호 영향을 미치지 않음
  • 레코드 락
  • 갭 락
  • 넥스트 키 락
  • 자동 증가 락

글로벌 락

MySQL에서 제공하는 잠금 중 가장 넓은 범위입니다.

• 영향을 미치는 범위 : MySQL 서버 전체
• 작업 대상 테이블, 데이터베이스 상관 없이 동일한 영향을 미칩니다.
• 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때 사용합니다.

-- 글로벌 락 획득
mysql> FLUSH TABLES WITH READ LOCK;
-- UNLOCK
mysql> UNLOCK TABLES;

🤔 한 세션에서 글로벌 락을 획득하면?

• 해제될 때까지 SELECT를 제외한 대부분의 DDL이나 DML 명령이 대기 상태가 됩니다.

💡 백업 락의 등장

• 글로벌 락은 MySQL 서버 전체 테이블에 읽기 잠금을 걸어버려 가급적 사용을 지양합니다.
• 8.0부터는 트랜잭션을 지원하는 InnoDB가 기본 스토리지 엔진으로 채택되어 모든 데이터 변경 작업을 멈출 필요는 없어졌습니다.
• 백업 툴들은 글로벌 락 상황에도 쓰기가 가능하지만 백업 도중에 스키마가 변경되면 백업이 실패됩니다.
• 이러한 상황을 막기 위해 백업 락이 도입되었습니다.

-- 백업 실행
mysql> LOCK INSTANCE FOR BACKUP;
-- UNLOCK
mysql> UNLOCK INSTANCE;

🤔 특정 세션에서 백업 락을 획득하면?

• 모든 세션에서 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 됩니다.
  • 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
  • REPAIR TABLE과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경
• 일반적인 테이블의 데이터는 변경 가능합니다.

테이블 락

개별 테이블 단위로 설정되는 잠금으로 명시적 방법과 묵시적 방법이 있습니다.

명시적 방법

-- 테이블 락
LOCK TABLES table_name [ READ | WRITE ]
-- UNLOCK
UNLOCK TABLES;

묵시적 방법

• 쿼리 실행 시 자동으로 잠금 또는 해제가 됩니다.
• MyISAM, MEMORY
  • 테이블의 데이터 변경을 위한 DML 쿼리를 실행했을 때
• InnoDB
  • 스키마를 변경하는 DDL 쿼리를 실행했을 때
  • InnoDB 테이블은 레코드 기반의 잠금을 제공하기 때문에 단순 DML 쿼리로 인해서는 테이블 락이 설정되지 않습니다.

네임드 락

임의의 문자열에 대해 잠금을 설정하며 유저 레벨 락으로도 불립니다.

🤔 문자열에 잠금을 건다는 건 어떤 의미일까?

단순히 사용자가 지정한 문자열 자체에 의미를 두어 같은 상황을 만들지 않는 용도입니다.
예를 들면 특정 유저가 동일 작업을 하는 것을 방지하기 위해
트랜잭션 시 GET_LOCK("user_name")으로 락을 걸어 동일한 user의 해당 작업을 잠금시켜 버릴 수 있습니다.

-- "mylock"이라는 문자열에 대해 잠금을 획득한다. 이미 잠금을 사용 중이면 2초 동안만 대기한다. 
select get_lock('mylock', 2); 

-- "mylock"이라는 문자열에 대해 잠금이 설정돼 있는지 확인한다. 
select is_free_lock('mylock'); 

-- "mylock"이라는 문자열에 대해 획득했던 잠금을 반납(해제)한다.  
select release_lock('mylock'); 

-- 3개 함수 모두 정상적으로 락을 획득하거나 해제한 경우에는 1을, 아니면 NULL이나 0을 반환한다.

• 8.0 부터 네임드 락을 중첩해서 사용할 수 있고, 동시에 모두 해제하는 기능도 추가되었습니다.
  • 책에는 8.0 부터라고 했지만 공식 도큐를 찾아보니 5.7 이상부터 가능한 것 같습니다.
  • function_release-all-locks, MySQL 5.7 Reference

-- 모든 문자열에 대한 잠금을 해제한다. 해제된 잠금 수를 반환한다.
select release_all_locks();

• 유용한 경우
  • 데이터베이스 서버 1대에 5대의 웹 서버가 접속해 서비스하는 상황에서 동기화가 중요한 경우
  • 배치 프로그램처럼 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션
  • 동일한 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 데드락 방지 가능

메타데이터 락

데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다.

• 명시적으로 획득하거나 해제할 수 있는 것이 아니고, 테이블의 이름을 변경하는 경우 자동으로 획득할 수 있습니다.
• 보통 배치 프로그램에서 실시간으로 테이블을 변경해야 하는 경우 사용됩니다.
  • 현재 서비스용 랭킹 테이블(rank) : rank_backup으로 백업
    새로 만들어진 랭킹 테이블(rank_new) : 서비스용으로 대체

🤔 아래처럼 2개로 나눠서 실행한다면?

mysql> RENAME TABLE rank TO rank_backup;
mysql> RENAME TABLE rank_new TO rank;

아주 짧은 시간이지만 rank 테이블이 존재하지 않아 Table not found 'rank' 오류가 발생합니다.

✅ 해결 방법

아래 구문 실행 시 자동으로 두 테이블 모두 메타데이터 락을 자동으로 획득하고, 잠금을 설정합니다.

mysql> RENAME TABLE rank TO rank_backup, rank_new TO rank;

3. InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은..

• MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식이 탑재되어 있습니다.
• 레코드 기반의 잠금 방식 때문에 훨씬 뛰어난 동시성 처리를 제공합니다.

레코드 락

테이블 레코드 자체를 잠그는 것입니다.

• 다른 상용 DBMS의 레코드 락과 동일한 역할
• 레코드 수준의 잠금은 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락, 또는 테이블 락으로 레벨업 되는 경우(락 에스컬레이션)이 발생하지 않습니다.
• 프라이머리 키, 유니크 인덱스로 조회해서 하나의 인덱스 레코드(row)에만 잠금을 거는 것입니다.
• InnoDB는 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점에서 중요한 차이가 있습니다.
• PK 인덱스를 명시적으로 지정하지 않아도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정합니다.
• 보조 인덱스에 의한 변경 작업
  • 넥스트 키 락
  • 갭 락
• PK 또는 유니크 인덱스에 의한 변경 작업
  • 갭에 대해서 잠그지 않고 레코드 자체에 대해서만 잠금

갭 락

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격 만을 잠그는 것을 의미합니다.

• 다른 상용 DBMS와의 차이
• 레코드와 레코드 사이의 간격에 새로운 레코드가 INSERT 되는 것을 제어합니다.
• 갭 락은 이 자체보다는 넥스트 키 락의 일부로 자주 사용됩니다.

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금입니다.

• 인덱스 레코드의 가장 처음 레코드의 이전, 그리고 마지막 레코드의 이후의 갭에도 잠금을 설정합니다.
• STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버
  • REAPETABLE READ 격리 수준 사용
• 🤔 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화(0)되면?
  • 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸립니다.
• REPEATABLE READ 격리 수준에서 팬텀 리드를 방지하기 위한 잠금
• SELECT * WHERE pk > 99 FOR UPDATE 실행 시 일어나는 일들

https://velog.io/@juijeong8324/MySQL-transactionandlock

 

• pk > 99 를 만족하는 첫번째 인덱스 레코드 pk=101 발견
• 첫번째 인덱스 발견 직전의 인덱스 레코드 pk=97부터 pk=101 사이에 갭 락 적용
• pk > 99 인 모든 인덱스 레코드들 사이에도 갭 락 적용
• pk > 99 인 모든 인덱스 레코드들 사이에도 레코드 락 적용
• 즉, 갭 락과 레코드 락이 복합적으로 적용되는 것을 넥스트 키 락이라고 합니다.

InnoDB의 갭 락과 넥스트 키 락의 목적

• 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것
• 하지만 의외로 이로 인해 데드락이나 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생합니다.

→ 가능하다면 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋습니다.
→ 8.0에서는 ROW 형태의 바이너리 로그가 디폴트가 되었습니다.

💡 바이너리 로그 포맷

STATEMENT

가장 오래된 포맷으로 데이터 변경에 사용되는 모든 쿼리를 쿼리대로 저장하는 방식 (5.7 기본)

한번에 많은 변경의 경우 효과적 (WHERE절로 수백개 데이터 UPDATE)

ROW

변경 작업으로 변경된 모든 ROW의 정보를 기록하는 방식 (8.0 기본)

작은 변경이 여러번 일어나는 경우 효과적

MIXED

두 방식을 혼합

자동 증가 락

🤔 AUTO_INCREMENT 가 사용된 테이블에 동시에 여러 레코드가 INSERT 된다면?

중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 합니다.

이를 위해 내부적으로 자동 증가 락이라는 테이블 수준의 잠금을 사용합니다.

• 트랜잭션과 관계없이 INSERT, REPLACE 문에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제됩니다.
  • UPDATE, DELETE 문에서는 걸리지 않습니다.
• 명시적으로 획득하고 해제하는 방법은 없지만
  • 아주 짧은 시간 동안 걸렸다가 해제되는 잠금이라서 대부분의 경우 문제가 되지 않습니다.
• AUTO_INCREMENT 컬럼에 명시적으로 값을 설정하더라도 자동 증가 락을 걸게 됩니다.
• 잠금을 최소화하기 위해 한 번 증가하면 절대 자동으로 줄어들지 않습니다.

🤔 두 개의 INSERT 쿼리가 동시에 실행된다면?

자동 증가 락은 테이블에 단 하나만 존재하기 때문에 하나의 쿼리가 락을 걸면 나머지 쿼리는 락을 기다려야 합니다.

innodb_autoinc_lock_mode 변수로 작동 방식을 설정할 수 있습니다. (5.1 이상)

• 0 값 : 모든 INSERT문에 사용합니다.
• 1 값 : 서버가 레코드 건수 예측 가능 시 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리합니다.
• 2 값 : 무조건 래치를 이용해 처리합니다.

인덱스와 잠금

레코드 락에서 나오는 인덱스 잠금에 대해 알아봅니다.

• 인덱스를 잠그는 방식이란?
  • 변경할 레코드를 찾을 때 검색한 인덱스의 레코드를 모두 락을 걸어야 하는 것
• 인덱스 설계가 중요한 이유?
  • 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 모든 레코드를 잠궈야 하는 일이 발생합니다.

예시) 🤔 아래 쿼리를 실행하면 1개의 UPDATE를 위해 몇 개의 레코드에 락을 걸어야 할까요?

-- 멤버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다는 가정으로 진행

mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi'; 
+-----------+  
|       253 |  
+-----------+  

mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
+-----------+  
|         1 |  
+-----------+ 

mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

first_name에는 인덱스가 존재하지만 last_name에는 인덱스가 없기 때문에 레코드 253건이 모두 잠깁니다.

🤔 만약 인덱스가 아예 존재하지 않는다면?

풀 스캔이 일어나면서 1개의 UPDATE를 위해 모든 레코드가 잠기게 됩니다.
그래서 UPDATE와 같이 잠금이 걸리는 작업 같은 경우, 잠금의 범위를 최소화 하도록 인덱스 설계를 잘 하는 것이 중요합니다.

레코드 수준의 잠금 확인 및 해제

레코드 수준의 잠금은 테이블 수준 잠금보다 조금 더 복잡하고 문제의 원인을 발견하고 해결하기도 어렵습니다.

🤔 버전별로 레코드 잠금과 잠금을 대기하는 클라이언트의 정보를 확인하고 싶다면?

• 5.1 ~ : information_schema의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 통해 확인
• 8.0 ~ : information_schema 정보는 조금씩 deprecated 됨
  performance_schema의 data_locks와 data_lock_waits 테이블로 대체되는 중입니다.

🤔 특정 스레드가 어떤 잠금을 가지고 있는지 상세히 확인하고 싶다면?

• performance_schema의 data_locks 테이블이 가진 컬럼을 모두 살펴보면 됩니다.

mysql> SELECT * FROM performance_schema.data_locks\G

🤔 강제로 잠금을 해제하려면?

• KILL 명령을 이용해 특정 스레드를 강제 종료합니다.

-- KILL {특정 스레드번호} 
mysql> KILL 17

4. MySQL의 격리 수준

🤔 트랜잭션의 격리 수준이란?

여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 할지 못 보게 할지를 결정하는 것

격리 수준

아래로 갈수록 고립성이 높아지며, 동시 처리 성능이 떨어집니다.

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ	비고
READ UNCOMMITTED	발생	발생	발생	거의 사용하지 않음
READ COMMTTIED	없음	발생	발생	오라클, PostgreSQL (기본)
REPEATABLE READ	없음	없음	발생(InnoDB는 없음)	InnoDB (기본)
SERIALIZABLE	없음	없음	없음	거의 사용하지 않음

AUTOCOMMIT

트랜잭션 격리 수준을 테스트 하기 위해서는 AUTOCOMMIT을 OFF로 해두어야 합니다.

• MySQL에서는 default로 enabled 되어 있습니다.

-- 활성화 여부 조회
mysql> SELECT @@AUTOCOMMIT;

-- OFF로 변경
mysql> SET autocommit=OFF;

1. READ UNCOMMITED

트랜잭션에서 변경 내용이 아직 COMMIT 되지 않은 상태에서도 조회할 수 있습니다.

Dirty Read 문제 발생

어떤 트랜잭션의 작업이 완료되지 않았는데도, 다른 트랜잭션에서 볼 수 있는 데이터 불일치 현상

Dirty Read 문제 때문에 실제 DB에 있는 데이터와 결과가 다른 문제가 발생할 수 있습니다.
예를 들어 사용자 A가 트랜잭션을 끝마치지 못해 롤백을 하더라도 사용자 B는 무효가 된 데이터 값(Lara)를 읽고 처리를 하게 될 것입니다.
RDBMS 표준에서 트랜잭션의 격리수준으로 인정하지 않을 정도로 정합성에 많은 문제가 있습니다.

2. READ COMMITED

• 오라클, PostgreSQL에서 기본으로 사용되는 격리수준
• 온라인 서비스에서 가장 많이 사용되는 격리수준

트랜잭션에서 변경 내용이 COMMIT 된 상태에서만 조회할 수 있습니다.
트랜잭션이 이루어지는 동안 다른 사용자는 해당 데이터에 접근이 불가능합니다.

READ COMMITED 동작 방식

사용자 A가 트랜잭션을 시작하여 데이터를 변경하였고, 아직 커밋은 하지 않은 상태입니다.
그러면 테이블은 먼저 갱신되고, 언두 로그로 변경 전의 데이터가 백업됩니다.

이때 사용자 B가 데이터를 조회하려고 하면, READ COMMITTED에서는 커밋된 데이터만 조회할 수 있으므로,
언두 로그에서 변경 전의 데이터를 찾아서 반환하게 됩니다.

최종적으로 사용자 A가 트랜잭션을 커밋하면 그때부터 다른 트랜잭션에서도 새롭게 변경된 값을 참조할 수 있게 됩니다.

NON-REPLEATABLE READ 문제 발생

하나의 트랜잭션 안에서 똑같은 SELECT 쿼리로 데이터를 여러번 요청했을 때, 항상 같은 결과를 가져오지 못하는 데이터 불일치 현상

중간에 다른 트랜잭션에서 커밋한 데이터 때문에 한 트랜잭션 안에서 같은 SELECT문의 실행 결과가 달라지는 문제가 발생합니다.

• 일반적으로는 크게 문제가 되지 않을 수 있지만, 금전적인 내용을 다루는 경우 주의해야 합니다.
  • 오늘 입금된 금액의 총합을 조회하는 상황에서 NON-REPEATABLE READ가 발생할 경우,
    총합을 계산하는 SELECT 쿼리가 실행될 때 마다 다른 결과를 가져올 수 있기 때문입니다.

3. REPEATABLE READ

• InnoDB에서 기본으로 사용되는 격리 수준
• MVCC를 이용해 한 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장합니다.
  • 단, 새로운 레코드가 추가되는 경우에는 데이터 불일치가 생길 수 있습니다.
• 보장하는 방법
  • 각각의 트랜잭션은 순차 증가하는 고유한 트랜잭션 번호가 존재합니다.
  • 주기적으로 언두 영역의 데이터를 삭제할 때 MVCC를 보장하기 위해 실행 중인 트랜잭션보다 이전 트랜잭션 번호를 가진 언두 영역의 데이터는 삭제하지 않습니다.
  • 그러다보니 트랜잭션이 제때 종료되지 않으면 언두 영역이 무한정 커지게 되어서 성능 이슈가 생길 수 있습니다.
• 바이너리 로그를 가진 MySQL 서버에서는 최소 이 격리 수준 이상을 사용해야 합니다.

REPEATABLE READ 동작 방식

• 사용자 A가 emp_no가 500000인 사원의 이름을 UPDATE 하고
  사용자 B가 emp_no가 500000인 사원을 SELECT 하는 과정

트랜잭션을 시작하고, emp_no가 500000인 레코드를 조회하면 1건이 조회되는 상황이라고 가정해보겠습니다. 아직 트랜잭션은 종료되지 않았습니다.

그리고 이때 다른 사용자 A의 트랜잭션에서 first_name이 Toto인 레코드를 갱신하는 상황입니다.
그러면 MVCC를 통해 기존 데이터는 변경되지만, 백업된 데이터가 언두 로그에 남게 됩니다.

이전에 사용자 B가 데이터를 조회했던 트랜잭션은 아직 종료되지 않은 상황에서, 사용자 B가 동일한 SELECT문을 실행하면 어떻게 될까요?

사용자 B의 트랜잭션(10)은 사용자 A의 트랜잭션(12)이 시작하기 전에 이미 시작된 상태입니다.
이 때 REPEATABLE READ는 트랜잭션 번호를 참고해 자신보다 먼저 실행된 트랜잭션의 데이터만을 조회합니다.
만약 테이블에 자신보다 이후에 실행된 트랜잭션의 데이터가 존재한다면? 언두 로그를 참고해서 데이터를 조회합니다.

따라서 사용자 A의 트랜잭션이 시작되고 커밋까지 되었지만 해당 트랜잭션(12)는 현재 트랜잭션(10)보다 나중에 실행되었기 때문에
조회 시 기존과 동일한 데이터를 얻게 됩니다.

즉, REPEATABLE READ는 어떤 트랜잭션이 읽은 데이터를 다른 트랜잭션이 수정하더라도 동일한 결과를 반환할 것을 보장해줍니다.

READ COMMITED vs. REPEATABLE READ

🤔 두 격리 수준 모두 언두 영역에 데이터를 백업해두는데 어떤 차이가 있을까요?
READ COMMITED도 언두 영역에서 데이터를 읽어오지만, REPEATABLE READ는 이보다 훨씬 전 버전의 데이터를 읽을 수 있습니다.

PHANTOM READ 문제 발생

트랜잭션 시작 시점 데이터를 읽었을 때 존재하지 않았던 데이터가 다시 같은 조건으로 데이터를 읽어 들였을 때 유령처럼 나타나는 현상

REAPETABLE READ는 새로운 레코드의 추가까지는 막지 않습니다.
따라서 SELECT로 조회한 경우 트랜잭션이 끝나기 전에 다른 트랜잭션에 의해 추가된 레코드가 발견될 수 있는데, 이를 Phantom Read 라고 합니다.

하지만 MVCC 덕분에 일반적인 조회에서는 Phantom Read가 발생하지 않습니다.
왜냐하면 자신보다 나중에 실행된 트랜잭션이 추가한 레코드는 무시하면 되기 때문입니다.

🤔 그렇다면 언제 Phantom Read가 발생하는 것일까요?
바로 잠금이 사용되는 경우입니다.

• 사용자 A가 employees 테이블에 INSERT를 실행하는 도중에
  사용자 B가 SELECT FOR UPDATE 쿼리로 employees 테이블을 조회하는 상황

MySQL은 갭 락으로 인해 다른 RDBMS와 동작이 다른 부분이 있어 일반적인 RDBMS 경우부터 살펴보겠습니다.

• 일반적인 RDBMS

사용자 B가 먼저 데이터를 조회하는데, SELECT FOR UPDATE 쿼리를 이용해 쓰기 잠금을 걸었습니다.

SELECT ... FOR UPDATE : 배타 락(쓰기 잠금)을 거는 것
LOCK IN SHARE MODE : 공유 락(읽기 잠금)을 거는 것

그리고 사용자 A가 새로운 데이터를 삽입하는 상황이라고 가정하겠습니다.
일반적인 DBMS는 갭 락이 없기 때문에 emp_no=500000인 레코드만 잠금이 걸린 상태이고, 사용자 A의 요청은 잠금 없이 즉시 실행됩니다.

이 때 사용자 B가 동일한 쓰기 잠금 쿼리로 다시 한번 데이터를 조회하면 이번에는 2건의 데이터가 조회됩니다. 동일한 트랜잭션 내에서도 새로운 레코드가 추가되는 경우에 조회 결과가 달라지는데 Phantom Read가 발생한 것입니다. 이는 다른 트랜잭션에서 새로운 레코드를 추가하거나 삭제하는 경우 발생할 수 있습니다.

이 경우에도 MVCC를 통해 해결될 것 같지만, 두 번째 실행되는 SELECT FOR UPDATE 때문에 그럴 수 없습니다.
MVCC에서는 데이터를 먼저 테이블에 반영하고, 언두 로그에 백업합니다.

즉, SELECT FOR UPDATE로 잠금을 걸어도 테이블에는 반영이 되고, 언두 로그에는 다른 트랜잭션에 의한 데이터가 계속해서 쌓이는 것입니다.
만약 먼저 시작된 트랜잭션이 존재하여 작업을 하면 테이블에는 반영되고, 언두 로그에는 이전 트랜잭션의 데이터가 쌓이게 됩니다.
그러므로 MVCC 만으로 정확한 데이터 제공이 불가능하고, 언두 로그에도 잠금을 걸어야 하는데, 언두 로그는 append only 형태이므로 잠글 수가 없습니다.

따라서 SELECT FOR UPDATE나 LOCK IN SHARE MODE로 레코드를 조회하는 경우
언두 영역의 데이터가 아니라 현재 테이블의 레코드를 가져오게 되고, 이로 인해 Phantom Read가 발생하는 것입니다.

하지만 MySQL에는 갭 락이 존재하기 때문에 위의 상황에서 문제가 발생하지 않습니다.

• MySQL

사용자 B가 SELECT FOR UPDATE로 데이터를 조회합니다.
MySQL은 emp_no=500000인 레코드에는 레코드 락, emp_no > 500000인 범위에는 갭 락으로 넥스트 키 락을 겁니다.
따라서 사용자 A가 emp_no=500001 인 멤버 삽입 시, 사용자 B의 트랜잭션이 종료될 때까지 대기하다가 대기가 너무 길어지면 락 타임아웃이 발생하게 됩니다.

따라서 일반적으로 MySQL의 REAPEATABLE READ에서는 Phantom Read가 발생하지 않습니다.

MySQL에서 Phantom Read가 발생하는 거의 유일한 케이스는 다음과 같습니다.

사용자 B는 트랜잭션을 시작하고, 잠금이 없는 SELECT 문으로 데이터를 조회합니다.
그리고 사용자 A는 INSERT 문을 사용해 데이터를 추가하였습니다. 이때 잠금이 없으므로 바로 COMMIT이 됩니다.
하지만 사용자 B가 SELECT FOR UPDATE로 조회를 했다면, 언두 로그가 아닌 테이블로부터 레코드를 조회하므로 Phantom Read가 발생합니다.

하지만 이러한 케이스는 거의 존재하지 않으므로,
MySQL의 REPEATABLE READ에서는 PHANTOM READ가 발생하지 않는다고 봐도 될 것 같습니다.

아래는 MySQL 기준으로 정리한 내용입니다.

• SELECT FOR UPDATE 이후 SELECT: 갭 락 때문에 팬텀 리드 X
• SELECT FOR UPDATE 이후 SELECT FOR UPDATE: 갭 락 때문에 팬텀 리드 X
• SELECT 이후 SELECT: MVCC 때문에 팬텀 리드 X
  • 트랜잭션 격리 수준이 반복 읽기(기본 수준)인 경우, 동일한 트랜잭션 내의 모든 일관된 읽기는 해당 트랜잭션의 첫 번째 읽기에 의해 설정된 스냅샷을 읽습니다. 현재 트랜잭션을 커밋하고 그 후에 새 쿼리를 실행하면 쿼리에 대한 최신 스냅샷을 얻을 수 있습니다.
  • https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
• SELECT 이후 SELECT FOR UPDATE: 팬텀 리드 O

🤔 INSERT 시 왜 PHANTOM READ가 생길까?

언두 로그는 잠금을 걸수가 없어서 현재 상태의 데이터를 읽게 된다.
SELECT FOR UPDATE 문으로 이미 쓰여진 레코드들에 쓰기 잠금을 걸면서 읽기 때문에
언두 로그를 참조하려 했지만 INSERT 된 언두 로그는 잠금을 걸 수가 없어서 현재 상태의 데이터를 읽게 됩니다.

NON-REPEATABLE READ vs. PHANTOM READ

• NON-REPEATABLE READ : 원래 있던 행의 값이 달라지는 것
• PHANTOM READ : 새로운 행이 생기는 것

🤔 InnoDB의 REPEATABLE READ에서는 왜 PHANTOM READ가 생기지 않을까?

PHANTOM READ는 언두 영역에 락을 못걸어서 바로 변경된 값을 가져오다보니 생기는 현상인데
InnoDB는 갭 락과 넥스트 키 락을 사용해 현재 트랜잭션에서 검색하는 레코드와 주변의 갭에 잠금이 걸리기 때문에
갭에 새 레코드를 삽입할 수 없어서 PHANTOM READ가 생기지 않습니다.

4. SERIALIZABLE

한 트랜잭션에서 사용하는 데이터를 다른 트랜잭션에서 접근할 수 없습니다.

• PHANTOM READ를 방지하기 위해 사용하는 락입니다.
• 가장 엄격한 격리 수준이고, 가장 낮은 동시성 처리 성능을 보여줍니다.
  • InnoDB는 순수한 SELECT 읽기 작업의 경우 잠금이 필요없는 일관된 읽기를 지원하므로 아무런 레코드 잠금 없이 실행됩니다.
  • 읽기 작업도 읽기 잠금이 무조건 걸려야 하고, 따라서 같은 읽기 작업이 아니라면 트랜잭션을 모두 기다려야 하기 때문에 성능이 저하됩니다.
• InnoDB에서는 PHANTOM READ 현상이 REPEATABLE READ 격리 수준에서 발생하지 않기 때문에 굳이 사용할 필요는 없습니다.