[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 1장. SQL 기본 - 4. TCL(Transaction Control Language)

TCL(Transaction Control Language)

트랜잭션 개요

• 트랜잭션(TRANSACTION)이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.
• 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.
• 트랜잭션은 분할할 수 없는 최소의 단위이기 때문에 전부 적용하거나 전부 취소한다.
• 트랜잭션을 콘트롤하는 TCL(TRANSACTION CONTROL LANGUAGE)의 분류
  • 커밋(COMMIT) : 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것
  • 롤백 (ROLLBACK) : 트랜잭션 시작 이전의 상태로 되돌리는 것
  • 저장점(SAVEPOINT)

트랜잭션의 특성

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

COMMIT

• 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료할 수 있다.
• COMMIT이나 ROLLBACK 이전의 데이터 상태
  • 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.
  • 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
  • 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
  • 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다.
• COMMIT 명령어는 이처럼 INSERT 문장, UPDATE 문장, DELETE 문장을 사용한 후에 이런 변경 작업이 완료되었음을 데이터베이스에 알려 주기 위해 사용한다.
• COMMIT 이후의 데이터 상태
  • 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
  • 이전 데이터는 영원히 잃어버리게 된다.
  • 모든 사용자는 결과를 볼 수 있다.
  • 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

Oracle의 COMMIT

• Oracle은 DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하며 DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료된다.
• PLAYER 테이블에 있는 데이터를 수정하고 COMMIT을 실행해보자.

UPDATE PLAYER
SET HEIGHT = 100; 

COMMIT;

SQL Server의 COMMIT

• SQL Server는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT이나 ROLLBACK을 처리할 필요가 없다.
• DML 구문이 성공이면 자동으로 COMMIT이 되고 오류가 발생할 경우 자동으로 ROLLBACK 처리된다.
• 이전 예제를 SQL Server용으로 변경하면 아래와 같다.

UPDATE PLAYER
SET HEIGHT = 100;

SQL Server에서 트랜잭션이 이루어지는 방식

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

ROLLBACK

• 테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경 사항을 취소
• 데이터가 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있음.

예제

• PLAYER 테이블에 있는 데이터를 삭제하고 ROLLBACK을 실행해보자.
• Oracle

DELETE FROM PLAYER;

ROLLBACK;

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

BEGIN TRAN
DELETE FROM PLAYER;

ROLLBACK;

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

• 데이터 무결성 보장
• 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능
• 논리적으로 연관된 작업을 그룹핑하여 처리 가능

SAVEPOINT

• 저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.
• 따라서 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다.
• 복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.
• 저장점 지정 없이 “ROLLBACK”을 실행했을 경우 반영안된 모든 변경 사항을 취소하고 트랜잭션 시작 위치로 되돌아간다.
• 다음의 SQL문은 SVPT1이라는 저장점을 정의하고 있다.

SAVEPOINT SVPT1;

예제

• 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);

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);

ROLLBACK TRAN SVTR1;

트랜잭션 정리!

• 커밋(COMMIT)과 롤백 (ROLLBACK)의 목적
  • 해당 테이블에 데이터의 변경을 발생시키는 입력(INSERT), 수정(UPDATE), 삭제 (DELETE) 수행시 그 변경되는 데이터의 무결성을 보장하는 것

Oracle의 트랜잭션

• 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, COMMIT 또는 ROLLBACK을 실행한 시점에서 종료된다.
• 단, 다음의 경우에는 COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료된다.
  • CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다.
  • 부연하면, DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
  • 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
  • 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.

SQL Server의 트랜잭션

• DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식
• 다음의 경우는 Oracle과 같이 자동으로 트랜잭션이 종료된다.
  • 애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.