[SQLD : Ⅲ. SQL 기본] 4. TCL

* 트랜잭션 특성

 - 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남음

 - 일관성 : 트랜잭션 실행 전에 내용이 잘못되지 않으면 트랜잭션 실행 후에도 내용이 잘못되면 안됨

 - 고립성 : 트랜잭션 실행 중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 안됨

 - 지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스 내용은 영구적으로 저장

 

* COMMIT

 - 메모리 버퍼에만 영향을 받아서 데이터 변경 이전 상태로 복구 가능

[예제] PLAYER 테이블에 데이터를 입력하고 COMMIT을 실행한다.

[예제] Oracle INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
                          VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1); 
                          1개의 행이 만들어졌다. COMMIT; 커밋이 완료되었다.

[예제] PLAYER 테이블에 있는 데이터를 수정하고 COMMIT을 실행한다.

[예제] Oracle UPDATE PLAYER SET HEIGHT = 100; 
480개의 행이 수정되었다. COMMIT; 커밋이 완료되었다.

[예제] PLAYER 테이블에 있는 데이터를 삭제하고 COMMIT을 실행한다.

[예제] Oracle DELETE FROM PLAYER; 
480개의 행이 삭제되었다. COMMIT; 커밋이 완료되었다.

 - COMMIT은 INSERT, UPDATE, DELETE 후 변경 작업이 완료되었음을 데이터베이스에 알려주기 위해 사용

 - SQL server

  [예제] PLAYER 테이블에 데이터를 입력한다.

[예제] SQL Server INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
                              VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1); 
                              1개의 행이 만들어졌다.

  [예제] PLAYER 테이블에 있는 데이터를 수정한다.

[예제] SQL Server UPDATE PLAYER SET HEIGHT = 100; 
480개의 행이 수정되었다.

  [예제] PLAYER 테이블에 있는 데이터를 삭제한다.

[예제] SQL Server DELETE FROM PLAYER; 
480개의 행이 삭제되었다.

 - SQL server에서 트랜잭션 이루지는 방식

   1) AUTO COMMIT SQL server의 기본 방식, DML DDL 수행마다 DBMS가 트랜잭션을 컨트롤하는 방식

     명령어가 성공적으로 수행되면 자동으로 COMMIT을 수행하고 오류 발생 시 자동으로 ROLLBACK 수행

   2) 암시적 트랜잭션 오라클과 같은 방식

     트랜잭션 시작은 DBMS가 처리하고 끝은 명시적으로 COMMIT 또는 ROLLBACK으로 처리

     인스턴스 단위 또는 세션 단위로 설정할 수 있음

     인스턴스 단위로 설정하려면 서버 속성 창의 연결화면에서 기본연결 옵션 중 암시적 트랜잭션에 체크

     세션 단위로 설정하려면 세션 옵션 중 SET IMPLICIT TRANSACCTION ON 설정

   3) 트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식

     BEGIN TRANSACTION으로 시작하고 COMMIT TRANSACTION또는 ROLLBACK TRANSACTION으로 종료

     ROLLBACK구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK

 

* ROLLBACK

 - 테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 COMMIT 이전에 변경 사항을 취소

 - 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며 관련 행에 대한 LOCKING이 풀림

[예제] PLAYER 테이블에 데이터를 입력하고 ROLLBACK을 실행한다.

[예제] Oracle INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) 
                          VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. 
              ROLLBACK; 롤백이 완료되었다.

[예제] PLAYER 테이블에 있는 데이터를 수정하고 ROLLBACK을 실행한다.

[예제] Oracle UPDATE PLAYER SET HEIGHT = 100; 480개의 행이 수정되었다. 
              ROLLBACK; 롤백이 완료되었다.

[예제] PLAYER 테이블에 있는 데이터를 삭제하고 ROLLBACK을 실행한다.

[예제] Oracle DELETE FROM PLAYER; 480개의 행이 삭제되었다. 
              ROLLBACK; 롤백이 완료되었다.

 - SQL server에서 ROLLBACK

  [예제] PLAYER 테이블에 데이터를 입력하고 ROLLBACK을 실행한다.

[예제] SQL Server BEGIN TRAN INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) 
                             VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. 
                  ROLLBACK; 롤백이 완료되었다.

   [예제] PLAYER 테이블에 있는 데이터를 수정하고 ROLLBACK을 실행한다.

[예제] SQL Server BEGIN TRAN UPDATE PLAYER SET HEIGHT = 100; 480개의 행이 수정되었다.
                  ROLLBACK; 롤백이 완료되었다.

  - ROLLBACK 후 : 데이터에 대한 변경 사항 취소, 이전 데이터 재저장, LOCKING이 풀리고 다른 사용자 조작 가능

  - COMMIT과 ROLLBACK 사용 : 데이터 무결성 보장, 영구적 변경 전 변경사항 확인 가능, 논리적으로 그루핑 처리 가능

 

* SAVEPOINT

 - ROLLBACK 시 SAVEPOINT까지 트랜잭션의 일부만 롤백 가능

 - 복잡한 대규모 트랜잭션에서 에러 발생 시 SAVEPOINT 까지만 롤백하고 실패 부분 다시 실행

SAVEPOINT SVPT1; // SAVEPOINT 정의
ROLLBACK TO SVPT1; // SAVEPOINT까지 롤백
ROLLBACK TRANSACTION SVTR1; // SAVEPOINT까지 롤백

[예제] SAVEPOINT를 지정하고,

PLAYER 테이블에 데이터를 입력한 다음 롤백(ROLLBACK)을 이전에 설정한 저장점까지 실행한다.

[예제] Oracle SAVEPOINT SVPT1; 저장점이 생성되었다. 
              INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) 
                          VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. 
              ROLLBACK TO SVPT1; 롤백이 완료되었다.
              
[예제] SQL Server SAVE TRAN SVTR1; 저장점이 생성되었다. 
                  INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) 
                              VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. 
                  ROLLBACK TRAN SVTR1; 롤백이 완료되었다.

[예제] 먼저 SAVEPOINT를 지정하고

PLAYER 테이블에 있는 데이터를 수정한 다음 롤백(ROLLBACK)을 이전에 설정한 저장점까지 실행한다.

[예제] Oracle SAVEPOINT SVPT2; 저장점이 생성되었다. 
              UPDATE PLAYER SET WEIGHT = 100; 480개의 행이 수정되었다.
              ROLLBACK TO SVPT2; 롤백이 완료되었다.
              
[예제] SQL Server SAVE TRAN SVTR2; 저장점이 생성되었다. 
                  UPDATE PLAYER SET WEIGHT = 100; 480개의 행이 수정되었다. 
                  ROLLBACK TRAN SVTR2; 롤백이 완료되었다.

[예제] SAVEPOINT를 지정하고,

PLAYER 테이블에 있는 데이터를 삭제한 다음 롤백(ROLLBACK)을 이전에 설정한 저장점까지 실행한다.

[예제] Oracle SAVEPOINT SVPT3; 저장점이 생성되었다. 
              DELETE FROM PLAYER; 480개의 행이 삭제되었다.
              ROLLBACK TO SVPT3; 롤백이 완료되었다.
              
[예제] SQL Server SAVE TRAN SVTR3; 저장점이 생성되었다. 
                  DELETE FROM PLAYER; 480개의 행이 삭제되었다. 
                  ROLLBACK TRAN SVTR3; 롤백이 완료되었다.

 - 저장점 A로 돌리고 나서 다시 B와 같이 미래의 방향으로 되돌릴 수는 없음

[예제] 새로운 트랜잭션을 시작하기 전에

PLAYER 테이블의 데이터 건수와 몸무게가 100인 선수의 데이터 건수를 확인한다.

[예제 및 실행 결과] Oracle 
SELECT COUNT(*) 
FROM PLAYER; 
COUNT(*) ------- 480 1개의 행이 선택되었다. 

SELECT COUNT(*) 
FROM PLAYER
WHERE WEIGHT = 100; 
COUNT(*) ------- 0 1개의 행이 선택되었다.

[예제] [그림 Ⅱ-1-11]을 확인하기 위해 새로운 트랜잭션을 시작하고

SAVEPOINT A와 SAVEPOINT B를 지정한다. (툴에 AUTO COMMIT 옵션이 적용되어 있는 경우 해제함)

[예제 및 실행 결과] Oracle 새로운 트랜잭션 시작 
INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) 
            VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. 
SAVEPOINT SVPT_A; 저장점이 생성되었다. 
UPDATE PLAYER SET WEIGHT = 100; 481개의 행이 수정되었다. 
SAVEPOINT SVPT_B; 저장점이 생성되었다. 
DELETE FROM PLAYER; 481개의 행이 삭제되었다. 
현재 위치에서 [예제] CASE 1,2,3을 순서대로 수행해본다.

[예제] CASE1. SAVEPOINT B 저장점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.

[예제 및 실행 결과] Oracle 
SELECT COUNT(*) FROM PLAYER; 
COUNT(*) -------- 0 1개의 행이 선택되었다. 

ROLLBACK TO SVPT_B; 롤백이 완료되었다. 
SELECT COUNT(*) 
FROM PLAYER; 
COUNT(*) ------- 481 1개의 행이 선택되었다.

[예제] CASE2. SAVEPOINT A 저장점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.

[예제 및 실행 결과] Oracle 
SELECT COUNT(*) 
FROM PLAYER 
WHERE WEIGHT = 100; 
COUNT(*) ------- 481 1개의 행이 선택되었다. 

ROLLBACK TO SVPT_A; 롤백이 완료되었다. 
SELECT COUNT(*) 
FROM PLAYER 
WHERE WEIGHT = 100; 
COUNT(*) ------- 0 1개의 행이 선택되었다.

[예제] CASE3. 트랜잭션 최초 시점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.

[예제 및 실행 결과] Oracle 
SELECT COUNT(*) 
FROM PLAYER; 
COUNT(*) ------- 481 1개의 행이 선택되었다. 

ROLLBACK; 롤백이 완료되었다. 
SELECT COUNT(*) 
FROM PLAYER; 
COUNT(*) ------- 480 1개의 행이 선택되었다.