2과목 · SQL 기본과 활용 › 2-3. 관리 구문

TCL

TCL(Transaction Control Language)은 DML로 변경한 내용을 확정하거나 취소하는 명령어다. 논리적 작업 단위인 트랜잭션을 제어한다.

트랜잭션의 개념

  • 트랜잭션은 하나의 논리적 작업 단위로 처리되어야 하는 연산의 묶음이다.
  • 트랜잭션 제어 대상은 주로 DML(INSERT, UPDATE, DELETE, MERGE)이다.
  • Oracle에서는 첫 DML 실행 시 트랜잭션이 시작되고, COMMIT 또는 ROLLBACK으로 끝난다.

계좌이체

"A 계좌 출금"과 "B 계좌 입금" 두 UPDATE는 하나의 트랜잭션이다. 둘 다 성공하면 COMMIT, 하나라도 실패하면 ROLLBACK해서 모두 성공하거나 모두 취소되어야 한다.

UPDATE account SET balance = balance - 10000 WHERE acc_no = 'A';
UPDATE account SET balance = balance + 10000 WHERE acc_no = 'B';
COMMIT;

트랜잭션의 특성 ACID

  • 원자성 (Atomicity) 모두 실행되거나 전혀 실행되지 않아야 한다(All or Nothing). 예: 출금만 되고 입금이 안 되는 상황 방지.
  • 일관성 (Consistency) 실행 전후 데이터베이스가 무결성 규칙을 만족하는 일관된 상태여야 한다. 예: 이체 전후 총 잔액 동일.
  • 고립성 (Isolation) 실행 중인 트랜잭션의 중간 결과를 다른 트랜잭션이 볼 수 없다. 예: 커밋 전 데이터는 타 세션에 미노출.
  • 지속성 (Durability) 커밋된 결과는 장애가 발생해도 영구 보존된다. 예: 커밋 후 서버 다운에도 데이터 유지.

시험 포인트: ACID 각 용어와 설명을 짝짓는 문제가 자주 나온다. "중간 결과를 다른 트랜잭션이 볼 수 없다"는 고립성, "커밋 결과가 영구히 보존된다"는 지속성이다.

동시성 문제와 격리 수준

여러 트랜잭션이 동시에 같은 데이터를 다룰 때 고립성이 약하면 다음 문제가 생긴다.

  • Dirty Read 다른 트랜잭션이 커밋하지 않은 데이터를 읽는다. 그 트랜잭션이 롤백하면 존재한 적 없는 값을 읽은 셈이다.
  • Non-Repeatable Read 같은 행을 두 번 읽는 사이 다른 트랜잭션이 수정·삭제 후 커밋해서 값이 달라진다.
  • Phantom Read 같은 조건으로 두 번 조회하는 사이 다른 트랜잭션이 새 행을 삽입해서 없던 행이 나타난다.

격리 수준이 높을수록 허용되는 문제가 줄지만 동시성(처리량)은 떨어진다.

격리 수준 Dirty Read Non-Repeatable Read Phantom Read
READ UNCOMMITTED 발생 발생 발생
READ COMMITTED 방지 발생 발생
REPEATABLE READ 방지 방지 발생
SERIALIZABLE 방지 방지 방지
  • Oracle과 SQL Server의 기본 격리 수준은 모두 READ COMMITTED 이다.

시험 포인트: Non-Repeatable Read는 기존 행의 수정·삭제, Phantom Read는 새 행의 삽입으로 생긴다. 두 개를 바꿔서 묻는 문제가 나온다.

COMMIT, ROLLBACK, SAVEPOINT

명령어 동작
COMMIT 트랜잭션의 모든 변경을 DB에 영구 반영하고 트랜잭션 종료
ROLLBACK 마지막 COMMIT 이후의 모든 변경을 취소하고 트랜잭션 종료
SAVEPOINT 이름 트랜잭션 중간에 되돌아갈 지점을 저장
ROLLBACK TO 이름 해당 저장점 이후의 변경만 취소(트랜잭션은 계속 진행) 
UPDATE emp SET sal = sal + 100 WHERE empno = 7369;
SAVEPOINT sp1;
DELETE FROM emp WHERE empno = 7499;
ROLLBACK TO sp1;   -- DELETE만 취소, UPDATE는 유지
COMMIT;            -- UPDATE 확정

SAVEPOINT 동작 규칙

저장점 다섯 가지 규칙

  • 저장점은 여러 개 둘 수 있다.
  • 앞선 저장점으로 롤백하면 그 뒤에 만든 저장점들도 사라진다.
  • 같은 이름으로 저장점을 다시 만들면 나중 것이 앞의 것을 대체한다.
  • 저장점 없이 ROLLBACK만 쓰면 트랜잭션 전체가 취소된다.
  • COMMIT하면 모든 저장점이 사라진다.

SAVEPOINT 결과 행 계산 예제

빈 테이블 T(col)에서 다음을 순서대로 실행한다(Oracle).

INSERT INTO t VALUES ('A');
COMMIT;
INSERT INTO t VALUES ('B');
SAVEPOINT sp1;
INSERT INTO t VALUES ('C');
SAVEPOINT sp2;
DELETE FROM t WHERE col = 'A';
ROLLBACK TO sp2;
INSERT INTO t VALUES ('D');
ROLLBACK TO sp1;
INSERT INTO t VALUES ('E');
COMMIT;
SELECT COUNT(*) FROM t;

시간 순서대로 그려 보면, ROLLBACK TO는 저장점 위치로 "되감기"하는 화살표다.

C·D 취소, sp2 소멸 DELETE A 취소 명령 테이블 INSERTCOMMITINSERTSAVEPOINTINSERTSAVEPOINTDELETEROLLBACKINSERTROLLBACKINSERTCOMMIT ABsp1Csp2ATO sp2DTO sp1E AAA,BA,BA,B,CA,B,CB,CA,B,CA,B,C,DA,BA,B,EA,B,E COMMIT(확정) SAVEPOINT ROLLBACK TO(저장점으로 되감기) 결과 3건
ROLLBACK TO sp1은 sp1 이후의 C·D를 모두 취소하고, 그 사이에 만든 sp2까지 없앤다
단계 테이블 상태 설명
INSERT A, COMMIT A A 확정
INSERT B, SAVEPOINT sp1 A, B sp1 = {A, B}
INSERT C, SAVEPOINT sp2 A, B, C sp2 = {A, B, C}
DELETE A B, C
ROLLBACK TO sp2 A, B, C DELETE 취소
INSERT D A, B, C, D
ROLLBACK TO sp1 A, B C, D 취소, sp2도 소멸
INSERT E, COMMIT A, B, E 확정
  • 결과는 3건이다.
  • 만약 마지막 COMMIT 대신 ROLLBACK을 실행했다면 첫 COMMIT 시점으로 돌아가 A 1건만 남는다.

시험 포인트: ROLLBACK TO는 해당 저장점 "이후"만 취소한다. 앞쪽 저장점으로 돌아간 뒤에는 뒤쪽 저장점으로 다시 롤백할 수 없다(이미 소멸).

커밋 이전과 이후의 데이터 상태

  • COMMIT/ROLLBACK 이전 아직 확정되지 않은 상태.
    • 변경 내용은 메모리 버퍼에만 반영되어 있고 이전 상태로 복구할 수 있다.
    • 변경한 사용자 본인은 SELECT로 변경 결과를 볼 수 있다.
    • 다른 사용자는 변경 결과를 볼 수 없고, 변경 전(마지막 커밋) 데이터를 본다.
    • 변경 중인 행에는 잠금이 걸려 다른 사용자가 해당 행을 변경할 수 없다.
  • COMMIT 이후 영구 확정된 상태.
    • 변경 내용이 DB에 영구 반영되어 이전 데이터는 사라진다.
    • 모든 사용자가 결과를 조회할 수 있다.
    • 관련 행의 잠금이 해제되어 다른 사용자가 다시 변경할 수 있다.
  • ROLLBACK 이후 없던 일이 된 상태.
    • 변경 이전 상태로 복구되고 잠금이 해제된다.

자동 커밋과 자동 롤백 상황(Oracle)

상황 결과
DDL(CREATE, ALTER, DROP, TRUNCATE, RENAME 등) 실행 실행 전후로 암묵적 COMMIT
DCL(GRANT, REVOKE) 실행 암묵적 COMMIT
SQL*Plus 등 도구를 정상 종료(EXIT) COMMIT
비정상 종료, 시스템 장애, 세션 강제 종료 ROLLBACK 
-- Oracle
INSERT INTO t VALUES ('X');
CREATE TABLE t2 (c NUMBER);   -- 이 시점에 앞선 INSERT도 커밋됨
ROLLBACK;                     -- 'X'는 취소되지 않음

시험 포인트: Oracle에서 DML 후 DDL을 실행하면 DDL이 실패하더라도 앞선 DML은 커밋된 것으로 본다. "DDL 뒤의 ROLLBACK으로 DML이 취소된다"는 함정 보기다.

Oracle과 SQL Server의 트랜잭션 차이

  • Oracle 수동 커밋이 기본이다. 첫 DML을 실행하면 트랜잭션이 자동으로 시작되고, 명시적으로 COMMIT해야 확정된다. DDL은 암묵적 커밋이라 롤백할 수 없다.
  • SQL Server AUTO COMMIT이 기본이다. 문장마다 자동 커밋되므로, 여러 문장을 묶으려면 BEGIN TRAN으로 트랜잭션을 명시한다. 명시적 트랜잭션 안이면 DDL도 롤백할 수 있다.
구분 Oracle SQL Server
기본 커밋 방식 수동 커밋(명시적 COMMIT 필요) AUTO COMMIT(문장마다 자동 커밋)
트랜잭션 시작 첫 DML 실행 시 자동 시작 BEGIN TRAN[SACTION]으로 명시
커밋 COMMIT COMMIT [TRAN]
저장점 생성 SAVEPOINT sp1 SAVE TRAN[SACTION] sp1
저장점 롤백 ROLLBACK TO sp1 ROLLBACK TRAN sp1
DDL의 롤백 불가(암묵적 커밋) 명시적 트랜잭션 안이면 가능 
-- SQL Server
BEGIN TRAN;
  INSERT INTO t VALUES ('A');
  SAVE TRAN sp1;
  INSERT INTO t VALUES ('B');
  ROLLBACK TRAN sp1;   -- B만 취소
COMMIT TRAN;           -- A 확정

저장점 문법을 섞지 말 것

Oracle은 SAVEPOINT / ROLLBACK TO, SQL Server는 SAVE TRAN / ROLLBACK TRAN이다. 보기에서 두 DBMS의 키워드를 바꿔 끼운 문장은 오답이다.

SQL Server의 세 가지 트랜잭션 방식

  • AUTO COMMIT: 기본값. 각 SQL 문이 성공하면 자동 커밋, 오류면 자동 롤백한다.
  • 암시적 트랜잭션: SET IMPLICIT_TRANSACTIONS ON 시 Oracle처럼 동작하며 명시적 COMMIT/ROLLBACK이 필요하다.
  • 명시적 트랜잭션: BEGIN TRAN으로 시작해 COMMIT 또는 ROLLBACK으로 끝낸다.

잠금(LOCK)

트랜잭션이 데이터를 변경하는 동안 다른 트랜잭션의 동시 변경을 막아 일관성을 보장하는 장치다.

  • 공유 잠금 (Shared Lock) 읽기용. 여러 트랜잭션이 동시에 걸 수 있다.
  • 배타 잠금 (Exclusive Lock) 변경용. 한 트랜잭션만 걸 수 있으며 다른 잠금과 공존할 수 없다.
  • DML이 건 잠금은 COMMIT 또는 ROLLBACK 시 해제된다.
  • 서로가 가진 잠금을 기다리며 무한 대기하는 상태를 교착 상태(Deadlock)라 하며, DBMS가 이를 감지해 한쪽에 오류를 발생시켜 해소한다. Oracle은 교착 상태를 일으킨 문장만 취소하고(ORA-00060) 트랜잭션은 유지하며, SQL Server는 희생자로 고른 트랜잭션 전체를 롤백한다.
  • Oracle은 읽기 일관성(언두 데이터)으로 읽기가 쓰기를 막지 않는다.

핵심 요약

  • 트랜잭션은 논리적 작업 단위이며 ACID(원자성·일관성·고립성·지속성)를 만족해야 한다.
  • COMMIT은 영구 반영, ROLLBACK은 마지막 커밋 이후 전체 취소, ROLLBACK TO는 저장점 이후만 취소한다.
  • 앞쪽 저장점으로 롤백하면 뒤쪽 저장점은 소멸하고, COMMIT 시 모든 저장점이 사라진다.
  • 커밋 전 변경은 본인만 볼 수 있고 해당 행은 잠겨 있다.
  • Oracle은 DDL·DCL 실행 시 암묵적 커밋, 비정상 종료 시 롤백된다.
  • SQL Server는 AUTO COMMIT이 기본이며 BEGIN TRAN, SAVE TRAN, ROLLBACK TRAN을 사용한다.
  • 잠금은 COMMIT/ROLLBACK 시 해제되고, 상호 대기는 교착 상태를 유발한다.

이 절 음성 강의 듣기