[RealMySQL8.0] 5장 - 트랜잭션과 잠금

hwwwa·2023년 7월 1일

📖 RealMySQL8.0

목록 보기

2/2

5장 - 트랜잭션과 잠금

잠금(Lock)과 트랜잭션, 트랜잭션의 격리수준은 MySQL의 동시성에 영향을 미칩니다.
잠금은 동시성을 제어하기 위한 기능이고, 트랜잭션은 정합성을 보장하기 위한 기능이다.

트랜잭션

트랜잭션은 작업의 완전성을 보장해줍니다. 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구하여 작업의 일부만 적용되는 Partial update 현상이 발생하지 않도록하여 데이터 정합성을 보장해줍니다.

트랜잭션을 지원하지 않는 MyISAM 테이블이나 MEMORY 테이블에서 Partial update 현상이 발생한다면, 실패한 쿼리로 인해 남은 레코드를 애플리케이션 코드 상에서 다시 삭제하도록 하는 복잡한 재처리 작업이 필요할 수 있습니다.

트랜잭션은 DBMS 커넥션과 동일하게 꼭 필요한 최소한의 코드에만 적용하여 트랜잭션의 범위를 최소화하는 것이 좋습니다.

예시) 사용자가 게시글을 등록하는 경우

// 트랜잭션이 긴 경우  
1) 처리 시작 
	=> 데이터베이스 커넥션 생성 
	=> 트랜잭션 시작 
2) 사용자의 로그인 여부 확인 
3) 사용자의 글쓰기 내용의 오류 여부 확인 
4) 첨부로 업로드된 파일 확인 및 저장 
5) 사용자의 입력 내용을 DBMS에 저장 
6) 첨부 파일 정보를 DBMS에 저장 
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회 
8) 게시물 등록에 대한 알림 메일 발송 
9) 알림 메일 발송 이력을 DBMS에 저장 
	<= 트랜잭션 종료(COMMIT) 
	<= 데이터베이스 커넥션 반납 
10) 처리 완료

위 순서에서 실제 데이터 저장이 일어나는 것은 5번부터입니다.
일반적으로 DB 커넥션은 개수가 제한적이므로 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 커넥션의 개수는 줄어들게 됩니다. 어느 순간 각 단위 프로그램에서 커넥션을 가지기 위해 대기해야 하는 상황이 생길 수 있어 좋지 않은 영향을 미칠 확률이 높아집니다. 따라서 2~4번 작업이 아무리 빨리 처리된다 해도 DMBS 트랜잭션에 포함될 필요는 없습니다.

특히 8번 작업에서 더 큰 위험이 존재합니다.
메일 발송은 네트워크를 통해 원격 서버와 통신하므로 DBMS의 트랜잭션에서 제거하는 것이 좋습니다. 만약 네트워크 장애 등의 이유로 메일 서버가 통신 불가의 상태에 빠진다면 해당 트랜잭션은 실패할 것이며, 웹 서버뿐 아니라 DBMS 서버 장애로까지 이어질 수 있습니다.

위와 같이 긴 트랜잭션을 다음과 같이 최소화하고 나눠주면 위험도를 낮출 수 있습니다.

1) 처리 시작 
2) 사용자의 로그인 여부 확인 
3) 사용자의 글쓰기 내용의 오류 발생 여부확인 
4) 첨부로 업로드된 파일 확인 및 저장 
	=> 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기) 
	=> 트랜잭션 시작 
5) 사용자의 입력 내용을 DBMS에 저장 
6) 청부 파일 정보를 DBMS에 저장 
	<= 트랜잭션 종료(COMMIT) 
7) 저장된내용 또는 기타 정보를 DBMS에서 조회 
8) 게시물등록에 대한 알림 메일 발송 
	=> 트랜잭션 시작 
9) 알림 메일 발송 이력을 DBMS에 저장 
	<= 트랜잭션 종료(COMMIT) 
	<= 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납) 
10) 처리 완료

💡 DB 커넥션을 가지고 있는 범위와 트랜잭션이 활성화되어 있는 프로그램의 범위를 최소화하자
💡 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제하자

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있습니다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨 잠금은 스토리지 엔진 간 상호 영향을 미치지 않습니다.

글로벌 락

MySQL에서 제공하는 잠금 가운데 범위가 가장 큰 잠금입니다.
SELECT를 제외한 대부분의 DDL이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 대기 상태로 남습니다.
여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야하는 경우 사용합니다.
글로벌 락은 MySQL 서버 전체 테이블에 영향을 주는 만큼 웹 서비스에서 가급적 사용하지 않는 것이 좋습니다.

MySQL 8.0부터는 InnoDB 엔진 사용 일반화로 조금 더 가벼운 글로벌 락의 필요성이 생겨 백업 락이 도입되었습니다.

특정 세션에서 백업 락을 획득하면 모든 세션에서 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 됩니다. 하지만 백업을 주로 실행하는 Replica 서버에서 백업과 복제를 동시에 수행해야하기 때문에 백업 락은 일반적인 테이블의 데이터 변경을 허용합니다.
백업 락은 DDL 명령이 실행된 경우 복제를 일시 중지하는 역할도 수행합니다.

테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금으로, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있습니다.

명시적 테이블 락은 글로벌 락과 동일하게 온라인 작업에 상당항 영향을 미치므로 특별한 상황이 아니라면 애플리케이션에서 사용할 필요가 거의 없습니다.

묵시적 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터 변경 쿼리를 실행하는 경우에 발생합니다.
하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적 테이블락이 설정되지 않습니다. 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경(DDL)하는 쿼리의 경우에만 영향을 미칩니다.

네임드 락

네임드 락은 임의의 문자열에 대해 설정하는 잠금으로, 테이블이나 레코드, 데이터베이스 객체가 아닌 사용자가 지정한 문자열에 대해 락을 획득하고 반납하는 잠금입니다.

예를 들어, 데이터베이스 서버 1대에 5대의 웹 서버가 접속해 서비스하는 상황에서 웹 서버가 특정 정보를 동기화해야 하는 경우처럼, 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 사용하면 쉽게 해결할 수 있습니다.

배치 프로그램처럼 많은 레코드를 변경하는 경우에 데드락이 발생할 확률이 높아지는데, 이때 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해 네임드 락을 걸고 쿼리를 실행하면 데드락을 아주 쉽게 해결할 수 있습니다.

MySQL 8.0 버전부터는 네임드 락을 중첩하여 사용할 수 있게 되었으며, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가되었습니다.

* 공식 도큐 참고 시 5.7도 가능한 것으로 보임

메타데이터 락

메타데이터 락은 데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금으로, 명시적으로 획득하거나 해제할 수 있는 잠금이 아닌 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금입니다.

테이블의 이름을 변경하는 쿼리를 2개의 쿼리로 나눠서 실행한다면 Table not found 오류를 발생시킬 수 있기 때문에 하나의 쿼리로 실행하는 것이 좋습니다.

InnoDB 스토리지 엔진 잠금

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

최근 버전에서는 information_schema DB의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블로 InnoDB의 트랜잭션, 잠금, 잠금 대기중인 트랜잭션 목록을 조회할 수 있게 되었습니다. 또한 Performace Schema를 이용하여 InnoDB 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링도 가능하게 되었습니다.

InnoDB 스토리지 엔진은 잠금 정보가 상당히 작은 공간으로 관리 되기 때문에 락 에스컬레이션이 발생하지 않습니다.

💡 락 에스컬레이션
레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업 되는 경우처럼 많은 수의 작은 잠금을 더 적은 수의 큰 잠금으로 올리는 프로세스를 뜻합니다. 락 에스컬레이션이 발생하면 동시성 경합 가능성이 올라가므로 동시성 처리 성능이 떨어집니다.

InnoDB 스토리지 엔진은 다른 DBMS와 다르게 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이 존재합니다.

출처: Real MySQL 8.0

점선은 실제 존재하지 않는 레코드를 나타냅니다.

레코드 락

레코드 락은 레코드 자체만을 잠그는 락입니다. InnoDB 스토리지 엔진은 레코드 자체가 아닌 인덱스의 레코드를 잠근다는 특징을 가집니다.

InnoDB에서 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용하지만, 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해 잠그지 않고 레코드 자체에 대해서만 락을 걸게 됩니다.

갭 락

갭 락은 레코드 자체가 아닌 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것입니다. 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어합니다.
넥스트 키 락의 일부로 주로 사용됩니다.

넥스트 키 락

넥스트 키 락은 레코드 락과 갭 락을 합쳐놓은 형태의 잠금입니다.
바이너리 로그에 기록되는 쿼리가 Replica 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장합니다.

데드락이나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하므로 가능하면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋습니다.

자동 증가 락

자동 증가 락은 AUTO_INCREMENT가 사용된 테이블에 동시에 여러 레코드가 insert되는 경우, 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가지도록 해주는 테이블 수준의 잠금입니다.
INSERT나 REPLACE 쿼리와 같이 새로운 레코드를 저장하는 쿼리에만 사용됩니다.

자동 증가 락은 하나만 존재하기 때문에 동시에 INSERT가 일어나는 경우 하나의 쿼리는 해당 잠금을 기다려야 합니다.
또한 자동 증가 락은 명시적으로 명시적으로 해제하거나 획득할 수 없습니다.

5.1 이상부터는 innodb_autoinc_lock_mode 시스템 변수를 통해 자동 증가 락의 작동 방식을 변경할 수 있습니다.

인덱스와 잠금

레코드 락에서 설명한 것 처럼 InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리됩니다. 즉, 변경할 레코드를 찾을 때 검색한 인덱스의 레코드를 모두 잠궈야 합니다. 이와 같은 특징 때문에 MySQL에서는 인덱스 설계가 굉장히 중요합니다.

다음 예제를 살펴보겠습니다.

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

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

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

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

위의 쿼리를 실행하면 1건의 UPDATE 쿼리를 위해 253건의 레코드가 잠기게 됩니다.
first_name에는 인덱스가 존재하지만 last_name에는 인덱스가 없기 때문에 first_name='Georgi' 인 레코드 253건이 모두 잠기게 됩니다.

만약 인덱스가 아예 존재하지 않는다면 풀 스캔이 일어나면서 1개의 UPDATE를 위해 모든 레코드가 잠기게 됩니다. MySQL의 InnoDB에서 인덱스 설계가 중요한 이유도 이 때문입니다.

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

레코드 수준의 잠금은 테이블 수준 잠금보다 조금 더 복잡하고 문제의 원인을 발견하고 해결하기도 어렵습니다. MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능해져 쿼리 하나로 잠금과 잠금 대기를 바로 확인할 수 있게 되었습니다.

// 명령이 실행된 상태의 프로세스 목록을 조회
SHOW PROCESSLIST;

// performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해 
// 잠금 대기 순서 조회

SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread, 
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id, 
    b.trx_mysql_thread_id blocking_thread, 
    b.trx_query blocking_query
FROM performance_schema.data_lock_waits w 
    INNER OOIN information_schema.innodb_trx b
        ON b.trx_id = w.blocking_engine_transaction_id 
    INNER JOIN information_schema.innodb_trx r
        ON r.trx_id = w.requesting_engine_transaction_id;

만약 특정 스레드가 어떤 잠금을 가지고 있는지 더 상세히 확인하고 싶다면 performance_schema의 data_locks 테이블이 가진 컬럼을 모두 살펴보면 됩니다.

SELECT * FROM performance_schema.data_locks\G

만약 특정 스레드가 잠금을 가진 상태에서 오랜 시간 멈춰있다면, 다음과 같이 특정 스레드를 강제 종료하여 잠금 경합을 끝낼 수 있습니다.

// KILL {특정 스레드번호}
KILL 17

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;

READ UNCOMMITED

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

Dirty Read 문제 발생

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

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

READ COMMITED

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

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

READ COMMITED 동작 방식

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

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

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

NON-REPLEATABLE READ 문제 발생

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

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

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

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가 생기지 않습니다.

SERIALIZABLE

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

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

스터디 피드백

격리 레벨 -> 개발자 입장에서 사용 시 주의해야할 부분
직접 테이블을 만들고 세션 창을 여러 개 열어서 시나이로 별로 테스트 해보면 좋다
READ COMMITED vs REPEATABLE READ 정답은 없음.
서비스 수준에서, 로직에서 문제가 발생하지 않게 잘 알고 개발해야 함. 잘 모르고 개발한다면 REPEATABLE READ를 사용.
잘 알고 개발한다면 READ COMMITED로 낮춰도 됨. 격리 레벨이 낮아질 수록 당연히 성능이 올라감
기본은 REPEATABLE READ. 개발자가 데드락으로 인해서 격리 수준을 낮추는걸 문의할 때가 있음.
서비스 특성에 맞게 문제가 없다면 변경 가능.

세션 레벨로 바꾸거나 힌트로 박아서 격리 수준이 다르게 동작하게 할 수 있음.

개발자에게 가장 문의가 많이 오는 내용: 어디서 데드락이 났는지. 락 경합이 얼마나 나고있는지 등.
장애 원인이 될 수 있으므로 락을 잡고있는 트랜잭션을 Kill 해줘야 함.
SHOW ENGINE INNODB STATUS 에서도 데드락, 롤백된 트랜잭션 등을 확인할 수 있음. 락을 잡고있는 트랜잭션을 볼 수 있음.
-> 추가 공부 하기

hwwwa

이전 포스트