격리 수준이 READ_COMMITTED인 MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블의 데이터 변경을 어떻게 처리하는지 확인해보자. READ_COMMITED는 COMMIT된 데이터만 다른 트랜잭션에서 확인 가능하다.
START TRANSACTION;
CREATE TABLE member (
m_id INT NOT NULL,
m_name VARCHAR(20) NOT NULL,
m_area VARCHAR(100) NOT NULL,
PRIMARY KEY (m_id),
INDEX ix_area (m_area)
);
INSERT INTO member (m_id, m_name, m_area) VALUES (1, '홍길동', '서울');
COMMIT;
INSERT 문이 실행되면 데이터베이스의 메모리(버퍼풀)와 디스크 각각에 데이터가 저장된다.
그리고, 다음과 같이 UPDATE 문을 실행해 데이터를 갱신한다고 하자.
Transaction A> START TRANSACTION;
Transaction A> UPDATE member SET m_area="경기" WHERE id=1;
UPDATE 문이 실행되면 커밋 여부와 관계없이 일단 InnoDB 버퍼풀의 데이터는 갱신되고 언두 로그에는 변경 전의 데이터가 복사된다.
디스크에도 수정한 값으로 갱신돼야 하는데, 이는 백그라운드 쓰레드에 의해서 처리가 되므로 시점에 따라 아직 갱신되지 않았을 수 있다. (MySQL은 ACID를 보장하므로 일반적으로 버퍼풀과 데이터 파일은 동일한 상태라고 가정해도 된다.)
MVCC를 통해 동일한 레코드가 여러 버전으로 관리되고 있고, 이를 통해 커밋 이전의 데이터 읽기, 데이터 롤백 등이 가능한 것이다. 그렇다면, 트랜잭션이 COMMIT이나 ROLLBACK되지 않은 상태에서 다른 트랜잭션이 해당 레코드를 조회하면 어떻게 될까?
Transaction B> SELECT * FROM member WHERE id=1;
+------+-----------+--------+
| m_id | m_name | m_area |
+------+-----------+--------+
| 12 | 홍길동 | 서울 |
+------+-----------+--------+
현재는 READ_COMMITTED 격리 수준이기 때문에 언두 영역에 있는 데이터를 읽어 m_area가 '서울'로 반환된다.
해당 결과는 격리 수준(Isolation level)에 따라 달라진다. 격리 수준이 READ_UNCOMMITED인 경우에는 InnoDB 버퍼 풀의 변경된 데이터를 읽어서 반환한다. 격리 수준이 READ_COMMITED, REPEATABLE_READ, SERIALIZABLE인 경우에는 아직 커밋되지 않았기 때문에 언두 영역의 변경 전 데이터를 반환한다.
만약 COMMIT을 하면 InnoDB는 더 이상의 변경 없이 지금의 상태를 영구적으로 만든다. 커밋이 된다고 언두 영역의 데이터가 항상 바로 삭제되지는 않는다. 언두 영역을 필요로 하는 트랜잭션이 더는 없을 때 비로소 삭제된다. ROLLBACK을 하면 언두 영역에 백업된 데이터를 버퍼 풀로 다시 복구하고, 언두 영역의 내용은 삭제해버린다.