MySQL MVCC(Multi-Version Concurrency Control)

InnoDB는 다중 버전 스토리지 엔진으로, 동시성과 롤백을 포함한 트랜잭션 기능을 지원하기 위해 변경된 행의 이전 버전을 저장한다. 이 정보는 롤백 세그먼트라는 데이터 구조의 Undo 테이블스페이스에 저장되며, 이를 통해 트랜잭션 롤백과 일관성 있는 읽기를 제공한다.

레코드에 추가되는 필드

InnoDB는 데이터베이스에 저장된 각 행에 다음과 같은 필드를 추가한다.

• 6바이트 DB_TRX_ID: 마지막으로 해당 행을 삽입하거나 업데이트한 트랜잭션 ID를 저장한다. 삭제 역시 업데이트로 처리되며, 행의 특수 비트에 삭제 여부가 표시된다.

  • 해당 트랜잭션 ID가 활성 트랜잭션 목록에 존재한다면 커밋되지 않음으로 판단한다.

• 7바이트 DB_ROLL_PTR: 롤 포인터로, 롤백 세그먼트에 기록된 Undo 로그 레코드를 가리킨다. 이 포인터는 행이 업데이트되기 전의 상태를 복원하는 데 필요한 정보를 제공한다.

• 6바이트 DB_ROW_ID: 새 행이 삽입될 때 증가하는 단조로운 ID 값이다. 클러스터형 인덱스가 자동으로 생성되는 경우, 이 값이 인덱스에 포함되며 그렇지 않으면 인덱스에 나타나지 않는다.

Undo 로그와 롤백 세그먼트

• 삽입 Undo 로그는 트랜잭션이 롤백될 때만 필요하며, 트랜잭션이 커밋되면 즉시 삭제될 수 있다.

• 업데이트 Undo 로그는 Repeatable Read 격리 레벨에서도 사용되며, 스냅샷 트랜잭션이 없어야만 삭제할 수 있다. 트랜잭션을 정기적으로 커밋하지 않으면, 업데이트 Undo 로그가 삭제되지 않아 롤백 세그먼트가 커지게 된다. 이는 실행 취소 테이블스페이스를 채울 수 있으므로, ß트랜잭션을 자주 커밋하는 것이 중요하다.

MVCC와 행 삭제

다중 버전 관리 체계에서, InnoDB는 SQL 명령으로 삭제된 행을 즉시 물리적으로 제거하지 않는다. 대신, 해당 행을 삭제로 표시하는 업데이트 Undo 로그가 작성되며, 이 로그가 삭제될 때만 행과 인덱스 레코드가 실제로 제거된다. 이 작업을 **퍼지(Purge) **라고 부르며, 일반적으로 삭제를 수행한 SQL 명령과 동일한 시간에 처리된다.

보조 인덱스 처리

InnoDB의 MVCC는 Secondary Index와 Primary Index를 다르게 처리한다. Primary Index의 레코드는 제자리에서 업데이트되며, 레코드에 자동으로 추가된 시스템 열(메타데이터 필드)을 통해 Undo 로그 항목을 가리킨다. 반면, Secondary Index는 제자리에서 업데이트되지 않고, 삭제 표시 후 새로운 레코드가 삽입된다.

보조 인덱스의 레코드가 삭제 표시되거나, 페이지가 업데이트될 경우 InnoDB는 클러스터형 인덱스에서 레코드를 조회하고, 필요한 경우 Undo 로그를 참조해 이전 버전을 가져온다.

성능 문제와 퍼지 스레드

작은 배치로 행을 삽입하고 삭제할 경우, 퍼지 스레드가 뒤처질 수 있다. 이로 인해 "죽은" 행이 쌓여 테이블 크기가 커지고 성능 저하가 발생할 수 있다. 이런 경우 innodb_max_purge_lag 변수를 조정해 퍼지 스레드에 더 많은 리소스를 할당하는 것이 필요하다.