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

트랜잭션(transaction) 처리#1

---

조금 어려운(?) 트랜젝션 처리를 생각해보자. 어렵지는 않은데 매우 자주 보게 되는 실수다. 

예를들어 보자

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

고객계좌 테이블(account) 하나와 모든 이체 이력을 저장 하는 테이블 (TransferHistory)이 있다고 하자. 

CREATE TABLE account (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR (50)    NOT NULL,
    amount DECIMAL (19, 4) NOT NULL
);

CREATE TABLE TransferHistory (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    from_id    INT    NOT NULL,
    to_id      INT    NOT NULL,
    amount     DECIMAL (19, 4) NOT NULL,
    trans_date DATETIME 
);

-- 기본데이터 
INSERT INTO Account(name,amount)
VALUES('철수',1000000), ('영희',90000);

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

헌데 모든 이체 이력은 TransferHistory 에 기록이 남아 있어야 하며, 해당 기록이 없다면 이체는 성공 하지 못하고 취소 되어야 한다. 

start transaction;

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

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

INSERT INTO TransferHistory (from_id, to_id, amount, trans_date)
VALUES(1, 2, 500000, now());

commit;

select * from account;
select * from TransferHistory;

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

start transaction;

.......

commit; or Rollback 

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

이를 PHP Code로 간단히 구현해 보자 

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

코드가 너무 길고,  모듈화 한다고 하여서 TransferHistory를 기록 하는 모듈을 별도로 빼서 2개의 PHP 로 만들었다고 하자. 

[TransactionDemo.php]

<?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) {
            die($e->getMessage());
        }
    }
 
    
     public function transfer($from, $to, $amount) {
 
        try {
            $this->conn->beginTransaction();
 
           // deduct from the transferred account
            $sql_update_from = 'UPDATE accounts
                                   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;
            }

             // transfer history insert
            $transObj = new TransHistory()
            $rtnFlag = $transObj->transferHistory($from, $to, $amount);

            if(!$rtnFlag){
                $this->conn->rollBack();
                return false;
            }

            // add to the receiving account
            $sql_update_to = 'UPDATE accounts
                                 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 and history write successfully';
 
            return true;
        } catch (PDOException $e) {
            $this->conn->rollBack();
            //echo $e->getMessage();
            return false;
        }
        finally{
             $this->conn = null;
        }
    }
}

[TransHistory.php]

<?php

class TransHistory {
 
    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) {
            die($e->getMessage());
        }
    }
 
    
     public function transferHistory($from, $to, $amount) {
 
        try {
            $this->conn->beginTransaction();
 
            // transfer history insert
            $sql_trans_history = "INSERT INTO TransferHistory (from_id, to_id, amount, trans_date)
                                  VALUES(:from, :to, :amount,  now())";
            $stmt = $this->conn->prepare($sql_trans_history);
            $stmt->bindParam(':from', $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 'transfer history write successfully';
 
            return true;
        } catch (PDOException $e) {
            $this->conn->rollBack();
            //echo $e->getMessage();
            return false;
        }
        finally{
             $this->conn = null;
        }
    }
 
}

$obj = new TransactionDemo();
 
// transfer 5000000 from from account 1 to 2
$obj->transfer(1, 2, 500000);
 

위의 코드는 문제가 없어 보이지만 심각한 문제를 가지고 있습니다.

Transaction 이라는 것은 동일 커넥션에서만 유효 합니다. 

즉, 위의 코드는 

1. 철수의 잔액에 - 500000 

update account

     set  amount = amount - 500000

 where id = 1 ;

2. 이력 저장 

INSERT INTO TransferHistory (from_id, to_id, amount, trans_date)

VALUES(1, 2, 500000, now());

3.영희의 잔액에 + 500000 

update account

     set  amount = amount + 500000

 where id = 2 ;

1(성공)->2(성공)->3(실패)-> Rollback 이 되는 경우

이력저장은 RollBack 되지 않습니다. 

즉, 계좌의 금액은 변경되지 않지만, 이체 이력은 취소되지 않고 저장됩니다. 

 

즉,위의 코드는 하나의 트랜잭션을 처리 해야 하는데 2개의 connection을 이용 하여 트랜잭션을 처리 하므로 문제가 될 수 있습니다.

실무에서는 이런 복잡한 트랜잭션을 처리 해야 할 경우가 있습니다.

이런 경우 Connection 객체를 레퍼런스로 넘겨서 아래처럼 처리 하는 방법을 추천합니다.

[TransactionDemo.php]

<?php

class TransactionDemo{

    const DB_HOST = 'localhost';
    ...
 
    ...
             // transfer history insert
            $transObj = new TransHistory()
            $rtnFlag = $transObj->transferHistory($from, $to, $amount, $this->conn);

   ....
 
}

[TransHistory.php]

<?php

class TransHistory {
 
     public function transferHistory($from, $to, $amount, &$conn) {
 
        try {
 
            // transfer history insert
            $sql_trans_history = "INSERT INTO TransferHistory (from_id, to_id, amount, trans_date)
                                  VALUES(:from, :to, :amount,  now())";
            $stmt = $conn->prepare($sql_trans_history);
            $stmt->bindParam(':from', $to);
            $stmt->bindParam(':to', $to);
            $stmt->bindParam(':amount', $amount);
            $affectedRows = $stmt->execute();

            if($affectedRows != 1){
                return false;
            }
 
            // commit the transaction
 
            //echo 'transfer history write successfully';
 
            return true;
        } catch (PDOException $e) {
            return false;
        }
    }
 
}

 

---