MySQL - 트랜잭션과 COMMIT, ROLLBACK

1. 트랜잭션

• 데이터베이스에서 여러 SQL 문을 하나의 작업 단위로 묶어 실행하는 논리적 작업 단위이다.
• 모든 작업이 성공해야 적용되며, 하나라도 실패하면 전체가 취소된다.( "전부 성공" 하거나 "전부 실패" 보장)
• 부분 성공/실패로 인한 오류를 방지해 데이터 무결성이 유지된다. 
• 실생활 은행 송금 예시 : A가 B에게 5만 원 송금 →  A 계좌에서 5만 원 감소, B 계좌에서 5만 원 증가 → 두 작업이 모두 성공해야 올바른 데이터가 저장된다.

 

 1-1. 트랜잭션이 없으면 생기는 문제( 은행 송금 예시)

• 부분 완료: 출금은 성공했지만 입금이 실패해 돈이 사라진다. (예: A 계좌에서 10만 원 차감 후 B 계좌 입금 실패)
• 데이터 불일치: 잔고가 음수가 되거나 송금 데이터가 일관성 없다.
• 동시 접근 충돌: 여러 송금이 동시에 처리되어 잔고 계산 오류가 발생한다. (예: A 계좌에서 B와 C로 동시 송금)
• 데이터 손실: 시스템 장애로 송금 기록이 사라진다.
• 결과: 재무적 손실과 은행 신뢰도가 하락한다.

 

2. COMMIT

• 트랜잭션의 모든 변경사항을 데이터베이스에 영구 저장한다.
• 모든 작업이 성공적으로 완료되었을 때 사용된다.
• 예시: 송금이 정상적으로 처리된 후 데이터베이스에 반영

 

3. ROLLBACK

• 트랜잭션의 모든 변경사항을 취소하고 이전 상태로 복구한다.
• 오류 발생 또는 작업 실패 시 사용된다.
• 예: 잔액 부족으로 송금이 불가능할 때

 

4. MySQL 트랜잭션 명령어

• START TRANSACTION: 트랜잭션 시작
• COMMIT: 변경사항 저장
• ROLLBACK: 변경사항 취소

---

※  MySQL 8.0 팁: InnoDB는 자동 커밋(autocommit)이 기본으로 활성화되어 있다. 트랜잭션을 수동으로 제어하려면 START TRANSACTION을 사용해야한다.

-- 트랜잭션이란?
-- 트랜잭션은 데이터베이스에서 여러 SQL 문을 하나의 작업 단위로 묶어서 실행합니다.
-- 모든 작업이 성공해야 적용되며, 하나라도 실패하면 전체가 취소됩니다.

-- 즉, 트랜잭션은 데이터베이스에서 하나의 논리적인 작업의 단위를 말한다.

-- 이체 기능(서비스 로직(기능))
-- 트랜잭션 스타트
-- 1. 내 계좌에 잔액여부를 확인해야 한다 -- select
-- 2. 이체 하는 통장에 잔액 금액 수정 -- update
-- 3. 내 계좌에 잔액을 수정해야 한다. -- update

-- 물리적인 메모리 공간에 commit 처리를 한다(영구히 저장)
-- 만약 하나라도 실패 한다면 rollback

 

• 기본 구조

-- 기본 문법 형태
start transaction;
-- SQL 구문 1
-- SQL 구문 2
-- SQL 구문 3
commit; -- 영구히 저장
-- 만약 실패 했을 시
rollback;

 

• 데이터베이스와 테이블 생성하기

create database mybank;
create table accounts(
	account_id int primary key,
    name varchar(50),
    balance int
);
insert into accounts values (1,'홍길동',100000),(2,'이순신',50000);

 

• 트랜잭션 성공 사례

-- 트랜잭션 시작
-- 1번 홍길동 계좌에서 2번 이순신 계좌로 3만원 이체

-- 자동 커밋으로 변경 됨
-- set autocommit = 1;

-- 자동 커밋을 수동 커밋으로 변경하는 명령어
set autocommit = 0;
-- 트랜잭션 성공 사례
start transaction;
update accounts set balance = balance - 30000 where account_id = 1;
update accounts set balance = balance + 30000 where account_id = 2;
commit;

 

• 트랜잭션 실패 사례

-- 트랜잭션 실패 사례 
set autocommit = 0;
start transaction;
update accounts set balance = balance - 30000 where accounts_id = 1;
update accounts set balance11 = null where accounts_id = 2;
rollback;

 

• 동적으로 쿼리 작성하기

-- MySQL 워크벤치에서 트랜잭션 사용해보기

-- 홍길동 계좌에서 이순신 계좌로 5만원 이체 요청
set autocommit = 0;
start transaction;

-- 홍길동 계좌에 잔액 확인
set @bal = (select balance from accounts where account_id = 1);

-- 잔액이 충분하다면 true, 아니면 false
set @is_valid = (@bal >= 50000);

-- 홍길동 계좌에 잔액 수정 처리
update accounts
set balance = balance - 50000 
where account_id = 1 and @is_valid;

-- 이순신 계좌의 잔액을 수정 처리
update accounts
set balance = balance + 50000 
where account_id = 2 and @is_valid;

select * from accounts;


-- if(@is_valid, commit, rollback);
-- commit where @is_valid;
-- rollback where @is_valid;