[sql] 10. TCL (COMMIT/ROLLBACK/SAVEPOINT)

1. TCL- 트랜잭션 제어어

• TRANSACTION CONTROL LANGUAGE
• 트렌젝션에 저장된 SQL 명령을 실제 테이블에 적용하여 실행하거나 적용하지 않고 취소하는 명령

 

 

 

2. 트렌젝션(TRANSACTION)의 개념

• 세션(SESSION)에서 DBMS 서버에 전달되어 실행될 SQL명령들을 저장하기 위한 작업단위
• 세션: DBMS 서버에 접속하여 SQL 명령을 전달하여 실행하기 위한 사용자의 작업환경
• SQL 명령 그룹
• 클라이언트에서 작성한 SQL 명령을 서버에 전달하여 데이터베이스에 바로 적용하는 것이 아니라,
  트렌젝션에 저장하여 나중에 실행되도록 처리

• 트렌젝션에 계속 계속 쌓이는 SQL 명령들...
• 우리가 세션에서 전달한 SQL명령을 곧바로 테이블에 적용하기에는 위험하므로, DBMS가 갖고 있는 트렌젝션에 저장한 뒤, 커밋 명령을 사용하면 DB서버에 저장함

1) 트렌젝션에 저장된 SQL 명령을 실제 테이블에 "적용"하는 방법

• 커밋(COMMIT) 처리
• 커밋 처리 후 트렌젝션 초기화
• 현재 세션이 정상적으로 서버 접속을 종료한 경우 자동 커밋 처리
• SQL DEVELOPER는 종료 시 커밋할지 물어봄
• DDL명령(개체관리) 또는 DCL명령(권한관리)을 작성하여 서버에 전달한 경우 자동 커밋 처리
• DML명령(데이타조작어)을 작성하여 서버에 전달한 경우 COMMIT명령을 사용하여 커밋 처리

2) 트렌젝션에 저장된 SQL 명령을 실제 테이블에 적용하지 않고 "삭제"하는 방법

• 롤백(ROLLBACK) 처리
• 현재 세션에서 비정상적으로 서버 접속을 종료한 경우 자동 롤백 처리
• DDL명령(개체관리)을 작성하여 서버에 전달한 경우 ROLLBACK명령을 사용하여 롤백 처리

 

 

 

 

 

3. TCL예시

--DEPT테이블에 저장된 모든 부서정보 검색
--트랜젝션에 저장된 SQL 명령이 없으므로 실제 테이블의 행을 검색하여 결과를 제공함
SELECT * FROM DEPT;

1) 롤백처리

--1)
--DEPT테이블에서 부서번호가 50인 부서정보 삭제
--세션과 연결되어있는 트랜젝션에 아래의 명령이 저장되어있음
--DELETE 명령을 서버에 전달하면 DEPT 테이블의 행을 삭제하지 않고 트렌젝션에 DELETE 명령 저장
DELETE FROM DEPT WHERE DEPTNO=50;
--실제 테이블의 행을 검색하여 트렌젝션에 저장된 SQL 명령이 실행된 결과를 제공해줌
SELECT * FROM DEPT; --실제 테이블에서 행이 삭제된 것은 아님!


--2)
--롤백처리 - 트렌젝션에 저장된 SQL 명령 삭제
ROLLBACK;
SELECT * FROM DEPT; --실제 테이블의 검색결과 제공

1)

2)

2) 커밋처리

--DEPT테이블에서 부서번호가 50인 부서정보 삭제
DELETE FROM DEPT WHERE DEPTNO=50;
--커밋처리 : 트렌젝션에 저장된 SQL 명령을 실제 테이블에 적용 - 트렌젝션 초기화
COMMIT;
SELECT * FROM DEPT; --실제 테이블의 검색결과 제공

2)

3) 특징1 - 데이타 무결성 기능

-- 롤백으로 복구 가능하기 때문

--1)
DELETE FROM EMP;
//16개 행 이(가) 삭제되었습니다.


--2)
SELECT * FROM EMP;


--3)
--서번에 전달된 SQL 명령이 잘못된 경우 롤백 처리를 통한 데이타 복구를 위해 트렌젝션 사용
-- 데이타 무결성 유지를 위한 방법
ROLLBACK;
//롤백 완료.


--4)
SELECT * FROM EMP;

2)

4)

4) 특징2 - 데이타 일관성 기능

-- COMMIT하지 않으면 다른 세션에서는 SQL 명령이 실행되기 전의 데이타 검색 가능
-- ex) 다른세션 : SQL DEVELOPER 프로그램

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


--데이타 일관성
--아직 COMMIT하지 않았기 때문에
--다른세션에서는 BONUS 테이블에서 사원이름이 KIM인 사원정보가 여전히 검색됨
--현재세션에서 커밋 처리 하기 전까지
--다른 세션에서는 SQL 명령이 실행되기 전의 검색 결과가 제공됨


--현재세션에서 커밋 처리를 했기 때문에 다른 세션에서도 SQL 명령이 적용된 검색결과 제공됨
COMMIT;

5) 특징3 - 데이타 잠김(LOCK) 기능

-- 데이타 잠김(LOCK) 기능을 제공하기 위해 트렌젝션 사용
-- DBMS는 다중 사용자 환경이므로 같은 테이블의 행을 다른 세션에서 조작 가능
-- 현재 세션에서 작업중인 테이블의 행을 다른 세션에서 작업하지 못하도록 트렌젝션을 이용하여 데이타 잠금 기능을 제공함

--1)
--BONUS테이블에서 사원이름이 ALLEND인 사원의 급여를 2000으로 변경
UPDATE BONUS SET SAL=2000 WHERE ENAME='ALLEN'; --데이타 잠김
SELECT * FROM BONUS;
--다른세션에서 BONUS 테이블에서 사원이름이 ALLEN인 사원의 성과급을 급여의 50%로 변경
--EX) SQL DEVELOPER 프로그램에서 디음과 같은 DML 명령을 작성해도 실행안됨
UPDATE BONUS SET COMM=SAL*0.5 WHERE ENAME='ALLEN'; -- 실행안됨!



--2)
--현재 세션에서 작업중인 테이블의 행을 다른 세션에서 조작할 경우, 트렌젝션에 의해 데이타 잠김 기능으로 인해 세션이 일시 중지됨
--현재 세션에서 작업중인 DML명령에 '커밋' 또는 '롤백' 처리를 해야만
--다른 세션에서 DML 명령 실행이 가능함
--이처럼 오라클(DB서버)는 데이타 잠금 기능을 제공하기 때문에. 다른 클라이언트 프로그램에 의해 동시에 테이블의 행 조작(데이타 조작)이 불가능함
--따라서 굳이 스레드 동기화와 관련된 메소드를 사용할 필요가 없음!
--이전에 자료구조클래스의 ARRAYLIST 사용하는 이유가 여기에 있음
COMMIT; --다른 클라이언트프로그램(SQL DEVELOPER 프로그램)에서 이제 데이타조작 가능!

1)

 

 

 

 

 

 

 

4. SAVEPOINT

• 트렌젝션에 라벨(위치정보)을 붙이는 명령
• 트렌젝션에 저장된 라벨을 이용하여 원하는 위치의 DML 명령들을 롤백 처리하기 위해 사용

1) [NO SAVEPOINT] 트렌젝션에 라벨 부착없이 롤백처리

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


--2)
--롤백처리
ROLLBACK; --트렌젝션에 저장된 모든 DML 명령을 삭제
SELECT * FROM BONUS; --작업했던 모든 것들이 다시 되돌아옴

1)

2)

2) [SAVEPOINT] 트렌젝션에 라벨 부착해 설정된 라벨을 이용하여 롤백 처리

-- 🖤형식
SAVEPOINT 라벨명

-- 🖤형식
ROLLBACK TO 라벨명 -- 라벨까지만  돌아가겠습니다! (SAVEPOINT 명령으로 설정된 라벨을 이용하여 롤백 처리)




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

--트렌젝션에 라벨 부착
SAVEPOINT ALLEN_DELETE_AFTER;

--BONUS 테이블에서 사원이름이 MARTIN인 사원정보 삭제
SELECT * FROM BONUS;


--2)
--트렌젝션에서 사원이름이 MARTIN인 사원정보를 삭제하는 DELETE명령 제거
ROLLBACK TO ALLEN_DELETE_AFTER;
SELECT * FROM BONUS;


--3)
ROLLBACK; --트렌젝션에 저장된 모든 DML 명령 제거
SELECT * FROM BONUS; -- ROLLBACK 명령만 사용하면 다시 작업했던 모든 것들 되돌아옴

 

1)

2)

3)