데이터베이스에서의 Transaction (트랜잭션)

Transaction(트랜잭션) 이란?

데이터베이스 트랜잭션(Transaction)은 데이터베이스에서 논리적인 작업 단위를 묶어서, 하나의 논리적인 작업으로 처리하기 위한 것이다. 트랜잭션은 여러 개의 쿼리나 명령어를 수행할 때, 그룹 단위로 묶어서 하나의 작업으로 처리한다. 따라서 작업이 끝까지 수행되거나, 전혀 수행되지 않도록 보장(All or Nothing)하는 것이 특징이다.

 

은행 계좌의 데이터베이스를 생각해 봤을 때, "Alice가 Bob에게 100$ 이체"라는 하나의 event에 대해서 아래 4개의 update 쿼리가 필요하다고 하자.

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice'; --잔액 테이블에서 앨리스 잔액 - 100 을 한다.
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice'); --거래내역 테이블에 반영한다.
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob'; --잔액 테이블에서 밥 잔액 + 100 을 한다.
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob'); --거래내역 테이블에 반영한다.

이때 트랜잭션이 지정되어 있지 않다면, 실행 도중 작업이 실패 했을때 4개의 Update 구문 중 일부만 성공한 상태가 된다. 그러면 Alice의 계좌에서 " - 100 $" 는 처리되었지만, 막상 Bob은 100$를 전달 받지 못하는 경우가 생긴다.

이러한 사태를 막기 위해서 하나의 작업 묶음이 전부 처리되거나 전부 취소가 될 수 있도록 트랜잭션(Transaction) 이 필요하다.

 

트랜잭션은 주로 데이터베이스에서 데이터의 일관성과 무결성을 보장하기 위해 사용된다. 트랜잭션을 사용하면 여러 개의 작업을 하나의 작업으로 묶어서 실행하고, 이 작업이 완전하게 실행되거나, 전혀 실행되지 않도록 보장할 수 있으므로, 데이터의 일관성과 무결성을 보다 안정적으로 유지할 수 있다.

Transaction의 속성

트랜잭션은 ACID라는 4가지 속성을 가지고 있다.

• 원자성(Atomicity): 트랜잭션에 속한 각각의 작업을 하나의 단위로 취급하여, 트랜잭션의 모든 작업이 완료되거나, 전혀 수행되지 않도록 보장한다. 즉, 트랜잭션의 모든 작업이 실행되어야만 결과를 저장하며, 하나라도 실패하면 이전 상태로 롤백된다.
• 일관성(Consistency): 트랜잭션의 작업처리 전후가 항상 일관성있는 데이터베이스 상태로 유지할 수 있어야 한다. 트랜잭션 수행이 보존해야 할 일관성은 기본 키, 외래 키 제약과 같은 명시적인 무결성제약 조건들뿐만 아니라, A에서 B로 돈을 이체할 때 A와 B 계좌의 돈의 총합이 같아야 한다는 사항과 같은 비명시적인 일관성 조건들도 있다.
• 고립성(Isolation): 트랜잭션은 서로 간섭하지 않으며, 동시에 실행될 경우에도 각각의 트랜잭션은 독립적으로 처리된다. 즉, 각각의 트랜잭션은 다른 트랜잭션의 연산 결과를 참조할 수 없다. 따라서 트랜잭션이 실행하는 도중에 변경한 데이터는 이 트랜잭션이 완료될 때까지 다른 트랜잭션에서 참조할 수 없다.
• 지속성(Durability): 트랜잭션이 정상적으로 종료된 다음에는 그 결과가 영구적으로 반영되어야 한다. 즉, 트랜잭션의 성공 결과값은 데이터베이스 장애 발생 후에도 변함없이 보관되어야 한다.

---

Transaction 처리

SQL에서 트랜잭션은 Autocommit 모드 on/off 여부에 따라 다르게 설정 될 수 있다.

Autocommit이 활성화가 되었다는 것은 각 SQL 문이 실행되면서 자동으로 커밋되어 데이터베이스에 즉시 적용된다는 것이다. 반대로, Autocommit 이 비활성화 되어 있으면 SQL문이 실행되는것 처럼 보여도 직접 commit()을 수행하기 전까지는 실제 데이터베이스에 물리적으로 적용이 되지 않는다.

• autocommit이 활성화 되었을 때(autocommit = True)
  "BEGIN", "COMMIT" 혹은 "END" 문을 사용한다.

import psycopg2

# PostgreSQL 데이터베이스에 연결
conn = psycopg2.connect(
    dbname="mydatabase",
    user="myusername",
    password="mypassword",
    host="localhost",
    port="5432"
)

# autocommit 모드 활성화
conn.set_session(autocommit=True)

# BEGIN 문을 사용하여 트랜잭션 시작
cur = conn.cursor()
cur.execute("BEGIN")

# SQL 쿼리 실행
cur.execute("DELETE FROM mytable")
cur.execute("INSERT INTO mytable (column1, column2) VALUES (%s, %s)", ("value1", "value2"))

# COMMIT 문을 사용하여 트랜잭션을 커밋
cur.execute("COMMIT")

# 연결 종료
conn.close()

 

아래는 try except 문을 사용하여 좀 더 안정적인 코드로 변경 한 것이다.

import psycopg2

# PostgreSQL 데이터베이스에 연결
conn = psycopg2.connect(
    dbname="mydatabase",
    user="myusername",
    password="mypassword",
    host="localhost",
    port="5432"
)

# autocommit 모드 활성화
conn.set_session(autocommit=True)

# BEGIN 문을 사용하여 트랜잭션 시작
cur = conn.cursor()

#Try except 문 사용
try:
	cur.execute("BEGIN")
	cur.execute("DELETE FROM mytable")
	cur.execute("INSERT INTO mytable (column1, column2) VALUES (%s, %s)", ("value1", "value2"))
	cur.execute("COMMIT")
except (Exception, psycopg2.DatabaseError) as error:
    print(error)
	cur.execute("ROLLBACK;")
finally :
	conn.close()

• autocommit이 활성화 되지 않았을 때(autocommit = False)
  "BEGIN"문을 사용하지 않아도 된다.

import psycopg2
# PostgreSQL 데이터베이스에 연결
conn = psycopg2.connect(
    dbname="mydatabase",
    user="myusername",
    password="mypassword",
    host="localhost",
    port="5432"
)
# autocommit 모드를 비활성화
conn.set_session(autocommit=False)
cur = conn.cursor()

# SQL 쿼리를 실행
cur.execute("DELETE FROM mytable")
cur.execute("INSERT INTO mytable (column1, column2) VALUES (%s, %s)", ("value1", "value2"))

# 트랜잭션을 커밋합니다
conn.commit()
# 연결을 닫습니다
conn.close()

 

참고:

https://ko.wikipedia.org/wiki/ACID

https://www.databricks.com/kr/glossary/acid-transactions

https://www.postgresql.kr/docs/9.6/tutorial-transactions.html