MariaDB 강좌 / 트랜잭션 시작하기: InnoDB·COMMIT·ROLLBACK

트랜잭션은 여러 데이터 변경을 하나의 확정 단위로 묶는 기능입니다. InnoDB에서 START TRANSACTION으로 시작해 결과가 맞으면 COMMIT, 잘못되었으면 ROLLBACK으로 끝내며, 같은 연결과 아직 확정하지 않은 범위라는 조건을 지켜야 합니다.

모두 성공해야 하는 작업 묶음 정하기

두 잔액 숫자를 함께 바꾸는 학습 예제로 트랜잭션의 확정·취소를 비교합니다. 한쪽만 바뀐 상태를 남기지 않으려는 것이 핵심입니다. 실제 결제·송금 시스템의 구현을 제공하는 예제는 아니며, 그러한 업무에는 원장·중복 요청 방지·권한·감사 등 별도 설계가 필요합니다. 실제 수정·삭제 결과를 취소하는 예는 UPDATE·DELETE를 실행하기 전 대상 행 확인하기, 동시 연결의 충돌은 두 연결로 격리 수준·잠금·교착 상태 확인하기에서 확인합니다. 호출자가 확정을 맡는 서버 작업은 저장 프로시저·함수·트리거의 차이와 사용 방법에서도 같은 경계를 사용합니다.

InnoDB 전용 테이블과 초기값 준비하기

course_user로 shopcourse에 접속해 서버·DB를 확인합니다. accounts_35_lab이 없는지 확인하고 아래 전체 코드를 실행합니다. 같은 이름이 있으면 먼저 정의·데이터를 확인하며 기존 자료를 덮어쓰지 않습니다.

USE shopcourse;
CREATE TABLE accounts_35_lab (
 account_id INT PRIMARY KEY,balance DECIMAL(12,2) NOT NULL CHECK(balance>=0)
) ENGINE=InnoDB;
INSERT INTO accounts_35_lab VALUES(1,10000),(2,5000);

SELECT @@autocommit,@@in_transaction;
SHOW CREATE TABLE accounts_35_lab;

엔진은 InnoDB, 초기값은 1번 10000.00·2번 5000.00입니다. 명시적 트랜잭션의 효과를 비교하기 위해 두 연결을 섞지 말고 한 연결에서 순서대로 실행합니다. 테이블 생성은 시작 전 끝냅니다.

변경 후 확인하고 ROLLBACK으로 취소하기

START TRANSACTION;
SELECT account_id,balance FROM accounts_35_lab
WHERE account_id IN (1,2) ORDER BY account_id FOR UPDATE;
UPDATE accounts_35_lab SET balance=balance-3000
WHERE account_id=1 AND balance>=3000;
SELECT ROW_COUNT() AS debit_rows;

debit_rows가 1일 때만 계속합니다. 0이거나 오류가 나면 이 연결에서 ROLLBACK;을 실행하고 아래의 입금 UPDATE를 실행하지 않습니다.

UPDATE accounts_35_lab SET balance=balance+3000 WHERE account_id=2;
SELECT ROW_COUNT() AS credit_rows;

credit_rows도 1이어야 합니다. 예상과 다르면 ROLLBACK;으로 두 변경을 취소하고 중단합니다. 둘 다 1일 때 다음 조회로 잔액을 대조합니다.

SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

초기값 그대로라면 debit_rows와 credit_rows는 각각 1이고 잔액은 7000.00·8000.00입니다. 각 변경 직후 예상 건수가 아니면 다음 변경을 진행하지 말고 ROLLBACK합니다. 이 문장 묶음을 임의 입력에 대한 자동 처리 코드로 그대로 사용하면 안 되며, 프로그램에서는 오류·건수 검사에 따른 분기가 필요합니다.

ROLLBACK;
SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

취소 후 10000.00·5000.00으로 돌아옵니다. 트랜잭션 안에서 본 변경과 확정된 변경을 구분해야 합니다. 결과가 다르면 같은 연결인지, 도구가 중간에 COMMIT하지 않았는지 확인합니다.

동일한 변경을 반복하고 COMMIT으로 확정하기

앞 절의 출금·입금·최종 조회 블록을 같은 순서로 다시 실행하고 각 행 수를 확인합니다. 이번에는 두 행 수와 잔액이 맞을 때만 ROLLBACK 대신 아래 COMMIT을 실행합니다.

COMMIT;
SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

확정 후 값은 7000.00·8000.00이며 같은 초기값으로 되돌리는 ROLLBACK은 더 이상 작동하지 않습니다. 재실습은 별도 초기화 절차로 하며, 업무 데이터에서 반대 숫자를 더하는 작업을 복구와 동일시하지 않습니다. START TRANSACTION과 확정 단위를 참고하세요.

오류·잠금·암묵적 확정의 경계 확인하기

문장 하나의 오류가 언제나 트랜잭션 전체를 자동 취소하는 것은 아닙니다. 오류 종류와 드라이버 처리를 확인하고 프로그램에서는 전체 작업의 실패 처리를 명시합니다. 잠금을 오래 잡은 채 사용자 입력을 기다리지 않고 가능한 한 짧게 처리합니다.

CREATE TABLE·ALTER TABLE·DROP TABLE·TRUNCATE 같은 구조 작업은 암묵적 확정 등 다른 규칙을 가지므로 이 실습 안에 섞지 않습니다. MyISAM처럼 비트랜잭션 엔진을 InnoDB 예제와 같은 취소 동작으로 생각해서도 안 됩니다. 외부 API·파일 전송은 DB ROLLBACK만으로 취소되지 않습니다.

연결을 정리하고 새 실습 준비하기

실행이 중간에 멈췄다면 현재 연결에서 ROLLBACK으로 미확정 범위를 정리하고 @@in_transaction을 확인합니다. 보관할 자료가 없는 accounts_35_lab만 삭제해 처음부터 재실습할 수 있습니다. 삭제는 구조 작업이며 취소용 트랜잭션 밖에서 합니다.

ROLLBACK;
DROP TABLE shopcourse.accounts_35_lab;