210527_DB5(SQL) / 트랜잭션

Transaction

 

Transaction 이란 논리 작업 단위를 형성하는 DML문의 모음을 말한다.
예를들어, 은행업무 데이터베이스에서 은행 고객이 보통 예금에서 당좌 예금으로 금액을 이체하는 경우,
보통예금 잔액 감소,, 당좌 예금 잔액 증가 및 트랜잭션 기록이라는 세 가지 작업을 묶어서 하나의 Transaction이라 한다.

 

이 세 가지 작업 중 하나라도 수행되지 못하면, 나머지 작업도 최소 되어야 만 해당 계정에 정확한 잔액이 유지될 수 있다.

 

✔ 트랜 잭션이란??

  하나 혹은 두 개 이상의 SQL 문들로 이루어진 작업의 논리적인 단위
  하나의 트랜잭션 안의 모든 SQL은 동일한 효과를 내며 성공하면 전부 성공하고 실패하면 전부 실패한다.
  하나의 트랜잭션은 커밋(COMMIT)될 수도 있고 롤백(ROLLBACK) 될 수도 있다..
 

  

😎 알고 가기 

  트랜잭션은 실행 가능한 첫 번째 SQL문이 실행될 때 시작되어 다음 이벤트가 발생하면 종료된다.
  1. COMMIT 또는 ROLLBACK 문이 실행된 경우
  2. DCL(Data Control Language : GRANT, REVOKE) 문이  3. 사용자(Client)가 SQL*PLUS SQL*PLUS를 종료하는 경우
  4. 시스템에 장애가 있거나 시스템이 고장 난 경우
  5. DDL문 (Data Definition Language : CREATE TABLE, ALTER, DROP, RENAME)이 실행된 경우

 

  DDL 실패 시 ORACLE은 DDL문 전 후에도 암시적 COMMIT을 실행하므로

  DDL문이 성공적으로 실행되지 않는 경우에도 서버가 실행한 커밋 때문에 이전 명령문은 롤백할 수 없다.
 트랜잭션이 종료되면, 실행 가능한 다음 SQL문이 다음 트랜잭션을 자동으로 시작한다.

 

✔ 명시적 TRANSACTION 제어

  COMMIT

    : 보류 중인 모든 데이터 변경 내용을 영구히 저장하고 현재의 트랜잭션을 종료한다.
  SAVEPOINT NAME(별칭)

    : 현재의 트랜잭션 내에 저장점을 표시한다.
  ROLLBACK

   : 보류 중 인모든 데이터의 변경 내용을 버리고 현재의 트랜잭션을 종료한다.
  ROLLBACK TO SAVEPOINT NAME

   : 현재의 트랜잭션을 지정된 저장점으로 ROLLBACK 하여 저장점 이후에 생성된 모든 변경 내용 및 저장점을 버린다.

 

✔ 암시적 TRANSACTION 제어

 

  자동 COMMIT

    : DDL, DCL문이 수행되는 경우, SQL*PLUS가 정상적으로 종료되는 경우
  자동 ROLLBACK

   : SQL*PLUS가 비정상적으로 종료되는 경우 , 시스템에 장애가 있을 때

 

✔ SAVEPOINT 

 

   하나의 트랜잭션 내에서 각각의 SQL문이 실행되었던 시점을 구분하는 데 사용한다.
   아주 긴 작업을 수행하다가 한 번의 실수로 모든 작업을 ROLLBACK 한다면 그동안 작업 내용까지 모두 취소가 될 경우가 발생되어 예비 책으로 사용된다.
   트랜잭션 중간중간에 특정 시점을 표시해두면 실수한 트랜잭션만 취소할 수 있다.

 

ex)

 

INSERT INTO TEST002 VALUES (99, '인사과', '서울');
SAVEPOINT A; -- INSERT 문의 실행까지 표시
UPDATE TEST001
SET MGR = 7902
WHERE EMPNO = 7934;
ROLLBACK TO A; -- UPDATE 문만 실행 취소되며 INSERT문의 트랜잭션은 유효
DELETE FROM EMP;
COMMIT ; -- SAVEPOINT A 이후에 실행된 INSERT 와 DELETE문의 결과가 테이블에 영구 적용된다..

 

SQL> INSERT INTO TEST002 VALUES(99, '인사과', '서울');
 1 row created.

SQL> SAVEPOINT A;
 Savepoint created.
 
 SQL> SELECT * FROM TEST002;

DEPTNO DNAME                        LOC
------ ---------------------------- --------------------------
    10 ACCOUNTING                   NEW YORK
    20 RESEARCH                     DALLAS
    30 SALES                        CHICAGO
    40 OPERATIONS                   BOSTON
    99 인사과                       서울
    
SQL> UPDATE TEST001
2 SET MGR = 7902
3 WHERE EMPNO = 7934;

1 row updated.

SQL> SELECT * FROM TEST001;

EMPNO ENAME                JOB                  MGR HIREDATE   SAL  COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
 7369 SMITH                CLERK               7902 80/12/17   800           20
 7499 ALLEN                SALESMAN            7698 81/02/20  1600   300     30
 7521 WARD                 SALESMAN            7698 81/02/22  1250   500     30
 7566 JONES                MANAGER             7839 81/04/02  2975           20
 7654 MARTIN               SALESMAN            7698 81/09/28  1250  1400     30
 7698 BLAKE                MANAGER             7839 81/05/01  2850           30
 7782 CLARK                MANAGER             7839 81/06/09  2450           10
 7839 KING                 PRESIDENT                81/11/17  5000           10
 7844 TURNER               SALESMAN            7698 81/09/08  1500     0     30
 7900 JAMES                CLERK               7698 81/12/03   950           30
 7902 FORD                 ANALYST             7566 81/12/03  3000           20

EMPNO ENAME                JOB                  MGR HIREDATE   SAL  COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
 7934 MILLER               CLERK               7902 82/01/23  1300           10

12 rows selected.

SQL> ROLLBACK TO A; -- UPDATE 문만 실행 취소되며 INSERT문의 트랜잭션은 유효

Rollback complete.

SQL> DELETE FROM TEST001;

12 rows deleted.


SQL> COMMIT; -- SAVEPOINT A 이후에 실행된 INSERT 와 DELETE문의 결과가 테이블에 영구적용된다.

 

 

✔ 동시 제어

 

  LOCK

      : 동시에 수행되는 트랜잭션 간에 파괴적인 상호작용을 방지한다.
       트랜잭션 수행기간 동안LOCK은 유지된다.

 

  배타적(exclusive(exclusive lock)

     : DML 문에서 수정하는 각 행에 자동으로 배타적 잠금이 수행된다.
      트랜잭션이 종료될 때까지 다른 트랜잭션에서 해당행을 변경하지 못하도록 한다.
      이 잠금은 다른 사용자가 동시에 동일한 행을 수정하지 못하도록 하고 아직 완료되지 않은 변경 내용을

      다른 사용자가 덮어쓰지 못하도록 한다.

 

  공유(SHERE LOCK)

     : DML 작업 도중 테이블 레벨에서 자동으로 수행된다.
      공유 잠금 모드에서는 여러 트랜잭션이 동일한 자원에 대해 공유 잠금을 획득할 수 있다.
       → DML : 테이블 공유 잠금,, 행은 배타적 잠금 수행된다.
       → 잠금은 COMMIT, ROLLBACK 시 자동 해제된다..

 

 

  DEADLOCK

     : 둘 이상의 트랜잭션이 서로 상대방의 LOCK을 순환 대기하여 어떤 트랜잭션도 더 이상 진행할 수 없는 상태 말한다.

 

   

😎 알고 가기 

   

    오라클이 주기적으로 자동으로 DETECT 하여 에러를 리턴한다.

     1. 데드락 상태는 두 명 이상의 USER가 서로  LOCK을 걸고있는 객체에 대기하고 있을 때 발생한다.
     2. 현재 세션에 데드락이 발생하는 경우
        > 다른 세션이 비 호환 모드에서 자원의 락을 실행할 경우
        > 현재 세션이 자원을 요구하는 경우
        > 현재 세션에 비해 비 호환 모드에서 LOCK 처리된 자원을 다른 세션이 기다리는 경우
     

    º 오라클 서버는 데드락 상태를 감지한 명령문을 롤백하여 데드락 상태를 자동으로 감지하고 해결한다. 

 

---

 

✨ 테이블 생성에 관한 구문을 이해하고 설정 할 수 있다.
✨ 제약 조건의 특징 및 동작을 이해하고, 적절한 제약 조건을 테이블에 설정 할 수 있다.
✨ VIEW, SEQUENCE, INDEX, SYNONYM 을 생성, 변경 및 삭제 할 수 있다.

 

 

 

 

ORACLE 의 OBJECT 종류

 

   : TABLE (데이터저장), INDEX (질의의 효율성), VIEW (하나 이상의 테이블 데이터집합),
     SEQUENCE (기본 키 값을 생성), SYNONYM(객체에 다른 이름을 제공)

 

✔ 오브젝트 관련 DDL의 명령어

 

    : CREATE, ALTER, DROP, RENAME, COMMENT,
      TRUNCATE (테이블에 저장되어있는 모든 행을 삭제할때사용)

 

✔ TABLE (데이터 저장) 

 

    : SYS 사용자는 모든 데이터 딕셔너리 테이블(사용자이름, 권한, 객체이름, 테이블 제약조건 등)을 소유한다 .

 

ex) 사용자가 소유한 테이블

      : SELECT CALLUMN , , FROM TABLENAME;

 

   사용자가 소유한 오브젝트 이름과 종류를 확인
      : SELECT OBJECT_NAME, OBJECT_TYPE
        FROM USER_OBJECTS;

 

 

✔ 데이터 딕셔너리뷰

 

USER_ : 사용자 소유하는 객체에 관한 정보
ALL_ : 사용자가 엑세스 할 수 있는 모든 테이블에 관한 정보
DBA_: DBA ROLE을 할당받은 사용자만 액세스가 가능하다.
V$ : 데이터 베이스 서버의 성능, 메모리 및 잠금에 대한 동성 성능 뷰

 

[테이블 생성형식]

CREATE TABLE [schema.] TABLE_NAME → 문자시작, 1~30자, 사용자 내에 유일한 명칭, 예약어 X 대소문자구분X
(COLUMN DATATYPE [DEFAULT...] , , )

 

CREATE TABLE EMP_TEST(
EMPID NUMBER(4),
ENAME VARCHAR2(10),
SAL NUMBER(7) DEFAULT 100,
HIREDATE DATE DEFAULT SYSDATE); →  NEXTVAL, CURRVAL, 다른열의 이름은 올 수없다.

 

문자

 

: CHAR(2000 : 고정길이), VARCHAR2(4000:가변길이), VARCHAR(2000:가변길이)
  NCHAR(2000 : 고정길이 유니코드) : UTF8 = SIZE * 3BYTE, AL16UTF16 = SIZE*2BYTE
  NCHAR2(4000:고정길이 유니코드),
  LONG (2G : 가변길이의 문자데이터)

 

숫자

 

: NUMBER([PREC 전체자리수, SCALE 소수이하자리)] : 가변숫자 21BYTE 최대값
  BINARY_FLOAT : 32BIT 부동소수 = 4BYTE
  BINARY_DOUBLE : 64BIT 부동소수 = 8BYTE

 

날짜

 

: DATE = 7BYTE = BC4712년 1월1일 부터 9999년도 12월 31일 까지

LOB

 

: CLOB : LONG타입의 확장형태/ 큰 문자 데이터 저장/ 최대 4G
  BLOB : 바이너리로 형태저장 / 이미지, 영상, 음성 / 최대 4G
  NCLOB : NATIONAL CHARCACTER SET 로 저장/최대 4G
  BFILE : 외부 운영체제에서 파일 시스템에 저장, 테이블의 접근가능

 

ANSI 타입 오라클 변화타입

1. CHARATER (N) -------------------------------> CHAR(N)
2. CHARACTER VARYING(N) -------------------> VARCHAR (N)
3. NATIONAL CHARACTER(N) -----------------> NCHR(N)
4. NATIONAL CHARACTER YARYING(N) ------> NVARCHAR(N)
5. DECIMAL -------------------------------------> NUMBER
6. INTEGER, INT, SAMALLINT ------------------> NUMBER (38)
7. FLOAT, REAL, DOUBLE ----------------------> NUMBER

✔ 테이블 변경

 

새로운 컬럼 추가

 ALTER TABLE table_name ADD(
 column datatype [defalut expr] , , , )

기존 컬럼의 수정 : 자리수, 컬럼에 데이터가 없을 경우 타입을 수정

 ALTER TABLE table_name MODIFY(
 column datatype [defalut expr] , , , )

 

✔ 컬럼지정

 

SET UNUESD COLUMN : 컬럼을 삭제 할 수 있도록 표시하고 실제 테이블에서 컬럼이 제거되지 않는다.
                                 SELECT *, DESCRIBEE 문을 실행해도 표시되지 않는다.

 

ALTER TABLE table_name
SET UNUSED COLUMN column_name;

 

DROP UNUSED COLUMN : UNUESD 로 표시된 모든 컬럼을 제거한다.
                                     테이블에서 UNUSED 열로 부터 디스크 공간을 회수 하려고 사용한다.

ALTER TABLE table_name
DROP UNUSED COLUMNS ;

 

ex) ALTER TABLE BOOK SET UNUSED COLUMN (AUTHOR);
     ALTER TABLE BOOK SET DROP UNUESD COLUMNS;

 

✔컬럼삭제

 

기존 컬럼의 삭제 : 반드시 테이블에 하나 이상의 열은 존재해야한다.

  ALTER TABLE table_name DROP(column);
  ALTER TABLE table_name DROP COLUMN column;

ex) ALTER TABLE BOOK DROP (AUTHOR);

TRUNCATE TABLE문 : 테이블의 모든 행을 제거한다. 저장공간 해제, 롤백할 수 없다.
TRUNCATE TABLE table_name;