Aurora MySQL 격리 수준

Aurora MySQL 클러스터의 DB 인스턴스가 격리의 데이터베이스 속성을 구현하는 방식을 알아봅니다. 이 주제에서는 Aurora MySQL 기본 동작이 엄격한 일관성과 높은 성능 사이에서 어떻게 균형을 이루는지 설명합니다. 이 정보를 사용하여 워크로드의 특성에 따라 언제 기본 설정을 변경할지 결정할 수 있습니다.

라이터 인스턴스에 사용 가능한 격리 수준

Aurora MySQL DB 클러스터의 기본 인스턴스에서 격리 수준 REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED 및 SERIALIZABLE을 사용할 수 있습니다. 이러한 격리 수준은 RDS for MySQL과 마찬가지로 Aurora MySQL에서 동일하게 작동합니다.

리더 인스턴스의 REPEATABLE READ 격리 수준

기본적으로, 읽기 전용 Aurora 복제본으로 구성된 Aurora MySQL DB 인스턴스에서는 항상 REPEATABLE READ 격리 수준을 사용합니다. 이 DB 인스턴스에서는 SET TRANSACTION ISOLATION LEVEL 문은 모두 무시하고 계속해서 REPEATABLE READ 격리 수준을 사용합니다.

DB 파라미터 또는 DB 클러스터 파라미터를 사용하여 리더 DB 인스턴스의 격리 수준을 설정할 수 없습니다.

리더 인스턴스의 READ COMMITTED 격리 수준

애플리케이션에 기본 인스턴스의 쓰기 집약적인 워크로드와 Aurora 복제본의 장기 실행 쿼리가 포함된 경우 상당한 제거 지연 시간이 발생할 수 있습니다. 제거 지연 시간은 내부 가비지 수집이 장기 실행 쿼리로 차단되는 경우 발생합니다. 사용자가 겪는 증상은 history list length 명령의 출력에서 SHOW ENGINE INNODB STATUS의 값이 커지는 것입니다. CloudWatch의 RollbackSegmentHistoryListLength 지표를 사용하여 이 값을 모니터링할 수 있습니다. 상당한 제거 지연은 보조 인덱스의 효율성과 전반적인 쿼리 성능을 저하하며 스토리지 공간 낭비로 이어질 수 있습니다.

이러한 문제가 발생하면 Aurora MySQL 세션 수준 구성 설정인 aurora_read_replica_read_committed를 Aurora 복제본에서 READ COMMITTED 격리 수준을 사용하도록 설정할 수 있습니다. 이 설정을 적용하면 장기 실행 쿼리를 테이블을 수정하는 트랜잭션과 동시에 수행하여 발생할 수 있는 속도 저하 및 공간 낭비를 줄이는 데 도움이 됩니다.

이 설정을 사용하기 전에 READ COMMITTED 격리의 특정 Aurora MySQL 동작을 이해하는 것이 좋습니다. Aurora 복제본 READ COMMITTED 동작은 ANSI SQL 표준을 준수합니다. 그러나 격리는 사용자에게 익숙한 일반적인 MySQL READ COMMITTED 동작보다 덜 엄격합니다. 따라서 Aurora MySQL 기본 인스턴스 또는 RDS for MySQL의 READ COMMITTED에서 실행한 동일 쿼리에 대한 결과와는 다른 쿼리 결과가 Aurora MySQL 읽기 전용 복제본의 READ COMMITTED에서 나올 수 있습니다. 매우 큰 데이터베이스를 스캔하는 종합 보고서와 같은 사례에서는 aurora_read_replica_read_committed 설정을 사용하는 것을 고려해 볼 수 있습니다. 반면에, 정밀도 및 반복성이 중요하고 결과 집합이 작은 짧은 쿼리에는 이 설정을 사용하지 않는 것이 좋습니다.

쓰기 전달 기능을 사용하는 Aurora 글로벌 데이터베이스의 보조 클러스터 내에 있는 세션에는 READ COMMITTED 격리 수준을 사용할 수 없습니다. 쓰기 전달에 대한 자세한 내용은 Amazon Aurora 글로벌 데이터베이스에서 쓰기 전달 사용 단원을 참조하십시오.

리더에 대해 READ COMMITTED 사용

Aurora 복제본에 대해 READ COMMITTED 격리 수준을 사용하려면 aurora_read_replica_read_committed 구성 설정을 ON으로 설정합니다. 특정 Aurora 복제본에 연결된 상태에서 이 설정을 세션 수준에서 사용합니다. 이렇게 하려면 다음 SQL 명령을 실행합니다.

set session aurora_read_replica_read_committed = ON;
set session transaction isolation level read committed;

이 구성 설정을 일시적으로 사용하여 대화형 일회성 쿼리를 수행할 수 있습니다. 또한 다른 애플리케이션에 대한 기본 설정을 변경하지 않은 상태에서 READ COMMITTED 격리 수준에서 이점을 얻는 보고 또는 데이터 분석 애플리케이션을 실행할 수 있습니다.

aurora_read_replica_read_committed 설정이 활성화된 경우 SET TRANSACTION ISOLATION LEVEL 명령을 사용하여 적절한 트랜잭션에 격리 수준을 지정하세요.

set transaction isolation level read committed;

Aurora 복제본에서 READ COMMITTED 동작의 차이

aurora_read_replica_read_committed 설정을 통해 Aurora 복제본에 대해 READ COMMITTED 격리 수준을 사용할 수 있게 할 수 있습니다. 이때 일관성 동작은 장기 실행 트랜잭션에 최적화됩니다. Aurora 복제본의 READ COMMITTED 격리 수준은 Aurora 기본 인스턴스보다 격리가 덜 엄격합니다. 이러한 이유로 쿼리에서 일관성 없는 특정 유형의 결과가 반환될 가능성을 수용할 수 있음을 사용자가 알고 있는 Aurora 복제본에서만 이 설정을 활성화해야 합니다.

aurora_read_replica_read_committed 설정이 켜져 있으면 쿼리 시 특정한 종류의 읽기 이상이 발생할 수 있습니다. 두 가지 종류의 이상이 애플리케이션 코드를 이해하고 애플리케이션 코드에서 처리하는 데 특히 중요합니다. 반복 불가능한 읽기는 쿼리가 실행 중일 때 다른 트랜잭션이 커밋되면 발생합니다. 장기 실행 쿼리에는 종료 시 반환되는 것과 다른 데이터가 쿼리 시작 시점에 반환될 수 있습니다. 가상 읽기는 쿼리가 실행 중일 때 다른 트랜잭션으로 인해 기존 행이 재편되고 쿼리에서 하나 이상의 행을 두 번 읽으면 발생합니다.

가상 읽기의 결과로 쿼리에서 행 수의 일관성이 결여될 수 있습니다. 또한 반복 불가능한 읽기로 인해 쿼리에서 불완전하거나 일관성 없는 결과를 반환할 수 있습니다. 예를 들어 INSERT 또는 DELETE와 같은 SQL 문에서 동시에 수정하는 테이블을 조인 연산자가 참조한다고 가정합시다. 이 경우 조인 쿼리에서는 테이블 하나에서 행 하나를 읽지만 다른 테이블에서 이에 상응하는 행은 읽지 않을 수 있습니다.

ANSI SQL 표준에서는 READ COMMITTED 격리 수준에 대해 이 두 가지 동작을 모두 허용합니다. 그러나 그러한 동작은 READ COMMITTED의 일반적인 MySQL 구현과 다릅니다. 따라서 aurora_read_replica_read_committed 설정을 활성화하기 전에 모든 기존 SQL 코드를 확인하여 이 코드가 더 느슨한 일관성 모델에서 예상대로 작동하는지 확인하십시오.

이 설정이 활성화되어 있는 상태에서 행 수 및 기타 결과는 READ COMMITTED 격리 수준에서 강한 일관성을 보이지 않을 수 있습니다. 따라서 대량 데이터를 집계하고 절대적인 정밀성이 필요하지 않은 분석 쿼리를 실행 중일 때만 일반적으로 이 설정을 활성화합니다. 이러한 종류의 장기 실행 쿼리와 함께 쓰기 집약적인 워크로드가 없는 경우에는 aurora_read_replica_read_committed 설정이 필요 없을 수 있습니다. 장기 실행 쿼리와 쓰기 집약적인 워크로드의 조합이 없으면 기록 목록 길이와 관련된 문제를 겪지 않을 가능성이 높습니다.

예 Aurora 복제본에서 READ COMMITTED에 대해 격리 동작을 보여주는 쿼리

다음 예시는 트랜잭션에서 연결된 테이블을 동시에 수정하는 경우 Aurora 복제본의 READ COMMITTED 쿼리에서 반복 불가능한 결과를 반환하는 방식을 보여줍니다. 쿼리 시작 전에 BIG_TABLE 테이블에는 1백만 개의 행이 포함되어 있습니다. 다른 데이터 조작 언어(DML) 문은 실행 중에 행을 추가, 제거 또는 변경합니다.

READ COMMITTED 격리 수준에서 Aurora 기본 인스턴스에 대한 쿼리를 통해 예측 가능한 결과가 산출됩니다. 그러나 모든 장기 실행 쿼리의 수명에 대해 일관성 있는 읽기 뷰를 유지하는 오버헤드로 인해 나중에 높은 가비지 수집 비용이 발생할 수 있습니다.

READ COMMITTED 격리 수준에서 Aurora 복제본에 대한 쿼리는 이러한 가비지 수집 오버헤드를 최소화도록 최적화됩니다. 하지만 쿼리가 실행 중일 때 커밋되는 트랜잭션에서 추가, 제거 또는 재편하는 행을 쿼리에서 가져오는지 여부에 따라 결과가 달라질 수 있다는 단점이 있습니다. 쿼리는 이러한 행을 고려하도록 허용될 뿐 반드시 고려해야 하는 것은 아닙니다. 데모용으로 쿼리에서는 COUNT(*) 함수를 사용해 테이블의 행 수만 확인합니다.

시간	Aurora 기본 인스턴스의 DML 문	READ COMMITTED로 Aurora 기본 인스턴스에 대해 쿼리	READ COMMITTED로 Aurora 복제본에 대해 쿼리
T1	INSERT INTO big_table SELECT * FROM other_table LIMIT 1000000; COMMIT;
T2		Q1: SELECT COUNT(*) FROM big_table;	Q2: SELECT COUNT(*) FROM big_table;
T3	INSERT INTO big_table (c1, c2) VALUES (1, 'one more row'); COMMIT;
T4		Q1이 지금 완료되면 결과는 1,000,000입니다.	Q2가 지금 완료되면 결과는 1,000,000 또는 1,000,001입니다.
T5	DELETE FROM big_table LIMIT 2; COMMIT;
T6		Q1이 지금 완료되면 결과는 1,000,000입니다.	Q2가 지금 완료되면 결과는 1,000,000 또는 1,000,001 또는 999,999 또는 999,998입니다.
T7	UPDATE big_table SET c2 = CONCAT(c2,c2,c2); COMMIT;
T8		Q1이 지금 완료되면 결과는 1,000,000입니다.	Q2가 지금 완료되면 결과는 1,000,000 또는 1,000,001 또는 999,999이거나 이보다 더 높은 수 일 수도 있습니다.
T9		Q3: SELECT COUNT(*) FROM big_table;	Q4: SELECT COUNT(*) FROM big_table;
T10		Q3이 지금 완료되면 결과는 999,999입니다.	Q4가 지금 완료되면 결과는 999,999입니다.
T11		Q5: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000;	Q6: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000;
T12	INSERT INTO parent_table (id, s) VALUES (1000, 'hello'); INSERT INTO child_table (id, s) VALUES (1000, 'world'); COMMIT;
T13		Q5가 지금 완료되면 결과는 0입니다.	Q6이 지금 완료되면 결과는 0 또는 1입니다.

다른 트랜잭션에서 DML 문을 수행하고 커밋하기 전에 쿼리가 빠르게 완료되면 결과는 예측 가능하며 기본 인스턴스와 Aurora 복제본 간에 동일합니다. 첫 번째 쿼리부터 시작하여 동작의 차이점을 자세히 살펴보겠습니다.

기본 인스턴스의 READ COMMITTED에서는 REPEATABLE READ 격리 수준과 유사한 강력한 일관성 모델을 사용하므로 Q1에 대한 결과는 예측 가능성이 매우 높습니다.

Q2에 대한 결과는 쿼리가 실행 중일 때 어떤 트랜잭션이 커밋되는가에 따라 달라질 수 있습니다. 예를 들어 쿼리가 실행 중일 때 다른 트랜잭션에서 DML 문을 수행하고 커밋한다고 가정합시다. 이 경우 격리 수준이 READ COMMITTED인 Aurora 복제본에 대한 쿼리에서는 이러한 변경 사항을 고려할 수도 하지 않을 수도 있습니다. 행 수는 REPEATABLE READ 격리 수준과 마찬가지로 예측할 수 없습니다. 또한 행 수를 기본 인스턴스 또는 RDS for MySQL 인스턴스의 READ COMMITTED 격리 수준에서 실행 중인 쿼리와 같은 수준으로 예측할 수는 없습니다.

T7의 UPDATE 문은 실제로 테이블의 행 수를 변경하지 않습니다. 그러나 이 문은 변수 길이 열의 길이를 변경함으로써 행이 내부적으로 재편되게 할 수 있습니다. 장기 실행 READ COMMITTED 트랜잭션에서는 행의 이전 버전이 생길 수 있고, 나중에 동일 쿼리 내에서는 동일 행의 새로운 버전이 생길 수 있습니다. 또한 쿼리는 행의 이전 버전과 새 버전을 모두 건너뛸 수 있으므로 행의 수가 예상과 다를 수 있습니다.

Q5 및 Q6의 결과는 서로 같거나 약간 다를 수 있습니다. READ COMMITTED에서 Aurora 복제본에 대한 쿼리 Q6에서는 쿼리가 실행 중일 때 커밋된 새 행이 반환될 수 있지만 반드시 반환되어야 하는 것은 아닙니다. 또한 테이블 하나에서 해당 행을 반환할 수 있지만 다른 테이블에서는 반환하지 않을 수 있습니다. 조인 쿼리가 두 테이블에서 일치하는 행을 찾지 못하는 경우 0이라는 수를 반환합니다. 이 쿼리가 PARENT_TABLE 및 CHILD_TABLE 모두에서 새 행을 찾지 못하면 쿼리는 1이라는 수를 반환합니다. 장기 실행 쿼리에서는 조인된 테이블에서의 조회는 간격이 크게 벌어진 여러 시점에 발생할 수 있습니다.

참고

동작의 이러한 차이는 트랜잭션이 커밋되는 시점과 쿼리에서 기본 테이블 행을 처리하는 시점에 따라 달라집니다. 따라서 몇 분 또는 몇 시간이 걸리고 OLTP 트랜잭션을 동시에 처리하는 Aurora 클러스터에서 실행되는 보고서 쿼리에서 이러한 차이가 반환될 가능성이 높습니다. 이것은 Aurora 복제본의 READ COMMITTED 격리 수준에서 가장 큰 이익을 얻는 혼합 워크로드 유형입니다.