MySQL 저장 프로시저에서 COMMIT 사용 시 START TRANSACTION 내 쿼리가 하나라도 실패하면 어떻게 될까?

MySQL 저장 프로시저 내에서 START TRANSACTION으로 트랜잭션을 시작한 뒤 COMMIT으로 마무리하는 경우, 쿼리 중 하나가 실패하거나 오류를 발생시키더라도 나머지 쿼리가 정상적으로 실행되었다면 MySQL은 정상 실행된 쿼리의 변경 사항을 그대로 커밋합니다.

즉, 트랜잭션 안에서 개별 쿼리가 실패한다고 해서 자동으로 전체가 롤백되지는 않으며, 별도의 에러 처리 로직이 없다면 성공한 쿼리의 결과만 데이터베이스에 반영됩니다. 다음 예제를 통해 자세히 살펴보겠습니다.

예제

먼저 다음과 같은 데이터를 가진 employee.tbl 테이블이 준비되어 있습니다.

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

이제 트랜잭션 안에서 INSERT와 UPDATE 두 개의 쿼리를 실행한 후 커밋하는 저장 프로시저를 생성합니다.

mysql> Delimiter //
mysql> Create Procedure st_transaction_commit_save()
    -> BEGIN
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl (name) values ('Rahul');
    -> UPDATE employee.tbl set name = 'Gurdas' WHERE id = 10;
    -> COMMIT;
    -> END //
Query OK, 0 rows affected (0.00 sec)

이 프로시저를 호출하면 테이블에 id = 10에 해당하는 행이 존재하지 않기 때문에 UPDATE 쿼리는 오류를 발생시킵니다. 하지만 앞선 INSERT 쿼리는 정상적으로 실행되었기 때문에, COMMIT은 해당 변경 사항을 그대로 테이블에 저장합니다.

mysql> Delimiter ;
mysql> Call st_transaction_commit_save()//
Query OK, 0 rows affected (0.07 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)

실행 결과를 보면 INSERT로 추가된 'Rahul' 데이터(id = 6)가 테이블에 그대로 남아 있는 것을 확인할 수 있습니다. 이처럼 트랜잭션 내 일부 쿼리가 실패하더라도, 성공한 쿼리의 변경 사항은 커밋 시점에 모두 반영됩니다.

참고: 오류 발생 시 전체 롤백하기

트랜잭션 도중 오류가 발생했을 때 지금까지의 모든 변경 사항을 되돌리고 싶다면 에러 핸들러를 함께 사용해야 합니다. 다음과 같이 DECLARE ... HANDLER를 선언하면 예외 발생 시 자동으로 롤백됩니다.

mysql> Delimiter //
mysql> Create Procedure st_transaction_safe()
    -> BEGIN
    -> DECLARE EXIT HANDLER FOR SQLEXCEPTION
    -> BEGIN
    -> ROLLBACK;
    -> END;
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl (name) values ('Rahul');
    -> UPDATE employee.tbl set name = 'Gurdas' WHERE id = 10;
    -> COMMIT;
    -> END //
Query OK, 0 rows affected (0.00 sec)

이렇게 작성하면 UPDATE 쿼리에서 오류가 발생하는 순간 트랜잭션이 롤백되어 INSERT된 데이터까지 저장되지 않으므로, 데이터의 일관성을 안전하게 유지할 수 있습니다.