Babelfish의 트랜잭션 격리 수준 - Amazon Aurora
트랜잭션 격리 수준 개요트랜잭션 격리 수준 설정트랜잭션 격리 수준 활성화 또는 비활성화Babelfish 및 SQL Server 격리 수준 간의 차이점

Babelfish의 트랜잭션 격리 수준

Babelfish는 트랜잭션 격리 수준 READ UNCOMMITTED, READ COMMITTED, SNAPSHOT을 지원합니다. Babelfish 3.4 버전부터 추가 격리 수준의 REPEATABLE READ 및 SERIALIZABLE이 지원됩니다. Babelfish의 모든 격리 수준은 PostgreSQL의 해당 격리 수준 동작으로 지원됩니다. SQL Server와 Babelfish는 트랜잭션 격리 수준(동시 액세스 차단, 트랜잭션에 의한 잠금, 오류 처리 등)을 구현하기 위해 서로 다른 기본 메커니즘을 사용합니다. 또한 워크로드에 따라 동시 액세스가 작동하는 방식에도 미묘한 차이가 있습니다. 이 PostgreSQL 동작에 대한 자세한 내용은 트랜잭션 격리를 참조하세요.

트랜잭션 격리 수준 개요

원래의 SQL Server 트랜잭션 격리 수준은 데이터 사본이 하나만 존재하고 쿼리가 행과 같은 리소스를 잠가야 액세스할 수 있는 수동적 잠금을 기반으로 합니다. 이후에 Read Committed 격리 수준의 변형이 도입되었습니다. 이를 통해 행 버전을 사용하여 비차단 액세스를 사용하는 리더와 라이터 간의 동시성이 높아졌습니다. 이 외에도 Snapshot이라는 새로운 격리 수준을 사용할 수 있습니다. 또한 행 버전을 사용하여 트랜잭션이 끝날 때까지 유지되는 읽기 데이터에 대한 공유 잠금을 피함으로써 REPEATABLE READ 격리 수준보다 더 나은 동시성을 제공합니다.

SQL Server와 달리 Babelfish의 모든 트랜잭션 격리 수준은 낙관적 잠금(MVCC)을 기반으로 합니다. 각 트랜잭션은 기본 데이터의 현재 상태에 관계없이 문의 시작 부분(READ COMMITTED) 또는 트랜잭션 시작 부분(REPEATABLE READ, SERIALIZABLE)에서 데이터 스냅샷을 확인할 수 있습니다. 따라서 Babelfish의 동시 트랜잭션 실행 동작은 SQL Server와 다를 수 있습니다.

SQL Server에서 처음에는 차단되었다가 나중에 성공하는 격리 수준 SERIALIZABLE의 트랜잭션을 예로 들어 보겠습니다. Babelfish에서는 동일한 행을 읽거나 업데이트하는 동시 트랜잭션과의 직렬화 충돌로 인해 실패로 끝날 수 있습니다. 동시 트랜잭션을 여러 개 실행하면 SQL Server와 비교하여 Babelfish의 최종 결과가 달라지는 경우도 생길 수 있습니다. 격리 수준을 사용하는 애플리케이션은 동시성 시나리오를 철저하게 테스트해야 합니다.

SQL Server 격리 수준 Babelfish 격리 수준 PostgreSQL 격리 수준 설명

READ UNCOMMITTED

READ UNCOMMITTED

READ UNCOMMITTED

Babelfish/PostgreSQL에서는 Read Uncommitted와 Read Committed에 차이가 없습니다.

READ COMMITTED

READ COMMITTED

READ COMMITTED

SQL Server Read Committed는 수동적 잠금 기반이고, Babelfish Read Committed는 스냅샷(MVCC) 기반입니다.

READ COMMITTED SNAPSHOT

READ COMMITTED

READ COMMITTED

둘 다 스냅샷(MVCC) 기반이지만 완전히 동일하지는 않습니다.

SNAPSHOT

SNAPSHOT

REPEATABLE READ

완전히 동일합니다.

REPEATABLE READ

REPEATABLE READ

REPEATABLE READ

SQL Server Repeatable Read는 수동적 잠금 기반이고, Babelfish Repeatable Read는 스냅샷(MVCC) 기반입니다.

직렬화

직렬화

직렬화

SQL Server Serializable은 수동적 격리이고, Babelfish Serializable은 스냅샷(MVCC) 기반입니다.
참고

테이블 힌트는 현재 지원되지 않으며 해당 동작은 Babelfish의 사전 정의된 이스케이프 해치 escape_hatch_table_hints를 사용하여 제어됩니다.

트랜잭션 격리 수준 설정

다음 명령을 사용하여 트랜잭션 격리 수준을 설정합니다.


SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }

트랜잭션 격리 수준 활성화 또는 비활성화

Babelfish에서는 트랜잭션 격리 수준 REATABLE READ 및 SERIALIZABLE이 기본적으로 비활성화되어 있으며, sp_babelfish_configure를 사용해 babelfishpg_tsql.isolation_level_serializable 또는 babelfishpg_tsql.isolation_level_repeatable_read 이스케이프 해치를 pg_isolation으로 설정하여 명시적으로 활성화해야 합니다. 자세한 내용은 이스케이프 해치를 사용하여 Babelfish 오류 처리 관리 섹션을 참조하세요.

다음은 각각의 이스케이프 해치를 설정하여 현재 세션에서 REPEATABLE READ 및 SERIALIZABLE 사용을 활성화 또는 비활성화하는 예제입니다. 필요에 따라 현재 세션뿐만 아니라 이후의 모든 새 세션에 대한 이스케이프 해치를 설정하는 server 파라미터를 포함하세요.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ를 현재 세션에서만 사용할 수 있도록 합니다.

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation'

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ를 현재 세션과 이후의 모든 새 세션에서 사용할 수 있도록 합니다.

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation', 'server'

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ를 현재 세션과 이후의 새 세션에서 사용할 수 없도록 합니다.

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'off', 'server'

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE을 현재 세션에서만 사용할 수 있도록 합니다.

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation'

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE을 현재 세션과 이후의 모든 새 세션에서 사용할 수 있도록 합니다.

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation', 'server'

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE을 현재 세션과 이후의 새 세션에서 사용할 수 없도록 합니다.

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'off', 'server'

Babelfish 및 SQL Server 격리 수준 간의 차이점

다음은 SQL Server와 Babelfish가 ANSI 격리 수준을 구현하는 방식의 미묘한 차이에 대한 몇 가지 예입니다.

참고

  • Babelfish에서는 Repeatable Read 및 Snapshot 격리 수준이 동일합니다.

  • Babelfish에서는 Read Uncommitted 및 Read Committed 격리 수준이 동일합니다.

다음 예제에서는 아래에 언급된 모든 예제에 대한 기본 테이블을 생성하는 방법을 보여줍니다.

CREATE TABLE employee (
    id sys.INT NOT NULL PRIMARY KEY,
    name sys.VARCHAR(255)NOT NULL,
    age sys.INT NOT NULL
);
INSERT INTO employee (id, name, age) VALUES (1, 'A', 10);
INSERT INTO employee (id, name, age) VALUES (2, 'B', 20);
INSERT INTO employee (id, name, age) VALUES (3, 'C', 30);

Babelfish의 READ UNCOMMITTED 격리 수준 및 SQL Server의 READ UNCOMMITTED 격리 수준 비교

SQL Server의 더티 읽기
트랜잭션 1 트랜잭션 2 SQL Server Read Uncommitted Babelfish Read Uncommitted

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE employee SET age=0;

업데이트 성공

업데이트 성공

INSERT INTO employee VALUES (4, 'D', 40);

삽입 성공

삽입 성공

SELECT * FROM employee;

트랜잭션 1은 트랜잭션 2에서 커밋되지 않은 변경 사항을 확인할 수 있습니다.

Babelfish의 Read Committed와 동일합니다. 트랜잭션 2의 커밋되지 않은 변경 사항은 트랜잭션 1에 표시되지 않습니다.

COMMIT

SELECT * FROM employee;

트랜잭션 2에서 커밋된 변경 사항을 확인합니다.

트랜잭션 2에서 커밋된 변경 사항을 확인합니다.

Babelfish의 READ COMMITTED 격리 수준 및 SQL Server의 READ COMMITTED 격리 수준 비교

읽기 - 쓰기 차단
트랜잭션 1 트랜잭션 2 SQL Server Read Committed Babelfish Read Committed

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM employee;

UPDATE employee SET age=100 WHERE id = 1;

업데이트 성공

업데이트 성공

UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);

트랜잭션 2가 커밋될 때까지 단계가 차단됩니다.

트랜잭션 2 변경 사항은 아직 표시되지 않습니다. id = 3인 행을 업데이트합니다.

COMMIT

트랜잭션 2가 성공적으로 커밋됩니다. 이제 트랜잭션 1의 차단이 해제되고 트랜잭션 2의 업데이트를 확인할 수 있습니다.

트랜잭션 2가 성공적으로 커밋됩니다.

SELECT * FROM employee;

트랜잭션 1이 id = 1인 행을 업데이트합니다.

트랜잭션 1이 id = 3인 행을 업데이트합니다.

Babelfish의 READ COMMITTED 격리 수준 및 SQL Server의 READ COMMITTED SNAPSHOT 격리 수준 비교

새로 삽입된 행에 대한 차단 동작
트랜잭션 1 트랜잭션 2 SQL Server Read Committed Snapshot Babelfish Read Committed

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

INSERT INTO employee VALUES (4, 'D', 40);

UPDATE employee SET age = 99;

트랜잭션 1가 커밋될 때까지 단계가 차단됩니다. 삽입된 행은 트랜잭션 1에 의해 잠깁니다.

3개 행이 업데이트됩니다. 새로 삽입된 행은 아직 표시되지 않습니다.

COMMIT

커밋 성공. 이제 트랜잭션 2가 차단 해제됩니다.

커밋 성공.

SELECT * FROM employee;

4개 행 모두 age=99가 됩니다.

id = 4인 행은 업데이트 쿼리 중에 트랜잭션 2에 표시되지 않았으므로 age 값이 40입니다. 다른 행은 age=99로 업데이트됩니다.

Babelfish의 REPEATABLE READ 격리 수준 및 SQL Server의 REPEATABLE READ 격리 수준 비교

읽기/쓰기 차단 동작
트랜잭션 1 트랜잭션 2 SQL Server Repeatable Read Babelfish Repeatable Read

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM employee;

UPDATE employee SET name='A_TXN1' WHERE id=1;

SELECT * FROM employee WHERE id != 1;

SELECT * FROM employee;

트랜잭션 2는 트랜잭션 1이 커밋될 때까지 차단됩니다.

트랜잭션 2는 정상적으로 진행됩니다.

COMMIT

SELECT * FROM employee;

트랜잭션 1의 업데이트가 표시됩니다.

트랜잭션 1의 업데이트가 표시되지 않습니다.

COMMIT

SELECT * FROM employee;

트랜잭션 1의 업데이트를 확인합니다.

트랜잭션 1의 업데이트를 확인합니다.
쓰기/쓰기 차단 동작
트랜잭션 1 트랜잭션 2 SQL Server Repeatable Read Babelfish Repeatable Read

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE employee SET name='A_TXN1' WHERE id=1;

UPDATE employee SET name='A_TXN2' WHERE id=1;

트랜잭션 2가 차단됩니다.

트랜잭션 2가 차단됩니다.

COMMIT

커밋이 완료되고 트랜잭션 2가 차단 해제되었습니다.

동시 업데이트로 인해 액세스를 직렬화할 수 없다는 오류와 함께 커밋이 완료되고 트랜잭션 2가 실패합니다.

COMMIT

커밋 성공.

트랜잭션 2는 이미 중단된 상태입니다.

SELECT * FROM employee;

id=1인 행에서 name='A_TX2'입니다.

id=1인 행에서 name='A_TX1'입니다.
가상 읽기
트랜잭션 1 트랜잭션 2 SQL Server Repeatable Read Babelfish Repeatable Read

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM employee;

INSERT INTO employee VALUES (4, 'NewRowName', 20);

트랜잭션 2는 차단 없이 진행됩니다.

트랜잭션 2는 차단 없이 진행됩니다.

SELECT * FROM employee;

새로 삽입한 행이 표시됩니다.

새로 삽입한 행이 표시됩니다.

COMMIT

SELECT * FROM employee;

트랜잭션 2에서 삽입한 새 행이 표시됩니다.

트랜잭션 2에서 삽입한 새 행이 표시되지 않습니다.

COMMIT

SELECT * FROM employee;

새로 삽입한 행이 표시됩니다.

새로 삽입한 행이 표시됩니다.
다양한 최종 결과
트랜잭션 1 트랜잭션 2 SQL Server Repeatable Read Babelfish Repeatable Read

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE employee SET age = 100 WHERE age IN (SELECT MIN(age) FROM employee);

트랜잭션 1이 id = 1인 행을 업데이트합니다.

트랜잭션 1이 id = 1인 행을 업데이트합니다.

UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);

SELECT 문이 트랜잭션 1의 UPDATE 쿼리로 잠긴 행을 읽으려고 하기 때문에 트랜잭션 2가 차단됩니다.

트랜잭션 2는 읽기가 차단되지 않으므로 차단 없이 진행됩니다. SELECT 문이 실행되고 마지막으로 트랜잭션 1의 변경 내용이 아직 표시되지 않으므로 id = 3인 행이 업데이트됩니다.

SELECT * FROM employee;

이 단계는 트랜잭션 1이 커밋된 후에 실행됩니다. id = 1인 행은 이전 단계의 트랜잭션 2에 의해 업데이트되며 여기에 표시됩니다.

트랜잭션 2가 id = 3인 행을 업데이트합니다.

COMMIT

이제 트랜잭션 2가 차단 해제됩니다.

커밋 성공.

COMMIT

SELECT * FROM employee;

두 트랜잭션 모두 id = 1인 행에서 업데이트를 실행합니다.

트랜잭션 1과 2가 서로 다른 행을 업데이트합니다.

Babelfish SERIALIZABLE 격리 수준 및 SQL Server SERIALIZABLE 격리 수준 비교

SQL Server의 범위 잠금
트랜잭션 1 트랜잭션 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SELECT * FROM employee;

INSERT INTO employee VALUES (4, 'D', 35);

트랜잭션 2는 트랜잭션 1이 커밋될 때까지 차단됩니다.

트랜잭션 2는 차단 없이 진행됩니다.

SELECT * FROM employee;

COMMIT

트랜잭션 1이 성공적으로 커밋됩니다. 이제 트랜잭션 2가 차단 해제됩니다.

트랜잭션 1이 성공적으로 커밋됩니다.

COMMIT

SELECT * FROM employee;

새로 삽입한 행이 표시됩니다.

새로 삽입한 행이 표시됩니다.
다양한 최종 결과
트랜잭션 1 트랜잭션 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

INSERT INTO employee VALUES (4, 'D', 40);

UPDATE employee SET age =99 WHERE id = 4;

트랜잭션 1은 트랜잭션 2가 커밋될 때까지 차단됩니다.

트랜잭션 1은 차단 없이 진행됩니다.

COMMIT

트랜잭션 2가 성공적으로 커밋됩니다. 이제 트랜잭션 1이 차단 해제됩니다.

트랜잭션 2가 성공적으로 커밋됩니다.

COMMIT

SELECT * FROM employee;

새로 삽입된 행의 age 값이 99로 표시됩니다.

새로 삽입된 행의 age 값이 40으로 표시됩니다.
고유한 제약 조건이 있는 테이블로 삽입
트랜잭션 1 트랜잭션 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

INSERT INTO employee VALUES (4, 'D', 40);

INSERT INTO employee VALUES ((SELECT MAX(id)+1 FROM employee), 'E', 50);

트랜잭션 1은 트랜잭션 2가 커밋될 때까지 차단됩니다.

트랜잭션 1은 트랜잭션 2가 커밋될 때까지 차단됩니다.

COMMIT

트랜잭션 2가 성공적으로 커밋됩니다. 이제 트랜잭션 1이 차단 해제됩니다.

트랜잭션 2가 성공적으로 커밋됩니다. 키 값 중복 오류로 인해 트랜잭션 1이 중단되었으며 고유한 제약 조건을 위반했습니다.

COMMIT

트랜잭션 1이 성공적으로 커밋됩니다.

트랜잭션 간의 읽기/쓰기 종속성으로 인해 액세스를 직렬화할 수 없어 트랜잭션 1 커밋이 실패합니다.

SELECT * FROM employee;

행 (5, 'E', 50)이 삽입됩니다.

4개의 행만 존재합니다.

Babelfish에서 직렬화 가능(serializable) 격리 수준으로 실행되는 동시 트랜잭션은 이러한 트랜잭션의 실행이 해당 트랜잭션의 가능한 모든 직렬(한 번에 하나씩) 실행과 일치하지 않으면 직렬화 이상 오류가 발생하여 실패합니다.

직렬화 이상
트랜잭션 1 트랜잭션 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SELECT * FROM employee;

UPDATE employee SET age=5 WHERE age=10;

SELECT * FROM employee;

트랜잭션 2는 트랜잭션 1이 커밋될 때까지 차단됩니다.

트랜잭션 2는 차단 없이 진행됩니다.

UPDATE employee SET age=35 WHERE age=30;

COMMIT

트랜잭션 1이 성공적으로 커밋됩니다.

트랜잭션 1이 먼저 커밋되고 커밋을 완료할 수 있습니다.

COMMIT

트랜잭션 2가 성공적으로 커밋됩니다.

직렬화 오류로 인해 트랜잭션 2 커밋이 실패하고 전체 트랜잭션이 롤백되었습니다. 트랜잭션 2를 재시도합니다.

SELECT * FROM employee;

두 트랜잭션의 변경 사항이 모두 표시됩니다.

트랜잭션 2가 롤백되었습니다. 트랜잭션 1의 변경 사항만 표시됩니다.

Babelfish에서는 모든 동시 트랜잭션이 SERIALIZABLE 격리 수준에서 실행되는 경우에만 직렬화 이상이 발생할 수 있습니다. 위의 예제를 사용해 트랜잭션 2를 REPEATABLE READ 격리 수준으로 대신 설정해 보겠습니다.

트랜잭션 1 트랜잭션 2 SQL Server 격리 수준 Babelfish 격리 수준

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM employee;

UPDATE employee SET age=5 WHERE age=10;

SELECT * FROM employee;

트랜잭션 2는 트랜잭션 1이 커밋될 때까지 차단됩니다.

트랜잭션 2는 차단 없이 진행됩니다.

UPDATE employee SET age=35 WHERE age=30;

COMMIT

트랜잭션 1이 성공적으로 커밋됩니다.

트랜잭션 1이 성공적으로 커밋됩니다.

COMMIT

트랜잭션 2가 성공적으로 커밋됩니다.

트랜잭션 2가 성공적으로 커밋됩니다.

SELECT * FROM employee;

두 트랜잭션의 변경 사항이 모두 표시됩니다.

두 트랜잭션의 변경 사항이 모두 표시됩니다.