[MYSQL]MYSQL 기본 문법: TCL

TCL이란???

TCL은 Transaction Control Language로 트랜잭션 제어 언어의 약자이다. 말 그대로 트랜잭션을 제어하는 언어인데, 이때 트랜잭션이란, 트랜잭션은 데이터베이스의 논리적 연산단위이다!

하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함되며 분할할 수 없는 최소의 단위이다. 즉 ALL OR NOTHING.

 

트랜잭션의 특성도 몇가지가 있다.

특성	설명
원자성 (Atomicity)	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아있어야 한다. ( All Or Nothing )
일관성 (Consistency)	트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 된다.
고립성 (Isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
지속성 (Durability)	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이커베이스의 내용은 영구적으로 저장된다.

그럼 이제 이 트랜잭션을 제어하는 언어에 대해 알아보자!

 

TCL: 트랜잭션 제어

• TRANSACTION: 최소 작업 단위
• (예시)계좌 이체
  • a구좌 인출
  • b구좌 입금
• (예시)도서관 도서 대량 구매
  • 동일한 도서가 중복 존재: 50권
  • 신규도서, 기존도서
  • 마스터 테이블: 도서에 대한 정보
  • 디테일 테이블: 중복된 도서의 수량만큼 정보
• start transaction
  • 트랜잭션 시작

• commit
  • 영구 반영

• rollback
  • 원래 상태 복구: 트랜잭션 시작 전체 원래 상태 복구
• rollback to 포인트명;
  • 포인트 지점까지만 원래 상태 복구

• savepoint 포인트명
  • 복구지점 지정

 

commit

• DDL: auto commit
  • 객체 생성, 변경, 삭제할 때 자동으로 커밋이 됨
• workbench 종료: auto commit
• DML:
  • 레코드 추가, 변경, 삭제: 명시적 commit, rollback
  • 개발자가 명시적으로 트랜잭션 처리하려면 tools 설정을 false로

-- autocommit 상태 조회: 1=자동커밋, 0=미사용
select @@autocommit;

-- autocommit 상태 변경: true(자동커밋), false(미사용 - 명시적tx처리)
-- workbench > Query > Auto-Commit Transaction > 클릭 토글(설정/해제)
set autocommit = false;

• 콘솔, workbench 설정
  • auto commit: 설정
  • auto commit: 미사용
  • 콘솔창에서

commit 예제

-- test 테이블 생성, 컬럼 data varchar(10)
create table test (data varchar(10));

-- 트랜잭션 시작 선언
start transaction;

-- test 테이블에 데이터 추가
insert into test values('a1');
insert into test values('b1');
insert into test values('b2');

-- 영구 반영
commit;

 

rollback

COMMIT 이전에는 변경 사항을 취소할 수 있는데 데이터베이스에서 ROLLBACK 기능을 사용함!

ROLLBACK은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 데이터를 변경 할 수 있음!

 

rollback 예제

-- test 테이블 생성, 컬럼 data varchar(10)
create table test (data varchar(10));

-- 트랜잭션 시작 선언
start transaction;

-- test 테이블에 데이터 추가
insert into test values('a');
insert into test values('b');

-- test 테이블 전체 조회
select * from test;

-- 원상복구
rollback;

-- test 테이블 전체 조회
select * from test;

-- 테이블에 데이터가 없는 상태로 돌아감!

 

DML과 DDL의 차이

-- delete 예제 --------------------------
-- 트랜잭션 시작 선언
start transaction;

-- 전체 레코드 삭제, delete는 원상복구 가능
delete from test;
-- delete는 원상복구 가능
rollback;

-- truncate 예제 --------------------------
-- 트랜잭션 시작 선언
start transaction;

-- 전체 레코드 삭제: 조건 부분삭제 불가, 원상복구 불가(완전 삭제)
truncate test;
-- 해봤자 안됨
rollback;

-- drop 예제-------------
-- test 테이블 삭제
drop table test;

rollback;
-- 해봤자 안됨

show tables;

• delete(DML)은 rollback이 되는 반면에 truncate(DDL)는 rollback이 되지 않음!(drop(DDL)도 안됨!)

 

savepoint

말 그대로 저장점. 저장점을 정의하면 rollback할 때 트랜잭션에 포함된 전체 작업을 rollback하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 rollback할 수 있음!

여러 개의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효!

 

savepoint 예제

-- 트랜잭션 포인트 지정: p1
savepoint p1;

-- test 테이블에 데이터 추가
insert into test values('a1');
insert into test values('b1');
insert into test values('b2');

-- test 테이블 전체 조회
select * from test;

-- 트랜잭션 포인트 p1까지 원상 복구
rollback to p1;

-- test 테이블 전체 조회
select * from test;

-- 트랜잭션 전체 원상 복구
rollback;

-- test 테이블 전체 조회
select * from test;

 

 

정리

TCL에는 COMMIT, ROLLBACK, SAVEPOINT가 있다!