postgreSQL Transaction 트랜잭션, ACID

유튜브 채널 쉬운코드의 트랜잭션 영상을 참고하여 작성된 게시글입니다.

---

Transaction 트랜잭션

여러 개의 SQL 작업을 하나의 묶음으로 처리하는 것

 

A의 계좌 정보 테이블과 B의 계좌정보 테이블이 있다고 하자.

A가 B에게 20만원을 계좌이체로 보낸다고 하면 아래와 같은 변경이 일어날 것이다.

1. A계좌에서 20만원 감소
2. B계좌에서 20만원 증가

이를  SQL문으로 써보면 이렇다.

UPDATE account SET balance = balance - 200000 WHERE id = 'A';
UPDATE account SET balance = balance + 200000 WHERE id = 'B';

 

그런데 만약 만약 A계좌의 SQL문이 실패한다면 B계좌에서만 20만원이 생겨나게 되고, 반대라면 A의 게좌에서만 20만원이 사라진다. 즉 두가지 SQL문이 전부 성공해야만 "계좌 이체" 라는 작업이 성공한 것. 이런 작업을 DB에서 Transaction 이라고 부른다.

Transaction

• 단일한 논리적인 작업 단위
• 논리적인 이유로 여러 SQL문을 단일 작업으로 묶어서 나뉘어질 수 없게 만든 것
• SQL문의 일부만 성공해서 DB에 반영되는 일은 일어나지 않는다.

예제

아래와 같은 accounts 테이블에서 A가 B에게 20만원을 이체하는걸 트랜잭션으로 만들어보자.

BEGIN; -- 트랜잭션 시작

UPDATE accounts 
SET balance = balance - 20 
WHERE user_name = 'A';

UPDATE accounts
SET balance = balance + 20
WHERE user_name = 'B';

COMMIT; -- 트랜잭션 끝

begin이후에 에러가 발생하면 ROLLBACK; 을 실행해줘야 한다. begin 이후 에러가 발생하면 ROLLBACK으로 BEGIN시점으로 돌아갈 수 있다. COMMIT을 실행하면 BEGIN부터 지금까지 작업한 내용을 DB에 영구적으로 저장하고 트랜잭션을 종료한다.

잘 됐다!

AUTOCOMMIT

각각의 SQL문을 자동으로 transaction 처리 해주는 개념.

SQL문이 성공적으로 실행하면 자동으로 COMMIT 한다.

실행 중에 문제가 있었다면 알아서 ROLLBACK 한다.

postgreSQL의 경우 psql, dbeaver등 cli, gui로 접속하면 보통 autocommit이 켜져있다. 켜고 끄는건 접속툴의 기능을 사용한다.

 

하지만 임베디드 postgreSQL에서는 기본적으로 자동커밋모드가 아니다. 필요할 때 명시적으로COMMIT을 실행해야한다. 

임베디드란 C++, Java같은 프로글매 안에서SQL을 넣어 실행하는 방식을 말한다. 때문에 c++코드로 sql문장을 10번 실행해도 메모리에만 적어두고 실제 db파일 데이터가 영구적으로 저장되지 않는다고 함. (테스트로 확인을 해봐야 할 문제같다.)

임베디드 환경에서도 ECPG라는 c언어 파일 전처리기를 사용해 오토커밋을 on으로 바꿔줄 수 있다.

 

좌 dbeaver 우 psql

autocommit이 있다면 A계좌를 수정했을때도 오토커밋이 되어서 ROLLBACK을 해도 이미 A계좌가 수정된 후 아닌가? 싶을 수 있지만 BEGIN으로 트랜잭션을 시작하면 오토커밋이 off되고, COMMIT/ROLLBACK과 함께 트랜잭션이 종료되면 원래 autocommit 상태로 돌아간다. 

---

일반적인 transaction의 사용패턴

1. BEGIN
2. SQL문들로 로직 수행
3. SQL문들이 문제없이 동작했다면 COMMIT
4. 문제가 발생했다면 ROLLBACK

임베디드 환경(c)에서의 transaction

postgreSQL 임베디드 환경에서는 오토커밋이 꺼져있기 때문에 위 과정에서 BEGIN부분만 빼면 된다.

아무튼 코드를 살펴보면 아래와 같다 (ai가 짜준 c언어 코드)

int transfer_money(char* from_user, char* to_user, int amount) {
    /* 1. 호스트 변수 선언 
       C의 변수를 SQL에서 쓰려면 EXEC SQL BEGIN/END DECLARE SECTION 안에 선언해야 함
    */
    EXEC SQL BEGIN DECLARE SECTION;
        char* host_from = from_user;
        char* host_to = to_user;
        int   host_amount = amount;
    EXEC SQL END DECLARE SECTION;

    /* 2. 에러 핸들링 설정: SQL 에러 발생 시 자동으로 error_label로 점프 */
    EXEC SQL WHENEVER SQLERROR GOTO error_label;

    /* 3. 트랜잭션 시작 
       Embedded SQL은 첫 번째 SQL 실행 시 자동으로 트랜잭션을 시작함 (Manual Commit 모드 가정)
    */

    // A의 계좌에서 돈 차감
    EXEC SQL UPDATE accounts 
             SET balance = balance - :host_amount 
             WHERE user_name = :host_from;

    // B의 계좌에 돈 추가
    EXEC SQL UPDATE accounts 
             SET balance = balance + :host_amount 
             WHERE user_name = :host_to;

    /* 4. 모든 작업이 성공했다면 영구 저장 */
    EXEC SQL COMMIT WORK;
    printf("송금 성공: %s -> %s (%d원)\n", from_user, to_user, amount);
    return 1; 

error_label:
    /* 5. 에러 발생 시 (예: 잔액 부족 제약조건 위반, DB 연결 끊김 등) 모든 작업 취소 */
    EXEC SQL ROLLBACK WORK;
    printf("송금 실패: 모든 작업이 취소되었습니다.\n");
    return 0; 
}

문법이 이상해보이지만 EXEC SQL 어쩌구 들은 ECPG 라는 전용 전처리기를 통해 알아서 c코드로 수정된다고 한다. (해보진 않았음)

 

만약 임베디드 환경에서도 오토커밋을 켜놓고싶다면 아래 코드를 실행시켜야한다. (ECPG)

EXEC SQL SET AUTOCOMMIT TO ON;

 

ACID

트랜잭션이 어떤 속성을 가져야 하는지 나타내는 개념들

• Atomicity - 모두 성공하거나 모두 실패해야함
• Consistency - 일관성. 트랜잭션은 consistent 상태에서 consistent 상태로 바꿔줘야 한다.
  DBMS가 commit전에 db에 정의된 룰을 위반했는지 알려주지만, 어플리케이션 관점에서도 확인해줘야 한다.
• Isolation - 격리. 여러 트랜잭션이 동시에 실행되면 이상현상이 발생한다. 때문에 여러 트랜잭션이 동시에 실행될 때도 혼자 실행되는 것 처럼 만들어줘야 한다. DBMS는 여러 종류의 isolation level을 제공한다. level 이 높을수록 엄격하게 격리해서 다른 트랜잭션으로부터 영향받을 경우 줄어듦. 대신 동시 실행을 못해서 db 서버 성능 떨어진다.
• Durability - 영구성. 커밋된 트랜지션은 DB에 영구적으로 저장된다.
  DB 시스템에 문제(power fail or db crash)가 생겨도 커밋된 트랜지션은 DB에 남아있는다. (하드에 저장된다는 말) 기본적으로 DBMS가 보장한다.

많은 부분을 DBMS가 해주지만 개발자가 신경써야 할 부분들도 있다. 특히 Isolation 부분이 빡센듯 하다.