[MySQL] 트랜잭션(transaction) 처리

트랜잭션(transaction) 처리

---

트랜젝션이라는 단어를 수 없이 들었을 것이며, 대부분의  개발자들은 잘 처리하리라 생각한다. 다만 DB에서의 트랜잭션 처리는 기본중의 기본이며, 데이터의 뮤결성을 유지 하는 핵심이다. 

MySQL, MariaDB에서 나의 불만 중 하나는 왜 autocommit = on이 default 인가 하는 문제다. 

과거 30년전 오라클을 처음 사용했을때 신나게 insert... 구문을 실행(약 1000번쯤)하고, 얼마 지나지 않아 DB가 먹통이 되어 욕을 바가지로 먹은 기억이 있다. 고참이 간단하게 commit; 한문장을 실행하고 DB가 정상으로 돌아 왔을때 그 놀라움이란...

예를들어 보자

트랜젝션 예를 들때... 항상 그렇듯이 은행 계좌에서 이체를 한다고 하자.

고객계좌 테이블(account) 하나만 예를 들자.

CREATE TABLE account (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR (50)    NOT NULL,
    amount DECIMAL (19, 4) NOT NULL
);
-- 기본데이터 
INSERT INTO account(name,amount)
VALUES('철수',1000000), ('영희',90000);

이제 철수가 영희에게 500,000 을 이체 한다고 하자.

start transaction;

--철수의 잔액에 - 50000 
update account
     set  amount = amount - 500000
 where id = 1 ;

--영희의 잔액에 + 50000 
update account
     set  amount = amount + 500000
 where id = 2 ;

commit;

select * from account;

위의 쿼리 2개면 처리 가능 하며, 정상적으로 처리 되는데 문제가 없다.

start transaction;

.......

commit; or Rollback 

으로 처리 하면 문제가 없다.

그러나,  위의 구문은 id가 잘못 들어가도 에러가 없다. (update 문은 업데이트된 row가 0 이든 1이든 모두 success이다)

예를들어, 영희 id를 3으로 잘못 되어도 에러가 없다. 

이럴 경우 철수의 잔액은 -500000이되지만 영희의 잔액은 변하지 않는다. 

위의 코드는 가능하면 update 된 row수를 체크 하는 것이  안전하다. 

start transaction;

--철수의 잔액에 - 50000 
update account
     set  amount = amount - 500000
 where id = 1 ;

--영희의 잔액에 + 50000 
update account
     set  amount = amount + 500000
 where id = 2 ;

-- update 된 row수 체크
SELECT row_count(); 

commit;

select * from account;

PHP Sample을 통해 Transaction 을 구현해 보자. 

<?php

class TransactionDemo {
 
    const DB_HOST = 'localhost';
    const DB_NAME = 'studyDB';
    const DB_USER = 'root';
    const DB_PASSWORD = '';
    private $conn = null;
 
   public function __construct() {
        // open database connection
        $conStr = sprintf("mysql:host=%s;dbname=%s", self::DB_HOST, self::DB_NAME);
        try {
            $this->conn = new PDO($conStr, self::DB_USER, self::DB_PASSWORD);
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
 
    
     public function transfer($from, $to, $amount) {
 
        try {
            $this->conn->beginTransaction();
 
           // amount send(deduct amount from the account)
            $sql_update_from = 'UPDATE account
                                   SET amount = amount - :amount
                                 WHERE id = :from';

            $stmt = $this->conn->prepare($sql_update_from);
            $stmt->bindParam(':from', $from);
            $stmt->bindParam(':amount', $amount);
            $affectedRows = $stmt->execute();

            if($affectedRows != 1){
                $this->conn->rollBack();
                return false;
            }

 
            // amount receive (add amount to account)
            $sql_update_to = 'UPDATE account
                                 SET amount = amount + :amount
                                WHERE id = :to';
            $stmt = $this->conn->prepare($sql_update_to);
            $stmt->bindParam(':to', $to);
            $stmt->bindParam(':amount', $amount);

            $affectedRows = $stmt->execute();

            if($affectedRows != 1){
                $this->conn->rollBack();
                return false;
            }
 
            // commit the transaction
            $this->conn->commit();
 
            echo 'The amount has been transferred successfully';
 
            return true;
        } catch (PDOException $e) {
            $this->conn->rollBack();
            echo $e->getMessage();
        }
        finally{
             $this->conn = null;
        }
    }
 
 
}
 
// test the transfer method
$obj = new TransactionDemo();
 
// transfer 500000 from from account 1 to 2
$obj->transfer(1, 2, 500000);
 

---