MySQL 저장 프로시저 내에서 ROLLBACK 트랜잭션을 수행하는 방법

ROLLBACK은 트랜잭션이 시작된 이후 데이터베이스에 가해진 모든 변경 사항을 되돌리는 기능입니다. MySQL 저장 프로시저 내에서 ROLLBACK을 수행하려면 반드시 EXIT 핸들러(handler)를 선언해야 합니다. 이 핸들러는 sqlexception 또는 sqlwarning 중 하나에 대해 선언할 수 있습니다.

ROLLBACK에 EXIT 핸들러가 필요한 이유

저장 프로시저 안에서 트랜잭션이 시작된 후 오류나 경고가 발생하면, 별도의 처리기가 없다면 프로시저가 비정상적으로 종료되면서 변경 사항이 그대로 남거나 불완전한 상태로 커밋될 수 있습니다. DECLARE exit handler를 선언해 두면 오류 발생 시 자동으로 ROLLBACK이 실행되어 데이터의 일관성을 유지할 수 있습니다.

예제 준비: 테이블 구조와 데이터

먼저 예제에 사용할 테이블 구조를 살펴보겠습니다. 아래 테이블은 Name 컬럼이 NULL 값을 가질 수 없도록 정의되어 있습니다.

mysql> SHOW CREATE TABLE tbl\G
*************************** 1. row ***************************
        Table: tbl
Create Table: CREATE TABLE `tbl` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `Name` varchar(30) NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

테이블에는 다음과 같은 데이터가 들어 있습니다.

mysql> SELECT * FROM employee.tbl;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Mohan   |
|  2 | Gaurav  |
|  3 | Sohan   |
|  4 | Saurabh |
|  5 | Yash    |
|  6 | Rahul   |
+----+---------+
6 rows in set (0.00 sec)

ROLLBACK을 포함한 저장 프로시저 작성하기

이제 sqlexception과 sqlwarning 두 종류의 EXIT 핸들러를 모두 선언하여, 오류나 경고가 발생하면 트랜잭션 전체를 롤백하는 저장 프로시저를 만들어 보겠습니다.

mysql> DELIMITER //
mysql> CREATE PROCEDURE st_transaction_Rollback()
    -> BEGIN
    -> DECLARE exit handler FOR sqlexception
    -> BEGIN
    -> ROLLBACK;
    -> END;
    -> DECLARE exit handler FOR sqlwarning
    -> BEGIN
    -> ROLLBACK;
    -> END;
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl(name) VALUES();
    -> UPDATE employee.tbl SET name = 'YashPal' WHERE id = 5;
    -> COMMIT;
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> CALL st_transaction_Rollback();
Query OK, 0 rows affected (0.00 sec)

프로시저 생성 후 호출까지 정상적으로 실행된 것처럼 보이지만, 실제로는 첫 번째 INSERT 문에서 NULL 값을 삽입하려다 오류가 발생했습니다.

실행 결과 확인

mysql> SELECT * FROM employee.tbl;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Mohan   |
|  2 | Gaurav  |
|  3 | Sohan   |
|  4 | Saurabh |
|  5 | Yash    |
|  6 | Rahul   |
+----+---------+
6 rows in set (0.00 sec)

위 결과 집합을 보면 UPDATE 문이 적용되지 않고 원래 데이터가 그대로 유지되고 있습니다. 즉, 첫 번째 INSERT 문이 NULL 값 삽입 시도로 인해 오류를 발생시켰고, 이때 선언해 둔 sqlexception EXIT 핸들러가 동작하여 ROLLBACK이 실행되었기 때문입니다. 결과적으로 트랜잭션 내의 모든 변경 사항이 취소되어 데이터베이스는 트랜잭션 시작 이전 상태를 그대로 유지하게 됩니다.

정리

MySQL 저장 프로시저에서 트랜잭션을 안전하게 관리하려면 다음과 같은 패턴을 사용하는 것이 좋습니다.

START TRANSACTION으로 트랜잭션을 시작합니다.
DECLARE exit handler FOR sqlexception과 sqlwarning을 선언하여 오류·경고 발생 시 ROLLBACK이 자동 실행되도록 합니다.
모든 작업이 성공했을 때만 COMMIT이 실행되도록 프로시저 마지막에 배치합니다.

이렇게 하면 일부 쿼리만 부분적으로 적용되는 문제를 방지하고, 데이터 무결성을 확실하게 보장할 수 있습니다.