Database/Oracle

[SQL-Oracle]TCL(트렌젝션 제어어)-COMMIT, ROLLBACK, SAVEPOINT

sukii 2023. 12. 4. 00:10
반응형

 

⚫ TCL(TRANSACTION CONTROL LANGUAGE) : 트렌젝션 제어어
--트렌젝션에 저장된 SQL 명령(DML)을 실제 테이블에 적용하거나 테이블에 적용하지 않고 취소하는 명령

🔸트렌젝션(TRANSACTION) : 사용자 접속 환경(세션)에서 전달된 SQL 명령을 DBMS 서버에 저장하는 논리적인 작업단위
=> 클라이언트로부터 전달받은 SQL 명령을 DBMS 서버의 테이블에 바로 적용하지 않고 트렌젝션에 저장하여 사용

🔘 트렌젝션에 저장된 SQL 명령(DML)을 테이블에 적용하기 위해서는 커밋(COMMIT) 처리 - 커밋 처리 후 트렌젝션 초기화
--1.현재 세션에서 정상적으로 DBMS 서버와 접속을 종료한 경우 자동으로 커밋 처리
--2.DDL 명령 또는 DCL 명령을 작성하여 DBMS 서버에 전달할 경우 자동으로 커밋 처리
--3.COMMIT 명령을 DBMS 서버에 전달하면 수동으로 커밋 처리

🔘 트렌젝션에 저장된 SQL 명령(DML)을 테이블에 적용하지 않고 삭제하기 위해서는 롤백 처리
--1.현재 세션에서 비정상적으로 DBMS 서버와 접속이 종료된 경우 자동으로 롤백 처리
--2.ROLLBACK 명령을 DBMS 서버에 전달하면 수동으로 커밋 처리

 

ROLLBACK & COMMIT 예제⬇️

--DEPT 테이블에서 부서번호가 50인 행(부서정보) 삭제
SELECT * FROM DEPT;
--DELETE 명령이 서버에 전달되어 실행되는 것이 아니라 트렌젝션에 저장 - 테이블의 행 미삭제
DELETE FROM DEPT WHERE DEPTNO=50;
--DEPT 테이블의 검색행에서 트렌젝션에 저장된 DELETE 명령이 실행된 검색결과 제공
SELECT * FROM DEPT;

--ROLLBACK 명령을 전달하여 롤백 처리 - 트렌젝션 초기화
ROLLBACK;
SELECT * FROM DEPT;

--DEPT 테이블에서 부서번호가 50인 행(부서정보) 삭제
DELETE FROM DEPT WHERE DEPTNO=50;--트렌젝션에 DELETE 명령 저장
SELECT * FROM DEPT;
--COMMIT 명령을 전달하여 커밋 처리 - 트렌젝션에 저장된 모든 DML 명령들을 테이블에 적용되도록 실행
COMMIT;

 


*데이타 무결성 : 테이블에 잘못된 값이 저장되지 않도록 하여 정상적인 검색결과 제공

--DBMS 서버에 전달된 DML 명령이 잘못된 경우 롤백 처리하여 데이타를 복구할 수 있도록 트렌젝션 사용 - 데이타 무결성
SELECT * FROM EMP;
DELETE FROM EMP;
SELECT * FROM EMP;
ROLLBACK;
SELECT * FROM EMP;

 


*데이타 일관성 : DBMS 서버에 접속된 모든 사용자에게 동일한 검색결과 제공

--현재 세션에서 작업중인 테이블의 행은 커밋 처리 전까지 다른 세션에서는 기존행이 검색되도록 트렌젝션 사용 - 데이타 일관성
--BONUS 테이블에서 사원이름이 홍길동인 사원의 급여를 4000으로 변경
SELECT * FROM BONUS;
UPDATE BONUS SET SAL=4000 WHERE ENAME='홍길동';--테이블의 행이 변경되지 않고 트렌젝션의 UPDATE 명령 저장
SELECT * FROM BONUS;



--다른 사용자의 접속 환경(세션)에서는 BONUS 테이블에서 홍길동 사원의 급여는 미변경된 상태로 검색
--트렌젝션에 저장된 DML 명령을 테이블에 적용해야만 다른 세션에서도 변경된 테이블의 행 검색 가능

(즉, COMMIT을 해야 다른 세션에서도 검색 가능)

--데이터 잠금 기능(LOCK)을 제공하기 위해 트렌젝션 사용

--BONUS 테이블에서 사원이름이 ALLEN인 사원의 급여를 2000으로 변경
SELECT * FROM BONUS;
UPDATE BONUS SET SAL=2000 WHERE ENAME='ALLEN';
SELECT * FROM BONUS;

 


--현재 세션의 트렌젝션 작업중인 행을 다른 세션에서 조작할 경우 데이터 잠금 기능으로 인해 다른 세션에서 작성된 SQL 명령이 트렌젝션에 미저장

--BONUS 테이블에서 사원이름이 ALLEN인 사원의 성과급을 급여의 50%로 변경
--다른 세션에서 UPDATE 명령을 작성되어 서버에 전달
UPDATE BONUS SET COMM-SAL*0.5 WHERE ENAME='ALLEN'; -- 다른 세션에 미저장


--현재 세션에서 작업중인 행에 조작을 커밋하거나 롤백해야 다른 세션에서 작성된 SQL 명령이 전달되어 트렌젝션에 저장

COMMIT;



🔘 SAVEPOINT: 트렌젝션에 라벨(위치정보)을 부여하는 명령
--트렌젝션에서 원하는 위치까지 저장된 DML 명령을 롤백하기 위해 사용

SAVEPOINT를 사용하지 않았을 때⬇️

--BONUS 테이블에서 사원이름이 ALLEN인 행(사원정보) 삭제
SELECT * FROM BONUS;
DELETE FROM BONUS WHERE ENAME='ALLEN';
SELECT * FROM BONUS;

--BONUS 테이블에서 사원이름이 MARTIN인 행(사원정보) 삭제
DELETE FROM BONUS WHERE ENAME='MARTIN';
SELECT * FROM BONUS;

--롤백 처리
ROLLBACK;
SELECT * FROM BONUS;--트렌젝션에 저장된 모든 DML 명령 삭제


SAVEPOINT를 사용하면⬇️

--BONUS 테이블에서 사원이름이 ALLEN인 행(사원정보) 삭제
DELETE FROM BONUS WHERE ENAME='ALLEN';
SELECT * FROM BONUS;
SAVEPOINT ALLEN_DELETE_AFTER;

--BONUS 테이블에서 사원이름이 MARTIN인 행(사원정보) 삭제
DELETE FROM BONUS WHERE ENAME='MARTIN';
SELECT * FROM BONUS;

--SAVEPOINT 명령으로 지정된 위치까지의 DML 명령만 삭제되도록 롤백 처리 
--형식) ROLLBACK TO 라벨명
ROLLBACK TO ALLEN_DELETE_AFTER;
SELECT * FROM BONUS;

ROLLBACK;
SELECT * FROM BONUS;
반응형