[SQL/MySQL] 트랜잭션 COMMIT, ROLLBACK, SAVEPOINT 사용법

 

트랜잭션이란?

여러 개의 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는 것이다.
여러 명령어 집합이 정상적으로 처리되면 정상 종료하도록 하고, 여러 명령어 중 하나의 명령이라도 잘못되었다면 전체를 취소한다.

하나의 트랜잭션은 All - OR - Nothing 방식으로 처리된다.

TCL (Transaction Control Language) 이며, Commit과 Rollback을 포함한다.

 

 

트랜잭션을 이름 그대로 번역한다면 "거래"라는 뜻이다.

즉, 데이터베이스에서 트랜잭션은 하나의 거래를 안전하게 처리하도록 보장해주는 뜻이기도 한다.

누군가 물건을 산다고 가정했을때, 물건을 받고 돈을 주기까지 2단계의 과정을 거친다.
이때 누가 물건을 받았는데 돈을 주지 않았다면 먹튀가 발생한다.
따라서 이를 방지하기 위해 2단계 모두 처리되지 않았다면 거래 전의 상태로 돌아갈 수 있도록 보장한다.

 

트랜잭션 ACID

원자성 - 트랜잭션 내에서 실행한 작업들은 마치 하나의 작업인 것처럼 모두 성공하거나 모두 실패해야 한다.

일관성 - 모든 트랜잭션은 일관성 있는 데이터베이스 상태를 유지해야한다.

격리성 - 동시에 실행되는 트랜잭션들이 서로에게 영향을 미치지 않도록 격리한다. (동시에 같은 데이터 수정 불가)

지속성 - 트랜잭션을 성공적으로 끝내면 그 결과가 항상 기록되야한다.

 

트랜잭션 격리 수준

• READ UNCOMMITED ( 커밋되지 않은 읽기 ) - 1단계 ( 레벨 0 )
• READ COMMITTED (커밋된 읽기 ) - 2단계 ( 레벨 1 )
• REPEATABLE ( 반복 가능한 읽기 ) - 3단계 ( 레벨 2 )
• SERIALIZABLE ( 직렬화 기능 ) - 4단계 ( 레벨 3 )

레벨 0 ( READ UNCOMMITED )

- 트랜잭션에 처리 중이거나, 아직 Commit 되지않은 데이터를 다른 트랜잭션이 읽는 것 허용.

 

레벨 1 ( READ COMMITTED )

- 트랜잭션이 수행되는 동안 다른 트랜잭션이 접근할 수 없어 대기. Commit이 이뤄진 트랜잭션만 조회 가능

 

레벨 2 ( REPEATABLE )

- 트랜잭션이 범위 내에서 조회한 데이터 내용이 항상 동일함을 보장. 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대해 수정 불가능.

 

레벨 3 ( SERIALIZABLE )

 - 완벽한 읽기 일관성 모드 제공. 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 및 입력 불가능.

 

데이터 변경 쿼리를 실행하고 데이터베이스에 결과를 반영하려면 커밋 명령어 COMMIT을 호출하고,

결과를 반영하고 싶지 않다면 롤백 명령어 ROLLBACK을 호출하면 된다.

 

START TRANSACTION

 트랜잭션을 사용하기 위해선 우선적으로 제어 시점을 기록해야한다.

MySQL이나 MariaDB는 기본적으로 DDL 문에 대해 모두 오토 커밋(Auto Commit)이 적용되어있다.

*오토커밋이란? - INSERT, DELECT, UPDATE 문을 통해 데이터 조작시 데이터가 그대로 테이블에 들어가는 것

 

따라서 데이터 조작 시 AUTO COMMIT을 할 수 없도록 만들 수 있지만,

START TRANSACTION하여 COMMIT 혹은 ROLLBACK 지점을 만들 수 있다.

 

START TRANSACTION은 무조건 COMMIT / ROLLBACK을 만나기 전에 선언되야 하고,

COMMIT 혹은 ROLLBACK을 만나게 되면 무조건 종료된다.

 

COMMIT

모든 작업들을 정상적으로 처리하겠다고 확정하는 명령어.
트랜잭션의 처리 과정을 데이터베이스에 모두 반영하기 위해 변경된 내용을 모두 영구 저장한다.

 

ROLLBACK

작업 중 문제가 발생되어 트랜잭션의 처리 과정에서 발생한 변경사항을 취소하는 명령어.
트랜잭션으로 인한 하나의 묶음처리가 시작 이전의 상태로 되돌린다.

 

장점

• 데이터 무결성이 보장된다.
• 영구적인 변경 전에 데이터 변경 사항을 확인할 수 있다.
• 논리적으로 연관된 작업을 그룹화할 수 있다.

---

MySQL / MariaDB는 기본적으로 자동 커밋이 설정되어 있다.

SELECT @@AUTOCOMMIT;
SET AUTOCOMMIT = FALSE; -- 오토 커밋 OFF
 
START TRANSACTION;
 
DELETE FROM STUDENT;
 
ROLLBACK;

SELECT @@AUTOCOMMIT 을 통해서 현재 DB의 상태를 확인할 수 있다.

1이 되어있다면, 자동으로 커밋이 된다. 0이라면 COMMIT을 직접 쳐주어야 커밋이 된다.

 

START TRANSACTION을 통해 제어 시점을 기록한다.

이후 STUDENT 테이블을 조회해보면, '박미경'이라는 사람이 있는데, 이를 지워보려고한다.

 

DELETE를 통해 박미경을 지우고, 다시한번 STUDENT 테이블을 조회했다.

MySQL과 MariaDB는 자동 커밋되기 때문에, 이런식으로 중요한 테이블을 날려버려 아주 곤란한 상황이 벌어질 수 있다.

따라서 이런식으로 ROLLBACK을 통해 지웠던 박미경 테이블을 살릴 수 있다.

이후 COMMIT을 통해 테이블을 저장하면 된다.

---

SAVEPOTIN

여러 SQL문의 실행을 하는 트랜잭션의 경우, 사용자가 트랜잭션 중간 단계에서 세이브포인트를 지정할 수 있다.

START TRANSACTION; -- 트랜잭션 시작
SELECT * FROM A;
INSERT INTO A (NAME) VALUES ('PARK');
 
SAVEPOINT S1; -- SAVEPOINT 설정
 
UPDATE A SET NAME = 'KIM' WHERE NAME = 'PARK';
 
SAVEPOINT S3;  -- SAVEPOINT 설정
 
DELETE FROM A WHERE NAME = 'KIM';
 
ROLLBACK TO S1;
ROLLBACK TO S3;

위 SAVEPOINT S1과 S3에 각각 저장 지점을 저장해두었고, 현재 DELETE를 통해 A 테이블에 있는 모든 데이터를 지웠다.

이렇게 ROLLBACK 지점을 역순으로 실행시켜 주었다. 바로 확인해볼 수 있듯이, S3, S1 에서 저장됬던 데이터들이다.

주의할점으로, ROLLBACK을 실행할때, 최근 순부터 해주어야한다.

만약 위 그림에서 S1부터 롤백해버리면, S3으로는 되돌아 올 수 없다.