SQL TCL

1. 트랜잭션

• 트랜젝션(Transaction)은 분할할 수 없는 최소단위이며 논리적인 작업 단위이다.
• 트랜젝션 제어어(TCL)는 데이터조작어(DML) 명령어 실행, 취소, 임시저장할 때 사용하는 명령어이다.
• 여러 단계의 처리를 하나의 처리처럼 다루는 기능이다.
• 여러 개의 명령어의 집합이 정상적으로 처리되면 정상 종료된다.
• 하나의 명령어라도 잘못되면 전체 취소된다.
• 트랜잭션을 쓰는 이유는 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구하기 위함이다.
  1) 활성(Active) : 트랜잭션이 정상적으로 실행중인 상태
  2-1) 부분 완료(Partially Committed) : 트랜잭션의 마지막까지 실행되었지만, Commit 연산이 실행되기 직전의 상태
  2-2) 완료(Committed) : 트랜잭션이 성공이 종료되어 Commit 연산을 실행한 후의 상태
  2-3) 실패(Failed) : 트랜잭션 실행에 오류가 발생하여 중단된 상태
  2-2) 철회(Aborted) : 트랜잭션이 비정상적으로 종료되어 Rollback 연산을 수행한 상태
  

2. COMMIT, ROLLBACK, SAVEPOINT

COMMIT(실행)

START TRANSACTION; -- 트랜잭션 시작

SELECT * FROM members; -- 초기 상태 보여줌
INSERT INTO members VALUES(1, '이순신', '2020-02-02'); -- 데이터 수정
SELECT * FROM members; -- 수정 상태 보여줌

COMMIT -- 트랜잭션을 DB에 적용

SELECT * FROM members; -- 적용된 결과 조회

ROLLBACK(취소)

START TRANSACTION; -- 트랜잭션 시작

SELECT * FROM members; -- 초기 상태 보여줌
INSERT INTO members VALUES(1, '이순신', '2020-02-02'); -- 데이터 수정
SELECT * FROM members; -- 수정 상태 보여줌

ROLLBACK -- 트랜잭션을 취소하고 START TRANSACTION 실행 전 상태로 롤백함

SELECT * FROM members; -- 조회

SAVEPOINT(임시저장)

DELETE FROM TRAT3 WHERE ID=2012;
COMMIT;
SAVEPOINT S1;
DELETE FROM TRAT3 WHERE ID=2014;
SAVEPOINT S2;
DELETE FROM TRAT3 WHERE ID=2015;
SAVEPOINT S3;
DELETE FROM TRAT3 WHERE ID=2011;
ROLLBACK TO S2;

• 맨 마지막에 ROLLBACK TO S2를 선언했으니, S2로 돌아간다

DELETE FROM TRAT3 WHERE ID=2012;
COMMIT;
SAVEPOINT S1;
DELETE FROM TRAT3 WHERE ID=2014;
SAVEPOINT S2;
DELETE FROM TRAT3 WHERE ID=2015;
SAVEPOINT S3;
DELETE FROM TRAT3 WHERE ID=2011;
ROLLBACK TO S2;
SELECT * FROM TRAT3;
COMMIT;
ROLLBACK TO S1;

• SAVEPOINT를 여러개 만들고 난뒤 COMMIT을 하면 앞에 있던 SAVEPOINT는 전부 지워지고 COMMIT까지만 완료된다. 그래서 ROLLBACK TO S1은 오류가 뜨기 때문에 주의해야 한다.

MYSQL 트랜잭션 전역 설정

• MySQL에서는 디폴트로 auto commit이 on으로 설정되어 있다.
• 세미콜론을 한 번 찍을 때마다 DB에서 자동으로 commit 해주는 시스템이다.

-- 오토커밋 off
SET AUTOCOMMIT = 0;

-- 오토커밋 on
SET AUTOCOMMIT = 1;

-- 오토쿼리 설정 확인
SELECT @@autocommit;

Reference
1) https://jerryjerryjerry.tistory.com/48
2) https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98Transaction-%EC%9D%B4%EB%9E%80-%F0%9F%92%AF-%EC%A0%95%EB%A6%AC