TCL(Transaction Control Language) in MySQL

DBMS에서 사용할 수 있는 SQL Commands는 크게

• DDL(Data Definition Launguage): 데이터베이스, 테이블을 제어
• DML(Data Manipulation Launguage): 테이블 내 데이터의 검색/등록/삭제/갱신의 연산 제어
• DCL(Data Control Launguage): 데이터베이스에 접근할 때의 권한을 제어
• TCL(Transaction Control Launguage): 논리적 작업 단위인 트랜잭션에 관한 연산을 제어

로 나뉜다.

 

대표적인 RDBMS인 MySQL에서의 TCL 연산을 직접 수행해보고 이해해보자

출처: https://media.geeksforgeeks.org/wp-content/uploads/sql-commands.jpg

 

TCL 연산

• COMMIT: 트랜잭션 내의 변경 사항을 영속적으로 데이터베이스에 반영한다
• ROLLBACK: SAVEPOINT 혹은 마지막 커밋 시점으로 데이터베이스의 상태를 되돌린다
• SAVEPOINT: 트랜잭션의 수행 도중 실패가 일어나 취소되거나, ROLLBACK 연산을 직접 수행했을 때 해당 시점으로 돌아가기 위한 체크포인트(해당 트랜잭션 내에서만 존재한다)
• SET TRANSACTION: 트랜잭션을 명시적으로 시작한다

 

Table DDL

drop table if exists test;
create table test(
	num int not null
);

int형 데이터타입의 'num'이라는 컬럼을 가진 테이블을 생성한다

 

Transaction 예시

start transaction;

insert into test values(2);
savepoint p2;
insert into test values(3);
insert into test values(4);
savepoint p4;
insert into test values(5);
insert into test values(6);

# rollback to p2; -> 1번 TCL
# rollback to p4; -> 2번 TCL

commit;

select * from test;

savepoint p2까지는 {2}라는 데이터가, savepoint p4까지는 {2,3,4}라는 데이터가, commit 시점까지는 {2,3,4,5,6}이라는 데이터가 저장되었을 것이다

주석을 하나씩 해제해보면서 테이블의 상태가 어떻게 되는지 관찰해보자

 

1️⃣ 1번 TCL의 주석을 해제했을 때

SAVEPOINT p2 시점으로 테이블의 상태가 돌아간다

트랜잭션의 시작 이후 "insert into test values(2);" 만 실행된 상태라서 테이블에는 {2}만 들어있다

 

2️⃣ 2번 TCL의 주석을 해제했을 때

SAVEPOINT는 p2, p4가 저장되어 있다. p4 시점으로 테이블 상태가 돌아가면 {2, 3, 4}가 insert된 상태를 확인할 수 있다.

 

3️⃣ 1, 2번 TCL의 주석을 둘 다 해제했을 때

SAVEPOINT p2 시점으로 이동하면 p4는 존재하지 않아서 그 다음 줄을 실행하면 오류가 발생한다

 

4️⃣ 1, 2번 TCL의 주석을 둘 다 해제하고 위치를 서로 바꾸었을 때

SAVEPOINT p4시점으로 이동한 뒤 p2시점으로 이동하면 1번 테스트 결과와 동일하다.

 

5️⃣ 1, 2번 TCL의 주석을 둘 다 해제하지 않았을 때

{2,3,4,5,6}의 insert가 수행되고 commit을 실행한다. 트랜잭션이 종료된 뒤 select를 수행한 모습이다.

 

SAVEPOINT의 Life Cycle

drop table if exists test;
create table test(
	num int not null
);

start transaction;

insert into test values(0);
savepoint p0;
insert into test values(1);

commit;

start transaction;

insert into test values(2);
savepoint p2;
insert into test values(3);
insert into test values(4);
savepoint p4;
insert into test values(5);
insert into test values(6);

rollback to p0;

commit;

select * from test;

두 개의 트랜잭션을 순차적으로 실행한 테스트이다.

첫 번째 트랜잭션에서는 p0라는 SAVEPOINT를 지정하고 {0, 1}의 데이터를 저장한 뒤 커밋했고

두 번째 트랜잭션에서는 p2, p4라는 SAVEPOINT를 지정하고 {2, 3, 4, 5, 6}의 데이터를 저장했다.

이 상황에서 두 번째 트랜잭션 수행 도중 p0로 ROLLBACK을 시도하면 {0}만 저장된 데이터베이스 상태로 돌아갈 수 있을까?

 

돌아갈 수 없다.

SAVEPOINT는 해당 트랜잭션이 수행되는 도중에만 저장되고, 트랜잭션이 종료되고 나면 모두 삭제된다.