[PostgreSQL] Repeatable Read, Serializable 테스트 방법

테스트 테이블 초기화 프로시저

먼저 테스트 테이블 초기화 프로시저를 생성합니다.

CREATE PROCEDURE test_table_reset() AS -- 프로시저는 기본적으로 return 이 없습니다.
$$
BEGIN
    -- test table 이 이미 존재한다면 지우고
    DROP TABLE IF EXISTS test;

    -- 테스트 테이블을 생성하고
    CREATE TABLE test
    (
        id INT4 NOT NULL PRIMARY KEY,
        name  VARCHAR(10)
    );

    -- 새로운 insert
    INSERT INTO test(id, name)
    VALUES (10, 'a')
         , (11, 'a')
         , (12, 'b')
         , (13, 'b');
END
$$ LANGUAGE plpgsql;

-- 프로시저 호출
-- call test_table_reset();

테스트 방법

이후에는 서로 다른 세션을 사용하는 query console 2개를 구동시키고,

그리고 나서 2개의 query console 에 각각에
Tx1 query console, Tx2 query console 목차의 내용을

• Tx1 한줄 실행, Tx2 한줄 실행,
• Tx1 한줄 실행, Tx2 한줄 실행...
• ... 반복 ...

이런식으로 계속 진행하면 됩니다.

다만 중간중간 test1, test2: 으로 각 테스트를 구분했는데요,
각 테스트가 끝날 때마다 call test_table_reset() 를 호출하여
테스트 테이블을 초기화해야 합니다.

Tx1 query console

---- test1 : repeatable read 을 사용. 서로 다른 row 에 대한 변환
begin transaction isolation level repeatable read;
update test set name = 'a' where name = 'b';
select * from test;
commit;

select * from test order by 1;
----- >> 결론: 마지막에 수행한 transaction 의 내용으로 덮어쓴다.




---- test 2 : repeatable read 을 사용. 같은 row 에 대한 변환
begin transaction isolation level repeatable read;
update test set name = 'c' where name = 'a';
select * from test;
commit;

select * from test order by 1;
----- >> 결론: 마지막에 수행한 transaction 의 내용으로 덮어쓴다.




---- test3: serialize 을 사용. 다른 row 를 변경
begin transaction isolation level serializable;
update test set name = 'a' where name = 'b';
select * from test;
commit;

select * from test order by 1;
----- >> 결론: 2번째로 수행한 transaction 에서 에러가 발생한다.

Tx2 query console

---- test1 : repeatable read 을 사용. 서로 다른 row 에 대한 변환
begin transaction isolation level repeatable read;
update test set name = 'b' where name = 'a';
select * from test;
commit;

select * from test order by 1;
----- >> 결론: 마지막에 수행한 transaction 의 내용으로 덮어쓴다.


---- test 2 : repeatable read 을 사용. 같은 row 에 대한 변환
begin transaction isolation level repeatable read;
update test set name = 'd' where name = 'a';
select * from test;
commit;

select * from test order by 1;
-- test2 결론: 마지막에 수행한 transaction 의 내용으로 덮어쓴다.




---- test3: serialize 을 사용. 다른 row 를 변경
begin transaction isolation level serializable;
update test set name = 'b' where name = 'a';
select * from test;
commit;
----- >> 결론: 2번째로 수행한 transaction 에서 에러가 발생한다. 아래와 같이 말이다.
/*
[2023-05-28 14:46:26] [40001] 오류: 트랜잭션간 읽기/쓰기 의존성 때문에 serialize 접근을 할 수 없음
[2023-05-28 14:46:26] Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
[2023-05-28 14:46:26] Hint: 재시도하면 그 트랜잭션이 성공할 것입니다.
*/


select * from test order by 1;