MySQL COMMIT과 ROLLBACK

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

2개의 계좌 사이에서 돈을 옮기는 데는 2개의 문이 필요하고, 그 사이에서 실패하면 돈은 어디에도 없게 됩니다. 트랜잭션은 문들을 묶어서 전부 반영되거나 전부 반영되지 않게 합니다. 이 예제들은 INSERT의 sakila_practice 데이터베이스를 사용합니다. 시작하기 전에 거기 있는 설정 블록을 다시 실행한 다음, mysql -u root -p sakila_practice로 클라이언트를 여세요.

트랜잭션의 동작 방식

START TRANSACTION;
FIRST_STATEMENT;
SECOND_STATEMENT;
COMMIT;

FIRST_STATEMENT와 SECOND_STATEMENT를 함께 성공해야 하는 문으로 바꾸세요. COMMIT은 작업을 영구적으로 만들고 다른 모든 사람에게 보이게 합니다. ROLLBACK은 작업을 버리고 행을 원래대로 되돌립니다. 둘 중 하나를 입력하기 전까지 변경은 당신만의 것입니다: 다른 세션은 여전히 이전 값을 봅니다. 트랜잭션 밖의 모든 문은 스스로 커밋되는데, autocommit이 켜져 있기 때문입니다.

SELECT @@autocommit AS autocommit;

+------------+
| autocommit |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

앞선 레슨들에 COMMIT이 필요 없었던 이유가 그것입니다: 각 INSERT와 UPDATE가 1개의 문으로 된 트랜잭션이었습니다. START TRANSACTION은 끝낼 때까지 그것을 멈춥니다.

예제

ROLLBACK으로 작업 되돌리기

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  500.00 |
|          2 | Patricia Johnson |  250.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

Mary에서 Patricia로 100을 옮기고, 결과를 본 다음, 마음을 바꾸세요.

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
SELECT * FROM account;
ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  400.00 |
|          2 | Patricia Johnson |  350.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

트랜잭션 안의 SELECT는 400과 350을 보고하는데, 세션은 자신의 커밋되지 않은 작업을 보기 때문입니다. 그다음 ROLLBACK이 두 업데이트를 모두 되돌립니다.

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  500.00 |
|          2 | Patricia Johnson |  250.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

이것이 MySQL이 가진 유일한 되돌리기입니다. 트랜잭션 밖의 문이 일단 실행되면 이전 값은 사라집니다.

COMMIT으로 작업 유지하기

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  400.00 |
|          2 | Patricia Johnson |  350.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

2개의 업데이트가 함께 반영되었습니다. 다른 어떤 세션도 400과 250을 동시에 본 적이 없고, 그것이 이렇게 쓰는 이유의 전부입니다.

일부 문은 묻지 않고 커밋합니다

데이터베이스의 형태를 바꾸는 문은 롤백할 수 없습니다. 더 나쁜 것은, 그런 문을 실행하면 열린 트랜잭션이 그 시점까지 한 모든 것이 커밋된다는 것입니다.

START TRANSACTION;
UPDATE account SET balance = 0 WHERE account_id = 1;
CREATE TABLE note (id INT);
ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ROLLBACK을 포함해 모든 줄이 성공을 보고합니다.

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |    0.00 |
|          2 | Patricia Johnson |  350.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

잔액이 0입니다. CREATE TABLE이 자신이 실행되기 전에 업데이트를 커밋했으므로, ROLLBACK은 되돌릴 것이 남아 있지 않았는데도 성공을 보고했습니다. ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE도 같은 방식으로 동작합니다. 이 5개는 모두 데이터를 보호하는 트랜잭션 밖에 두세요. 임시 테이블은 예외입니다. CREATE TEMPORARY TABLE과 DROP TEMPORARY TABLE은 아무것도 커밋하지 않으므로, 중간에 임시 테이블을 쓴 같은 테스트는 잔액을 500.00으로 남깁니다.

요약

START TRANSACTION을 쓰고, 문들을 쓴 다음, COMMIT이나 ROLLBACK을 쓰세요.
autocommit이 켜져 있으므로 트랜잭션 밖의 모든 문은 스스로 커밋된다고 예상하세요.
자신의 세션은 자신의 커밋되지 않은 변경을 보고, 다른 세션은 보지 못한다고 예상하세요.
ROLLBACK을 유일한 되돌리기로 다루세요.
CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE은 트랜잭션 밖에 두세요. 각각이 트랜잭션을 커밋합니다.
CREATE와 DROP의 TEMPORARY 형태는 아무것도 커밋하지 않는다고 예상하세요.

함께 보기

UPDATE — 트랜잭션으로 감쌀 가치가 가장 큰 문
DELETE — 되찾고 싶을 수 있는 행 삭제하기
MySQL의 트랜잭션 — 격리 수준, 잠금, 그리고 그 비용
MySQL의 교착 상태 — 2개의 트랜잭션이 같은 행을 원할 때 일어나는 일

MySQL Tutorial

가이드

데이터 저장

쿼리 작성

데이터 보안

성능 최적화

AI로 구축

MySQL 운영

애플리케이션 연결

확장 작성