[SQL]트랜잭션이란? Autocommit 설정하기

트랜잭션 

여러개의 SQL을 동시에 성공 또는 실패 해야지만 데이터 정확성에 문제 없는 경우 SQL을 묶어서 하나의 작업처럼 처리하는 방법. 

 

ex. 계좌이체

동작1. A의계좌로부터 인출 동작2. B의 계좌에 인출된 금액 입금 -> 동시에 성공 또는 실패해야됨 => 'Atomic 하다'

 

- DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미있음

- SELECT에는 트랜잭션을 사용할 이유가 없음

- BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용

- ROLLBACK

BEGIN;
   A의 계좌로부터 인출;
   B의 계좌에 입금;
END(=COMMIT);
BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK 실행.

 

트랜잭션 커밋 모드: autocommit

autocommit = True 

- 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐. 이름 commit 된다고 함

- 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리

autocommit = FALSE

- 모든 레코드 수정/삭제/추가 작업이 commit 호출될 때까지 커밋되지 않음

 

트랜잭션 방식

Google colab의 트랜잭션

- 기본적으로 모든 SQL statement가 바로 커밋됨 (autocommit = True)

- 바꾸고 싶다면 BEGIN;END; 혹은 BEGIN;COMMIT을 사용 (혹은 ROLLBACK;)

psycopg2 트랜잭션

- autocommit 이라는 파라미터로 조절가능

- autocommit = True 가 되면 기본적으로 postgreSQL의 커밋 모드와 동일

- autocommit = False가 되면 커넥션 객체의 .commit()과 .rollback()함수로 트랜잭션 조절 가능

 

DELETE FROM vs TRUNCATE

DELETE FROM table 

- 테이블에서 모든 레코드를 삭제 

- vs.DROP TABLE 

- WHERE 사용해서 일부 레코드만 삭제 가능

 

TRUNCATE table 도 테이블에서 모든 레코드 삭제

- DELETE FROM은 속도가 느림

- TRUNCATE이 전체 테이블 내용 삭제시에는 여러모로 유리

- 단점1. TRUNCATE으로 삭제된 레코드들은 ROLLBACK이 안돼서 트랜잭션을 지원하지 않음.  

               -> 정말 ROLLBACK이 필요없는 경우에만 사용

- 단점2. WHERE을 지원하지 않음 

 

실습: autocommit False, True 설정 바꾸기

autocommit을 False 또는 True로 설정할 수 있는 코드 작성 

import psycopg2

# Redshift connection 함수: autocommit 모드설정하는 함수

def get_Redshift_connection(autocommit):

    host = 

    redshift_user =

    redshift_pass = 

    port = 5439

    dbname = "dev"

    conn = psycopg2.connect("dbname={dbname} user={user} host={host} password={password} port={port}".format(

        dbname=dbname,

        user=redshift_user,

        password=redshift_pass,

        host=host,

        port=port

    ))

    conn.set_session(autocommit=autocommit)

    return conn

autocommit=False로 설정

conn = get_Redshift_connection(False)  #autocommit=False 로 설

cur = conn.cursor()

cur.execute("SELECT * FROM adhoc.keeyong_name_gender;")

res = cur.fetchall() #리스트를 하나씩 출력

for r in res:

  print(r) #레코드 하나씩 출력 

 

cur.execute("DELETE FROM adhoc.keeyong_name_gender;") 

cur.execute("SELECT * FROM adhoc.keeyong_name_gender;")

res = cur.fetchall()

for r in res: 

  print(r)

레코드 없지만, 다른 외부 DB에서 출력하면 여전히 존재함. commit이 안되었기 떄문

cur.execute("COMMIT;")   # conn.commit()는 동일한 결과를 가져옴. cur.execute("ROLLBACK;") conn.rollback()

commit 하여서 변경사항 외부에도 반영

 

INSERT SQL을 autocommit=False로 실행하고 psycopg2로 컨트롤하기

conn = get_Redshift_connection(False)

cur = conn.cursor()

try:

  cur.execute("DELETE FROM adhoc.keeyong_name_gender;")

  cur.execute("INSERT INTO adhoc.keeyong_name_gender VALUES ('Claire', 'Female');")

  conn.commit()

except (Exception, psycopg2.DatabaseError) as error:

  print(error)

  conn.rollback()

finally :

  conn.close()

error가 나면 rollback()해주기