9. 트랜잭션
1) 트랜잭션이 필요한 이유
이 쇼핑몰 스키마에서 “주문 1건”은 실제로 여러 작업으로 이루어진다.
orders에 주문 헤더 생성order_items에 주문 상세 여러 건 생성products.stock재고 차감orders.total_amount계산/반영payments결제 생성/승인
이 작업 중 하나라도 실패하면 서비스 데이터가 깨진다.
예시(깨지는 케이스):
orders는 만들어졌는데payments가 실패 → “주문은 있는데 결제가 없음”- 재고 차감만 되고 주문 생성 실패 → “재고는 줄었는데 주문이 없음”
- 주문상세 일부만 저장 → “주문 금액이 틀림”
트랜잭션은 여러 SQL을 하나의 ‘원자적 작업’으로 묶어, 성공이면 전부 반영, 실패면 전부 취소하게 해준다.
2) 트랜잭션 핵심 개념
A (Atomicity, 원자성)
- 주문 처리 5단계 중 1개라도 실패하면 전체 롤백
- “부분 성공” 금지
C (Consistency, 일관성)
- FK, UNIQUE, CHECK(비슷한 제약) 등을 지키면서 규칙 위반 불가
- 예: 존재하지 않는 order_id로 결제 생성 불가
I (Isolation, 격리성)
- 동시에 여러 사용자가 주문해도 서로의 중간 상태가 보이지 않게
- “동시성 문제(더티 리드/팬텀/갱신 손실)” 방지
D (Durability, 지속성)
- COMMIT 된 주문/결제는 서버가 죽어도 남아야 함
3) 실습 준비: 오토커밋 확인
MySQL은 기본이 autocommit=1인 경우가 많다. (각 SQL이 자동 COMMIT)
SHOW VARIABLES LIKE 'autocommit';autocommit = ON이면, 트랜잭션을 명시적으로 시작해야 한다.
4) 실습 1: 트랜잭션 기본 TCL(COMMIT / ROLLBACK)
4-1. COMMIT
START TRANSACTION;
UPDATE products
SET stock = stock - 1
WHERE product_id = 1;
COMMIT;
COMMIT을 하면 트랜잭션이 종료 되므로 더 작업을 하고 싶을땐 다시START TRANSACTION을 선언해야한다.
4-2. ROLLBACK
START TRANSACTION;
UPDATE products
SET stock = stock - 1
WHERE product_id = 2;
ROLLBACK; -- 방금 UPDATE 취소검증:
SELECT product_id, name, stock
FROM products
WHERE product_id IN (1,2);5) 실습 2: “주문 생성”을 하나의 트랜잭션으로 처리하기
목표
고객 1명이 상품 2개를 주문하고 결제까지 생성하는 과정을 한 트랜잭션으로 묶는다.
- 상품 1: product_id=1, qty=2
- 상품 2: product_id=2, qty=1
- 주문 상태: PAID
- 결제 상태: APPROVED
5-1. 트랜잭션 스크립트 (성공 케이스)
START TRANSACTION;
-- 1) 주문 헤더 생성
INSERT INTO orders (customer_id, status, ordered_at, total_amount)
VALUES (1, 'PAID', NOW(), 0);
-- 방금 생성된 주문 ID 확보
SET @oid := LAST_INSERT_ID();
-- 2) 주문 상세 생성 (단가를 products에서 가져와 “그 시점 가격”으로 고정하는 게 포인트)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT @oid, p.product_id, v.qty, p.price
FROM products p
JOIN (
SELECT 1 AS product_id, 2 AS qty
UNION ALL
SELECT 2, 1
) v ON v.product_id = p.product_id;
-- 3) 재고 차감 (재고 부족 방지 조건 포함)
UPDATE products
SET stock = stock - CASE product_id
WHEN 1 THEN 2
WHEN 2 THEN 1
ELSE 0
END
WHERE product_id IN (1,2)
AND (
(product_id = 1 AND stock >= 2) OR
(product_id = 2 AND stock >= 1)
);
-- 재고 차감이 2개 상품 모두 반영됐는지 검증 (실무 포인트)
-- 둘 다 성공해야 즉, rowcount=2가 나와야 정상
SELECT ROW_COUNT() AS updated_rows;
-- 4) 주문 합계 계산 후 업데이트
UPDATE orders o
JOIN (
SELECT order_id, SUM(line_amount) AS s
FROM order_items
WHERE order_id = @oid
GROUP BY order_id
) x ON x.order_id = o.order_id
SET o.total_amount = x.s
WHERE o.order_id = @oid;
-- 5) 결제 생성 (orders.total_amount를 그대로 결제금액으로)
INSERT INTO payments (order_id, method, paid_amount, paid_at, status)
SELECT @oid, 'CARD', total_amount, NOW(), 'APPROVED'
FROM orders
WHERE order_id = @oid;
COMMIT;검증:
SELECT * FROM orders WHERE order_id = @oid;
SELECT * FROM order_items WHERE order_id = @oid;
SELECT * FROM payments WHERE order_id = @oid;
SELECT product_id, stock FROM products WHERE product_id IN (1,2);6) 실습 3: 실패 케이스를 만들고 ROLLBACK 확인
케이스: 결제 테이블은 order_id에 UNIQUE(주문당 결제 1개)가 있음
SHOW INDEX FROM payments;
-- uk_payments_order (order_id) 확인6-1. 일부러 결제를 두 번 넣어 실패 유도 → 전체 롤백 확인
START TRANSACTION;
INSERT INTO orders (customer_id, status, ordered_at, total_amount)
VALUES (2, 'PAID', NOW(), 0);
SET @oid2 := LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT @oid2, 3, 1, price FROM products WHERE product_id=3;
UPDATE orders
SET total_amount = (
SELECT SUM(line_amount) FROM order_items WHERE order_id=@oid2
)
WHERE order_id=@oid2;
-- 결제 1회
INSERT INTO payments (order_id, method, paid_amount, paid_at, status)
SELECT @oid2, 'KAKAO', total_amount, NOW(), 'APPROVED'
FROM orders WHERE order_id=@oid2;
-- 결제 2회 (UNIQUE 위반 -> 에러 발생)
INSERT INTO payments (order_id, method, paid_amount, paid_at, status)
SELECT @oid2, 'NAVER', total_amount, NOW(), 'APPROVED'
FROM orders WHERE order_id=@oid2;
-- 여기까지 오면 안 됨 (두 번째 INSERT에서 에러)
COMMIT;에러가 났다면 반드시:
ROLLBACK;검증(주문 자체가 없어야 정상):
SELECT * FROM orders WHERE order_id=@oid2;
SELECT * FROM order_items WHERE order_id=@oid2;
SELECT * FROM payments WHERE order_id=@oid2;7) 실습 4: SAVEPOINT (부분 롤백)
“결제만 실패하면 주문을 취소 상태로 남기고 싶다” 같은 운영 정책을 사용할 경우.
START TRANSACTION;
INSERT INTO orders (customer_id, status, ordered_at, total_amount)
VALUES (3, 'CREATED', NOW(), 0);
SET @oid3 := LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT @oid3, 4, 1, price FROM products WHERE product_id=4;
UPDATE orders
SET total_amount = (SELECT SUM(line_amount) FROM order_items WHERE order_id=@oid3)
WHERE order_id=@oid3;
SAVEPOINT before_payment;
-- 일부러 결제 실패 유도 (없는 ENUM 값 등)
INSERT INTO payments (order_id, method, paid_amount, paid_at, status)
SELECT @oid3, 'CASH', total_amount, NOW(), 'APPROVED'
FROM orders WHERE order_id=@oid3;
-- 실패 시 여기로 돌아간다
ROLLBACK TO SAVEPOINT before_payment;
-- 결제 실패했으니 주문 상태를 CANCELLED로 변경 후 마무리
UPDATE orders SET status='CANCELLED' WHERE order_id=@oid3;
COMMIT;검증:
SELECT * FROM orders WHERE order_id=@oid3;
SELECT * FROM payments WHERE order_id=@oid3; -- 없어야 정상8) 트랜잭션 격리 수준(Isolation Level)
MySQL(InnoDB) 기본 격리 수준은 보통 REPEATABLE READ이다.(환경에 따라 확인 필요).
SHOW VARIABLES LIKE 'transaction_isolation';격리 수준 체감은 2세션 실습이 좋다.
9) 실습 5: 2세션으로 “동시성” 체험 (재고 갱신 충돌)
목표
동시에 같은 상품 재고를 차감하려 할 때 락이 어떻게 걸리는지 본다.
세션 A
START TRANSACTION;
-- 같은 행을 잡고(잠그고) 있는 상태 유지
SELECT product_id, stock
FROM products
WHERE product_id = 5
FOR UPDATE;
-- (여기서 COMMIT/ROLLBACK 하지 말고 대기)세션 B (동시에 실행)
START TRANSACTION;
UPDATE products
SET stock = stock - 1
WHERE product_id = 5;
-- 세션 A가 COMMIT할 때까지 대기(락 대기)하게 된다.세션 A에서 마무리
COMMIT;세션 B도 이어서
COMMIT;SELECT ... FOR UPDATE는 해당 행에 배타 락- 동시 주문에서 “재고 같은 공유 자원”은 락/격리가 없으면 깨짐
연습문제 : 트랜잭션 실습 (5문제)
핵심: COMMIT/ROLLBACK, 재고 차감, 주문 생성, 결제 반영(샘플 수준)
T1. 트랜잭션을 시작하고, customers에 신규 고객 1명을 INSERT 후 ROLLBACK 하시오.
→ ROLLBACK 이후 고객이 남아있지 않아야 함.
START TRANSACTION;
INSERT INTO customers (email, name, phone, status)
VALUES ('tx_test@example.com', 'TX TEST', '010-9999-9999', 'ACTIVE');
ROLLBACK;
-- 결과 확인 --
SELECT * FROM customers WHERE email = 'tx_test@example.com';T2. 트랜잭션을 시작하고, order_id=30 주문의 status를 'PAID'로 변경한 뒤 COMMIT 하시오.
START TRANSACTION;
UPDATE orders
SET status = 'PAID'
WHERE order_id = 30;
COMMIT;
-- 결과 확인 --
SELECT order_id, status FROM orders WHERE order_id = 30;
T3. 트랜잭션으로 “주문 1건 + 주문상세 1~2건”을 생성하시오.
요구:
1. orders에 customer_id=1, status='CREATED'로 1건 생성
2. 방금 생성된 order_id로 order_items 2건 추가 (상품 1,2 적당 수량)
3. orders.total_amount를 order_items 합계로 업데이트
4. COMMIT
START TRANSACTION;
-- 1. 주문 생성
INSERT INTO orders (customer_id, status)
VALUES (1, 'CREATED');
-- 생성된 주문 ID 저장
SET @new_order_id := LAST_INSERT_ID();
-- 2. 주문상세 2건 추가 (상품 1, 2)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
(@new_order_id, 1, 2, 9000),
(@new_order_id, 2, 1, 19000);
-- 3. 주문 금액 업데이트
UPDATE orders o
JOIN (
SELECT order_id, SUM(line_amount) AS total
FROM order_items
WHERE order_id = @new_order_id
GROUP BY order_id
) x ON o.order_id = x.order_id
SET o.total_amount = x.total
WHERE o.order_id = @new_order_id;
COMMIT;
T4. (실패 시 롤백) 트랜잭션에서 아래를 수행하시오.
- products.product_id=24(NAS 2-bay)의 stock을 1 감소
- 만약 stock이 0 미만이 되면 ROLLBACK 처리(수동으로 조건 체크 쿼리 작성)
START TRANSACTION;
SELECT stock into @stock_before
FROM products
WHERE product_id = 24
FOR UPDATE;
UPDATE products
SET stock = stock - 1
WHERE product_id = 24;
SELECT stock INTO @stock_after
FROM products
WHERE product_id = 24;
SELECT @stock_before AS stock_before, @stock_after AS stock_after;
-- 위에서 SELECT으로 직접 확인해보고 음수가 아니면 커밋 --
COMMIT
-- 위에서 SELECT으로 직접 확인해보고 음수라면 롤백 --
ROLLBACK
T5. 트랜잭션에서 주문 취소 처리: order_id=5를 'CANCELLED'로 변경하고, 해당 주문의 결제(payments)가 있으면 payment.status='CANCELLED'로 변경 후 COMMIT 하시오.
START TRANSACTION;
-- 주문 취소
UPDATE orders
SET status = 'CANCELLED'
WHERE order_id = 5;
-- 결제 취소 (있을 경우만)
UPDATE payments
SET status = 'CANCELLED'
WHERE order_id = 5;
COMMIT;
전체 핵심 정리 (시험/면접용)
트랜잭션 필수 패턴
START TRANSACTION;
-- DML
COMMIT; -- 성공 시
ROLLBACK; -- 실패 시자주 쓰는 기술
-
LAST_INSERT_ID()→ 방금 생성한 PK -
재고/금액 처리 → UPDATE → 조건 체크
-
실패 조건은
ROW_COUNT()활용이 가장 안전
Front-end Developer, Cloud Engineer