[MySQL] DML과 트랜잭션 오토커밋 제어 완벽 정리 ⚙️

MySQL 트랜잭션 제어는 대용량 처리나 여러 데이터 조작이 필요한 실무에서 매우 중요한 요소다.
특히 INSERT, UPDATE, DELETE와 같은 DML(데이터 조작어)을 사용할 때 오토커밋(AUTOCOMMIT) 설정을 이해하지 못하면 원치 않는 데이터 손실이나 중간 저장 문제가 발생할 수 있다.

🔍 오토커밋(AUTOCOMMIT) 이란?

MySQL에서는 기본적으로 모든 DML 명령이 실행되면 자동으로 COMMIT 처리된다.
즉, autocommit이 ON 상태면 다음과 같은 동작을 한다.

INSERT INTO users (id, name) VALUES (1, 'Alice'); -- 자동 커밋됨

반면, autocommit = OFF인 경우에는 COMMIT 명령을 수동으로 입력해야 실제로 반영된다.

SET autocommit = 0; -- 자동 커밋 OFF
INSERT INTO users (id, name) VALUES (2, 'Bob');
-- 아직 반영되지 않음
COMMIT; -- 이때 실제 반영됨

반응형

🛠️ 트랜잭션 수동 제어 방법

트랜잭션 수동 제어를 위해선 다음 명령어를 알아야 한다.

명령어	설명
START TRANSACTION 또는 BEGIN	트랜잭션 시작
COMMIT	변경사항 저장
ROLLBACK	변경사항 취소
SET autocommit = 0	자동 커밋 비활성화

예제 1: 수동 트랜잭션 제어

SET autocommit = 0;
START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;

→ 중간에 예외가 발생해도 ROLLBACK 하면 모두 취소된다.

⚠️ 오토커밋 주의사항

• 트랜잭션을 시작하지 않고 autocommit을 끄기만 하면 무한정 열린 상태로 유지됨
• 실수로 ROLLBACK을 안 하면 반영되지 않아 데이터 손실 발생 가능
• GUI 툴 사용 시에도 autocommit 여부를 명확히 확인할 것

💡 autocommit 상태 확인 및 변경

현재 autocommit 상태 확인:

SELECT @@autocommit;

값이 1이면 ON, 0이면 OFF다.

autocommit을 OFF로 설정:

SET autocommit = 0;

트랜잭션 작업 후 다시 ON으로 복구:

SET autocommit = 1;

반응형

🧪 트랜잭션과 함께 사용하는 DML 예제 모음

예제 2: 오류 발생 시 롤백

START TRANSACTION;

INSERT INTO products (name, stock) VALUES ('Keyboard', 10);
-- 다음 INSERT가 실패하면
INSERT INTO orders (product_id, quantity) VALUES (999, 1); -- 존재하지 않는 product_id

ROLLBACK; -- 위 INSERT까지 모두 취소됨

예제 3: 트랜잭션 내 DELETE

START TRANSACTION;

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

COMMIT;

→ 로그를 일정 주기마다 정리할 때 안정적으로 처리 가능

🧷 오토커밋 제어는 실무 핵심

• 배치 작업, 정기 처리, 마이그레이션 시 autocommit OFF + 수동 트랜잭션 사용이 안정적이다.
• 오류 복구 가능성과 롤백 처리가 필요하다면 반드시 트랜잭션을 수동으로 제어해야 한다.
• GUI 툴로 쿼리 실행 시도할 경우, 툴 설정에도 autocommit 옵션이 있으므로 확인 필수이다.

📘 공식 문서 참고

MySQL 트랜잭션 및 autocommit 공식 가이드