트랜잭션

트랜잭션이란?

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
• DDL, DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음
• BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
• ROLLBACK

트랜잭션 예시

• 계좌 이체: 인출과 입금의 두 과정으로 이뤄짐
• 만일 인출은 성공했는데 입금이 실패하면 성립되지 않음
• 이 두 과정은 동시에 성공하던지 실패해야함 -> Atomic하다는 의미
• 이런 과정들을 트랜잭션으로 묶어주어야함

트랜잭션 로직

BEGIN;
  A의 계좌로부터 인출;
  B의 계좌로 입금;
END;

• END와 COMMIT은 동일
• 만일 BEGIN전의 상태로 돌아가고 싶다면 ROLLBACK 실행
• 이 동작은 commit mode에 따라 달라짐
• 하나라도 실패하면 BEGIN전으로 돌아감

커밋 모드: autocommit

• autocommit = True
  - 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐. 이를 커밋 된다고 함
  - 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리
• autocommit = False
  -모든 레코드 수정/삭제/추가 작업이 COMMIT 호출될 때까지 커밋되지 않음

SQL 프로그램들의 커밋

• 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_name(not DELETE*FROM)

• 테이블에서 모든 레코드를 삭제
• VS. DROP TABLE table_name
• WHERE 사용해 특정 레코드만 삭제 기능:
  - DELETE FROM raw_data.user_session_channel WHERE channel = 'Google'

TRUNCATE table_name

• DELETE FROM은 속도가 느림
• TRUNCATE이 전체 테이블의 내용 삭제시에는 여러모로 유리
• 하지만 두가지 단점이 존재
  - TRUNCATE는 WHERE을 지원하지 않음
  - TRUNCATE는 Transaction을 지원하지 않음

SQL 코드로 다뤄보기

커밋

%%sql

DROP TABLE IF EXISTS adhoc.keeyong_name_gender;
CREATE TABLE adhoc.keeyong_name_gender (
  name varchar(32),
  gender varchar(16)
);
INSERT INTO adhoc.keeyong_name_gender VALUES ('Ben', 'Male'), ('Maddie', 'Female');

• 테이블과 각 컬럼, 컬럼속성을 만들어주고 데이터 밸류를 넣어줌

• 정상적으로 테이블과 밸류가 만들어짐

• 레드쉬프트를 커넥션 해줌

conn = get_Redshift_connection(False)
cur = conn.cursor()

• 인자를 통해 오토커밋 여부를 설정

cur.execute("SELECT * FROM adhoc.keeyong_name_gender;")
res = cur.fetchall()
for r in res:
  print(r)

• 데이터베이스에서 데이터웨어하우스로 잘 가져와짐

• 오토커밋이 꺼져있기 때문에 딜리트, 인서트 쿼리를 전달해서 수행하더라도 본래 데이터베이스엔 영향이 없음

cur.execute("COMMIT;") 

• 커밋을 해주면 그제서야 지우고 인서트한 내용들이 반영됨

rollback

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()

• try를 통해서 두개의 쿼리를 날리고 하나라도 에러가 발생하면 except 안에 rollback되도록 해줌

conn = get_Redshift_connection(True)
cur = conn.cursor()

cur.execute("BEGIN;")
cur.execute("DELETE FROM adhoc.keeyong_name_gender;")
cur.execute("INSERT INTO adhoc.keeyong_name_gender VALUES ('Benjamin', 'Male');")
cur.execute("END;")

• try문을 사용하지 않더라도 잘된 쿼리라면 문제가 없는데

• 위처럼 잘못된 커리를 날리게 되면 이미 날린 쿼리에 대해선 데이터베이스에 반영이 됐기 때문에 롤백이 안됨