[MySQL] UPDATE 트랜잭션 적용하기: 데이터 무결성을 지키는 안전한 수정 전략

MySQL에서 UPDATE 문은 데이터 값을 수정하는 가장 기본적인 명령어이다.
그러나 여러 UPDATE, INSERT, DELETE 문이 논리적으로 하나의 작업 단위로 묶여야 할 때, 이를 안전하게 처리하려면 반드시 트랜잭션(Transaction) 기능을 적용해야 한다.

트랜잭션을 사용하면 데이터의 정합성과 안정성을 보장할 수 있으며, 예기치 않은 오류 발생 시 데이터를 ROLLBACK(되돌리기) 할 수 있어 매우 중요하다.

✅ 트랜잭션이란?

트랜잭션은 하나 이상의 SQL 문을 하나의 논리적인 작업 단위로 처리하는 기능이다.
즉, 트랜잭션 내부의 모든 쿼리가 성공해야만 COMMIT으로 반영되고, 중간에 오류가 발생하면 ROLLBACK으로 이전 상태로 되돌릴 수 있다.

트랜잭션의 4대 특성(ACID)

1. 원자성(Atomicity): 전부 성공하거나 전부 실패해야 함
2. 일관성(Consistency): 트랜잭션 수행 전후 데이터 일관성 유지
3. 격리성(Isolation): 동시에 수행되는 트랜잭션 간 간섭 방지
4. 지속성(Durability): 커밋된 결과는 영구 저장됨

반응형

✅ MySQL 트랜잭션 기본 문법

START TRANSACTION;

-- 여러 개의 UPDATE 또는 DML 실행
UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE id = 2;

COMMIT; -- 정상 처리 시 반영
-- ROLLBACK; -- 오류 발생 시 복구

✅ 트랜잭션 적용 예제 1: 계좌 이체

START TRANSACTION;

UPDATE bank_accounts SET balance = balance - 50000 WHERE account_id = 'A123';
UPDATE bank_accounts SET balance = balance + 50000 WHERE account_id = 'B456';

COMMIT;

A 계좌에서 5만 원을 출금하고 B 계좌로 입금하는 예제이다. 중간에 하나라도 실패하면 둘 다 처리되지 않아야 한다.
이러한 경우 트랜잭션이 없으면 한 쪽 계좌만 변경되어 데이터 불일치가 발생할 수 있다.

✅ 트랜잭션 적용 예제 2: 주문 및 재고 동시 처리

START TRANSACTION;

UPDATE products
SET stock = stock - 1
WHERE product_id = 100 AND stock > 0;

UPDATE orders
SET status = '구매완료', ordered_at = NOW()
WHERE order_id = 20240626;

COMMIT;

재고 차감과 주문 상태 변경이 동시에 이뤄져야 하는 대표적인 트랜잭션 예제이다.
상품 재고가 없는 상태에서 주문 상태만 변경되면 치명적인 오류가 발생할 수 있다.

✅ 오류 발생 시 ROLLBACK 처리

START TRANSACTION;

UPDATE a SET col1 = 'value' WHERE id = 1;

-- 오류 발생 시점
-- UPDATE b SET col2 = NULL WHERE id = 'not_exist';  → 오류 발생

ROLLBACK;

하나의 쿼리라도 오류가 발생하면 ROLLBACK 명령을 실행하여 이전 상태로 복원할 수 있다.

반응형

✅ 프로그래밍 언어에서 트랜잭션 처리(PHP 예시)

$conn->begin_transaction();

try {
    $conn->query("UPDATE users SET point = point - 100 WHERE id = 1");
    $conn->query("UPDATE users SET point = point + 100 WHERE id = 2");
    $conn->commit();
} catch (Exception $e) {
    $conn->rollback();
}

 

PHP, Java, Python 등에서도 MySQL 트랜잭션은 반드시 try-catch 구조로 제어하는 것이 일반적이다.

✅ 트랜잭션 사용을 위한 엔진 조건

MySQL에서 트랜잭션은 InnoDB 스토리지 엔진에서만 사용 가능하다.
MyISAM은 트랜잭션을 지원하지 않기 때문에 반드시 아래 쿼리로 엔진을 확인해야 한다.

SHOW TABLE STATUS WHERE Name = '테이블명';

또는 테이블 생성 시 아래처럼 명시한다.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  ...
) ENGINE=InnoDB;

✅ 트랜잭션 적용 실전 팁

항목	설명
COMMIT은 신중하게	중간 저장 없이 자동 커밋되면 복구 불가
ROLLBACK은 빠르게	오류 발생 즉시 처리하여 불일치 방지
적절한 격리 수준 설정	READ COMMITTED, REPEATABLE READ 등
세션 단위로 제어	각 클라이언트별 트랜잭션은 독립됨

 

✅ 공식 문서 참고 링크

• MySQL 트랜잭션 공식 문서
• START TRANSACTION 문법