TCL

TCL(Transaction Control Language, 트랜잭션 제어어)은 DML로 바꾼 내용을 확정하거나(COMMIT) 되돌립니다(ROLLBACK). SAVEPOINT로 중간 지점을 정해 두면 그 지점까지만 되돌릴 수도 있습니다.

트랜잭션(transaction)은 모두 성공하거나 모두 취소되어야 하는 작업 묶음입니다. 무엇을 한 묶음으로 넣을지는 모델이 표현하는 트랜잭션에서 봤고, 이 레슨은 그 묶음을 끝내는 문장을 봅니다.

UPDATE EMPSET SAL = 500WHERE EMPNO = 1003;CREATE TABLE MEMO (  TXT VARCHAR2(20));

SELECT EMPNO,  ENAME,  SALFROM EMP;

1트랜잭션과 ACID

트랜잭션이 지킬 네 성질은 머리글자를 따서 ACID라 부릅니다.

2COMMIT·ROLLBACK·SAVEPOINT

COMMIT 전의 변경은 내 세션에만 보이고, 다른 사용자는 바뀌기 전 값을 보며 그 행을 고칠 수 없습니다(잠금). SAVEPOINT(저장점)는 트랜잭션 안의 한 지점에 이름을 붙이고, 같은 이름으로 다시 만들면 앞의 것은 지워집니다.

ROLLBACK TO는 그 저장점과 앞의 저장점을 남기고, 뒤에 만든 저장점만 지웁니다. 트랜잭션이 끝나면 저장점도 모두 사라져서, COMMIT 뒤의 ROLLBACK TO SP1은 ORA-01086 오류입니다.

3DDL 자동 커밋과 SQL Server

Oracle은 DDL(CREATE·ALTER·DROP·TRUNCATE·RENAME 등)을 실행하기 전과 후에 자동으로 COMMIT합니다. 그래서 하던 DML까지 확정되고, 문법이 맞는 DDL이면 실행에 실패해도 앞의 COMMIT은 일어납니다. 도구를 정상 종료하면 대부분 COMMIT, 비정상 종료하면 ROLLBACK됩니다.

SQL Server에서 BEGIN TRANSACTION으로 연 것을 명시적 트랜잭션이라 합니다. SET IMPLICIT_TRANSACTIONS ON⁠으로 켜는 암시적 트랜잭션은 문장이 트랜잭션을 저절로 열고, Oracle처럼 COMMIT·ROLLBACK으로 끝냅니다.

예제

예제는 저마다 처음 데이터에서 시작하고, 블록 하나가 한 세션입니다. 20번 부서의 월급을 올렸다가 ROLLBACK하면 처음 값으로 돌아갑니다.

UPDATE EMP
SET SAL = SAL + 100
WHERE DEPTNO = 20;
ROLLBACK;
SELECT EMPNO,
  ENAME,
  SAL
FROM EMP
WHERE DEPTNO = 20
ORDER BY EMPNO;

그림의 저장점 장면입니다. ROLLBACK TO SP1은 ROLLBACK TO SAVEPOINT SP1⁠으로 써도 같습니다.

UPDATE EMP
SET SAL = 400
WHERE EMPNO = 1002;
SAVEPOINT SP1;
DELETE FROM EMP
WHERE EMPNO = 1005;
ROLLBACK TO SP1;
COMMIT;
SELECT EMPNO,
  ENAME,
  SAL
FROM EMP
WHERE EMPNO IN
    (1002, 1005)
ORDER BY EMPNO;

그림의 DDL 장면입니다. 자동 COMMIT 뒤라 ROLLBACK을 해도 500이 남습니다.

UPDATE EMP
SET SAL = 500
WHERE EMPNO = 1003;
CREATE TABLE MEMO (
  TXT VARCHAR2(20)
);
ROLLBACK;
SELECT EMPNO,
  ENAME,
  SAL
FROM EMP
WHERE EMPNO = 1003;

저장점 장면을 SQL Server 문법으로 쓴 것입니다.

BEGIN TRANSACTION;
UPDATE EMP
SET SAL = 400
WHERE EMPNO = 1002;
SAVE TRANSACTION SP1;
DELETE FROM EMP
WHERE EMPNO = 1005;
ROLLBACK TRANSACTION SP1;
COMMIT TRANSACTION;

시험 포인트

• COMMIT하면 다른 사용자도 바뀐 값을 보고 잠금이 풀리며, ROLLBACK으로 되돌릴 수 없습니다.
• ROLLBACK TO 저장점은 그 뒤만 되돌리고 트랜잭션을 이어 갑니다. ROLLBACK은 전체를 되돌리고 끝냅니다.
• 같은 이름의 SAVEPOINT는 나중 것만 남고, 끝난 트랜잭션의 저장점으로는 돌아갈 수 없습니다(ORA-01086).
• Oracle은 DDL 앞뒤에 자동으로 COMMIT해서, 문법만 맞으면 실패한 DDL이라도 앞의 DML이 확정됩니다.
• SQL Server의 기본은 자동 커밋이고, 저장점은 SAVE TRANSACTION입니다.
• 자료에 따라 고립성을 독립성·격리성, 지속성을 영속성이라고도 부릅니다. 어느 이름으로 나와도 같은 것입니다.

확인 문제

1. 다음 문장을 차례로 실행한 뒤 김민준(1001)의 SAL은 얼마일까요?

UPDATE EMP
SET SAL = 600
WHERE EMPNO = 1001;
SAVEPOINT A;
UPDATE EMP
SET SAL = 700
WHERE EMPNO = 1001;
SAVEPOINT B;
UPDATE EMP
SET SAL = 800
WHERE EMPNO = 1001;
ROLLBACK TO A;
COMMIT;
SELECT EMPNO,
  SAL
FROM EMP
WHERE EMPNO = 1001;

2. 저장점 이름을 SP 하나로 두 번 썼습니다. 김민준의 SAL은 얼마일까요?

UPDATE EMP
SET SAL = 600
WHERE EMPNO = 1001;
SAVEPOINT SP;
UPDATE EMP
SET SAL = 700
WHERE EMPNO = 1001;
SAVEPOINT SP;
UPDATE EMP
SET SAL = 800
WHERE EMPNO = 1001;
ROLLBACK TO SP;
COMMIT;
SELECT EMPNO,
  SAL
FROM EMP
WHERE EMPNO = 1001;

3. 옳지 않은 설명을 고르세요.

• ① COMMIT 전의 변경이 남에게 안 보이는 것은 고립성 때문입니다.
• ② ROLLBACK TO SAVEPOINT⁠는 트랜잭션을 끝냅니다.
• ③ Oracle에서 UPDATE 뒤에 CREATE TABLE을 하면 UPDATE도 확정됩니다.
• ④ SQL Server는 기본 설정에서 문장마다 자동으로 커밋합니다.