SQLD - TCL

1. 트랜잭션 개요

- 트랜잭션은 데이터베이스의 논리적 연산단위이다.

- 트랜잭션(TRANSACTION)이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

 

- 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.

- 트랜잭션은 분할할 수 없는 최소의 단위이다. 그렇기 때문에 전부 적용하거나 전부 취소한다. 즉, TRANSACTION 은 ALL OR NOTHING 의 개념인 것이다.

 

은행에서의 계좌이체 상황을 연상하면 트랜잭션을 이해하는데 도움이 된다. 계좌이체는 최소한 두 가지 이상의 작업으로 이루어져 있다.
계좌이체 사례 - STEP1. 100 번 계좌의 잔액에서 10,000 원을 뺀다. - STEP2. 200 번 계좌의 잔액 에 10,000 원을 더한다.
계좌이체라는 작업 단위는 이런 두 개의 업데이트가 모두 성공적으로 완료되었을 때 종료된다.
둘 중 하나라도 실패할 경우 계좌이체는 원래의 금액을 유지하고 있어야만 한다.
만약 어떠한 장애에 의해 어느 쪽이든 한 쪽만 실행했을 경우, 이체한 금액은 어디로 증발해 버렸거나 마음대로 증가하 게 된다.
당연히 그런 일이 있어서는 안 되므로 이러한 경우에는 수정을 취소하여 원 상태로 되돌려야 한다.
이런 계좌이체 같은 하나의 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합을 트랜잭션이라 한다.
이런 관점에서 데이터베이스 응용 프로그램은 트랜잭션의 집합으로 정의할 수도 있다.

 

2. TCL (Transaction Control Language)

트랜잭션	설명
COMMIT	올바르게 반영된 데이터를 DB에 반영시키는 것
ROLLBACK	트랜잭션 시작 이전의 상태로 되돌리는 것
SAVEPOINT	저장점 기능

 

3. 트랜잭션 대상이 되는 SQL

- UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML 문이다.

- SELECT 문장은 직접적인 트랜잭션의 대상이 아니지만, SELECT FOR UPDATE 등 배타적 LOCK 을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있다. 

 

4. 트랜잭션의 특징

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

- 트랜잭션의 특성(특히 원자성)을 충족하기 위해 데이터베이스는 다양한 레벨의 잠금 기능을 제공하고 있는데, 잠금은 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다.

 

- 잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있고 다른 트랜잭션으로부터 간섭이나 방해를 받지 않는 것이 보장된다.

 

- 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만이 해제할 수 있다.

 

5. COMMIT

1. ORACLE의 경우

- 입력, 수정, 삭제한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료할 수 있다.

- COMMIT 명령어는 INSERT, UPDATE, DELETE 문장을 사용한 후에 이런 변경이 완료되었음을 DB에 알려주기 위해 사용된다.

 

상태	설명
COMMIT 이나 ROLLBACK 이전의 데이터 상태	- 단지 메모리 BUFFER 에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다. - 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.   - 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.   - 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다.
COMMIT 이후의 데이터 상태	- 데이터에 대한 변경 사항이 데이터베이스에 반영된다. - 이전 데이터는 영원히 잃어버리게 된다.   - 모든 사용자는 결과를 볼 수 있다.   - 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

2. SQL Server의 경우

- SQL Server 는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT 이나 ROLLBACK 을 처리할 필요가 없다.

- DML 구문이 성공이면 자동으로 COMMIT 이 되고 오류가 발생할 경우 자동으로 ROLLBACK 처리된다.

 

mysql> INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID ) VALUES ('1997035', '이운재', 'K07');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM PLAYER;
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| PLAYER_ID | PLAYER_NAME | TEAM_ID | E_PLAYER_NAME | NICKNAME   | JOIN_YYY | POSITION | NATION | BACK_NO | BIRH_DATE | SOLAR | HEIGHT | WEIGHT |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| 1997035   | 이운재      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |   NULL |   NULL |
| 2002007   | 박지성      | K07     | NULL          | NULL       | NULL     | MF       | NULL   |       7 | NULL      | NULL  |    178 |     73 |
| 2002010   | 이청용      | K07     |               | BlueDragon | 2002     | MF       |        |      17 | NULL      | 1     |    180 |     67 |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
3 rows in set (0.00 sec)

mysql> UPDATE PLAYER SET HEIGHT = 100
    -> ;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM PLAYER;
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| PLAYER_ID | PLAYER_NAME | TEAM_ID | E_PLAYER_NAME | NICKNAME   | JOIN_YYY | POSITION | NATION | BACK_NO | BIRH_DATE | SOLAR | HEIGHT | WEIGHT |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| 1997035   | 이운재      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    100 |   NULL |
| 2002007   | 박지성      | K07     | NULL          | NULL       | NULL     | MF       | NULL   |       7 | NULL      | NULL  |    100 |     73 |
| 2002010   | 이청용      | K07     |               | BlueDragon | 2002     | MF       |        |      17 | NULL      | 1     |    100 |     67 |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
3 rows in set (0.00 sec)

 

* SQL Server에서의 Transaction
1) AUTO COMMIT
SQL Server 의 기본 방식이며, DML, DDL 을 수행할 때마다 DBMS가 트랜잭션을 컨트롤하는 방식이다.
명령어가 성공적으로 수행되면 자동으로 COMMIT 을 수행하고 오류가 발생하면 자동으로 ROLLBACK 을 수행한다.
2) 암시적 트랜잭션
은 Oracle 과 같은 방식으로 처리된다.
즉, 트랜잭션의 시작은 DBMS 가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK 으로 처리한다.
인스턴스 단위 또는 세션 단위로 설정할 수 있다.
인스턴스 단위로 설정하려면 서버 속성 창의 연결화면에서 기본 연결 옵션 중 암시적 트랜잭션에 체크를 해주면 된다.
세션 단위로 설정하기 위해서는 세션 옵션 중 SET IMPLICIT TRANSACTION ON 을 사용하면 된다.
3) 명시적 트랜잭션
트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다.
BEGIN TRANSACTION (BEGIN TRAN 구문도 가능)으로 트랜잭션을 시작하고 COMMITTRANSACTION(TRANSACTION 은 생략 가능) 또는 ROLLBACK TRANSACTION(TRANSACTION 은 생략 가능)으로 트랜잭션을 종료한다.
ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK 이 수행된다.

 

6. ROLLBACK

1. ROLLBACK 후의 데이터 상태

- 데이터에 대한 변경 사항은 취소된다.

- 이전 데이터는 다시 재저장된다.

- 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

2. ORACLE의 경우

- COMMIT 이전에는 변경 사항을 취소할 수 있다.

- 롤백(ROLLBACK)은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.

 

3. SQL Server의 경우

- SQL Server 는 위에서 언급한 바와 같이 AUTO COMMIT이 기본 방식이므로 임의적으로 ROLLBACK을 수행하려면 명시적으로 트랜잭션을 선언해야 한다.

 

** SQL Server의 경우 **
UPDATE/ INSERT/ DELETE 절 전에 BEGIN TRAN 을 붙인당.

** MYSQL의 경우 **


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

mysql> UPDATE PLAYER SET HEIGHT = 400;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

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

mysql> SELECT * FROM PLAYER;
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| PLAYER_ID | PLAYER_NAME | TEAM_ID | E_PLAYER_NAME | NICKNAME   | JOIN_YYY | POSITION | NATION | BACK_NO | BIRH_DATE | SOLAR | HEIGHT | WEIGHT |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| 1995124   | 유현지      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    400 |   NULL |
| 1997035   | 이운재      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    400 |   NULL |
| 2002007   | 박지성      | K07     | NULL          | NULL       | NULL     | MF       | NULL   |       7 | NULL      | NULL  |    400 |     73 |
| 2002010   | 이청용      | K07     |               | BlueDragon | 2002     | MF       |        |      17 | NULL      | 1     |    400 |     67 |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
4 rows in set (0.00 sec)

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

mysql> UPDATE PLAYER SET HEIGHT = 100;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

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

mysql> SELECT * FROM PLAYER;
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| PLAYER_ID | PLAYER_NAME | TEAM_ID | E_PLAYER_NAME | NICKNAME   | JOIN_YYY | POSITION | NATION | BACK_NO | BIRH_DATE | SOLAR | HEIGHT | WEIGHT |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| 1995124   | 유현지      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    400 |   NULL |
| 1997035   | 이운재      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    400 |   NULL |
| 2002007   | 박지성      | K07     | NULL          | NULL       | NULL     | MF       | NULL   |       7 | NULL      | NULL  |    400 |     73 |
| 2002010   | 이청용      | K07     |               | BlueDragon | 2002     | MF       |        |      17 | NULL      | 1     |    400 |     67 |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
4 rows in set (0.00 sec)

 

7. COMMIT 과 ROLLBACK 을 사용함으로써 얻는 효과

- 데이터 무결성 보장

- 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능

- 논리적으로 연관된 작업을 그룹핑하여 처리 가능

 

8. SAVEPOINT

- 트랜잭션 START 후에 정의해야함

- 저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT 까지 트랜잭션의 일부만 롤백할 수 있다.

- 따라서 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT 까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다. (일부 툴에서는 지원이 안 될 수 있음)

- 복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.

 

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

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

mysql> INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID) VALUES ('1998456', '이천수', 'K07');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM PLAYER;
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| PLAYER_ID | PLAYER_NAME | TEAM_ID | E_PLAYER_NAME | NICKNAME   | JOIN_YYY | POSITION | NATION | BACK_NO | BIRH_DATE | SOLAR | HEIGHT | WEIGHT |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| 1995124   | 유현지      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    400 |   NULL |
| 1997035   | 이운재      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    400 |   NULL |
| 1998456   | 이천수      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |   NULL |   NULL |
| 1998753   | 기성용      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |   NULL |   NULL |
| 2002007   | 박지성      | K07     | NULL          | NULL       | NULL     | MF       | NULL   |       7 | NULL      | NULL  |    400 |     73 |
| 2002010   | 이청용      | K07     |               | BlueDragon | 2002     | MF       |        |      17 | NULL      | 1     |    400 |     67 |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
6 rows in set (0.00 sec)

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

mysql> SELECT * FROM PLAYER;
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| PLAYER_ID | PLAYER_NAME | TEAM_ID | E_PLAYER_NAME | NICKNAME   | JOIN_YYY | POSITION | NATION | BACK_NO | BIRH_DATE | SOLAR | HEIGHT | WEIGHT |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
| 1995124   | 유현지      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    400 |   NULL |
| 1997035   | 이운재      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |    400 |   NULL |
| 1998753   | 기성용      | K07     | NULL          | NULL       | NULL     | NULL     | NULL   |    NULL | NULL      | NULL  |   NULL |   NULL |
| 2002007   | 박지성      | K07     | NULL          | NULL       | NULL     | MF       | NULL   |       7 | NULL      | NULL  |    400 |     73 |
| 2002010   | 이청용      | K07     |               | BlueDragon | 2002     | MF       |        |      17 | NULL      | 1     |    400 |     67 |
+-----------+-------------+---------+---------------+------------+----------+----------+--------+---------+-----------+-------+--------+--------+
5 rows in set (0.00 sec)

- 저장점 A로 되돌리고 나서 다시 B와 같은 미래로 되돌릴 수 없다.

- 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점은 무료가 된다.

- 저장점 없이 롤백하면 모든 변경 사항을 취소한다.

 

9. Transaction 정리

1. COMMIT과 ROLLBACK의 목적

- 해당 테이블에 데이터의 변경을 발생시키는 입력(INSERT), 수정(UPDATE), 삭제(DELETE) 수행 시 변경되는 그 데이터의 무결성을 보장하는 것.

 

2. COMMIT

- 변경된 데이터를 테이블에 영구적으로 반영해라”라는 의미

 

3. ROLLBACK

- 변경된 데이터가 문제가 있으니 변경 전 데이터로 복귀하라는 의미

 

4. Oracle 의 트랜잭션

- 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, COMMIT 또는 ROLLBACK 을 실행한 시점에서 종료된다.

- 단, 다음의 경우에는 COMMIT 과 ROLLBACK 을 실행하지 않아도 자동으로 트랜잭션이 종료된다.

• CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전 후 시점 에 자동으로 커밋된다.
• DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전 에 자동으로 커밋된다.
• 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
• 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다. 

5. SQL Server 의 트랜잭션

- DBMS 가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT 이 기본 방식 이다.

- 다음의 경우는 Oracle 과 같이 자동으로 트랜잭션이 종료된다.

• 애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.