PostgreSQL - 트랜잭션 격리 수준(transaction isolation level) 테스트

PostgreSQL로 트랜잭션 격리 수준 동작 테스트를 하고, 결과를 정리해 놓는다.

 

트랜잭션 범위

PostgreSQL에서 트랜잭션은 BEGIN으로 시작하고, COMMIT 또는 ROLLBACK으로 끝난다.

BEGIN;
...
(트랜잭션)
...
COMMIT; (또는 ROLLBACK;)

 

트랜잭션 격리 수준 확인/ 변경

PostgreSQL에서 트랜잭션 격리수준 확인 및 변경은 아래 포스트를 참고한다.

PostgreSQL 트랜잭션 격리수준 확인/변경

 

테스트에 사용한 스키마

CREATE TABLE students (
    id serial,
    name varchar(255) NOT NULL,
    major varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

 

트랜잭션 격리 수준별 동작 테스트

본 테스트를 위해 Docker를 기반으로 PostgreSQL으로 동작 확인하였다.

Docker 기반 Postgresql 설치하기

서로 다른 세션으로 각각 접속하여 동작 테스트를 하기 위해, 다음과 같이 CLI를 사용하여 확인한다.

$ psql -h 호스트명 -d DB명 -U 사용자계정 -W

기존에 설정한 DB에, 사용자계정 및 password로 로그인할 수 있을 것이다.

 

0. Read Uncommitted

테스트를 시작하기 전의 데이터 상태는 아래와 같다.

mydb=# select * from students;

 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
(3 rows)

두 개의 세션에서 각각 트랜잭션을 시작하되, 다음과 같이 격리 수준을 Read Uncommitted로 지정한다.

begin transaction isolation level read uncommitted;

두 개의 트랜잭션을 각각 tx1, tx2라고 하자. tx1에서만 아래와 같이 데이터를 변경하고, commit은 실행하지 않는다. 그 상태에서 tx1과 tx2에서 각각 데이터를 조회해 본다.

mydb=*# INSERT INTO students (name, major) VALUES
mydb-*#     ('Terry', 'Architecture');

<tx1의 상태>

mydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | Architecture
(4 rows)

<tx2의 상태>

mydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
(3 rows)

우리가 기대했던 read uncommitted와는 다른 동작을 보인다. 원래 read uncommitted였다면 tx1의 상태가 곧바로 tx2에 나타나야 했기 때문이다. 참고로, PostgreSQL에서는 read uncommitted의 상태는 정의는 되어 있으나, read committed와 동일하다. 따라서, tx1에서 commit을 수행하고 나서 tx1, tx2는 모두 동일한 결과를 리턴한다.

 

1. Read Committed

테스트를 시작하기 전의 데이터 상태는 아래와 같다.

mydb=# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | Architecture
(4 rows)

두 개의 세션에서 각각 트랜잭션을 시작하되, 다음과 같이 격리 수준을 Read Committed로 지정한다.

begin transaction isolation level read committed;

위의 테스트와 동일하게 테스트를 수행해 보자. tx1에서만 데이터를 변경하고 commit은 하지 않는다. 그 상태에서 tx1과 tx2의 데이터를 각각 조회해 본다.

mydb=*# update students set major = 'Music' where id = 4;
UPDATE 1

<tx1의 상태>

mydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | Music
(4 rows)

<tx2의 상태>

mydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | Architecture
(4 rows)

마찬가지로 tx1에서 commit을 수행하면, tx2에서 commit/rollback을 수행하지 않아도 tx1과 tx2의 상태가 같아진다.

 

2. Repeatable Read

테스트를 시작하기 전의 데이터 상태는 아래와 같다.

mydb=# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | Music
(4 rows)

두 개의 세션에서 각각 트랜잭션을 시작하되, 다음과 같이 격리 수준을 Repeatable Read로 지정한다.

begin transaction isolation level repeatable read;

위의 테스트와 동일하게 테스트를 수행해 보자. tx1에서만 데이터를 변경하고 commit은 하지 않는다. 그 상태에서 tx1과 tx2의 데이터를 각각 조회해 본다.

mydb=*# update students set major = 'English' where id = 4;
UPDATE 1

<tx1의 상태>

mydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | English
(4 rows)

<tx2의 상태>

mydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | Music
(4 rows)

앞선 테스트 결과와 조금 달라지는데, 이제 tx1에서 commit을 수행하고 tx1과 tx2의 상태를 각각 조회해 본다.

<tx1 커밋 후, tx1의 상태>

mydb=# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | English
(4 rows)

<tx1 커밋 후, tx2의 상태>

ydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | Music
(4 rows)

 

3. Serializable

테스트를 시작하기 전의 데이터 상태는 아래와 같다.

mydb=# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | English
(4 rows)

두 개의 세션에서 각각 트랜잭션을 시작하되, 다음과 같이 격리 수준을 Serializable로 지정한다.

begin transaction isolation level serializable;

위의 테스트와 동일하게 테스트를 수행해 보자. tx1에서만 데이터를 변경하고 commit은 하지 않는다. 그 상태에서 tx1과 tx2의 데이터를 각각 조회해 본다.

mydb=*# update students set major = 'Math' where id = 4;
UPDATE 1

<tx1의 상태>

mydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | Math
(4 rows)

<tx2의 상태>

mydb=*# select * from students;
 id | name  |      major
----+-------+------------------
  1 | John  | Music
  2 | Sarah | Art
  3 | Steve | Computer Science
  4 | Terry | English
(4 rows)

commit을 하면, tx2에서의 데이터 조회는 repeatable read와 같이 기존에 읽어들인 데이터가 조회된다. PostgreSQL은 pessimistic lock기반으로 Serializablity를 보장하는 대신, Serial Snapshot Isolation으로 이를 보장한다. 따라서, 위와 같이 이웃 트랜잭션이 lock으로 인해 기다렸다 액세스 하지 않고, 같은 값이 항상 읽히는 현상이 발생한다.

 

참고

DB 트랜잭션 - 격리수준(Transaction Isolation Level)