앞에서 나온 격리수준에서의 경우들을 직접 테스트해보자
Mac 기준으로 테스트를 진행한다
postgres 세팅
# homebrew 미설치인 경우
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
# 설치 후 나오는 next step 대로 따라서 패스 설정
# 코어 저장소 탭
brew tap homebrew/core
# postgres 설치
# @{버전}을 붙여 원하는 버전 설치
brew install postgresql
brew services start postgresql아마 위의 과정을 거쳐서 설치후 실행하면 최신 릴리즈 버전인 14가 설치될 것이다.
# 기본 데이터베이스로 쉘 진입
psql postgres
CREATE DATABASE testdb;
# 추후 psql testdb로 접근가능
\c testdb # 해당 db로 이동
-------
# 만약 특정 유저를 생성해서 db권한을 주고 싶은 경우
# 유저명 testuser
CREATE USER testuser WITH ENCRYPTED PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
# 아마 conf파일을 안건들면 위와 같이 특정 db에 특정유저로 접근가능하게 해둬도
# local에서는 기본적으로 인증없이 접근가능할 것이다.위와 같이 db를 생성하고 이제 우리가 원하는 테이블을 만들어보자
CREATE TABLE test (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
positive_integer INT CHECK (positive_integer > 0)
);
-------
testdb=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | testuser
(1 row)\dt를 통해 원하는 테이블이 생성된 것을 확인할 수 있다.
testdb=> SHOW TRANSACTION ISOLATION LEVEL;
transaction_isolation
-----------------------
read committed
(1 row)기본적인 격리 수준은 read committed이다.
non-repeatable read
READ COMMITED에서 발생하는 non-repeatable read에 관해서 테스트해보자.
a = 10, b = 10
A 트랜잭션
트랜잭션 시작
a : - 5
(이 시점에 B 트랜잭션 동작)
b : + 5
트랜잭션 종료
B 트랜잭션
트랜잭션 시작
c : a 카피
(A 트랜잭션 종료)
d : a 카피
트랜잭션 종료위와 같은 과정을 위해
testdb=> SELECT * FROM test;
id | name | positive_integer
----+------+------------------
1 | a | 10
2 | b | 10
(2 rows)위와같이 데이터를 추가했다.
위와 같은 과정을 진행해보자 현재 이상태는
A, B 트랜잭션이 동시에 진행되는 상태고
c : a 카피 # 이부분까지 진행된 상태다
위에서 A 트랜잭션이 나머지 작업을 마치고 커밋후 B 트랜잭션이 진행된다면 어떻게 될까?
testdb=*> UPDATE test SET positive_integer = positive_integer + 5 WHERE name = 'b';
UPDATE 1
testdb=*> COMMIT;A 트랜잭션 먼저 commit되었고
testdb=*> DO $$
DECLARE
a_var INTEGER;
BEGIN
SELECT positive_integer INTO a_var FROM test WHERE name = 'a';
INSERT INTO test (name, positive_integer) VALUES ('d', a_var);
END $$;
DO
testdb=*> COMMIT;
COMMIT그 후 B 트랜잭션에서 다시 a값을 읽어 d를 만들고 commit되었다.
testdb=> SELECT * FROM test;
id | name | positive_integer
----+------+------------------
1 | a | 5
2 | b | 15
6 | c | 10
7 | d | 5
(4 rows)그 결과로 a, b는 정상적으로 5, 15가 되었고
c,d 는 한 트랜잭션 내에서 동일한 쿼리를 통해 값을 가져와서 데이터를 읽었으나 다른 값이 나오는 non-repeatable read가 발생한 것을 알 수 있다.
Phantom Read
그러면 이제 Repeatable Read 단계에서 발생하는 phantom read를 확인해보자
rows : 1, 1, 1, 3, 3
A 트랜잭션
트랜잭션 시작
a : 값이 1인 row의 갯수
(이 시점에 B 트랜잭션 동작)
b : 값이 1인 row의 갯수
트랜잭션 종료
B 트랜잭션
트랜잭션 시작
rows에 1 추가
트랜잭션 종료저번 포스트처럼 위와 같이 같은 트랜잭션에서 같은 쿼리를 2번 진행했을때 서로 다른 값, 그 중에서도 행의 삭제, 생성에 따라 달라지는것을 phantom read라고 한다.
A 트랜잭션
testdb=> BEGIN;
BEGIN
testdb=*> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
testdb=*> DO $$
DECLARE
a_count INTEGER;
BEGIN
SELECT COUNT(*) INTO a_count FROM test WHERE name = 'a';
INSERT INTO test (name, positive_integer) VALUES ('c', a_count);
END $$;
DO
(B트랜잭션 시작 및 종료)
testdb=*> DO $$
DECLARE
a_count INTEGER;
BEGIN
SELECT COUNT(*) INTO a_count FROM test WHERE name = 'a';
INSERT INTO test (name, positive_integer) VALUES ('d', a_count);
END $$;
DO
testdb=*> COMMIT;
B 트랜잭션
testdb=> BEGIN;
BEGIN
testdb=*> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
testdb=*> INSERT INTO test (name, positive_integer) VALUES ('a', 15);
INSERT 0 1
testdb=*> COMMIT;
COMMIT두 트랜잭션의 격리수준을 repeatable read로 변경 후 A 트랜잭션에서 name이 a인 것들의 갯수를 세서 insert를 하고 B트랜잭션에서 name이 a인 row를 추가 후 커밋한다. 그 후 A 트랜잭션에서 다시 a인 row의 갯수를 세서 insert를 하면 다음과 같은 결과가 나타난다.
testdb=> SELECT * FROM test;
id | name | positive_integer
----+------+------------------
1 | a | 10
2 | b | 10
11 | c | 1
12 | a | 15
13 | d | 1
(5 rows)B트랜잭션 이전에 생성된 c와 이후에 실행된 d의 값이 똑같은것을 볼 수 있다. 이것은 postgres에서는 MVCC를 이용하여 repeatable read 격리 수준에서도 phantom read가 발생하지 않기 때문이다.
Serialization Anomaly
저번 포스트에서 작성한 아래와 같은 경우를 테스트해보자
A 트랜잭션
트랜잭션 시작
row 추가 : name = a, value = 모든 b row의 value 총합
트랜잭션 종료
B트랜잭션 시작
row 추가 : name = b, value = 모든 a row의 value 총합
트랜잭션 종료초기 데이터는 아래와 같다.
testdb=> SELECT * FROM test;
id | name | positive_integer
----+------+------------------
14 | a | 1
15 | a | 2
16 | a | 3
17 | b | 2
18 | b | 3
19 | b | 4
(6 rows)A 트랜잭션
testdb=> BEGIN;
BEGIN
testdb=*> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
testdb=*> DO $$
DECLARE
integer_sum INTEGER;
BEGIN
SELECT sum(positive_integer) INTO integer_sum FROM test WHERE name = 'b';
INSERT INTO test (name, positive_integer) VALUES ('a', integer_sum);
END $$;
DO
testdb=*> COMMIT;
COMMITB 트랜잭션
testdb=> BEGIN;
BEGIN
testdb=*> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
testdb=*> DO $$
DECLARE
integer_sum INTEGER;
BEGIN
SELECT sum(positive_integer) INTO integer_sum FROM test WHERE name = 'a';
INSERT INTO test (name, positive_integer) VALUES ('b', integer_sum);
END $$;
DO
testdb=*> COMMIT;
COMMIT위와 같은 두 트랜잭션을 동작시킬때
A, B 트랜잭션이 순서대로 발생하면
21 | a | 9
22 | b | 15위와 같이 (a, 9), (b, 15)가 결과값으로 나오게 된다.
B, A 순서대로 진행되면
23 | b | 6
24 | a | 15위와 같이 (b, 6), (a, 15)가 결과값으로 나오게 된다.
그런데 만약 A 트랜잭션 진행중에 B 트랜잭션이 먼저 끝나면 어떻게 될까?
25 | a | 9
26 | b | 6위와 같이 두 트랜잭션이 순차적으로 진행되었을때의 나오는 결과값들에 포함되지 않는 결과값이 나타나게 된다. 이를 Serialization Anomaly라고 한다.
만약 serializable 레벨에서는 위와 같은 상황을 어떻게 막을까? 똑같이 진행해보자.
A 트랜잭션 진행중 B 트랜잭션을 먼저 commit 후 A 트랜잭션을 commit하려고 하면 다음과 같은 오류가 발생하면서 트랜잭션이 종료된다.
testdb=*> COMMIT;
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.테이블을 보면 B 트랜잭션만 성공했다는 것을 확인 할 수 있다.
28 | b | 6위와 같은 경우가 발생하면 어플리케이션 레벨에서 A 트랜잭션을 다시 동작시켜 우리가 원하는 값을 얻을 수 있다. 위와 같은 방법을 통해서 serializable 격리 수준에서 Serialization Anomaly를 막는다.
