[SQL]트랜잭션(Transaction) 총정리

Transaction 개념

• 논리적인 이유로 여러 SQL 문을 단일한 작업으로 묶은 것.
  A가 B에게 송금한다고 가정해보자

UPDATE account SET balance = balance - 20000 WHERE id = 'A'

그렇다면 B는 돈을 받는 과정이 필요하다

UPDATE account SET balance = balance + 20000 WHERE id = 'B'

이렇게 두 개의 SQL이 필요한데, 위 SQL 문만 진행되고 밑에는 진행되지 않아서 20,000원이 A 계좌에서는 나가지만 B에는 안들어오는 문제가 생긴다.

그래서 이런 단일한 SQL문을 묶어서 하나의 단위로 만들어줘야 하는데 이걸 Transaction이라고 한다.

• SQL문 중 단일한 일부만 성공해서 DB에 반영되지 않는다.

Transaction 구현

사전지식

START TRANSACTION;
UPDATE account SET balance = balance - 20000 WHERE id = 'A'
UPDATE account SET balance = balance + 20000 WHERE id = 'B'
COMMIT;

MySQL은 autocommit이 있어서 SQL문을 하나씩 돌릴 때마다 자동 저장된다. 만약 문제있으면 rollback이 된다.
하지만 START TRANSACTION이라고 SQL문을 시작하게 되면 autocommit이 자동으로 꺼진다는 것을 잊지말자.

자바로 구현하는 Transaction

public void transfer(String fromId, String toId, int amount) {
    Connection connection = null;
    
    try {
        // get DB connection
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/yourdatabase", "username", "password");
        
        connection.setAutoCommit(false); // means START TRANSACTION
        
        // update at fromId
        String updateFromAccountSQL = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
        try (PreparedStatement updateFromAccountStmt = connection.prepareStatement(updateFromAccountSQL)) {
            updateFromAccountStmt.setInt(1, amount);
            updateFromAccountStmt.setString(2, fromId);
            updateFromAccountStmt.executeUpdate();
        }
        
        // update at toId
        String updateToAccountSQL = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
        try (PreparedStatement updateToAccountStmt = connection.prepareStatement(updateToAccountSQL)) {
            updateToAccountStmt.setInt(1, amount);
            updateToAccountStmt.setString(2, toId);
            updateToAccountStmt.executeUpdate();
        }
        
        connection.commit();
    } catch (Exception e) {
        try {
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        e.printStackTrace();
    } finally {
        try {
            if (connection != null) {
                connection.setAutoCommit(true);
                connection.close();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
}

Try 문으로 잘 되면 commit을 하고 catch에서는 예외가 발생한다면 Exception을 만들면 되는데
특히 Finally에서 Transaction을 시작하면서 오토커밋을 껐기 때문에 다시 True를 사용해서 켜주어야 한다.

Transaction의 속성 ACID

1. Atomicity

• 원자성은 하나의 트랜잭션에 속해있는 모든 작업이 전부 성공하거나 전부 실패해서 결과를 예측할 수 있어야 한다는 것으로 ALL or NOTHING라고 한다.
• 계좌 이체를 예로, 출금은 성공했는데, 입금이 실패한다면 이 전체 송금과정을 실패해서 ROLLBACK 해야한다는 것.

2. Consistency

• 데이터베이스의 상태가 일관되어야 한다는 성질. INT 칼럼에 CHAR을 쓴다던지, 계좌 주의 이름 없이 계좌를 만든다던지 이런 것들을 의미

3. Isolation

• J가 H에게 송금했는데, 이 20만원을 받기 위해 먼저 계좌의 잔액을 읽고, 20만원을 더해 적는 과정이 필요하다.
  근데 20만원을 보내고 다시 또 30만원을 보내는 과정에서 문제가 생긴다.

• Read - Write의 과정에서 Read Read - Write Write가 되면, H의 잔고가 200만원인걸 두 번 Read 하게 되어서 200 + 20, 200 + 30을 하게되어 250이 아니라 230을 받게 된다.

• 하나의 SQL 조건이 끝나고 다음 조건을 넣어야 하는데 SQL 구동 중간에 또 다른 SQL 문이 들어가서는 안된다. 그걸 막는게 Isolation이다.

4. Durability

• Commit된 Transaction은 DB에 영구적으로 저장한다.
• DB 시스템에 문제 생겨도 commit된 transaction은 DB에 남아 있는다.
• 기본적으로 Transaction의 Durability는 DBMS가 보장한다.