SQL 트랜젝션(commit, rollback)과 테이블 삭제 옵션들

트랜젝션

트렌젝션이란 트렌젝션 제어언어(TCL)로, commit과 rollack이 이에 해당한다. 트렌젝션은 연속적인 업무 단위로 분할할 수 없는 최소의 단위이며, 모두 commit하거나 모두 rollback해야하는 all or nothing의 개념을 가지고 있다.

commit은 영구저장을 뜻하며 rollback은 컨트롤+z와 비슷한 역할을 한다.

commit을 수행하면 commit이전에 수정된 DML은 모두 영구저장되며 되돌릴 수 없다. 

rollback의 경우 단순 rollback을 하게 되면 바로 전에 실행된 DML언어를 되돌려주지만, 내가 되돌아가고싶은 지점에 savepoin를 지정하여 되돌아 갈 수 도 있다.

-commit, rollbck--

--테이블 생성--
create table rollback_test(
id number not null,
name varchar2(100)
);

select * from rollback_test ;

--트랜젝션 시작--
insert into rollback_test values(1,'정우');
insert into rollback_test values(2,'유다빈');
insert into rollback_test values(3, '김뜻돌');
savepoint sp1;
update rollback_test set name='박문치' where id=3;
delete from rollback_test where name='정우';
rollback to sp1;
commit;

select * from rollback_test;

insert into rollback_test values(1,'정우'); insert into rollback_test values(2,'유다빈'); insert into rollback_test values(3, '김뜻돌');

먼저 insert문을 사용하여 3가지의 값을 넣어준 후에, savwpoint를 설정했다. 

update rollback_test set name='박문치' where id=3; delete from rollback_test where name='정우';

그 후에 update문을 사용하여 '김뜻돌'의 이름을 '박문치'로 바꿔주었고 '정우'의 행을 삭제했다.

rollback to sp1; commit; select * from rollback_test;

그 상태에서 rollback을 실행하여 다시 첫번째의 원래 상태로 테이블이 되돌아오게한후 commit한 결과이다.

 

실습

--실습--

--실습용 테이블 만들기--
create table emp_sal
as select empno, ename, sal
from emp
where sal=0;


select * from emp_sal;


--트렌잭션 시작--
insert into emp_sal values(7780, '김뜻돌', 2000);
insert into emp_sal values(7781, '김한주', 2500);
insert into emp_sal values(7782, '유다빈', 3000);

update emp_sal set sal=5000 where empno=7780;
rollback;

select * from emp_sal; --바로 이전으로 롤백--

insert into emp_sal values(7783, '박문치', 6000);
update emp_sal set sal = sal+100 where sal <=2000;
delete from emp_sal where sal>=4500;
rollback;
commit;

insert into emp_sal values(7783, '박문치', 6000);
commit

select * from emp_sal;

select * from emp_sal;

마지막으로 출력되는 직원 이름 박문치.

 

 

테이블 삭제 옵션 명령어

테이블을 삭제하는건 블로그 게시글 초반에 잠깐 다뤘었는데, 사실 테이블을 삭제하는것에도 몇가지 옵션들이 존재한다.

--테이블 삭제 옵션 명령어--
select * from emp2;

drop table emp1 purge; --테이블 완벽삭제--
drop table emp2; --휴지통에 보관--

--휴지통 확인--
show recyclebin;

--휴지통에 있는 테이블 지우기--
purge recyclebin;


--휴지통에 보관된 테이블 복구하기--
drop table emp_sal;
show recyclebin;
flashback table emp_sal to before drop;

 테이블을 삭제할때 drop 명령어 뒤에 purge를 붙이면 테이블이 영구삭제 되며 그 어디에도 남지않아, 복구할 수 없는 상태로 삭제된다. 만약 테이블을 삭제를 할건데 나중에 복구할 지도 모르니 복구가 가능한 형태로 일단 삭제하고싶다면 휴지통기능을 하는 recyclebin에 남겨두는 형태로 삭제할 수 있다. 기존 테이블을 삭제하는 방법과 drop  table로 테이블 명령어를 적어주되, purge를 작성하지 않는다면 show recyclebin 명령어를 통해 내가 삭제한 테이블이 휴지통에는 남아있다는것을 알 수 있다.

만약 작업을 하다가 휴지통에 삭제보관되어있는 테이블을 다시 복구하고싶다면 flashback table 테이블명 to before drop명령어를 통해 다시 복구가 가능하다.

 

---

 

학습일기

sql공부를 처음 하기시작했을때 트랜젝션을 배웠었어도 항상 commit하는걸 까먹어서 데이터 일부를 날리거나 했던 경험이 있다.

이상하게 rollback은 필요할때마다 안잊어버리고 바로바로하는데 commot의 경우 내가 따로 당장 commit하지 않아도 스크립트 창에서는 바로 보여서 그런지...이미 저장됐구나~~하고있으면 막상 같이 작업했던 다른분께는 commit하지 않아서 테이블 내용이 안보인다던지 하는 그런....그리고 DDL의경우에는 자동 commit되기때문에 DML을 작성하다가 DDL을 작성하지 않도록 주의할것!

그리고 휴지통의 경우 실무에서는 어떻게 또 다시 예전에 쓰던 테이블을 꺼내볼 수 도 있으니, 일단 purge로 냅다 영구삭제하는것보다 일단 recyclebin으로 보내놓고 나중에 recyclebin을 일괄 삭제하는 습관을 기르도록 하자!