아래 그림을 보세요.
사용자가 INSERT 문을 사용해 데이터를 삽입하고, UPDATE 문으로 데이터를 갱신하고, DELETE 문으로 데이터를 삭제하였다고 합시다. 만약 이 모든 과정이 오류없이 수행되었다면 지금까지 실행한 모든 작업을 '실제로' 수행하라는 명령을 내릴 수 있는데 이 때의 명령이 바로 'COMMIT' 명령입니다.
만약 INSERT 작업을 한 다음 'SAVEPOINT A'라는 명령을 실행하였다면 나중에 'ROLLBACK A'라는 명령을 통해 INSERT 작업을 한 그 위치로 되돌아 올 수 있는 것이죠. 그 전에 'COMMIT' 명령을 실행하지 않았다면 말입니다.
이와 같이 COMMIT 명령으로 하나의 작업이 성공적으로 끝났을 때 우리는 트랜잭션이 성공적으로 수행되었다고 말합니다.
트랜잭션 제어를 위한 명령어(Transaction Coltrol Language)에는 다음과 같은 것들이 있습니다.
COMMIT
SAVEPOINT
ROLLBACK
하나 하나 살펴볼까요?
COMMIT은 저장되지 않은 모든 데이터를 데이터베이스에 저장하고 현재의 트랜잭션을 종료하라는 명령입니다.
SAVEPOINT [이름]는 현재까지의 트랜잭션을 특정 이름으로 지정하라는 명령입니다.
ROLLBACK [TO SAVEPOINT 이름]저장되지 않은 모든 데이터 변경 사항을 취소하고 현재의 트랜잭션을 끝내라는 명령입니다. 만약 이전에 SAVEPOINT로 지정한 이름이 있으면 그 위치까지 되돌아 갑니다.
이와 같이 COMMIT 또는 ROLLBACK 명령으로 직접 트랜잭션을 완성하거나 취소할 수도 있지만,
다음과 같은 경우에는 자동으로 트랜잭션이 종료됩니다.
DDL(CREATE, ALTER, DROP) 명령어를 실행할 때
DCL(GRANT, REVOKE) 명령어를 실행할 때
Deadlock같은 특정 Error를 만날 때
SQL*Plus를 종료할 때
위와 같은 경우에는 COMMIT나 ROLLBACK 문이 없어도 트랜잭션이 종료됩니다.
그럼, 정리하는 의미로 다음과 같이 작업을 할 경우 어떠한 결과가 나타날지 한번 생각해 보세요.
SQL*Plus시작
SELECT * FROM EMP;
UPDATE EMP SET JOB_CODE = 'AA' WHERE EMP_NUM = '10001';
DELETE FROM EMP;
ROLLBACK TO update_point;
DELETE FROM EMP WHERE JOB_CODE = 'SS';
COMMIT;
MSSQL
1. SQL 문장 앞에 SET IMPLICIT_TRANSACTIONS ON 을 설정하면 됩니다
ON 은 암시적 트랜젝션 모드로 설정되고 OFF 는 자동 commit 모드로 설정됩니다.
SET IMPLICIT_TRANSACTIONS ON
2. 문장앞에 BEGIN TRAN 문을 설정합니다.
= > sql 문장 실행후 결과에 따라 commit tran / rollback tran 을 실행합니다.
------------------------------------------------------------------------------------
본 내용을 2004년 7월 9일 수업 내용을 토대로 개인적으로 정리한 것입니다. 작성자 : 홍혁기(litwave)
------------------------------------------------------------------------------------
http://cafe.naver.com/litave/14
<<== 목차 ==>>
1. TCL
2. Transaction
1) Commit
2) Rollback
3) AutoCommit
4) Save Point
5) Rollback to
6) Rollback의 내부적 수행방법
9) 읽기 일관성
10) DML Lock
11) DaedLock
-------------------------------------------------------------------------------------
1. TCL(Transaction Control Language)
하나의 Transaction은 사용자가 Oracle에 로그인한 시점부터 로그아웃 할때 까지이다.(특별한 제어가 없는경우)
2. 트랜젝션 이란 : 연관된 DML 등의 묶음(작업단위)이다.
A 은행 B은행
┌────────┐ ┌────────┐
│ │--계좌이체--> │ │
│ │ │ │
│ │ │ │
│ │ │ │
└────────┘ └────────┘
A은행에서 B은행으로 계좌이체를 한다고 가정해 보자.
B은행에 정전등의 시스템적인 문제가 발생하여, A은행에서는 돈을 빼는 작업이 정상적으로 수행되었으나 B은행에서는 돈을 넣는 작업을 수행하지 못할 수도 있다.
이것의 문제를 해결하기 위해서는 A은행에서 B은행으로 돈을 이체하는 작업 전체가 하나의 묶음으로 묶여 전체가 수행되던지 전체가 수행되지 말아야 한다.
1) Commit
- 수행이 정상적으로 되었다고 여기까지 실행하라고 명령하는 것
2) Rollback
- 수행이 정상적으로 이루어지지 않아 기존 상태로 되돌리는 것.
3) DML 사용중 DDL, DCL 명령이 들어오면 자동으로 Auto Commit이 수행됨.
4) Save Point
- Oracle에서 사용됨(Oracle 따라한 DB만 제공함)
- SQL 문이지만 ANSI 표준은 아님
- 책 갈피와 동일한 기능을 수행한다.
- Save Point 이름 의 형식으로 사용한다.
5) Rollback to SavePoint지점
- 이렇게 하면 해당 위치로 RollBack 할 수 있다.
- 뒤로는 이동해도 앞으로는 못간다.
6)Rollback의 내부적 수행
Emp Table에 Update를 한다고 해서 해당 부분이 바로 바뀌는 것은아니다.
또다른 저장영역에 원래 값을 복사하고, update명령을 수행한다.
이런 저장영역을 Rollback Segment(RollBack 시만 사용)라 한다.
롤백을 하면 Rollback Segment에 있는 값을 뒤에서부터 적용해 간다.
=> 그러므로 한번 Save Point로 롤백하면 그 이후에 저장되었던 값들은 모두 지워진다.
7) 하나의 Transaction은 Login -> TCL 까지 수행된다. TCL -> logout
8) Auto Commit Auto Rollback
발생상황 DDL, DCL 사용시 비정상 종료시(UPS가 비정상 종료를 막아준다.)
정상종료(exit)
- DB는 무결성(결점이 없어야 한다.)이 중요하다.
9) 읽기 일관성
- 모든 작업의 단위를 시간으로 기록한다.
- 모든 문장은 내가 Commit 했던 상태만을 보게 된다.
- 두개의 창을 나누어서 수행해봄
- A 창에서 수행
SQL> Select *
2 from emp
3 where empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 87/04/19 3000
20
SQL> update emp
2 set Sal=100
3 where empno = 7788;
1 행이 갱신되었습니다.
- B 창에서 수행
SQL> select *
2 from emp
3 where empno = 7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 87/04/19 3000 널이네
20
- A창에서 수행
SQL> Select *
2 from emp
3 where empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 87/04/19 100
20
- A와 B는 다른 화면을 보게됨
- A가 Sal 정보를 수정중에 B가 다시 수정을 하려하면 락이 걸려서 A가 Commit 할때까지 기다리게 된다.
- B창에서 수행
SQL> update emp Set Sal=200
2 where empno = 7788;
- A창에서 Rollback 수행
SQL> update emp Set Sal=200
2 where empno = 7788;
- B창은 이때서야 수행하게 됨(Lock 당시에 입력한 것들 모두 입력됨)
SQL> update emp Set Sal=200
2 where empno = 7788;
1 행이 갱신되었습니다.
10) DML Lock : 두 Application 간에 상호 데이타를 파괴하는 것을 막아주는 것
- DML 하기전에 Lock을 거는 것
- 무한히 대기하게 됨
- 자동으로 Lock을 걸고 해제해줌(최저레벨로..) : 같은 Row 만 모두 Lock이 걸림
11) Dead Lock : Lock중 특수한 Lock(무한 대기에 빠지는 경우)
- A, B 두창 모두 Rollback 해준다.
- A창에서 Scott의 금액을 Update 해줌
SQL> update emp
2 Set sal=100
3 where ename='SCOTT';
1 행이 갱신되었습니다.
- B창에서 SMITH의 값을 Update 해줌
SQL> update emp
2 Set sal = 200
3 Where ename='SMITH';
1 행이 갱신되었습니다.
- A창에서 SMITH의 값을 Update 해줌
- lock이 걸려서 대기상태에 빠짐
SQL> update emp
2 Set Sal = 300
3 where ename = 'SMITH';
- B창에서 SCOTT의 값을 Update 해줌
- DaedLock 에 걸림
SQL> update emp
2 Set Sal=400
3 where ename = 'SCOTT';
- A창에서 에러메세지 출력 후 자동으로 바로 이전 상태로 돌려 놓음
update emp
*
1행에 오류:
ORA-00060: 자원 대기중 교착상태가 검출되었습니다
12) 명령문 Level Rollback : A가 제일 마지막에 수행한 하나의 명령만 Rollback 해줌
- DML을 수행하면 암시적으로 Savepoint를 지정하고 있다가 바로 이전 명령으로 돌려준다.
'DB > Oracle' 카테고리의 다른 글
| 오라클, 범용 엔지니어드시스템 '스팍 슈퍼클러스터 T4-4' 출시 (0) | 2011.11.17 |
|---|---|
| 오라클 엔지니어드 시스템 (0) | 2011.11.17 |
| ORACLE Data Guard Architecture (0) | 2011.11.02 |
| SQL 쿼리문 2 (0) | 2011.08.16 |
| SQL 쿼리문 1 (0) | 2011.08.16 |
