SQL 표준은 transaction isolation에 대해 네 가지 레벨로 정의하고 있습니다. 이 네 가지중 가장 엄격한 것은 Serializable이며, 이것은 마치 여러 세션의 같은 트랜잭션 작업을 한 줄로 세워 차례 대로 진행하는 것과 같은 결과를 보장합니다. 나머지 세 가지 레벨은 동시에 진행되는 트랜잭션들 사이 간 허용되는 작업의 범위에 따라서 구분됩니다. 표준안에서는 Serializable이 가능한 transaction isolation 수준은 동시에 진행되는 트랜잭션 사이의 상호 관계가 전혀 없어야 한다고 정의합니다. (실 세계에서는 당연히 동시에 여러 트랜잭션들이 발생할 것이고, 이것들이 어떻게 서로 상호 관계를 안 할 수 있을까? 라는 의문점을 남깁니다.)

각 수준별 이름과 상호 작용 범위는 다음과 같습니다:


dirty read

한 트랜잭션은 다른 트랜잭션에 아직 커밋하지 않은 자료도 읽을 수 있습니다.

nonrepeatable read

한 트랜잭션은 다른 트랜잭션에서 커밋한 자료를 읽을 수 있습니다. (처음 어떤 자료를 읽고, 다시 읽으려고 하는데, 그 사이 다른 트랜잭션이 자료를 변경하고 커밋했다면, 다음 읽는 값이 커밋된 값으로 읽을 수 있습니다.)

phantom read

위와 같은 상황에서 다른 트랜잭션에 의한 커밋된 자료가 있다 하더라도, 항상 자신의 트랜잭션에서 조회 했던 그 자료값 그대로 보여줍니다.

serialization anomaly

트랜잭션 그룹을 성공적으로 커밋 한 결과는 한 번에 하나씩 모든 트랜잭션을 실행할 수 있는 순서와 일치하지 않습니다.


아래는 SQL 표준과 PostgreSQL에서 구현한 트랜잭션 isolation level을 나타낸 표입니다.

Isolation levelDirty ReadNonrepeatable ReadPhantom ReadSerialization Anomaly
Read uncommitted허용, PG에서는 없음가능가능가능
Read committed불가능가능가능가능
Repeatable read불가능불가능허용, PG에서는 없음가능
Serializable불가능불가능불가능불가능


PostgreSQL에서는 네 가지 표준 transaction isolation level 중 하나를 요청할 수 있지만 내부적으로 세 가지 별도의 isolation level만 구현됩니다. 즉, PostgreSQL의 Read Uncommitted 모드는 커밋이 된 자료만 읽기가 가능합니다. 이는 PostgreSQL의 다중 버전 동시성 제어 아키텍처를 표준 isolation level에 매핑하는 유일한 방법이기 때문입니다.

또한 위의 표는 PostgreSQL의 Repeatable read 구현이 phantom reads를 허용하지 않음을 보여줍니다. 더 엄격한 동작은 SQL 표준에 의해 허용됩니다. 네 가지 isolaction level은 어떤 현상이 발생해서는 안되고 어떤 현상이 발생해야 하는지를 정의합니다. 사용가능한 isolation level의 동작은 아래에서 자세히 설명하겠습니다.

만약 트랜잭션 isolation level을 지정하려면 SET TRANSACTION 명령을 사용하면 됩니다.

  • PostgreSQL의 몇몇 자료형과 함수는 트랜잭션 내 특별한 형태로 읽기 특성을 제공합니다. 특히 자동 증가 컬럼으로 사용하는 serial 자료형과 sequence같은 객체는 rollback이 없으며, 자료 변경 즉시 다른 모든 세션에서도 그 변경된 값을 볼 수 있습니다.

PostgreSQL에서의 트랜잭션 레벨

Read Committed Isolation Level

PostgreSQL에서의 기본 레벨임.

SELECT 쿼리

  • 쿼리가 시작했을 당시의 커밋된 데이터만 참조 가능
  • 쿼리가 실행되는 도중 커밋되지 않은 데이터 혹은 동시에 실행되고 있던 트랜잭션에서 커밋된 변경들은 참조하지 않음
  • 외부 트랜잭션이 아닌 자신의 트랜잭션 도중에 일어난 커밋되지 않은 수정 사항은 참조할 수 있음
  • 트랜잭션 내부에서 select가 여러번 있을 때, 각 select 쿼리 사이에 외부 트랜잭션이 커밋을 했으면 커밋된 다음에 실행된 select 쿼리는 커밋된 데이터를 참조하게 됨

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE 쿼리

  • select 쿼리와 동일하게 동작
  • 단지 데이터를 변경하는 도중 다른 트랜잭션이 같은 로우에 접근하면 그 트랜잭션이 끝난 후에 변경사항에 대해서 작업을 진행하게 됨. 이 때, 외부 트랜잭션 때문에 변경된 로우가 현재 명령의 where 조건에 맞는지 다시 평가를 하게 됨


Read Committed는 복잡한 검색조건이 있는 상황에 맞지 않고, 은행 잔고 변경과 같은 단순한 경우에 적합.

Repeatable Read Isolaction Level

SELECT 쿼리

  • 현재 트랜잭션이 시작하기전에 커밋된 데이터만 참조
  • 외부 트랜잭션에서 커밋되거나 커밋되지 않은 변경을 참조하지 않음
  • 외부 트랜잭션이 아닌 자신의 트랜잭션 도중에 일어난 커밋되지 않은 수정 사항은 참조할 수 있음

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE 쿼리

  • 데이터를 변경하는 도중 다른 트랜잭션이 같은 로우에 접근하면 그 트랜잭션이 끝난 후에 변경하려던 로우가 변경됐는지 확인해서 변경사항이 없으면 원래 진행하려던 명령을 실행하고, 변경됐으면 다음과 같은 에러메세지를 내면서 트랜잭션을 롤백 (ERROR: could not serialize access due to concurrent update)
  • 읽기만 하는 트랜잭션에서는 에러가 없음


Read Committed와 차이점은 현재 트랜잭션에 여러번의 쿼리가 있을 경우, 현재 트랜잭션의 쿼리 중간에 다른 트랜잭션에서 변경이 있었더라도 참조하지 않고, 현재 트랜잭션이 시작되었을 때의 데이터와 현재 트랜잭션이 수행되던 도중에 일어난 변경사항만 참조

이 레벨을 사용하면 실패했을 때 재시도하는 것을 어플리케이션에서 처리해야함

Serializable Isolation Level

가장 엄격한 트랜잭션 레벨

  • 트랜잭션들이 동시에 일어나지 않고, 하나씩 순서대로 실행되는 것처럼 작동
  • Repeatable Read처럼 어플리케이션에서 실패했을 때 재시도 처리를 해줘야함
  • Repeatable Read와 동일하게 작동하면서 트랜잭션들이 순서대로 실행중인지 모니터링
  • 잘 고려하여 사용하지 않으면 성능저하 이슈가 존재


참조문서:

https://www.postgresql.org/docs/current/static/transaction-iso.html

http://arisu1000.tistory.com/27756

limit과 offset은 보통 쿼리의 pagination을 개발할 때 주로 사용됩니다.

-- 처음 10개의 Row를 반환
SELECT * FROM test LIMIT 10;
-- 위 SQL과 아래의 SQL은 같은 결과
SELECT * FROM test LIMIT 10 OFFSET 0;
 
-- 11번째 부터 10개의 Row를 반환.
SELECT * FROM test LIMIT 10 OFFSET 10;


PL/pgSQL 함수는 블럭 구조로 되어있으며 아래와 같은 문법을 따르고 있습니다.

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements;
 ...
END [ label ];


각 블록에는 declaration과 body라는 두 개의 섹션이 있습니다. body 섹션은 필수적인 반면, declaration 섹션은 선택 사항입니다. 블록은 END 키워드 다음에 세미콜론 (;)으로 끝납니다.

블록에는 시작과 끝 부분에 추가적인 label이 있을 수 있습니다. 이 label의 처음과 끝은 동일해야합니다. 이 블록 label은 EXIT 문에서 블록을 사용하거나 블록에서 선언 된 변수의 이름을 정규화하려는 경우에 사용됩니다.

declaration 섹션은 body 섹션에서 사용되는 모든 변수를 선언하는 곳입니다. declaration 섹션의 각 명령문은 세미콜론 (;)으로 끝납니다.

body 섹션은 블록의 logic을 두는 곳입니다. 유효한 모든 명령문을 포함합니다. ㅠㅐ요 섹션의 각 문은 세미콜론 (;)으로 끝납니다.

예제

DO 문은 블록에 속하지 않습니다. DO문은 익명 블록을 실행하는 데 사용됩니다. PostgreSQL에서는 버전 9.0부터 DO 문을 도입했습니다.

DO $$ 
<<first_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;
END first_block $$;
 
 
 
NOTICE:  The current value of counter is 1

위 예시는 declaration 섹션에서 count라는 변수를 선언하고 값을 0으로 설정했습니다. body 섹션에서 count를 1로 늘리고 RAISE NOTICE 문을 사용하여 값을 출력했습니다. first_block이라는 label은 데모 목적으로만 사용됩니다. 이 예제에서는 아무것도 수행하지 않습니다.

Subblock

블록을 다른 블록의 body 안에 넣을 수 있습니다. 다른 블록 안에 중첩된이 블록을 하위 블록이라고합니다. 서브 블록을 포함하는 블록을 외부 블록이라고합니다.

큰 블록을 더 작은 논리적 서브 블록으로 나눌 수 있도록 명령문을 그룹화하기 위해 종종 서브 블록을 사용합니다. 하위 블록의 변수는 바깥 규칙에있는 변수와 마찬가지로 이름을 가질 수 있습니다.

외부 블록의 이름과 동일한 이름을 가진 하위 블록 내에서 변수를 정의하면 외부 블록의 변수가 하위 블록에 숨겨집니다. 외부 블록의 변수에 액세스하려면 블록 레이블을 사용하여 해당 이름을 한정합니다.

DO $$ 
<<outer_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;
 
   DECLARE 
       counter integer := 0;
   BEGIN 
       counter := counter + 10;
       RAISE NOTICE 'The current value of counter in the subblock is %', counter;
       RAISE NOTICE 'The current value of counter in the outer block is %', outer_block.counter;
   END;
 
   RAISE NOTICE 'The current value of counter in the outer block is %', counter;
   
END outer_block $$;
 
 
NOTICE:  The current value of counter is 1
NOTICE:  The current value of counter in the subblock is 10
NOTICE:  The current value of counter in the outer block is 1
NOTICE:  The current value of counter in the outer block is 1

이 예에서는 먼저 outer_block에 counter라는 변수를 선언했습니다. 그런 다음 하위 블록에서 동일한 이름의 변수도 선언했습니다. 그런 다음 하위 블록에 들어가기 전에 counter 값은 1입니다. 하위 블록에서 counter 값을 10으로 늘린 다음이를 인쇄합니다. 변경 사항은 하위 블록의 counter 변수에만 영향을줍니다. 그런 다음 outer_block.counter 이름을 한정하기 위해 블록 label을 사용하여 외부 블록의 counter 변수를 참조했습니다. 마지막으로 바깥 쪽 블록에 값 counter 변수를 출력합니다. 값은 그대로 유지됩니다. 이 튜토리얼에서는 PL/pgSQL의 블록 구조와 DO 문을 사용하여 블록을 실행하는 방법에 대해 배웠다.


set search_path to 변경할 스키마;
 
-- 예시
abcd.temp1
public.temp2 
과 같이 abcd 스키마에는 temp1 테이블, public 스키마에는 temp2 테이블이 존재한다고 가정
 
select * from temp1;
ERROR:  relation "temp1" does not exist
 
set search_path to abcd;  -- abcd스키마로 변경
select * from temp1; -- 성공


해당 오류는 트랜잭션 수행 중간에 쿼리가 오류를 생성되고 롤백을 실행하지 않고 다른 쿼리를 실행하려고 할 때 PostgreSQL이 발생시키는 에러입니다. 이 문제를 해결하려면 잘못된 쿼리가 실행되는 코드의 위치를 파악해야합니다. postgresql 서버에서 log_statement와 log_min_error_statement 옵션을 사용하면 도움이 됩니다. 또는 에러가 발생되는 해당 쿼리 부분의 데이터들을 전부 truncate 하면 해결되기도 합니다. 

문제점

트랜잭션 블록에서 쿼리 실행
열려있는 트랜잭션 블록에 잘못된 구문 또는 잘못된 쿼리가 실행되어 오류가 발생
오류로 인해 트랜잭션 블록이 유효하지 않아져 다음 트랜잭션부터 오류가 발생


이러한 문제가 발생되면 트랜잭션을 롤백하고 전체 데이터를 초기화 시키는 것 밖에 없습니다.

예시

gpadmin=# BEGIN;
BEGIN
gpadmin=# insert into Cant_Ignore values(1);
INSERT 0 1
gpadmin=# insert into Cant_Ignore values(2);
INSERT 0 1
gpadmin=# select * from Cant_Ignore;
id
----
  1
  2
(2 rows)

gpadmin=# insert into Cant_Ignore values(ERROR);
ERROR:  column "error" does not exist
LINE 1: insert into Cant_Ignore values(ERROR);
                                       ^
gpadmin=#
gpadmin=# insert into Cant_Ignore values(3);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
gpadmin=# COMMIT;
ROLLBACK
gpadmin=#
gpadmin=# select * from Cant_Ignore;
id
----
(0 rows)


log_statement와 log_min_error_statement 세팅법


1. postgresql 서버의 postgresql.conf 접속

$ cd /etc/postgresql/9.5/main
$ vi postgresql.conf
 
...
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_destination = 'stderr'    #주석해제          # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on    #주석해제       # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log'       #주석해제         # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #주석해제 # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600      #주석해제              # creation mode for log files,
 
...
log_min_error_statement = error # 주석해제 # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic (effectively off)
 
log_statement = 'mod'      #주석 해제 후 none에서 mod로 변경             # none, ddl, mod, all

위와같이 postgresql.conf 파일을 수정한 다음 postgres 서버를 재실행합니다.

각 속성에 대해서는 https://www.postgresql.org/docs/current/static/runtime-config-logging.htm에서 확인할 수 있습니다.

다음 에러나는 (스크립트 또는 코드)를 실행하면 /etc/var/lib/postgres/9.5/main/pg_log 폴더에 로그가 쌓입니다. 해당 폴더로 접근한 다음 로그파일을 열어 어디서 에러가 났는지 확인한 다음, 수정하면 됩니다.


위의 에러가 났을 때, 전체 트랜잭션 롤백이 아닌 에러난 부분을 제외한 나머지를 commit하는 방법:


https://discuss.pivotal.io/hc/en-us/articles/205111468-HowTo-Overcome-Error-current-transaction-is-aborted-commands-ignored-until-end-of-transaction-block-

postgres 데이터베이스에 postgres 유저 접속

$ psql -U postgres -d postgres
postgres 사용자의 암호:

psql에 지정한 비밀번호로 접속

$ PGPASSWORD=password psql -U postgres -d postgres
psql (9.5.4)
도움말을 보려면 "help"를 입력하십시오.
postgres=#

psql에서 query 실행

$ PGPASSWORD=password psql -U postgres -d postgres -c 'select * from test'

psql에서 .sql 파일에 저장되어 있는 쿼리 실행

$ PGPASSWORD=password psql -U postgres -d postgres -f test.sql 
 
$ PGPASSWORD=password psql -U postgres -d postgres < test.sql

원격 서버의 postgres 데이터베이스의 postgres 유저 접속 

$ psql -h 100.100.100.10 -U postgres -d postgres


+ Random Posts