트렌젝션(Transaction): 논리적 작업의 단위
예) 계좌 이체 transeaction
1. 돈을 A 통장 인출 => update A 통장 set 잔고 = 잔고 - 1만원 where 계좌번호 = ~~ and 비번 = ~~;
2. 돈을 B 통장 입금 => update B 통장 set 잔고 = 잔고 + 1만원 where 계좌번호 = ~~ ;
DML => 여러개의 문장이 하나의 transaction 구성
DDL, DCL => 한개의 문장이 하나의 transaction 구성
Transaction 처리(종료)
1. 저장
Commit
자동 => DDL,DCL를 만나면, 정상 log out
2. 취소
Rollback;
자동 => 비정상 log out, system 정전등 down
1.commit에 대한 실습
update emp
set sal = sal *2;
select * from emp; => 모든 사원들의 급여값이 두배인상되었는지 확인
commit; => 작업저장
창의 x버튼을 눌러 닫은후 다시 실행하여
select * from emp; => sal의 값은?(인상)
2. rollback에 대한 실습
delete emp
where ename='MILLER'; => miller 사원의 정보 삭제 시킴
update emp
set sal=sal*2
where ename='SCOTT'; => scott사원의 급여를 두배로 인상시킴
select * from emp; => miller? scott의 급여?
rollback; =>작업취소
select * from emp; => miller? scott의 급여?
/////////////////////////////////
1. A세션에서
select * from emp; =>행의 갯수등 확인해놓을 것
insert into emp(dmpno, ename, deptno)
values(9000, 'cjedu', 20);
select * from emp; => 9000번 사원입력되었나?(0)
2. B세션에서
select * from emp; => 9000번 사원의 정보?(0)
3. A세션에서
commit;
4. B세션에서
select * from emp; => 9000번 사원의 정보?(0)
*** 1,2,3,4번 실습의 결론
DML 문장은 트랜잭션을 종료하기 전까지 메모리에서만 수횅되므로 다른세션에서 확인할 수 없다. commit 명령이 수행되면 그제서야 DB에 반영됨(read consistency : 읽기 일관성)
5. A세션에서
create table trantest(
id number);
select * from tab; => trantest (0)
6. B세션에서
select * from tab; => trantest?(0)
** 5,6 번 실습의 결론
DDL문장은 자동commit되는 명령문이므로 한문장이 하나의 트랜잭션임, 다른 세션에서 바로 확인된다. 주의) DDL명령문은 rollback이 안됨!
7. A세션
delete emp
where empno = 9000;
select * from emp; => 9000번 삭제됨
drop table trantest;
select * from tab;
rollback;
select * from tab;
select * from emp;
**7번 실습의 결론
DDL명령문은 자동커밋되므로 그전에 실행했던 DML 명령문들도 함께 커밋됨, 커밋은 save와 동일, 따라서 DDL명령문이후에 rollback이나 commit이라는 명령문은 아무의미가 없음.
참고)트랜잭션의 종료(작업이 종료)되는 경우
: commit, rollback 되면 작업이 종료됨
**commit의 종류
1)commit; 명령어
2)자동 commit => DDL, DCL등의 명령문이 실행되거나 세션을 정상적으로 log out 하는 경우 (exit;라는 명령문)
**rollback 의 종류
1)rollback; 명령어
2)자동 rollback => 비정상적인 log out (x버튼을 누른 경우), 정전등에 의한 시스템 down, dead lock
8.자동 commit 되는 경우
a세션에서
update emp
set sal = sal *2;
select * from emp; => 급여값 확인
exit;
다시 새로운 창을 열어서
select * from emp; => 급여값(변경되어있음)
9.자동 rollback되는 경우
update emp
set sal = sal *0.5;
select * from emp; => 급여값
창의 x버튼을 눌러 닫은후
다시 새로운 창을 열어서
select * from emp; => 급여값(변경되지 않음)
1. savepoint 명령어 => rollback시를 위해 transaction중간에 기점을 선언하는 명령어.
update emp
set sal = sal *2
where ename='SCOTT';
select * from emp
where ename = 'SCOTT';
savepoint s1;
delete emp
where ename = 'SCOTT';
select * from emp;
rollback to savepoint s1;
select * from emp;
rollback;
select * from emp;
2. transaction과 lock
1)세션 A에서
rollback;
select *from emp;
update emp
set sal=sal*2
where ename ='MILLER';
select * from emp
where ename ='MILLER';
2)세션 B에서
select * from emp
where ename ='MILLER';
update emp
set sal=sal*2
where ename ='SMITH';
update emp
set sal= sal*2
where ename = 'MILLER';
세션 a로 돌아와서
commit;
세션 b에서 확인
3) 세션 a에서
drop table emp;
(no wait에러 : 테이블의 행에 대해서 다른세션에서 DML중이므로 테이블의 구조 변경, drop은 금지)
결론 : 트렌잭션 중에는 동시에 두가지 lock이 걸림
-row level lock (행수준 락) : 트랜잭션중인 그 행은 오로지 select만 가능, update, delete 불가한 배타적인 속성의 lock(exclusive lock)이 걸림
-table level lock(테이블 수준 락) : 트랜잭션에 참여하지 않은 행은 update, delete가능, but 테이블 자체의 drop, alter 불가한 공유 lock(shared lock)이 걸림
3. dead lock
1)세션 A에서
rollback;
update emp
set sal=sal*2
where ename ='SCOTT';
2)세션 B에서
rollback;
update emp
set sal = sal*2;
where ename ='JONES';
update emp
set sal=sal*2
where ename='SCOTT';
3)세션 A에서
update emp
set sal = sal*2
where ename='JONES';
4. undo segment
: DML 작업중에 발생하는 이전데이터를 저장하는 database내의 영역
conn /as sysdba
show parameter undo_tablespace
select * from dba_tablespaces
where tablespace_name='UNDO';
select * from dba_data_files
where tablespace_name='UNDO';
show parameter undo_retention
**10g Flashback 버전 질의 : 두 시점간의 변경된 데이터를 표시하는 기능
create table rates(
currency varchar2(4),
rate number(15,10)
);
insert into rates values('EURO', 1.1012);
commit;
update rates set rate = 1.1014;
commit;
update rates set rate = 1.1013;
commit;
delete rates;
commit;
insert into rates values('EURO', 1.1016);
commit;
update rates set rate = 1.1011;
commit;
**view : 가상의 논리적인 테이블, 실제 데이터는 테이블에 존재, 보안, 쿼리문을 간단하게 처리하기 위한 목적
1)view의 생성
- emp 테이블에서 10번 부서원들의 모든 정보를 볼 수 있는 emp10이라는 view를 생성하시오.
conn scott/tiger
select * from role_sys_privs;
select * from user_sys_privs;
conn /as sysdba
grant create view to scott;
conn scott/tiger
create or replace view emp20
as select *
from emp
where deptno=20;
-생성된 view들의 정보 조회
select * from tab;
select * from user_views;
2)view를 통한 데이터 조회 및 처리
select * from emp20;
insert into emp20(empno,ename, deptno)
values(1,'a',20);
select * from emp;
insert into emp20(empno,ename, deptno)
values(2,'b',30);
select * from emp20;
select * from emp;
**top-n query
1. emp 테이블에서 가장 최근에 입사한 5명의 모든 정보를 조회하시오.
select rownum,empno,hiredate
from
(select empno, hiredate
from emp
order by hiredate desc)
where rownum <=5;
2. 1번 결과를 제외한 나머지 사원들의 모든 정보를 조회하시오.
select rownum,empno,hiredate
from
(select empno, hiredate
from emp
order by hiredate desc)
minus
select rownum,empno,hiredate
from
(select empno, hiredate
from emp
order by hiredate desc)
where rownum <=5;
**emp테이블에 존재하는 사원의 이름을 sql*plus툴에서 입력받아 그 사원의 업무가 MANAGER@
set verify off
set serveroutput on
accept p_name prompt '이 름: '
declare
--선언부
v_empno emp.empno%type;
v_name emp.ename%type:= upper('&p_name');
v_sal emp.sal%type;
v_oldsal emp.sal%type;
v_job emp.job%type;
begin
select empno, job, sal
into v_empno, v_job, v_oldsal
from emp
where ename = v_name;
if v_job IN ('MANAGER', 'ANALYST') then
v_sal := v_oldsal*1.5;
else
v_sal := v_oldsal*1.2;
end if;
update emp
set sal = v_sal
where empno = v_empno;
--그 사원의 변경된 급여값을 조회하여
--화면에 출력하기
dbms_output.put_line ('변경전 사원의 급여는 ' || v_oldsal ||'이며, 변경된 급여는 ' || v_sal || '입니다.');
end;
