PostgreSQL 16 autocommit 기능

OS환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : PostgreSQL 16

 

방법 : PostgreSQL 16 autocommit 기능 확인

PostgreSQL 의 commit 방식은 auto commit 이 기본값임

그렇기 때문에 dml 후 따로 commit을 해주지 않아도 자동으로 commit이 됨

 

 

auto commit on 상태에서 테스트

auto commit 확인

postgres=# \echo :AUTOCOMMIT
on

auto commit이 on(활성화) 상태임

 

 

샘플 테이블 생성 후 데이터 삽입

postgres=# 
drop table test_tbl;
create table test_tbl (col1 int);
insert into test_tbl select generate_series from generate_series(1, 10000000);

 

 

다른 세션 접속 후 테이블 count 확인

(테이블 삭제 및 재생성 후 commit 명령 실행 전 조회)

postgres=# select count(*) from test_tbl;
  count
----------
 10000000
(1 row)

명시적으로 commit 하지 않았음에도 데이터가 보임

 

 

commit 시도

postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT

현재 (커밋을 실행할)트랜젝션이 없다고 나옴 

 

 

auto commit off 상태에서 테스트

auto commit off 로 변경 후 확인

postgres=# \set autocommit off
postgres=# \echo :autocommit
off

auto commit이 off(비활성화) 상태로 변함

 

 

샘플 테이블 생성 후 데이터 삽입

postgres=# 
drop table test_tbl2;
create table test_tbl2 (col1 int);
insert into test_tbl2 select generate_series from generate_series(1, 10000000);

 

 

다른 세션 접속 후 테이블 count 확인

postgres=# select count(*) from test_tbl2;
  count
----------
 10000000
(1 row)

명시적으로 commit 하지 않았음에도 데이터가 보임?

 

 

insert 후 commit 시도

postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT

현재 (커밋을 실행할)트랜젝션이 없다고 나옴?

 

 

테스트 결과가 예상한것과 다르게 다른 세션에서도 테이블이 조회됨

이유는 autocommit이 off이더라도 PostgreSQL의 기본 동작은 여전히 SQL 명령어가 하나의 트랜잭션으로 실행되게끔 동작하지 때문임. 그렇기 때문에 각 SQL 문(statement)은 자동으로 커밋됨

그래서 begin 이라는 명령으로 명시적으로 트랜젝션을 시작해주어야 오라클db 처럼(commit 전까지는 다른세션에서는 볼수없는 상태) 동작함

 

 

auto commit off 상태에서 테스트2

auto commit off 로 변경 후 확인

postgres=# \set autocommit off
postgres=# \echo :autocommit
off

auto commit이 off(비활성화) 상태로 변함

 

 

샘플 테이블 생성 후 데이터 삽입

postgres=# 
drop table test_tbl3;
create table test_tbl3 (col1 int);
begin;
insert into test_tbl3 select generate_series from generate_series(1, 10000000);

 

 

다른 세션 접속 후 테이블 count 확인

postgres=# select count(*) from test_tbl2;
 count -------      0 (1 row)

명시적으로 commit 하지 않아서 데이터가 보이지 않음

 

 

insert 후 commit 시도

postgres=*# commit;
COMMIT

정상적으로 commit이 수행됨

결론 :

PostgreSQL에서는 autocommit on이 기본값이지만 off로 변경하여 사용할 수 있음

이 값이 off 이더라도 dml 전 명시적으로 begin;명령을 수행해 트랜젝션 시작을 선언해주지 않으면

개별 sql 문장이 개별 트랜젝션으로 수행되 auto commit이 되어 버림

auto commit을 off 한 상태로 dml을 사용하고 싶은 경우 dml 수행전 꼭 begin; 명령을 수행해주어야함

 

 

참조 : 

https://www.postgresql.org/docs/current/ecpg-sql-set-autocommit.html