제목없음

과정 목표

본 과정에서는 트랜잭션의 개념에 대해서 상세히 알아보고, 트랜잭션을 구성하는 여러 가지 요소들에 대해서 배웁니다. 또한 COMMIT, SAVEPOINT 그리고 ROLLBACK 문장으로 트랜잭션을 제어하는 방법을 배웁 니다.

트랜잭션의 시작과 종료 시기는?

트랜잭션은 실행 가능한 SQL 문장이 제일 처음 실행될 때 시작하고, 다음의 경우에 종료합니다.

COMMIT이나 ROLLBACK 문장이 완료 시
CREATE 같은 DDL 문장이 완료 시
DCL 문장이 완료 시
사용자가 SQL*Plus을 종료 시
머신 실패나 시스템 충돌 시

하나의 트랜잭션이 끝난 후에 다음의 실행 가능한 SQL 문장은 자동적으로 다음 트랜잭션을 시작할 것입니다.

DDL 문장이나 DCL 문장은 자동적으로 커밋되므로 트랜잭션을 암시적으로 종료합니다.

명시적 트랜잭션 제어 문장

COMMIT, SAVEPOINT 그리고 ROLLBACK 문장을 사용하여 트랜잭션의 논리를 제어할 수 있습니다.

문장

설명

COMMIT

모든 미결정 데이터를 영구적으로 변경함으로서 현재 트랜잭션을 종료합니다.

SAVEPOINT name

현재 트랜잭션 내에 savepoint 를 표시합니다.

ROLLBACK [TO SAVEPOINT name]

ROLLBACK은 모든 미결정 데이터 변경을 버림으로써 현재의 트랜잭션을 종료합니다. ROLLBACK TO SAVEPOINT name 은 savepoint와 모든 연이은 변경을 버립니다.

주: SAVEPOINT는 ANSI 표준 SQL이 아닙니다.

암시적 트랜잭션 처리

상태

상황

자동 커밋

DDL 문장이나 DCL 문장은 문제가 되지 않습니다.
명시적인 COMMIT 이나 ROLLBACK 없이 SQL*Plus를 정상적으로 종료

자동 롤백

SQL*Plus 의 비정상적인 종료나 ,또는 시스템 오류

주: SQL*Plus AUTOCOMMIT 명령은 ON이나 OFF로 토글될 수 있습니다. ON이면 각각의 개별적인 DML 문장이 실행되자마자 커밋됩니다.그러므로 변경을 롤백할 수 없습니다. OFF로 설정하면 COMMIT은 명시적으로 실행될 수 있습니다. 또한 COMMIT은 DDL 문장이 완료되거나 SQL*Plus를 종료할 때 완료됩니다

시스템 실패

트랜잭션이 시스템 실패에 의해 중단될 때 모든 트랜잭션은 자동적으로 롤백됩니다. 이것은 데이터에 대해서 원하지 않는 변경을 막아 주고 테이블을 마지막으로 커밋된 시점으로 상태를 되돌립니다. SQL은 테이블의 무결성을 보호합니다.

변경을 커밋

트랜잭션 동안에 이루어지는 모든 데이터 변경은 트랜잭션이 커밋되기 전까지는 임시적 입이다.

COMMIT이나 ROLLBACK 이전의 데이터 상태

데이터 조작 작업은 주로 데이터베이스 버퍼에 영향을 미치므로 데이터의 이전 상태는 복구 가능합니다.
현재 사용자는 테이블을 질의하여 데이터 조작 작업의 결과를 검사할 수 있습니다.
다른 사용자는 현재 사용자에 의해 만들어진 데이터 조작 작업의 결과를 볼 수 없습니다.
변경된 행은 잠금상태입니다. 다른 사용자들은 변경된 행들 내의 데이터를 변경 할 수 없습니다.

변경 커밋(계속)

COMMIT 문장을 사용하여 모든 미결정 변경을 영구적인 변경으로 만듭니다.
다음이 COMMIT입니다.

변경 데이터가 데이터베이스에 영구히 반영됩니다.
데이터의 이전 상태가 완전히 상실됩니다.
모든 사용자가 트랜잭션의 결과를 볼 수 있습니다.
변경된 행들이 잠금해제됩니다. 행들은 이제 새로운 변경을 수행하려는 다른 사용자들에 의해 이용 가능합니다.
모든 savepoint가 제거됩니다.

변경 커밋(계속)

위의 예는 EMP 테이블을 갱신하고 종업원 7782(Clark)에 대한 부서 번호를 10으로 설정합니다. 그런 다음에 COMMIT 문장을 수행하여 영구적으로 변경을 합니다.

예
최소한 한 명의 종업원이 있는 새로운 ADVERTISING 부서를 생성합니다. 데이터 변경을 영구적으로 만듭니다.

SQL> INSERT INTO department(deptno, dname, loc)
  2 VALUES      (50, 'ADVERTISING', 'MIAMI');
1 row created.

SQL> UPDATE employee
  2 SET    deptno = 50
  3 WHERE empno = 7876;
1 row updated.

SQL> COMMIT;
Commit complete.

변경 롤백
ROLLBACK 문장을 사용하여 모든 미결정 변경을 버립니다.
다음은 ROLLBACK입니다.

데이터 변경은 취소됩니다.
데이터의 이전 상태로 복구됩니다.
변경된 행의 잠금이 해제됩니다.

예
TEST 테이블의 레코드를 제거하려고 하면 뜻하지 않게 테이블을 지워 버릴 수 있습니다. 실수를 정정한 다음에 올바른 문장을 다시 실행하여 데이터를 영구적으로 변경합니다.

SQL> DELETE FROM test;
25,000 rows deleted.
SQL> ROLLBACK;
Rollback complete.
SQL> DELETE FROM test
  2 WHERE        id = 100;
1 row deleted.
SQL> SELECT   *
  2 FROM     test
  3 WHERE    id = 100;
No rows selected.
SQL> COMMIT;
Commit complete.

Savepoint로 변경을 롤백

SAVEPOINT 문장을 사용하여 현재 트랜잭션 내에 표시자(marker)를 생성할 수 있습니다.그러므로 트랜잭션은 보다 작은 영역으로 나눌 수 있습니다. 그런 다음에 ROLLBACK TO SAVEPOINT 문장을 사용하여 해당 표시자까지로 미결정의 변경들을 버립니다.

이전의 savepoint와 똑같은 이름으로 두번째 savepoint를 생성한다면 이전의 savepoint는 삭제 됩니다.

문장 레벨 롤백

문장 실행 에러가 발견되면 트랜잭션의 일부는 암시적 롤백으로 버려질 수 있습니다. 단일 DML 문장이 트랜잭션 실행 동안에 실패한다면 그 문장에 의한 변경은 문장 레벨 롤백에 의해 취소되지만, 트랜잭션에 있는 이전의 DML 문장에 의해 만들어진 변경은 버려지지 않을 것입니다. 그 문장들은 사용자에 의해서 명시적으로 커밋이나 롤백될 수 있습니다.

오라클은 모든 데이터 정의어(DDL) 문장에서 암시적 COMMIT을 수행합니다. 그래서, 비록 DDL 문장이 성공적으로 실행되지 않았더라도 서버가 커밋을 실행했기 때문에 이전의 문장으 로 롤백할 수 없습니다.

COMMIT이나 ROLLBACK 문장을 실행하여 트랜잭션을 명시적으로 종료합니다.

읽기 일관성

데이터베이스 사용자가 데이터베이스를 엑세스하는 두가지 유형이 있습니다.
읽기 연산자 (SELECT 문장)
쓰기 연산자(INSERT, UPDATE, DELETE 문장)

읽기 일관성이 필요한데 이것은 다음을 발생시킵니다.

데이터베이스 reader와 writer는 일관되게 데이터를 보게됩니다.
Reader는 변경 중인 데이터는 보지를 않습니다.
Writer는 데이터베이스에 대해서 일관되게 수행된 변경을 보증합니다.
하나의 writer에 의해 만들어진 변경은 다른 writer가 만드는 변경과 분열이나 충돌하지 않습니다.

읽기 일관성의 목적은 각각의 사용자가 DML 작업을 시작하기 전에, 마지막 커밋 시에 저장된 데이터를 알 수 있도록 보증하는 것입니다.

잠금(Locking)이란?

Lock은 사용자 객체(테이블이나 행)나 사용자에게 보이지 않는 시스템 객체(공유 데이터 구조 및 데이터 사전 행) 등의 똑같은 자원을 액세스하는 트랜잭션들 사이의 유해한 상호작용 을 막아 줍니다.

오라클 데이터 잠금(Locking) 방법

오라클 데이터베이스에서 Locking은 완전히 자동적이며 사용자 액션을 요구하지 않습니다. 암시적 locking은 SELECT를 제외한 모든 SQL에 대해서 발생합니다. 오라클 디폴트 locking 기법은 자동적으로 제약사항의 가장 낮은 레벨을 사용하여, 가장 높은 단계의 동시성을 제공하며 또한 데이터 무결성을 최대한 제공합니다. 오라클은 또한 사용자가 데이터를 수동 으로 잠그는(lock) 것을 허용합니다.

잠금(Locking) 모드

오라클은 다중사용자 데이터베이스에서 두 가지의 locking 모드를 사용합니다.

Lock 모드

설명

exclusive

자원이 공유되는 것을 막아줍니다.
자원을 배타적으로 lock하는 첫번째 트랜잭션은 배타적 잠금이 해제되기 전까지는 자원을 변경할 수 있는 유일한 트랜잭션입니다.

share lock

자원이 공유되도록 허용합니다.
데이터를 읽는 다중 사용자는 데이터를 공유하고, writer(배타적 잠금이 필요한)에 의해 동시에 액세스 되는 것을 막기 위해서 공유 잠금을 유지합니다. 똑같은 자원상에서 여러 개의 트랜잭션은 공유 잠금을 요구할 수 있습니다.

Implicit commit이 일어나는 경우는?(두 가지)
A. SQL*Plus가 비정상적으로 종료되었을 경우.
B. ALTER TABLE문을 실행했을 경우.
C. GRANT문을 실행했을 경우.
D. COMMIT문을 실행했을 경우.
답

X 정답:BC

TRANSACTION 종료를 발생시키는 명령문을 모두 고르시오.
A. ROLLBACK
B. UPDATE
C. DELETE
D. ALTER
답

X 정답:AD

implicit commit이 발생하는 경우가 아닌 것은?
A. insert into dept values(50,'SALES','LA');
B. create table new_emp as select * from emp;
C. rename dept to department;
D. grant select on emp to scott;
답

X 정답:A

implicit commit이 발생하는 경우들은?
A. select * from emp
B. insert into emp values (값의 나열...);
C. create table x as select * from emp;
D. SQL*PLUS의 종료시(exit)
E. rename x to test_tbl;
F. trucate table test_tbl;
답

X 정답:CDEF

다음 보기의 SQL 명령문을 순서대로 실행했을 때 database에 영구적으로 반영되는 문장은?
1. INSERT INTO emp (empno, ename, deptno) VALUES (999, 'Smith', 10);
2. SAVEPOINT a;
3. DELETE emp WHERE empno = 202;
4. SAVEPOINT b;
5. UPDATE emp SET ename = 'Clark';
6. ROLLBACK TO SAVEPOINT a;
7. INSERT INTO emp (empno, ename, deptno) VALUES (300, 'Thomas', 30);
8. SAVEPOINT c;
9. DELETE emp WHERE deptno = 20;
10. COMMIT; A. 1, 3, 5, 7, 9번
B. 1, 3, 5, 7번
C. 3, 5, 7, 9번
D. 1, 7, 9번
답

X 정답:D

문장	설명
COMMIT	모든 미결정 데이터를 영구적으로 변경함으로서 현재 트랜잭션을 종료합니다.
SAVEPOINT name	현재 트랜잭션 내에 savepoint 를 표시합니다.
ROLLBACK [TO SAVEPOINT name]	ROLLBACK은 모든 미결정 데이터 변경을 버림으로써 현재의 트랜잭션을 종료합니다. ROLLBACK TO SAVEPOINT name 은 savepoint와 모든 연이은 변경을 버립니다.

상태	상황
자동 커밋	DDL 문장이나 DCL 문장은 문제가 되지 않습니다. 명시적인 COMMIT 이나 ROLLBACK 없이 SQL*Plus를 정상적으로 종료
자동 롤백	SQL*Plus 의 비정상적인 종료나 ,또는 시스템 오류

Lock 모드	설명
exclusive	자원이 공유되는 것을 막아줍니다. 자원을 배타적으로 lock하는 첫번째 트랜잭션은 배타적 잠금이 해제되기 전까지는 자원을 변경할 수 있는 유일한 트랜잭션입니다.
share lock	자원이 공유되도록 허용합니다. 데이터를 읽는 다중 사용자는 데이터를 공유하고, writer(배타적 잠금이 필요한)에 의해 동시에 액세스 되는 것을 막기 위해서 공유 잠금을 유지합니다. 똑같은 자원상에서 여러 개의 트랜잭션은 공유 잠금을 요구할 수 있습니다.