Transaction

INSERT, UPDATE, DELETE가 안되고 실패하는 경우가 있는데
그러면 다시 시도하면 되긴하지만
동시에 여러 쿼리를 실행해야할 때 그 중 하나만 실패하는 경우 서비스 운영에 심각한 문제가 발생할 수 있다.

예를 들어 은행을 운영하는데
A고객이 B고객에게 1000원을 이체한다고 한다.
1. A고객의 잔고에서 -1000원을 하고
2. B고객의 잔고에서 +1000원을 하면 된다.
UPDATE 문법을 2번 쓰면 된다.

근데 1번은 성공했는데 2번은 실패하면 어떻게 될까?
이러한 문제가 생기지 않도록 하기 위해 transaction이라는 기능을 사용할 수 있다.

Transaction 기능

위와 같은 문제를 해결하고 싶으면
쿼리문 여러개 중 최소 하나가 에러나는 경우 전체 쿼리문 실행을 취소해달라고 요청하는 코드를 짤 수 있다.

transaction 기능을 쓰면 되는데 transaction은 여러개의 쿼리를 한 번에 묶어서 처리할 수 있게 도와주는 기능이다.
START TRANSACTION / COMMIT / ROLLBACK 문법을 사용하면된다.

START TRANSACTION;
이거 INSERT 해주세요~;
저거 UPDATE 해주세요~;
COMMIT;

start transaction이라고 작성하고 실행한 다음
삽입, 수정, 삭제 쿼리문을 실행하면 실행은 되지만 실제 테이블에 반영이 되지 않고 보류된다.
마지막에 commit까지 실행하면
보류되었던 쿼리문들이 테이블에 반영이된다.

START TRANSACTION;
이거 INSERT 해주세요~;
저거 UPDATE 해주세요~;
ROLLBACK;

commit 말고 rollback이라는 명령어를 마지막에 실행하면
처리가 보류되었던 쿼리문들이 실행취소된다.(롤백됨)
그래서 이러한 문법들을 사용하면 위와 같은 불상사를 막을 수 있다.

START TRANSACTION;
테이블에 있는 A 계좌에 -1000원 하기 ~~;
테이블에 있는 B 계좌에 +1000원 하기 ~~;
하나라도 row 변경에 실패하면 ROLLBACK 하고 별문제없으면 COMMIT ;

이런식으로 코드를 짜면 여러 줄의 SQL 쿼리를 전부 반영하거나 아예 반영을 하지 않거나 해서 위와 같은 실수를 방지할 수 있다.

(주의1) 테이블 생성시 engine을 선택할 수 있는데 InnoDB인 경우에만 사용가능하다. 기본이 InnoDB이다.
(주의2) 옛날 MySQL 5.x 버전은 따로 auto_commit 변수를 0으로 설정해줘야 transaction이 가능하다.
(주의3) 테이블 생성, 수정 문법은 start transaction 안에서 써도 보류되지않고 바로 반영된다.

실은 서버에서 많이 쓰는 문법이다.

START TRANSACTION ;
테이블에 있는 A 계좌에 -1000원 하기 ~~;
테이블에 있는 B 계좌에 +1000원 하기 ~~;
하나라도 row 변경에 실패하면 ROLLBACK 하고 별문제없으면 COMMIT 해주세요~ ;

이 SQL 코드를 어떻게 실제로 짤 수 있을까?
보통 삽입/수정/삭제는 DBeaver 말고 웹서버에서 DB에 명령을 내리기 때문에 단독으로 SQL만으로 구현하는 경우는 잘 없다.

(서버 파일 예시)
try {
  db.query('START TRANSACTION')
  db.query('테이블에 있는 A 계좌에 -1000원')
  db.query('테이블에 있는 B 계좌에 +1000원')
  db.query('COMMIT')
} 

catch {
  db.query('ROLLBACK')
}

이런식으로 서버 개발자들이 db에 명령을 내리는 방식이고
try catch 문법은 try안에 있는 코드가 에러가 나면 catch에 있는 코드를 대신 실행해준다.
그래서 위 처럼 transaction 기능을 구현하는 경우가 많다.

SQL 문법으로 성공시 COMMIT, 에러시 ROLLBACK 만들기

MySQL은 에러났을 때를 체크해서 다른 코드를 실행하고 싶으면 DECLARE/HANDLER 문법을 쓰면된다.
(procedure안에서만 가능!!)

DROP PROCEDURE IF EXISTS 테스트.transaction_test; 
DELIMITER $$ 
CREATE PROCEDURE 테스트.transaction_test() 
BEGIN 

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN 
    ROLLBACK;
  END;
  
  START TRANSACTION;
    INSERT INTO 테스트.teacher VALUES (10, '테스트', '테스트','ㄴㅇㄹ'); 
  COMMIT; 
  
END$$ 
DELIMITER ; 

CALL 테스트.transaction_test();

DECLARE EXIT HANDLER 라고 작고 뒤에 에러명을 적으면
1. procedure 실행 중 에러발생시
2. BEGIN/END 사이에 있는 코드를 대신 실행해주고
3. procedure를 종료한다.

그래서 위 코드를 실행시
INSERT에서 에러가 나면 COMMIT을 실행하지 않고 ROLLBACK이 실행된다.
그래서 SQL 코드 만으로 Transaction을 사용하려면 이렇게 하는 경우가 있다.

(참고) SQLEXCEPTION은 그냥 전반적인 에러이다. 다른 DBMS예써는 TRY, CATCH 문법 같은거 써도 된다.

참고1) auto commit에 대해

테이블을 만들 때 엔진을 InnoDB로 설정해놓으면
모든 일반 쿼리문은 하나의 transaction으로 처리해준다.
작성한 모든 쿼리문을 한 줄 마다 start transaction / commit으로 몰래 감싸놓고 실행해준다는 뜻이다.
이것을 auto commit 기능이라고 한다.
그래서 작성하 INSERT같은 문법이 한 줄 실행될 때 마다 바로 테이블에 반영되는 것이다.

auto commit 기능을 끄고 싶다면 START TRANSACTIOn 코드를 실행하면 자동으로 꺼진다.

참고2) ACID 속성

transaction 기능을 ACID transaction 이렇게 부르는 경우가 있는데
Atomicity, Consistency, Isolation, Durability 속성을 가져야 transaction이라고 부를 수 있다는
transaction 기능의 교과서적 정의같은 것이다.

Atomicity(원자성) - 1개의 transaction 안에 있는 코드들은 전부 실행되거나 실행되지 않거나 택1 해야한다
Consistency(일관성) - transaction은 조작하려는 테이블의 제약조건과 룰 같은걸 잘 따라야한다
Isolation(격리성) - transaction 끼리는 서로 간섭하지 않아야한다
Durability(지속성) - transaction이 commit 되면 데이터베이스에 영구적으로 기록되어야한다 (심지어 도중에 컴퓨터 전원을 껐다 켜도)