트랜잭션
하나의 논리적인 작업 단위
ACID
트랜잭션이 안전하게 수행된다는 것을 보장하기 위한 성질
Atomicity (원자성)
- 트랜잭션 내에서 모든 연산은 전부 실행되거나, 실행되지 않아야 한다.
- All or Nothing
Consistency (일관성)
- 트랜잭션을 통해 데이터가 변해도, 데이터베이스의 일관성을 유지해야 한다.
- 제약조건을 위반하면 안된다. (ex: not null, unique)
Isolation (격리성)
- 동시에 여러 트랜잭션이 실행될 경우, 서로 영향을 미치면 안된다.
- 격리성은 격리 수준을 통해 설정할 수 있다.
Durability (지속성)
- 트랜잭션이 성공적으로 수행되면, 그 결과는 반영되어야 한다.
- 시스템에 문제가 발생해도 로그 등을 사용하여 성공한 트랜잭션의 내용을 복구할 수 있어야 한다.
트랜잭션의 격리 수준
트랜잭션이 동시에 변경을 수행하고 쿼리를 수행할 때 성능과 안정성, 일관성 및 결과 재현성 간의 균형을 미세 조정하는 설정
Read Uncommitted
가장 낮은 격리 수준
다른 트랜잭션에서 변경하고 커밋하지 않은 데이터를 읽을 수 있다.
Dirty Read, Non-Repeatable Read, Phantom Read가 발생한다.
Dirty Read
한 트랜잭션에서 커밋하지 않은 데이터를 다른 트랜잭션이 읽을 수 있는 현상
트랜잭션 A가 데이터를 수정하고, 커밋하지 않은 상태에서 트랜잭션 B가 해당 내역을 조회할 수 있다.
이때, 트랜잭션 A가 롤백하면, 해당 데이터는 반영되지 않았지만, 트랜잭션 B에서는 조회가 되었기 때문에 정합성에 문제가 발생한다.
Read Committed
다른 트랜잭션에서 커밋한 데이터만 읽을 수 있다.
Oracle의 경우 기본 격리 수준
Non-Repeatable Read, Phantom Read가 발생한다.
Non-Repeatable Read
한 트랜잭션 내에서 같은 데이터를 여러 번 읽을 때, 중간에 다른 트랜잭션에 의해 값이 변경되어 다른 값을 읽는 현상
트랜잭션 A가 값을 변경하고, 트랜잭션 B가 값을 조회했을 때, 트랜잭션 A는 아직 커밋을 하지 않았기 때문에 변경 사항이 조회되지 않는다.
하지만 트랜잭션 A가 커밋을 하면, 트랜잭션 B에서 변경 사항이 조회된다.
Repeatable Read
트랜잭션 내에서 같은 데이터를 여러 번 읽을 때 항상 동일한 결과를 반환한다.
Phantom Read가 발생한다.
하지만 MySQL에서 일반적인 상황에서는 Phantom Read가 발생하지는 않는다.
자세한 상황은 밑에서 설명하겠다.
Phantom Read
한 트랜잭션 내에서 특정 조건의 데이터를 두 번 이상 읽을 때, 첫 번째 조회와 두 번째 조회 사이에 다른 트랜잭션에 의해 데이터가 추가, 삭제되어 결과가 달라지는 현상
트랜잭션 A가 값을 조회했을 때, 한 건의 데이터만 조회된다. 이때 트랜잭션 B가 새로운 값을 추가하고 커밋한 뒤, 트랜잭션 A가 다시 값을 조회했을 때 트랜잭션 B에서 추가된 값이 조회된다.
Serializable
가장 높은 격리 수준
트랜잭션이 순차적으로 실행된다. 따라서 동시성이 떨어질 수 있지만, 데이터의 일관성은 최대한 보장된다.
이 수준에서는 모든 문제들이 해결된다.
MySQL 8.0을 통한 실험
Dirty Read
발생
격리 수준을 Read Uncommitted으로 설정한다.
2번 쿼리를 실행하면 다음과 같이 데이터가 조회된다.
그리고 3번 쿼리를 실행하고 다시 2번 쿼리를 실행하면 다음과 같이 커밋되지 않은 데이터가 조회된다.
해결
격리 수준을 Read Committed으로 설정한다.
Read Comitted에서는 Read Uncommited에서 발생하는 Dirty Read가 발생하지 않는다.
Non-Repeatable Read
발생
다음과 같이 격리 수준을 Read Committed로 설정한다.
3번 쿼리를 커밋하고, 2번 쿼리를 실행하면 변경된 데이터가 조회된다.
해결
격리 수준을 Repeatable Read로 설정한다.
Repeatable Read에서는 Read Commited에서 발생하는 Non-Repeatable Read가 발생하지 않는다.
다음과 같이 다른 트랜잭션에서 변경 후 커밋을 해도, 기존의 데이터가 조회된다.
주의
이건 당연한 결과겠지만, 트랜잭션이 시작하고, 다른 트랜잭션에서 데이터를 변경 후 커밋한다.
이때 기존에 실행했던 트랜잭션에서 데이터를 조회하면 다른 트랜잭션에서 변경한 데이터가 조회된다.
(1-4-2-3-5 순서)
Phantom Read
발생
MySQL 8.0 기준 InnoDB 스토리지 엔진을 사용한다면 일반적으로 Repeatable Read 격리 수준에서는 발생하지 않는다.
Real MySQL 8.0 Part.1 176p 에서는 다음과 같이 설명한다.
SQL-92, SQL-99 표준에 따르면 REPEATABLE READ 격리 수준에서는 PHANTOM READ가 발생할 수 있지만, InnoDB에서는 독특한 특성 때문에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않는다.
여기서 말하는 InnoDB의 독특한 특성이란, 갭 락 그리고 넥스트 키 락을 말한다.
하지만 SELECT ... FOR UPDATE, SELECT ... FOR SHARE 쿼리에서는 Phantom Read가 발생한다.
우선 일반적인 상황이다.
다음과 같이 격리 수준을 Repeatable Read로 설정한다.
그리고 다음과 같이 다른 트랜잭션에서 2번 쿼리를 실행하여 데이터를 조회한 뒤, 다른 트랜잭션에서 3번 쿼리를 실행 후 커밋을 해도, 다른 트랜잭션에서 다시 조회를 해도 기존의 데이터가 조회된다.
따라서 일반적인 상황에서는 Phantom Read가 발생하지 않는다.
하지만 특수한 상황에서는 Phantom Read가 발생한다.
다음과 같이 name이 "fiddich"인 데이터가 있다.
이때 3번 쿼리를 실행해도 name이 "livet"인 데이터가 없기 때문에 값이 반영되지 않는다.
따라서 반영된 row가 하나도 없다.
하지만 다음과 같이 다른 트랜잭션에서 데이터를 추가한 뒤 해당 쿼리를 실행한다면?
다음과 같이 5번 쿼리에서 1 row affected 메시지가 출력된다.
즉, SELECT 쿼리로 조회되지 않았던 쿼리가 반영이 된 것이다.
그리고 커밋을 하지 않고 이전에 실행했던 SELECT 쿼리를 다시 실행해보면 다음과 같이 Phantom Read가 발생한다.
왜 이런 일이 발생할까?
우선 InnoDB에서 Phantom Read를 막는 방법은 언두 로그, 트랜잭션 번호를 사용하여 Phantom Read를 방지한다.
A 트랜잭션을 시작하고, 데이터를 조회한다. A 트랜잭션 번호는 3이다.
B 트랜잭션을 시작하고, 데이터를 추가하고 커밋한다. B 트랜잭션 번호는 4이다.
그리고 다시 A 트랜잭션이 데이터를 조회할 때 언두 로그를 조회하는데, 언두 로그에 자신의 트랜잭션 번호보다 큰 트랜잭션 번호는 조회하지 않는다. (B 트랜잭션이 추가한 데이터는 트랜잭션 번호가 4번)
따라서 Phantom Read가 발생하지 않는다.
하지만 UPDATE 쿼리를 실행했을 때, Phantom Read가 발생하는 이유는 다음과 같다.
UPDATE쿼리는 쓰기 락이 걸린다.- 이때 언두 로그에 쓰기 락을 걸 수 없기 때문에 테이블에 쓰기 락을 걸고, 데이터를 변경한다.
- 그리고
UPDATE쿼리가 반영되며, 언두 로그에 자신의 트랜잭션 번호가 갱신된 데이터가 생긴다. - 그리고
SELECT쿼리를 실행하면 언두 로그에 있는 데이터를 조회한다. - 언두 로그에 있던 데이터의 트랜잭션 번호는 자신의 트랜잭션 번호이므로,
Phantom Read가 발생한다.
결론
트랜잭션과 ACID 그리고 MySQL InnoDB의 격리 수준에 따라 발생하는 정합성에 관련된 내용을 알아봤다.
기본적으로 MySQL 8.0 이상의 버전을 사용한다면 Repeatable Read 격리 수준에서 Phantom Read는 발생하지 않는다.
하지만 특수한 상황(FOR UPDATE, FOR SHARE)에서는 Phantom Read가 발생하므로, MySQL InnoDB를 사용하면 Phantom Read가 발생하지 않는다는 것은 아니다.
따라서 이러한 정합성 문제가 발생할 수 있는 상황을 미리 에측하고 대비할 수 있어야 한다.
좋은 글 감사합니다!