오라클 트랜잭션(Oracle Transaction), SAVEPOINT, COMMIT, ROLLBACK
하나 이상의 SQL문을 포함하는 논리적인 작업의 단위 입니다. 또한 트랜잭션(Transaction)은 논리적인 하나의 작업단위의 SQL문을 모두 DB에 영구히 저장 되도록 Commit 하거나 모두 취소하기 위해 Rollback 합니다.
모든 오라클 트랜잭션은 ACID라고 하는 트랜잭션의 기본속성을 따르는데 ACID는 아래 4가지 속성에 대한 약어 입니다.
원자성(Atomicity)
트랜잭션의 모든 SQL 수행 작업은 모두다 수행되거나 수행되지 않습니다. 일부 실행은 없습니다.만약 어떤 트랜잭션이 100 개 행 업데이트를 한다고 했을 때 10개 실행 후 오류가 발생 했다면 이 트랜잭션은 모두 취소 되지 10개만 실행 되고 나머지가 취소 되지는 않습니다.
일관성(Consystency)
트랜잭션이 성공적으로 수행 되었을 때 전체 DB는 일관된 상태를 유지해야 한다는 것입니다. 일관된 상태란 도메인의 유효 범위, 무결성 제약 조건등이 위배되지 않은 정장적인 상태를 유지해야 한다는 것 입니다. 만약 성별 컬럼에는 “남”, “여” 두 값만 들어와야 하는데 트랜잭션 수행 후 다른 값이 들어와 있다는 것은 잘못된 경우 이겠죠. 이처럼 트랙잭션 수행 후 모든 상태가 그 전과 일관되어야 한다는 것 입니다.
격리성(Isolation)
트랜잭션 내의 SQL문 실행 효과는 트랜잭션이 커밋 될 때까지 다른 트랜잭션에 표시되지 않습니다. 예를들면 EMP 테이블에 “홍길동” 이라는 사원을 입력 후 Commit을 하지 않았으면 다른 사용자가 DB에 로그인하여 EMP 테이블을 SELECT 했을 때 나타나지 않아야 한다는 것 입니다.
지속성(Durability)
성공적으로 Commit 된 트랜잭션은 영구히 DB에 반영되어야 함을 이야기 합니다. 혹시 정전이 되어 데이터베이스 서버가 꺼졌다가 다시 실행되더라도 그 트랜잭션 결과는 반영되어 있어야 합니다. 즉 트랜잭션이 완료된 후 데이터베이스는 복구 메커니즘을 통해 트랜잭션의 변경 사항이 손실되지 않도록 해야 합니다.
다음은 트랜잭션을 제어하기 위한 명령어(Transaction Control Language)들 입니다.
COMMIT : 현재 트랜잭션의 저장되지 않은 결과를 영구히 DB에 반영하고 현재 트랜잭션을 종료 합니다.
SAVEPOINT : 현재까지 진행된 트랜잭션 지점을 특정 이름을 부여해서 저장 합니다. 트랜잭션 진행중 어떤 지점을 마킹해 둔다고 생각을 하면 됩니다.
ROLLBACK : 현재까지 진행된 트랜잭션의 변경사항을 모두 취소하고 트랜잭션을 종료 합니다.
트랜잭션 명령어 실습을 위해 EMP_BACK 이라는 테이블을 EMP 테이블에서 20번 부서 사원들의 이름을 기본으로 만듭니다. |
CREATE TABLE emp_back AS SELECT ename FROM emp WHERE deptno = 20 |
<실행결과>
Table EMP_BACK이(가) 생성되었습니다.
SELECT * FROM emp_back |
<실행결과>
ENAME | |
1 | JONES |
2 | SCOTT |
3 | ADAMS |
4 | FORD |
EMP_BACK 테이블에 ‘홍길동’을 입력하여 현재의 SQL Developer가 아닌 윈도우 명령프롬트프를 통해 SQL*Plus로 SCOTT 계정으로 접속을 하여 EMP_BACK 테이블을 SELECT 해보자. 아마도 트랜잭션을 COMMIT을 하지 않아 방금 입력한 ‘홍길동’은 보이지 않을 것이다. |
INSERT INTO emp_back (ename) VALUES ('홍길동'); |
<실행결과>
1 행 이(가) 삽입되었습니다.
윈도우10 명령프롬프트를 실행하고 sqlplus scott/tiger로 로그인을 하자. 이 세션은 위의 ‘홍길동’을 입력한 세션과는 다른 세션이므로 COMMIT 되지 않은 데이터는 볼 수 없다. |
‘홍길동’을 입력한 원래 세션에서 COMMIT을 하고 명령프롬프트 sqlplus 에서 다시 확인을 해보자. sqlplus에서 슬래시(‘/’)는 이전에 싱행한 SQL문장을 실행한다는 의미 입니다. |
COMMIT |
<실행결과>
커밋 완료.
‘홍길동’ 이라는 이름의 사원이 보입니다. 트랜잭션이 COMMIT 되었기 때문이죠.
‘JONES’ 사원을 삭제 합니다. |
DELETE FROM emp_back WHERE ename = 'JONES' |
<실행결과>
1 행 이(가) 삭제되었습니다.
현재 트랜잭션 실행 지점을 SAVPOINT 명령으로 A 라는 이름으로 저장 합니다. |
SAVEPOINT A; |
<실행결과>
Savepoint이(가) 생성되었습니다.
‘FORD’ 사원을 삭제 합니다. |
DELETE FROM emp_back WHERE ename = ‘FORD’ |
<실행결과>
1 행 이(가) 삭제되었습니다.
현재 트랜잭션 실행 지점을 SAVPOINT 명령으로 B 라는 이름으로 저장 합니다. |
SAVEPOINT B |
<실행결과>
Savepoint이(가) 생성되었습니다.
SELECT * FROM emp_back |
<실행결과>
ENAME | |
1 | SCOTT |
2 | ADAMS |
3 | 홍길동 |
현재는SAVEPOINT로 B라고 지점 이름을 부여했고, 이 시점에 COMMIT을 하면 EMP_BACK 테이블의 데이터는 위 결과처럼 3건이 될 것 입니다. 이번에는 SAVEPOINT로 마킹한 지점 A로 돌아가서 EMP_BACK 테이블을 SELECT 해보겠습니다. |
ROLLBACK TO A |
<실행결과>
롤백 완료.
SELECT * FROM emp_back |
<실행결과>
ENAME | |
1 | SCOTT |
2 | ADAMS |
3 | FORD |
4 | 홍길동 |
‘FORD’ 사원이 삭제되기 전 지점이 A 이므로 ‘FORD’가 출력 됩니다.
이 지점에서는 B 지점으로는 다시 돌아갈 수는 없습니다. B 이전의 A 지점까지 롤백을 해버렸으니 말입니다. |
ROLLBACK TO B |
<실행결과>
'B' 저장점이 이 세션에 설정되지 않았거나 부적합합니다.
‘홍길동’을 삭제하고 COMMIT 후 데이터를 확인 합니다. |
DELETE FROM emp_back WHERE ename = '홍길동' |
<실행결과>
1 행 이(가) 삭제되었습니다.
COMMIT을 하고 데이터를 확인 합니다. |
COMMIT |
<실행결과>
커밋 완료.
SELECT * FROM emp_back |
<실행결과>
ENAME | |
1 | SCOTT |
2 | ADAMS |
3 | FORD |
명령프롬프트의 다른 세션에서도 COMMIT된 상태를 확인 합니다. |
‘홍길동’ 사원을 다시 입력 합니다. |
INSERT INTO emp_back (ename) VALUES ('홍길동'); |
<실행결과>
1 행 이(가) 삽입되었습니다.
SELECT * FROM emp_back |
<실행결과>
ENAME | |
1 | SCOTT |
2 | ADAMS |
3 | FORD |
4 | 홍길동 |
명령프롬프트의 다른 세션에서 데이터를 확인해 볼까요? ‘홍길동’ 사원은 COMMIT 되지 않았으므로 보이지 않겠죠. |
a 라는 하나의 컬럼을 가진 A라는 테이블을 생성 합니다. |
CREATE TABLE A ( ❶ a VARCHAR2(10) ) |
<실행결과>
Table A이(가) 생성되었습니다.
명령프롬프트의 다른 세션에서 EMP_BACK 테이블의 데이터를 다시 확인해 볼까요? ‘홍길동’ 사원은 여전히 안보일까요? 아마도 보일 것 입니다. |
❶ CREATE TABLE 명령은 DDL(Data Definition Language)로 테이블을 생성하는 명령 입니다. 이러한 DDL 명령은 경우 실행전 현재의 트랜잭션을 자동 COMMIT 합니다. 그래서 다른 세션에서 ‘홍길동’ 데이터가 보인거죠. 이러한 DDL은 대부분 CREATE, DROPE, ALTER 로시작하는 명령들이니 자동 COMMIT 된다는 것 기억해 주세요.
이번에는 오라클의 SET TRANSACTION 명령에 대해 알아 보겠습니다
SET TRANSACTION 명령은 독립성수준과 읽기전용 또는 읽기쓰기와 같은 트랜잭션의 다양한 속성을 설정 가능 하게 합니다.
SET TRANSACTION READ WRITE : 현재의 트랜잭션을 읽기, 쓰기로 설정 합니다. 이 값이 오라클 트랜잭션의 기본값 입니다.
SET TRANSACTION READ ONLY : 현재의 트랜잭션을 읽기전용으로 설정 합니다.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE : 트랜잭션 내의 모든 SQL 문장을 수행할 때 트랜잭션 시작 시점의 데이터베이스 스냅샷을 보여 줍니다.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT SUM(sal) FROM emp; ……(A) SELECT SUM(sal) FROM emp; 이 경우는 첫 번째 SELECT문의 수행 시작 시점의 데이터베이스 스냅샷 값을 기준으로 두 문장 모두 같은 sum() 값을 구하게 된다. 즉 다른 사용자가 (A) 지점에서 EMP 테이블의 sal을 변동 시켜도 그 값은 반영되지 않는 다는 의미 입니다. |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED : 개별 SQL 문장의 수행 시작 시점의 데이터를 기준으로 결과를 보여줍니다.
SET TRANSACTION ISOLATION READ COMMITTED; SELECT SUM(sal) FROM emp; …… (A) SELECT SUM(sal) FROM emp; 즉, READ COMMIT 레벨은 트랜잭션 개별 문장의 수행 시작 시점의 스냅샷을 보여줍니다.
|
SET TRANSACTION 명령에 대해 실습을 해보겠습니다. EMP_BACK 테이블을 DROP 후 CREATE 하여 실습을 진행 합니다. |
DROP TABLE emp_back; CREATE TABLE emp_back AS SELECT ename, sal FROM emp WHERE deptno = 20; |
<실행결과>
Table EMP_BACK이(가) 삭제되었습니다.
Table EMP_BACK이(가) 생성되었습니다.
SELECT * FROM emp_back |
<실행결과>
ENAME | SAL | |
1 | JONES | 2975 |
2 | SCOTT | 3000 |
3 | ADAMS | 1100 |
4 | FORD | 3000 |
SET TRANSACTION READ ONLY 로 설정하면 그 트랜잭션은 읽기전용 입니다. |
SET TRANSACTION READ ONLY |
<실행결과>
Transaction READ을(를) 성공했습니다.
DELETE FROM emp_back |
<실행결과>
ORA-01456: READ ONLY 트랜잭션은 삽입/삭제/업데이트 작업을 수행할 수 없습니다.
다음 실습을 위해 현시점의 트랜잭션을 COMMIT 하여 정리합니다. |
COMMIT |
<실행결과>
커밋 완료.
SET TRANSACTION READ WRITE로 설정하면 그 트랜잭션은 읽기, 쓰기 가능합니다. |
SET TRANSACTION READ WRITE |
<실행결과>
Transaction READ을(를) 성공했습니다.
DELETE FROM emp_back |
<실행결과>
4개 행 이(가) 삭제되었습니다.
다음 실습을 위해 삭제한 데이터를 복구합니다. |
ROLLBACK |
<실행결과>
롤백 완료.
SET TRANSACTION ISOLATION READ COMMITTED를 실습합니다. 먼저 EMP_BACK 테이블에서 급여의 합을 SELECT하여 확인 합니다. |
SELECT SUM(sal) FROM emp_back |
<실행결과>
SUM(SAL) | |
1 | 10075 |
이때 명령프롬프트의 다른 세션에서 sal값을 변경 합니다. COMMIT 까지 해서 데이터베이스에 영구히 반영합니다. |
다시 원래의 SQL Developer쪽 세션으로 돌아와서 SUM(sal)을 수행 합니다. |
SELECT SUM(sal) FROM emp_back |
<실행결과>
SUM(SAL) | |
1 | 100750 |
합계가 10을 곱한 결과가 되었네요, 다른 세션에서 변경된 sal 값이 반영이 되어 합계가 SELECT 되었습니다. 오라클에서 트랜잭션 독립성 기본 설정은 SET TRANSACTION ISOLATION LEVEL READ COMMITTED로 COMMIT을 한 결과에 대해서 다른 세션에서도 확인이 가능합니다.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE을 실습합니다. EMP_BACK 테이블에서 급여의 합을 SELECT하여 확인 합니다. |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE |
<실행결과>
Transaction ISOLATION을(를) 성공했습니다.
SELECT SUM(sal) FROM emp_back |
<실행결과>
SUM(SAL) | |
1 | 100750 |
이때 명령프롬프트의 다른 세션에서 sal값을 변경 합니다. COMMIT 까지 해서 데이터베이스에 영구히 반영합니다. 급여를 10으로 나누었습니다. |
SELECT SUM(sal) FROM emp_back |
<실행결과>
SUM(SAL) | |
1 | 100750 |
명령프롬프트쪽 다른 세션에서 급여(sal) 값이 10으로 나누어 졌지만 반영되지 않았습니다. 트랜잭션 독립성 레벨이 SERIALIZABLE 이기 때문이죠.SET TRANSACTION ISOLATION LEVEL SERIALIZABLE은 트랜잭션 내의 모든 SQL 문장을 수행할 때 그 트랜잭션 시작 시점의 데이터베이스 스냅샷을 기준으로 결과를 보여주기 때문 입니다.
다음 실습을 위해 현시점의 트랜잭션을 COMMIT 하여 정리하고 EMP_BACK, A 테이블을 삭제합니다. |
COMMIT |
<실행결과>
커밋 완료.
DROP TABLE emp_back; DROP TABLE A; |
<실행결과>
Table EMP_BACK이(가) 삭제되었습니다.
Table A이(가) 삭제되었습니다.
#SAVEPOINT, #COMMIT, #트랜잭션, #오라클트랜잭션, #SQL트랜잭션, #트랜잭션이란, #오라클, #ORACLE
