[MySQL]TCL (Transaction Control Language)

haejun-kim·2020년 8월 14일

[DATABASE]

목록 보기

1/7

과제 - 동영상 시청 후 TCL(transaction Control Language)에 대하여 조사한 후 MySQL 에서 Author 테이블을 만들고 트랜젝션 쿼리문 테스트 할 것

What is TCL

TCL이란 Transaction Control Language의 약어로써, DCL(Data Control Language)에서 트랜잭션을 제어하는 명령인 COMMIT과 ROLLBACK 만을 따로 분리해서 TCL이라고 표현하고 있다.

COMMIT 연산

COMMIT 연산은 트랜잭션 처리가 정상적으로 종료되어 트랜잭션이 수행한 변경 내용을 데이터베이스에 반영하는 연산이다.
내용을 변경 한 트랜잭션이 완료되면 그 트랜잭션에 의해 데이터베이스는 새롭게 일관 된 상태로 변경되며, 이 상태는 시스템 오류가 발생하더라도 취소되지 않는다.
COMMIT 명령어는 데이터베이스에서 자동으로 실행해주는것 같다. 그 예로 COMMIT 명령어를 따로 입력해주지 않아도 데이터베이스에 값을 저장하면 그대로 반영된다.

Syntax : COMMIT;

ROLLBACK 연산

ROLLBACK 연산은 하나의 트랜잭션 처리가 비정상적으로 종료되어 데이터베이스의 일관성이 깨졌을 때, 트랜잭션이 행한 모든 변경 작업을 취소하고 이전 상태로 되돌리는 연산
ROLLBACK 연산 시 해당 트랜잭션은 받았던 자원과 잠금(LOCK)을 모두 반환하고, 재시작(Restart) 또는 폐기가 된다.
ROLLBACK 연산 시 마지막 COMMIT 이후의 작업으로 돌아가는데 이 때, 만약 COMMIT을 하지 않았다면 모든 작업이 사라진다.

Syntax : ROLLBACK TO 'SAVEPOINT_NAME';

LOCK

트랜잭션이 수행되는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법

SAVEPOINT

SAVEPOINT는 현재의 트랜잭션을 작게 분할하는 명령어
저장 된 SAVEPOINT는 ROLLBACK TO SAVEPOINT 문을 사용하여 지정한 곳까지 ROLLBACK 할 수 있다.
여러개의 SQL문을 수반하는 트랜잭션의 경우, 사용자가 트랜잭션 중간 단계에서 SAVEPOINT를 지정할 수 있다.
이 SAVEPOINT는 ROLLBACK과 함께 사용하며, 현재 트랜잭션 내의 특정 SAVEPOINT까지 ROLLBACK 할 수 있다.

Syntax : SAVEPOINT 포인터명;

SAVEPOINT 유의사항

SAVEPOINT 후 COMMIT 연산을 하게 된다면 COMMIT 연산 이전에 만든 SAVEPOINT들은 모두 사라진다.
SAVEPOINT는 여러개 생성할 수 있다.

예제

mysql> select * from Author;
+----+---------+
| id | name    |
+----+---------+
|  1 | Abhijit |
|  2 | Chris   |
|  3 | Bravo   |
|  4 | James   |
+----+---------+
4 rows in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVEPOINT D;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Author VALUES(5, 'Cap');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM Author;
+----+---------+
| id | name    |
+----+---------+
|  1 | Abhijit |
|  2 | Chris   |
|  3 | Bravo   |
|  4 | James   |
|  5 | Cap     |
+----+---------+
5 rows in set (0.00 sec)

mysql> ROLLBACK TO D;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->
    -> * FROM Author;
+----+---------+
| id | name    |
+----+---------+
|  1 | Abhijit |
|  2 | Chris   |
|  3 | Bravo   |
|  4 | James   |
+----+---------+
4 rows in set (0.00 sec)

SELECT * FROM Author; 를 통해서 현재 Author 테이블이 내용을 볼 수 있다. 현재는 총 네개의 row가 저장되어 있다.

START TRANSACTION으로 트랜잭션을 시작해주고, 그 상태에서 D 라는 savepoint를 생성해주었다.

그 상태에서 id=5, name=Cap이라는 값을 insert 해주었고 savepoint인 D로 ROLLBACK 해주었더니 row를 추가하기 전의 상태로 돌아갔다.

이런 경우에 TCL 을 주로 사용하는 것 같다. 지금처럼 간단히 연습용이 아니라 프로젝트를 시작하거나 실제 서비스에서 데이터를 직접 다룰 경우 실수를 방지하기 위해 이 개념을 숙지하고 있는것은 유용할거라 생각된다.

What is START TRANSACTION

위의 설명에 start transaction이라는 문법을 중간에 넣어주다.

START TRANSACTION이라는 명령어는 트랜잭션을 시작할 때 사용하는 명령어다. 해당 명령어 외에 BEGIN 이나 BEGIN WORK를 입력해도 된다.

위의 명령어를 입력하고 Query OK라는 메세지가 출력되면 정상적으로 트랜잭션이 시작된 것이고, 해당 문구가 출력되지 않으면 트랜잭션이 정상적으로 시작되지 않은것이니 확인이 필요하다.

처음 개념만 공부할때에는 START TRANSACTION 이라는 명령어에 대해서 알지 못했는데 막상 연습을 해보려고 하니 분명히 savepoint를 생성했고, Query OK 문구가 출력됐음에도 불구하고 해당 세이브포인트로 롤백을 하려고 하면 ERROR 1305 (42000): SAVEPOINT A does not exist 에러가 발생됐다.

에러가 발생한 내용에 대해서 내가 이해한 바를 적어보면, START TRANSACTION 명령을 해 주어야 SAVE POINT가 제대로 생성이 되고, 그 시점으로 ROLLBACK을 할 수가 있는 로직이라는 것이다. 원래는 COMMIT을 해주어야 데이터베이스에 저장을 하는 트랜잭션을 시작하는것인데 데이터베이스에서 자동으로 COMMIT 기능을 지원해주기에 COMMIT을 안해주어도 데이터가 저장이 되는것이고, 세이브 포인트를 저장하기 위해선 START TRANSACTION을 해주고 만들어주면 이제 '이 시점을 하나의 세이브 포인트로 만들거야'라는 인식을 시켜준다는 개념으로 생각했다.

따라서 다음과 같이 생각하자.

세이브 포인트를 만들 때는 START TRANSACTION을 실행하고 SAVE POINT를 생성하자. 그 후 롤백을 하면 에러가 발생하지 않고 해당 세이브 포인트로 롤백이 가능해진다.

haejun-kim

다음 포스트