팬텀 리드(Phantom read)에서 언두 영역에 접근하지 않는 이유가 무엇인가요?

Question

REPEATABLE READ 격리수준에서 PHANTOM READ 부정합 문제에 대해서 공부하고 있습니다.REPEATABLE READ 격리수준에서 NON-REPEATABLE READ 문제를 트랜잭션 번호로 해결한다는 것을 이해하였습니다.그리고 PHANTOM READ 부정합 문제는 아래와 같은 이유로 언두 영역의 변경 전 데이터를 가져오는 것이 불가능하여 PHANTOM READ 문제가 발생한다고 되어있습니다. (Reay MySQL 참고)'SELECT ... FOR UPDATE' 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.그래서 SELECT ... FOR UPDATE나 SELECT ... LOCK IN SHARE MODE로 조회하는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.여기서 궁금한 점이 SELECT ... FOR UPDATE를 할 때에도 NON-REPEATABLE READ 문제를 해결한 것처럼 트랜잭션 번호를 사용하면 될거 같은데, 왜 현재 레코드에서 데이터를 읽어오는 것인가요?SELECT ... FOR UPDATE의 경우 쓰기 잠금을 언두 레코드에 걸 수 없다고 하더라도, SELECT ... FOR UPDATE 로 조회할 때에는 트랜잭션 번호를 이용해서 조회해도 되지 않을까요?제가 아직 락에 대해서 개념이 부족한 것일까요?...&#x1f625;

엡실론 · Accepted Answer

select for update는 말그대로 업데이트를 하기 위해서 읽어 오는 거죠. 보통 뭔가를 읽어서 일부를 고치고 업데이트, 혹은 뭔가를 읽어서 거기에 더하기 얼마를 해서 업데이트 하는 상황입니다.은행 이체 같은 경우죠. 보내는 쪽의 현재 잔고를 읽어서 액수만큼 차감하고 저장. 여기서 "현재"라는게 중요합니다. 업데이트에 사용해야 하니 가장 최신의 데이터를 읽어야 합니다. 트랜잭션 id를 사용한다는 건 "과거"의 데이터를 읽는 것입니다.

Devvy · Answer

사실 MySQL의 repeatable read 단계에서 위 상황의 phantom read 현상은 발생하지 않습니다(SQL 표준은 특정 isolation level이라면 최소한 SQL 표준에서 정의하는 비정상적인 현상을 방지해야한다고 정의합니다. 따라서 데이터베이스별로 isolation level이 같더라도 방지할 수 있는 비정상적인 현상이 달라집니다.)그럼 MySQL의 repeatable read에서 위 상황이 정말 발생하지 않는지 살펴보겠습니다. room 테이블을 사용해 테스트해보겠습니다. (참고로 미리 더미데이터를 생성했습니다) create table room ( id bigint auto_increment primary key, name varchar(50) not null );그리고 각각 커넥션을 맺어 트랜잭션을 실행하겠습니다.업데이트 대상에 락을 획득하기 위해 SELECT ... FOR UPDATE 구문을 실행합니다.1번의 조회 쿼리에서 락을 획득시 사용한 조건에 해당되는(id > 1001020) 데이터(id &#61; 1001028)를 room 테이블에 insert 합니다.위 시나리오를 직접 실행해보면 2번 쿼리는 락을 획득하지 못해 timeout이 발생해서 실패하게 되는데요, 1번 쿼리에서 어떤 lock이 잡혀서 실패하는지 확인해보겠습니다. 아래 쿼리를 사용해서 어떤 테이블에 어떤 유형의 lock이 잡혀있는지 확인할 수 있습니다.select ENGINE_TRANSACTION_ID, OBJECT_NAME as table_name, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA from performance_schema.data_locks;supremum pseudo-record는 MySQL이 next key lock을 잡을 때 사용하는, 인덱스에 저장된 값 중 가장 큰 값보다도 더 큰 값(실제로 존재하지 않기에 pseudo-record라고 하는 것 같습니다)을 의미합니다. 즉 SELECT ... FOR UPDATE로 인해 id가 (1001020 ~ supremum pseudo-record)인 레코드에 대해 X lock(exclusive lock)을 획득합니다.자 그렇다면 2번 쿼리가 실행될 수 없는 이유를 아시겠나요? 2번 쿼리에서 insert하고자 하는 데이터의 id는 1001028이지만 해당 id는 1번 쿼리가 획득한 lock(1001020 ~ supremum pseudo-record)의 범위에 포함되므로 2번 쿼리는 실행될 수 없습니다. 즉 MySQL의 repeatable read는 next key lock을 활용해 질문주신 위 상황의 phantom read도 방지할 수 있습니다.SELECT ... FOR UPDATE의 경우 쓰기 잠금을 언두 레코드에 걸 수 없다고 하더라도, SELECT ... FOR UPDATE 로 조회할 때에는 트랜잭션 번호를 이용해서 조회해도 되지 않을까요?마지막으로 작성자분의 위 질문에 대해 개인적인 생각을 말씀드리겠습니다. SELECT ... FOR UPDATE 구문은 MySQL이 next key lock 등을 활용해서 효과적으로 잠금을 획득하는 방법이 있기 때문에 트랜잭션 번호를 이용할 필요가 없어보입니다.

팬텀 리드(Phantom read)에서 언두 영역에 접근하지 않는 이유가 무엇인가요?

2개의 답변

기술 목록