DBeaver)돈 다루다가 큰일나기 싫으면 Transaction

데이터베이스를 너무 믿으면 안됩니다.

간혹가다 INSERT, UPDATE, DELETE가 안되고 실패하는 경우가 많은데 

그럼 뭐 다시 시도하면 되긴 합니다만 

동시에 여러 쿼리를 실행해야하는데 그 중에 하나만 실패하는 경우

여러분들 서비스 운영에 심각한 문제가 발생하는 경우가 있습니다. 

 

예를 들어 여러분이 은행 DB를 운영하는데

A 고객 -> B 고객 이렇게 1000원을 보내야한다고 칩시다. 

1. 그럼 A 고객의 잔고에서 -1000을 하고 

2. B 고객의 잔고에 +1000을 하면 됩니다. 

UPDATE 문법을 2번 쓰면 되겠군요. 

 

근데 1번은 성공했는데 2번은 실패하면 어떻게되죠? 

은행 문닫아야합니다. 

문닫기 싫으면 transaction이라는 기능을 사용합시다. 

 

 

 

 

 

 

Transaction 기능

 

위와 같은 문제를 해결하고 싶으면

"쿼리문 여러개 중 최소 하나가 에러가 나는 경우 전체 쿼리문을 실행 취소해주세요~" 라고 코드짤 수 있습니다.  

transaction 기능을 쓰면 되는데 

transaction은 여러개의 SQL 쿼리를 한 번에 묶어서 처리할 수 있게 도와주는 기능인데 

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. 전부 반영하거나 2. 아니면 아예 반영하지말거나 

이렇게 실행할 수 있기 때문에 아까와 같은 실수를 방지해줄 수 있습니다. 

 

 

(주의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만으로 구현하는 경우는 별로 없습니다. 

(그리고 어짜피 DBeaver에선 에러같은건 바로 잡아주니까 필요없음) 

 

 

 

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

catch {
  db.query('ROLLBACK')
}

대충 서버 개발자들이 이런 식으로 프로그래밍 해둡니다. 간결하게 적은 예시일 뿐이고

try catch 문법은 try안에 있는 코드가 에러가 나면 catch 안에 있는 코드를 대신 실행해줍니다. 

그래서 위 처럼 transaction 기능을 구현하는 경우가 많으니 참고만 합시다. 

 

 

 

 

 

 

 

 

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

 

MySQL은 에러났을 때를 체크해서 다른 코드를 실행하고 싶으면 DECLARE/HANDLER 문법을 씁니다.

(procedure 안에서만 사용가능)

그래서 그 문법으로 에러나면 ROLLBACK; 실행해달라고 코드짜놓으면 됩니다. 

 

 

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 문법같은거 써도 됩니다.

 

 

 

 

결론은 삽입/수정/삭제 여러개를 처리해야할 때

전부 성공적으로 처리되었을 경우에만 결과를 반영하고 싶으면 

일단 transaction 문법 안에 작성해놓고 경우에 따라서 COMMIT/ROLLBACK 실행하면 됩니다. 

평소에 SELECT 문법만 쓰는 사람들에겐 쓸데없는 문법입니다.

 

아니면 UPDATE, DELETE를 직접 해야하는데 잘못될까봐 손발이 바들바들 떨리면

transaction으로 먼저 테스트부터 해볼 수도 있습니다.

1. START TRANSACTION 입력해두고

2. 수정, 삭제 해보고

3. 제대로 되었는지 출력해보고 제대로 되었으면 COMMIT 

4. 뭔가 이상하면 ROLLBACK

이런 식으로 하면 좀 안전하게 할 수 있지 않을까요.

 

 

 

 

 

 

참고1. auto commit에 대해 

 

테이블 만들 때 엔진을 InnoDB로 설정해놓으면 (기본이 InnoDB임)

모든 일반 쿼리문은 하나의 transaction으로 처리해줍니다.

그냥 님이 작성한 모든 쿼리문을 한 줄 마다 start transaction / commit으로 몰래 감싸놓고 실행해준다는 뜻입니다.

이걸 auto commit 기능이라고 합니다. 

그래서 님이 작성한 INSERT 같은 문법이 한 줄이 실행될 때 마다 바로바로 테이블에 반영되는 것임

 

auto commit 기능을 끄고 싶으면 

START TRANSACTION 코드를 실행하면 자동으로 꺼집니다.

혹은 예전 MySQL 5.x 버전에선 auto commit 옵션을 끄는 명령어도 있는데 그걸 써야 가능했습니다. 

 

 

 

 

 

 

참고2. ACID 속성

 

transaction 기능을 ACID transaction 이렇게 부르는 경우가 있는데

Atomicity, Consistency, Isolation, Durability 속성을 가져야 transaction이라고 부를 수 있다는

transaction 기능의 교과서적 정의같은 겁니다. 

 

Atomicity - 1개의 transaction 안에 있는 코드들은 전부 실행되거나 실행되지 않거나 택1 해야한다

Consistency - transaction은 조작하려는 테이블의 제약조건과 룰 같은걸 잘 따라야한다 

Isolation - transaction 끼리는 서로 간섭하지 않아야한다 

Durability - transaction이 commit 되면 데이터베이스에 영구적으로 기록되어야한다 (심지어 도중에 컴퓨터 전원을 껐다 켜도) 

 

이렇게 4개 속성을 만족하면서 transaction이 동작되어야한다고 정의하는 경우가 있습니다.

어디서 DB관련 시험볼거면 외우는 것들이고 

내가 DBMS를 직접 처음부터 만들게 아니라면 굳이 몰라도 됩니다.