5-1) MySQL 고급기능(트랜잭션)

트랜잭션 소개

• 테이블 내용을 변경하는 SQL들이 연달아 실행되며이것들이 마치 하나의 SQL처럼 다 같이 성공하던지 아니면 실패해야 한다면 이전 상태로 돌아가는 트랜잭션의 사용이 필수!

---

트랜잭션이란?

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법 → 다수의 SQL문을 실행해도 하나의 SQL처럼 다같이 성공하거나 실패하는 것처럼 실행되는 것

  • DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음 → SELECT는 읽기만 하므로 트랜잭션을 사용할 이유가 없음

  • BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용

  • ROLLBACK - BEGIN 뒤에 실행된 문장들이 무효화됨

    → END나 COMMIT이 불리기 전까지는 MySQL사용자에게는 바뀐것 처럼 보이지만 다른 사람들 눈에는 안보임

• 은행 계좌 이체가 아주 좋은 예
  • 계좌 이체: 인출과 입금의 두 과정으로 이뤄짐
  • 에러 : 만일 인출은 성공했는데 입금이 실패한다면? → 돈이 중간에 사라짐
  • 이 두 과정은 동시에 성공하던지 실패해야함 -> Atomic하다는 의미
  • 이런 과정들을 트랜잭션으로 묶어주어야함
  • 조회(SELECT)만 한다면 이는 트랜잭션으로 묶일 이유가 없음

BEGIN; -- START TRANSACTION
	A의 계좌로부터 인출;
	B의 계좌로 입금;
END; -- COMMIT

빨간색 형광펜 : 이 명령어들은 마치 하나의 명령어처럼 처리됨. 다 성공하던지 다 실패하던지 둘중의 하나가 됨

• BEGIN과 START TRANSACTION은 같은 의미
• END와 COMMIT은 동일
• 만일 BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK 실행
• 이 동작은 autocommit 모드에 따라 달라짐!

---

트랜잭션 커밋 모드: autocommit

• autocommit = True(보통 이렇게 사용)
  • 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐 → 커밋(Commit)된다고 함.
  • 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리 → True일 때만 BEGIN과 END 사용 가능!
• autocommit = False

  • 모든 레코드 수정/삭제/추가 작업이 COMMIT이 호출될 때까지 커밋되지 않음 → 명시적으로 커밋을 해야함

  • ROLLBACK이 호출되면 앞서 작업들이 무시됨

  • BEGIN과 END(COMMIT)/ROLLBACK이 없음 → 트랜잭션이라는 개념이 X

    ⇒ autocommit 여부는 SQL 클라이언트/라이브러리에 따라 달라짐
    ⇒ MySQL Workbench 기본은 autocommit이 True

  • 확인 방법: SHOW VARIABLES LIKE 'AUTOCOMMIT'; →출력이 on이면 true

  • SET autocommit=0 (혹은 1)의 실행으로 변경가능

autocommit 실습

[사전 테이블 생성]

• 아래 테스트 테이블 이름에서 빨간색 형광펜(keeyong_) 대신에 각자 영문 이름을 사용할 것
• test 데이터베이스는 guest 계정도 쓰기 권한이 있음

DROP TABLE IF EXISTS test.hyoin_name_gender; -> 만약 똑같은 이름있으면 충돌이 나니까 삭제
CREATE TABLE test.hyoin_name_gender (
	name varchar(16) NOT NULL,
	gender enum('Male','Female') default NULL
);
INSERT INTO test.hyoin_name_gender VALUES('Keeyong', 'Male');
INSERT INTO test.hyoin_name_gender VALUES('Jane', 'Female');
INSERT INTO test.hyoin_name_gender VALUES('Unknown'); -> 테이블의 필드보다 값이 적으면 에러
	-> 해결방법 : INSERT INTO test.hyoin_name_gender(name) VALUES('Unknown');
INSERT INTO test.hyoin_name_gender VALUES('Keeyong2', 'Male2'); -> Male2은 없는 값이지만 에러는 X 대신 비어있는 문자열("")로 설정

[예제 1] autocommit = True

SHOW VARIABLES LIKE 'AUTOCOMMIT';
-- SET autocommit=1;
SELECT * FROM test.hyoin_name_gender;

BEGIN;
DELETE FROM test.hyoin_name_gender;
INSERT INTO test.hyoin_name_gender VALUES ('Kevin', 'Male');
ROLLBACK;

SELECT * FROM test.hyoin_name_gender;

[예제 2] autocommit = False

SET autocommit=0;
SHOW VARIABLES LIKE 'AUTOCOMMIT';

SELECT * FROM test.hyoin_name_gender;
-- BEGIN이 없음
DELETE FROM test.hyoin_name_gender;
INSERT INTO test.hyoin_name_gender VALUES ('Kevin', 'Male');
ROLLBACK;

SELECT * FROM test.hyoin_name_gender;

---

DELETE FROM vs. TRUNCATE

1. DELETE FROM table_name (not DELETE * FROM)
   1. 테이블에서 모든 레코드를 삭제 → 테이블은 존재
   2. vs. DROP TABLE table_name → 테이블 자체도 삭제
   3. WHERE 사용해 특정 레코드만 삭제 가능:
      ▪ DELETE FROM raw_data.user_session_channel WHERE channel = 'Google';
2. TRUNCATE table_name도 테이블에서 모든 레코드를 삭제
   1. DELETE FROM은 속도가 느림
   2. TRUNCATE이 전체 테이블의 내용 삭제시에는 여러모로 유리
   3. 하지만 두가지 단점이 존재
      ▪ TRUNCATE는 WHERE을 지원X
      ▪ TRUNCATE는 Transaction을 지원X → Transaction내에서 사용되면 commit을 쓰지 않아도 삭제함