[PostgreSQL] Read Committed 에 대해서

기본 내용

SQL 표준 안에서는 네 종류의 트랜잭션 격리 수준을 정의하고 있다. 

 

격리수준	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly	PostgreSQL 지원
Read uncommitted	허용	가능	가능	가능	X
Read committed	불가능	가능	가능	가능	O
Repeatable read	불가능	불가능	허용, PG에서는 없음	가능	O
Serializable	불가능	불가능	불가능	불가능	O

PostgreSQL에서는 3개의 isolation level을 지원한다. Read uncommitted는 Read committed 처럼 작동한다.

 

PostgreSQL의 동시성 제어의 방식으로 인해 3개의 isolation level을 지원하게 된다.

 

다중 버전 동시성 제어 ( MVCC, Multiverison Concurrency Control)

여러 버전의 레코드를 저장해 과거의 특정 타임스탬프의 데이터베이스의 일관성을 보장하는 방식

MVCC는 하나의 트랜잭션만을 채택하는 검증 기법을 사용해 구현하거나 타임스탬프 순서화 기법과 같은 무잠금 방식 또는 2단계 잠금과 같은 잠금 기반의 방식으로도 구현 가능

 

세팅

create sequence test_increment_seq;

nextval('test_increment_seq'::regclass)

test_db=# select * from test;
 id | balance | name  
----+---------+-------
  2 |      10 | danny
  1 |       9 | amy

 

Read Committed

Read Committed 단계에서는 Select( select for update/share을 제외한) 쿼리가 실행되기 이전의 커밋된 내용들을 보여준다.

커밋되지 않은 내용이나 변경된 내용은 볼 수 없다. Select 쿼리가 나갈 때 데이터베이스의 현재 스냅샷을 본다. 만일 트랜잭션 안에서 select 쿼리가 날라가기 전에 update를 했다면 업데이트된 내용을 볼 수 있다. 

 

begin; 
update test set balance = balance -1 where id =1;
select * from test; 
commit;

==== RESULT ====
BEGIN
UPDATE 1
 id | balance | name  
----+---------+-------
  2 |      10 | danny
  1 |       8 | amy
(2 rows)

COMMIT

심지어 한 트랜잭션 내에서의 연속된 같은 SELECT문이라도 결과가 달라질 수 있다.(이러한 것을 반복 불가능 읽기 라고 한다.)

 

begin;
select * from test;
update test set balance = balance +1 where id=1;
select * from test;
commit;


==== RESULT ====
BEGIN
 id | balance | name  
----+---------+-------
  2 |      10 | danny
  1 |       7 | amy
(2 rows)

UPDATE 1
 id | balance | name  
----+---------+-------
  2 |      10 | danny
  1 |       8 | amy
(2 rows)

COMMIT

 

특정 row에 lock을 거는 update, delete, select for update는 특정 row를 찾는 것에서는 select과 똑같이 행동한다.

그러나 특정 row가 발견될 때 이미 다른 동시 트랜잭션에 의해 업데이트(삭제 또는 잠김)이 될수도 있다. 즉, 먼저 작업하는 트랜잭션이 commit되거나 update 할 때까지 기다린다는 뜻이다. 그리고 다른 트랜잭션은 진행 중인 트랜잭션에서 특정 row에 대하여 변화를 준다면 그 row에 대한 변화는 무시한다.

 

snapshot isolation
스냅샷 격리 수준에서 트랜잭션은 시작당시의 다른 트랜잭션이 커밋한 내용을 확인할 수 있다. 트랜잭션은 데이터의 스냅샷을 생성하고 이에 대해 쿼리하며 트랜잭션 수행 중에는 스냅샷을 변경할 수 없다. 트랜잭션에서 수정한 값이 수행 중에 변경되지 않은 경우에만 커밋될 수 있다. 변경됐다면 롤백한다.

 

더 나아가기

xid

PostgreSQL 내부에서 트랜잭션을 구별하기 위해 특정 아이디가 필요한데 바로 그것이 xid이다. xid는 트랜잭션을 구별하는 순차적 번호이다.

 

xmin 과 xmax

xmin: 레코드가 생성 되었을 때 가지게 되는 값

xmax: 삭제되었을 때 가지게 되는 값(그렇지 않을 때에는 0의 값을 가지게 됩니다. 일반적으로 삭제 트랜잭션이 아직 commit/rollback되지 않았을 때 값을 가지게 됩니다.)

 

 

 

============ 트랜잭션 2 =============

test_db=# select * from test;
 id | balance | name
----+---------+-------
  2 |      10 | danny
  1 |       7 | amy
(2개 행)

test_db=# select * from test;
 id | balance | name
----+---------+-------
  2 |      10 | danny
  1 |       7 | amy
(2개 행)

test_db=# select * from test;
 id | balance | name
----+---------+-------
  2 |      10 | danny
  1 |       7 | amy
(2개 행)

test_db=# select * from test;
 id | balance | name
----+---------+-------
  2 |      10 | danny
  1 |       6 | amy
(2개 행)

트랜잭션1

트랜잭션1이 업데이트를 하는데 무슨 일이 있어 10초동안 해당 변화를 커밋하지 않은 상황이다.

이 때 트랜잭션2는 계속 select문을 날리는 상황인데 이전까지 amy의 상태는 7이었다가 트랜잭션1에서 커밋이 나가고 6으로 바뀐 것을 볼 수가 있다.

 

 

좀 더 복잡한 상황으로 가보자. 

모든 유저의 잔금에 대해서 더하기 1을 하는 상황에서 동시에 어떠한 조건에 맞는 유저를 DELETE를 하는 상황이라고 해보자. 이럴 때는 어떻게 될까?

바뀌기 전
id | balance | name
----+---------+-------
  2 |      13 | danny
  1 |      12 | amy

진행 방향은

begin;select pg_sleep(1); update test set balance = balance +1;

하고 나서 동시에
begin ; select pg_sleep(1); delete from test where balance = 13;

 

공식문서에 의하면 미리 업데이트 되는 12는 제외대상이 되고, 그리고 업데이트가 끝나고 나서 삭제 쿼리가 해당 row에 대해서 lock을 얻게 되는데, 이 때는 13이 아닌 14가 존재하게 되면서, 아무런 변화가 일어나지 않게 된다. ( 위에서 말한 특정 row를 탐색하는 것은 모든 쿼리가 동일하지만 그 이후에 대한 행동(수정, 삭제, 잠금)은 lock 이 필요하다는 차이점이 있다.)

The  DELETE will have no effect even though there is a website.hits = 13 row before and after the UPDATE. This occurs because the pre-update row value 12 is skipped, and when the UPDATE completes and DELETE obtains a lock, the new row value is no longer 13 but 14, which no longer matches the criteria.