Real MySql 8.0 1 : 쿼리 실행, 트랜잭션과 잠금

저자 : 백은빈, 이성욱

Real MySQL 8.0

들어가며

---

 MySql 동작 원리를 자세하고 이해하기 쉽게 알려주는 책이다. 동작 원리를 이해하고 나니 백엔드 개발자로서 테이블 설계와 쿼리 작성을 어떻게 해야하는지 감이 잡혔다. 데이터를 저장하고 가공하는 백엔드 개발자에게는 반드시 읽어야할 책이다. MySql을 사용하는 개발자에게는 더할나위없이 좋고, 오라클이나 postgreSql과 같이 다른 진영에 있더라도 RDBMS를 MySql 예제를 통해 학습할 기회로 삼으면 좋다.

MySql에 대해 A부터 Z까지 풀어주는 책이므로 설치, 클러스터링, 복제 등 상대적으로 관여도가 낮은(DBA에게 높은) 단원은 가볍게 읽고 넘어갔다. 개발하면서 자주 접하는 문제들 위주로 정리하였다.

 

다음부터는 발췌한 내용이다.

04 아키텍처

---

쿼리 실행 구조

MySql 쿼리 실행 구조

MySql이 쿼리 실행하는 과정을 도식화하면 위와 같고, 아래는 각 기능별 설명이다.

쿼리파서

사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다. 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메세지를 전달한다.

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 컬럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행한다. 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.

옵티마이저

옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당하며, DBMS의 두뇌에 해당한다고 볼 수 있다. 쿼리 변환, 비용 최적화, 실행 계획 수립을 담당한다.

실행 엔진

옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다. 실행 엔진이 하는일을 간단하게 예를 들어 살펴보자. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 해보자.

1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.

핸들러(스토리지 엔진)

핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다.

 

05 트랜잭션과 잠금

---

InnoDB 스토리지 엔진 잠금

레코드 락

레코드 자체만을 잠그는 것을 레코드 락(Record lock, Record only lock)이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다. 한 가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 것이다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. 레코드 자체를 잠그는 것과 인덱스를 잠그는 것은 상당히 큰 차이를 만든다.

갭 락

다른 DBMS와의 또 다른  차이가 바로 갭 락(Gap lock)이다. 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(Insert)되는 것을 제어하는 것이다. 갭 락은 그 자체보다는 넥스트 키 락의 일부로 자주 사용된다.

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락(Next key lock)이라고 한다. STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면(0으로 설정되면) 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 떄 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

자동 증가 락

MySQL에서는 자동 증가하는 숫자 값을 추출(채번)하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공한다. AUTO_INCREMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다. AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 떄문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야 한다(AUTO_INCREMENT 컬럼에 명시적으로 값을 설정하더라도 자동 증가 락을 걸게 된다).

AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없다. AUTO_INCREMENT 락은 아주 짧은 시간 동안 걸렸다가 해제되는 잠금이라서 대부분의 경우 문제가 되지 않는다. MySQL 5.1 이상부터는 innodb_autoinc_lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.

innodb_auto_lock_mode=0

MySQL 5.0과 동일한 잠금 방식으로 만든 INSERT 문장은 자동 증가 락을 사용한다.

innodb_auto_lock_mode=1

단순히 한 건 또는 여러 건의 레코드를 INSERT하는 SQL 중에서 MySQL 서버가 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간 동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금이 해제된다. 하지만 INSERT ... SELECT와 같이 쿼리 실행 전 건수를 예측 할 수 없을 때는 MySQL 5.0에서와 같이 자동 증가 락을 사용한다. 이때는 INSERT 문장이 완료되기 전까지는 자동 증가 락은 해제되지 않기 떄문에 다른 커넥션에서는 INSERT를 실행하지 못하고 대기하게 된다. 이렇게 대량 INSERT가 수행될 떄는 InnoDB 스토리지 엔진은 여러 개의 자동 증가 값을 한 번에 할당받아서 INSERT되는 레코드에 사용한다. 그래서 대량 INSERT되는 레코드는 자동 증가 값이 누락되지 않고 연속되게 INSERT된다. 하지만 한 번에 할당 받은 자동 증가 값이 남아서 사용되지 못하면 폐기하므로 대량 INSERT 문장의 실행 이후에 INSERT되는 레코드의 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다. 이 설정에서는 최소한 하나의 INSERT 문장으로 INSERT되는 레코드는 연속된 자동 증가 값을 가지게 된다. 그래서 이 설정 모드를 연속 모드(Consecutive mode)라고도 한다.

innodb_auto_lock_mode=2

InnoDB 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 하지만 이 설정에서는 하나의 INSERT 문장으로 INSERT되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지 않는다. 그래서 이 설정 모드를 인터리빙 모드(Interleaved mode)라고도 한다. 이 설정 모드에서는 INSERT ... SELECT와 같은 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다. 하지만 이 설정에서 작동하는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장한다. STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수도 있기 때문에 주의해야 한다.

자동 증가 값이 한 번 증가하면 절대 줄어들지 안흔 이유가 AUTO_INCREMENT 잠금을 최소화하기 위해서다. 설령 INSERT 쿼리가 실패했더라도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다.

 

인덱스와 잠금

InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있다. InnoDB의 잠금은 인덱스를 잠그는 방식으로 처리되기 떄문에 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다. 아래 UPDATE 상황을 가정해보자.

-- employees 테이블에는 first_name 컬럼만 인덱스가 걸려있다.
-- first_name='Georgi'인 사원은 253명이 있고, 
-- first_name='Georgi'이고 last_name='Klassen'인 사람은 1명만 있다.
SELECT COUNT(*) FROM employees WHERE first_name='Georgi'; 
	-> 253
SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
	-> 1

-- Georgi Klassen이라는 사원의 입사 일자를 오늘로 변경하는 쿼리를 실행한다.
UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen;

UPDATE 문장이 실행되면 1건의 레코드가 업데이트될 것이다. 하지만 이 1건의 업데이트를 위해 253건의 레코드가 모두 잠긴다. 인덱스가 first_name에만 있고 last_name에는 없기 때문이다. UPDATE 문장을 위해 적절히 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 것이다. 만약 이 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE 작업을 하는데, 모든 레코드들이 잠기게 된다. MySQL의 InnoDB에서 인덱스 설계가 중요한 이유 또한 이것이다.

 

MySQL의 격리 수준

트랜잭션의 격리 수준(isolation level)이란 여러 트랜잭션이 동시에 처리될 떄 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다. 격리 수준은 크게 "READ UNCOMMITED", "READ COMMITED", "REPEATABLE READ", "SERIALIZABLE"의 4가지로 나뉜다.

데이터베이스의 격리 수준을 이야기하면 항상 함께 언급되는 세 가지 부정합의 문제점이 있다. 이 세가지 부정합의 문제는 격리 수준의 레벨에 따라 발생여부가 결정된다.

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITED	발생	발생	발생
READ COMMITED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

 

READ UNCOMMITED

READ UNCOMMITED 격리 수준에서는 아래 그림과 같이 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다. 

DIRTY READ

사용자 A는 emp_no가 500000이고 first_name이 "Lara"인 새로운 사원을 INSERT한다. 사용자 B가 변경된 내용을 커밋하기도 전에 사용자 B는 emp_no=500000인 사원을 검색하고 있다. 하지만 사용자 B는 사용자 A가 INSERT한 사원의 정보를 커밋되지 않은 상태에서도 조회할 수 있다. 그런데 문제는 사용자 A가 처리 도중 알 수 없는 문제가 발생하 INSERT된 내용을 롤백한다고 하더라도 여전히 사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 계속 처리할 것이라는 점이다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty Read)라 한다. 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 애플리케이션 개발자와 사용자를 상당히 혼란스럽게 만들 것이다. 또한 더티 리드를 유발하는 READ UNCOMMITED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다.

READ COMMITED

오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 이 레벨에서는 더티 리드같은 현상은 발생하지 않는다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다. 아래는 READ COMMITED 격리 수준에서 사용자 A가 변경한 내용이 사용자 B에게 어떻게 조회되는지 보여준다.

READ COMMITED

사용자 A는 emp_no=500000인 사원의 first_name을 "Lara"에서 "Toto"로 변경했는데, 이 떄 새로운 값인 "Toto"는 employees 테이블에 즉시 기록되고 이전 값인 "Lara"는 언두 영역으로 백업된다. 사용자 A가 커밋을 수행하기 전에 사용자 B가 emp_no=500000인 사원을 SELECT하면 조회된 결과의 first_name 컬럼의 값은 "Toto"가 아니라 "Lara"로 조회된다. 여기서 사용자 B의 SELECT 쿼리 결과는 employees 테이블이 아니라 언두 영역에 백업된 레코드에서 가져온 것이다. READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없기 때문이다. 최종적으로 사용자 A가 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 백업된 언두 레코드("Lara")가 아니라 새롭게 변경된 "Toto"라는 값을 참조할 수 있게 된다.

READ COMMITED 격리 수준에서도 "NON-REPEATABLE READ"("REPEATABLE READ"가 불가능하다)라는 부정합의 문제가 있다. 아래 그림은 왜 발생하고 어떤 문제를 만들어낼 수 있는지 보여준다.

NON-REPEATABLE READ

처음 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고 first_name이 "Toto"인 사용자를 검색했는데, 일치하는 결과가 없었다. 하지만 사용자 A가 사원번호가 500000인 사원의 이름을 "Toto"로 변경하고 커밋을 실행한 후, 사용자 B가 똑같은 SELECT 쿼리로 다시 조회하면 이번에는 결과가 1건이 조회된다. 이는 별다른 문제가 없어 보이지만, 사실 사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 "REPEATABLE READ" 정합성에 어긋나느 것이다.

이러한 부정합 현상은 일반적인 웹 프로그램에서는 크게 문제되지 않을 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 도 있다. 예를 들어, 다른 트랜잭션에서 입금과 출금 처리가 계속 진행될 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회한다고 가정해보자. 그런데 "REPEATABLE READ"가 보장되지 않기 때문에 총합을 계산하는 SELECT 쿼리는 실행될 때마다 다른 결과를 가져올 것이다. 중요한 것은 사용 중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지를 정확히 예측할 수 있어야 한다는 것이다. 그리고 당연히 이를 위해서는 각 트랜잭션의 격리 수준이 어떻게 작동하는지 알아야 한다.

가끔 사용자 중에서 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 없이 실행되는 SELECT 문장의 차이를 혼동하는 경우가 있다. READ COMMITED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없다. 하지만 REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다. 즉, START TRANSACTION(또는 BEGIN) 명령으로 트랜잭션을 시작한 상태에서 온종일 동일한 쿼리를 반복해서 실행해 봐도 동일한 결과만 보게 된다(아무리 다른 트랜잭션에서 그 데이터를 변경하고 COMMIT을 실행한다고 하더라도 말이다). 별로 중요하지 않은 차이처럼 보이지만 이런 문제로 데이터의 정합성이 깨지고 그로 인해 애플리케이션에 버그가 발생하면 찾아내기 쉽지 않다.

REPEATABLE READ

MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다. 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 "NON-REPEATABLE READ" 부정합이 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경한다. 이러한 변경 방식을 MVCC(Multi Version Concurrency Control)이라고 한다. REPEATABLE READ는 이 MVCC를 이용해 언 두 영역에 백업된 이전 데이터를 이용해 동일 트랜재겻ㄴ 내에서는 동일한 결과를 보여줄 수 있게 보장한다. 사실 READ COMMITTED도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여준다. REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 언두 영역의 백엄된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 어두 데이터가 필요한 것은 아니다. 더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.

아래 그림은 REPEATABLE READ 격리 수준이 작동하는 방식을 보여준다. 우선 이 시나리오가 실행되기 전에 employees 테이블은 번호가 6인 트랜잭션에 의해 INSERT됐다고 가정하자. 그래서 그림에서 employees 테이블의 초기 두 레코드는 트랜잭션 번호가 6으로 표현된 것이다. 그림의 시나리오에서는 사용자 A가 emp_no가 500000인 사원의 이름을 변경하는 과정에서 사용자 B가 emp_no=500000인 사원을 SELECT할 때 어떤 과정을 거쳐서 처리되는지 보여준다.

REPEATABLE READ

그림에서 사용자 A의 트랜잭션 번호는 12였으며 사용자 B의 트랜잭션 번호는 10이었다. 이 때 사용자 A는 사원의 이름을 "Toto"로 변경하고 커밋을 수행했다. 그런데 A 트랜잭션이 변경을 수행하고 커밋을 했지만, 사용자 B가 emp_no=500000인 사원을 A 트랜잭션의 변경 전후 각각 한 번씩 SELECT했는데 결과는 항상 "Lara"라는 값을 가져온다. 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하면서 10번이라는 트랜잭션 번호를 부여받았는데, 그때부터 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.

그림에서는 언두 영역에 백업된 데이터가 하나만 있는 것으로 표현했지만 사실 하나의 레코드에 대해 백업이 하나 이상 얼마든지 존재할 수 있다. 한 사용자가 BEGIN으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수도 있다. 이렇게 언두에 백업된 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.

REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 발생할 수 있다. 아래 그림에서는 사용자 A가 employees 테이블에 INSERT를 실행하는 도중에 사용자 B가 SELECT ... FOR UPDATE 쿼리로 employees 테이블을 조회했을 떄 어떤 결과를 가져오는지 보여준다

PHANTOM READ(PHANTOM ROWS)

사용자 B는 BEGIN 명령으로 트랜잭션을 시작한 후 SELECT를 수행한다. REPEATABLE READ에서 본 것처럼 두 번의 SELECT 쿼리는 똑같아야 한다. 하지만 위 그림에서 사용자 B가 실행하는 두 번의 SELECT ... FOR UPDATE 쿼리 결과는 서로 다르다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ(또는 PHANTOM ROWS)라고 한다. SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 그래서 SELECT ... FOR UPDATE나 SELECT ... LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수한 SELECT(INSERT ... SELECT ... 또는 CREATE TABLE ... AS SELECT ...가 아닌)은 아무런 레코드 잠금도 설정하지 않고 실행된다. InnoDB 매뉴얼에서 자주 나타나는 "Non-locking consistent read(잠금이 필요 없는 일관된 읽기)"라는 말이 이를 의미하는 것이다. 하지만 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다. SERIALIZABLE 격리 수준에서는 일반적인 DBMS에서 일어나는 "PHANTOM READ"라는 문제가 발생하지 않는다. 하지만 InnoDB 스토리지 엔진에서느 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 "PHANTOM READ"가 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요성은 없어 보인다.