트랜잭션은 데이터 정합성을 보장하기 위한 기능으로 논리적인 작업 셋을 모두 완벽하게 적용하거나 원상태로 복구하기 위한 기능입니다.
개발 과정에서 2개 이상의 쿼리가 실행되어야 하는 비즈니스 로직에 트랜잭션 기능을 사용하여 원자성을 보장할 수 있습니다.
SQL 표준에서 정의하는 4단계의 트랜잭션 격리 수준도 같이 테스트해보았습니다.
이 글의 목적은 실제 격리 수준별 동작을 테스트해 보는 목적으로 작성되었습니다.
DB 환경 & 출처
mysql
- version. 8.0.35
- storage engine. innoDB
- autocommit disabled
postgres
- version 14.10
정보 출처 : mysql, postgres 공식문서 및 Real MySQL 8.0
트랜잭션 격리 수준(transaction isolation level)
트랜잭션 격리 수준이란 하나의 트랜잭션 내 또는 여러 트랜잭션 간 작업 내용 공유 수준을 뜻합니다.
어느 테이블의 2개의 커넥션에서 별도의 트랜잭션을 생성했다고 가정해 봅시다.
트랜잭션 1에서 이 테이블의 레코드를 수정했을 때 이 트랜잭션 1이 커밋(또는 롤백) 되기 전에 트랜잭션 2가 이 테이블의 수정된 내용을 읽을 수 있는지를 결정하는 수준이라고 할 수 있겠습니다.
격리 수준은 총 4단계로 아래와 같으며, 격리(고립) 정도가 아래로 갈수록 낮습니다.
저는 오늘 이 4단계 수준에 대해 정의와 더불어 각 수준별로 알려진 현상들에 대해 경험해보려고 합니다.
- SERIALIZABLE
- REPEATABLE_READ
- READ_COMMITTED
- READ_COMMITTED
1. SERIALIZABLE
가장 단순하면서도 엄격한 관리 수준으로 특수한 비즈니스 요구사항에서 사용될 수 있습니다.
mysql innoDB에서는 모든 select 문에... for share를 적용합니다.(공유락을 획득) 그만큼 성능도 떨어지는 격리 수준입니다.
This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled.
위 같은 이유로 아래처럼 동작합니다.
A. 선행 트랜잭션에서 어떤 행에 공유락을 걸었다면, 후행 트랜잭션에서 읽기밖에 못합니다.
B. select ... for update로 배타락을 걸었다면 후행 트랜잭션에서 조회조차 불가능하게 됩니다.
물론 innoDB는 검색 조건에 사용된 인덱스 락이므로 조건에 해당하지 않는 레코드를 조회, 업데이트하는 것은 가능합니다.
C. 쓰기 작업을 위해 배타락을 획득하면, 후행 트랜잭션에서는 조회조차 할 수 없습니다.
D. mysql 8.0.22 이상부터는 순수한 select 구문은 락과 관계없이 조회가 가능합니다.
2. REPEATABLE_READ
mysql innoDB의 디폴트 격리 수준입니다. MVCC(*) 방식을 활용하여 작동합니다.
(*) MVCC(Multi Version Concurrency Control) - 잠금을 사용하지 않고 일관된 읽기를 제공하기 위한 목적으로, 트랜잭션 내에서 데이터가 수정되면 그 수정내역을 언두(Undo) 공간 등에 보관하여 격리 수준에 따라 수정내역에서 참조하여 일관된 데이터를 읽을 수 있게 합니다.
아래는 선행 트랜잭션이 이름을 변경하고 커밋까지 하였음에도, 후행 트랜잭션에서 여전히 변경 전 이름을 읽는 상황입니다.
2-1. phantom read(팬텀 리드)
SQL 표준에 의하면 REPEATABLE_READ 수준부터는 팬텀 리드 현상이 발생할 수 있습니다. 수정내역을 기록하는 undo 영역에는 락을 걸 수 없어서 select for ...update 과 같이 쓰기 잠금을 획득해야 하는 쿼리는 최신화된 테이블로부터 직접 읽기 때문에 발생합니다.
어떤 트랜잭션에서 2번의 조회 사이에 다른 트랜잭션에서 레코드 삽입 및 커밋을 하게 되면, 처음 조회와 다르게 나중 조회 결과 셋에는 다른 트랜잭션에서의 레코드가 포함되어 있는 현상입니다. 그러나 제가 테스트해 본 MySQL innoDB와 postgreSQL의 경우 각자의 방식으로 이 현상을 방지하여 REPEATABLE_READ 수준에서 팬텀리드를 방지하였습니다.
아래는 MySQL MyISAM 엔진에서 발생하는 팬텀리드 현상입니다.
#DDL
create table member_MyISAM
(
member_id bigint unsigned auto_increment
primary key,
name varchar(255) not null,
age int not null,
team_id int not null,
constraint member_id
unique (member_id)
) engine=MyISAM;
3. READ_COMMITED
오라클, postgresql의 기본 격리 수준이라고 할 수 있습니다.
이름과 같이 커밋된 결과는 트랜잭션 중간에도 읽을 수 있기 때문에 여러 번의 읽기에서 결과 셋이 다르게 보일 수 있습니다.
이를 NON-REPEATEABLE READ 현상이라고 합니다. 아래 그림과 같이 connection2의 트랜잭션 커밋을 한 시점부터 읽을 수 있게 됩니다. 이 격리 수준은 [2.REPEATABLE_READ] 보다 더 느슨하므로 팬텀 리드 현상 역시 발생합니다.
4. READ_UNCOMMITED
다른 트랜잭션의 커밋되지 않은 내용까지 모두 반영하는 격리 수준입니다. 사실상 쓰이지 않는 격리 수준으로 다른 격리 수준과 비교용으로 접해보았습니다. 팬텀리드, NON_REPEATABLE READ 현상과 더불어 커밋되지 않은 결과 셋이 보이다 안 보이다 하는 DIRTY_READ 현상까지 발생할 수 있습니다. 아래는 그 예시입니다. connection 2에서 insert 이후 롤백을 하더라도, 그 사이에 읽기를 한 connection1 은 2개 레코드에 대하여 비즈니스 로직을 수행하게 됩니다. 문제가 발생할 여지가 많습니다.
회고.
어렴풋이 그렇다고 알고 있는 내용도 실제로 경험을 하게 되면 깨닫게 되는 점이 많은 것 같습니다. RDBMS는 쿼리튜닝과 실행계획을 공부해 보면서도 느꼈지만, 데이터베이스에서 최대한 빠르게 원하는 데이터를 가져오기 위해 RDBMS 기종별, 엔진별, 버전별 최적화가 각각 다른 것 같아 특히 더 그렇다고 생각합니다. 이번 격리 수준 정리는 정의 공부하는 것을 넘어 실제로 테스트를 해보고 그렇다는 것을 알게 된 좋은 경험이었습니다.
