[오라클 트랜잭션] oracle transaction

//*****************************************

-- 트랜잭션(transaction)

   트랜잭션은 하나의 논리적 작업 단위로 수행되는 일련의 작업으로 테이블에 INSERT, UPDATE, DELETE 문으로 지시하고, COMMIT을 실행하면 모든 변경 사항이 영구히 데이터베이스에 저장되며, ROLLBACK은 마지막 COMMIT이나 ROLLBACK 이후의 변경 사항을 취소하고 데이터베이스를 원래의 상태로 되돌려 준다. 즉, 트랜잭션에서 데이터베이스를 변경하라는 명령이 COMMIT이고, COMMIT 전까지 변경된 것을 되돌리는 것이 ROLLBACK이다.

-- COMMIT

-- ROLLBACK

-- SAVEPOINT

   -- 트랜잭션 내의 한 시점을 표시한다.

   -- ROLLBACK TO SAVEPOINT 명령어로 표시 지점까지 ROLLBACK하는데 쓰인다.

   

CREATE TABLE insa1 AS SELECT num, name, city FROM insa;

INSERT INTO insa1 VALUES(2000, '테스트', '서울');

SELECT * FROM insa1;

SAVEPOINT a;

UPDATE  insa1  SET  city='울산' WHERE  num=1001;

SELECT * FROM insa1;

ROLLBACK TO a;  -- UPDATE 는 취소되고 INSERT는 최소되지 않은 상태

SELECT * FROM insa1;

ROLLBACK ;

SELECT * FROM insa1;

//*****************************************

-- SET TRANSACTION : 다양한 트랜잭션 속성을 지정

-- 커넥션1

SELECT * FROM insa1;

INSERT INTO insa1 VALUES(2000, '테스트', '서울');

-- 커넥션2 : cmd>sqlplus sky/"java$!"

SELECT * FROM insa1;

-- 커넥션1

ROLLBACK;

SET TRANSACTION READ ONLY;  -- SELECT만 가능

SELECT * FROM insa1;

DELETE FROM insa1;  -- 에러

ROLLBACK;

SET TRANSACTION READ WRITE; 

DELETE FROM insa1;

ROLLBACK;

//*****************************************

-- SET AUTOCOMMIT

sql>SHOW AUTOCOMMIT -- 상태확인

AUTOCOMMIT OFF

sql>SET AUTOCOMMIT ON

sql>SHOW AUTOCOMMIT

AUTOCOMMIT IMMEDIATE

sql>SET AUTOCOMMIT 3

sql>SHOW AUTOCOMMIT

모든 3 DML문에 AUTOCOMMIT ON

sql>SET AUTOCOMMIT OFF

//*****************************************

-- LOCK : 현재의 트랜잭션이 사용하고 있는 데이터에 대해 다른 트랜잭션의 검색이나 변경을 막아 여러 트랜잭션이 동시에 같은 데이터를 사용 할 수 있도록 하는 것

-- 커넥션 1

UPDATE insa1  SET  city='seoul' WHERE num=1001;

SELECT * FROM insa1;

-- 커넥션 2 : sqlplus

SELECT * FROM insa1;

SELECT * FROM insa1 FOR UPDATE WAIT 5; -- 5초후 에러 발생

-- 커넥션 1

ROLLBACK;

LOCK  TABLE  insa1  IN  EXCLUSIVE  MODE;

       -- EXCLUSIVE : 잠긴 테이블에 쿼리만 사용 가능

   -- NOWAIT : 다른 사용자가 이미 LOCK 했어도 바로 자신에게 제어가 넘어옴

DELETE FROM insa1;

-- 커넥션 2 : sqlplus

SELECT * FROM insa1;

UPDATE insa1  SET  city='seoul' WHERE num=1001; -- 케넥션1이 트랜잭션이 완료될때 까지 대기

-- 커넥션 1

ROLLBACK;

DROP TABLE insa1 PURGE; -- 에러

-- 커넥션 2 : sqlplus

ROLLBACK;

-- 커넥션 1

DROP TABLE insa1 PURGE; 

//*****************************************

-- COMMIT이 되지 않는 상태 확인

-- 관리자 계정에서 확인

SELECT s.inst_id inst,

       s.sid||','||s.serial# sid,

       s.username,

       s.program,

       s.status, 

       s.machine,

       s.service_name,

       '_SYSSMU'||t.xidusn||'$' ROLLNAME,

       --r.name rollname, 

       t.used_ublk, 

       ROUND(t.used_ublk * 8192 / 1024 / 1024, 2) used_bytes,

       s.prev_sql_id,

       s.sql_id

  FROM gv$session s,

       --v$rollname r,

       gv$transaction t

  WHERE s.saddr = t.ses_addr

 ORDER BY used_ublk, machine;