Day 19 (SQL)

트랜잭션(Transaction)과 잠금(Lock)

트랜잭션은 하나의 작업단위를 말하며 원자성(Aotomicity), 일관성(Consistency), 독립성(Isolation), 영속성(Durability)을 가진다.

원자성(Aotomicity)은 트랜잭션은 최소의 작업 단위로서 전체가 처리되거나 취소되는것이지 일부만 처리되지 않는 속성이다.

일관성(Consistency)은 트랜잭션이 실행된 이후 DB의 무결성은 반드시 유지되어야 하는 속성이다. 함수적 종속관계에 있어야 한다는 말이다.

독립성(Isolation)은 여러개의 트랜잭션을 여러 개 동시에 실행하더라도 각각의 트랜잭션은 서로 영향을 줄 수 없는 속성이다. 즉 실행이 종료되지 않는 트랜잭션의 결과는 다른 트랜잭션에서 참조하는 것이 불가능하다. 대표적인 예시로 Day 18(SQL)에서COMMIT이나 ROLLBACK을 하지 않으면 테이블안의 데이터가 변경되지 않는 것이다.

영속성(Durability)은 종료된 트랜잭션의 결과를 반드시 데이터베이스에 반영되야 한다는 속성이다. 독립성과 연관되는데 COMMIT이나 ROLLBACK으로 종료되면 테이블 안에 데이터의 변화가 적용되야 한다는 것이다. 종료되기 이전의 데이터는 메모리에 정보가 저장되어 있는지 알 수 없다.

이제 트랜잭션의 시작과 종료에 대해 살펴보자.

시작은 이전 트랜잭션이 종료된 이후에 DML(INSERT, UPDATE, DELETE)문장이나 DDL(CREATE, ALTER, DROP, TRUNCATE)문장, DCL(GRANT, REVOKE)문장이 실행되면 시작한다.

(※ DCL은 계정권한이나 스키마에 대한 문장이다.)

종료는 문장이나 상태에 따라 다른데 DML문장은 COMMIT이나 ROLLBACK명령을 실행하면 종료된다. DDL이나 DCL문장은 실행이 완료되면 자동으로 종료된다. 따라서 DML문장과 DDL,DCL문장은 같이 사용하면 안된다. 그리고 사용자가 정상 종료시에도 종료되는데, Deadlock이 걸리면 트랜잭션의 일부만 종료된다.

Deadlock상황은 두 섹션에서 데이터 작업을 한번에 사용하지 못하는데 교차해서 리소스를 요청하여 아무도 사용 못하는 상태이다. 이를 Waiting상태에 있다고 한다. Day 18(SQL)에서 Wating걸린 상태가 있다.

가운데 음식을 먹기어야 하는데 각자 젓가락을 1개씩만 가져 아무도 음식을 못먹는 상태

이제 Lock에 관해 설명하겠다. 트랜잭션 진행과정은 먼저 데이터 수정 명령어를 입력하면 UNDO segement에 수정하는 데이터의 크기만큼 공간을 확보하고 임시 저장해 둔다. 여기서 segement는 물리적인 공간을 차지하는 object를 가르킨다. 그리고 데이터 값을 수정하게 되는데 이때 수정하는 행에 Lock이 걸려 다른 sql에서는 사용할 수 없다. 이를 독점 잠금(Exclusive lock)이라고한다. 다른 Lock방식은 공유잠금(Share lock)이 있는데 Table에 Lock을 거는 방식이다.

DDL(CREATE, ALTER, DROP, TRUNCATE)문장에 대해 알아보겠다.

테이블 생성은 CREATE TABLE 테이블( 컬럼 데이터타입 [DEFAULT default값] [컬럼레벨제약조건]..[테이블레벨제약조건];의 구성으로 되어 있다.

테이블 생성할때 테이블 이름을 정하는 것에는 규칙이 있다.

1. 문자로 시작한다.
2. 30자 이내로 한다.
3. 영문, 숫자, _, $, #, 한글이 사용가능하지만 영문과 _정도만 사용해야한다.
4. 테이블의 이름은 동일한 유저 즉 동일한 스키마안에서 유일해야 한다. 중복되면 안된다는 의미이다.
5. 예약어는 사용이 불가능하며 대소문자를 구별하지 않는다. 다만 Literal은 대소문자를 구별한다.
6. 테이블 이름은 딕셔너리에 모두 대문자로 정의되어 저장된다.

데이터 타입은 컬럼에 입력될 데이터의 종류와 크기를 결정한다. 뒤에 좀더 상세하게 다뤄볼 것이다.

DEFAULT는 입력이 누락됬을때 기본 입력값을 정의 하는 부분이다. 만약 기본 입력값을 정의하지 않는다면 NULL값이 저장된다.

컬럼 레벨 제약조건과 테이블 레벨 제약조건은 잘못된 데이터가 입력되지 않게 하여 무결성을 확보하기위해 사용한다. 하지만 실제로는 CREATE TABLE에서 사용하지 않고 따로 사용해준다.

테이블 삭제는 DROP TABLE 테이블 [CASCADE CONSTRAINT];로 구성된다. 테이블을 삭제할때는 삭제 순서가 중요한데 FK를 가지고 있는 자식테이블을 먼저 지워야 한다. 테이블을 생성할때는 fk를 할수있게 부모테이블을 먼저 생성한 후에 자식테이블을 생성하지만 삭제는 반대순서이다. [CASCADE CONSTRAINT]는 함수적 종속관계에 있는 열을 포함해서 삭제 하는 것이다. 만약 SCORE테이블과 STUDENT테이블을 삭제한다면 SCORE테이블을 먼저 삭제하고 STUDENT테이블을 학제해야 된다. 이때 [CASCADE CONSTRAINT]를 사용하면 STUDENT테이블을 먼저 지울 수 있는데, 이때 SCORE에서 STUDENT테이블을 FK하는 열도 같이 삭제한다. ~~실무에서는 책임에 관련된 일이기 때문에 DROP을 사용하면 안된다.~~

PURGE RECYCLEBIN;은 윈도우의 휴지통비우기와 같은 역할을 한다. 데이터를 삭제하게 되면 바로 완전히 삭제 되는것이 아니라 UNDO segment로 데이터가 이동되어진다. UNDO segment 데이터를 삭제하는 명령어 이다.

SELECT table_name FROM user_tables;를 하게되면 SELECT * FROM tab;과 같은 역할을 한다.

또한 SELECT table_name, column_name, data_type, data_length FROM user_tab_columns [WHERE table_name = '테이블'];은 DESC 테이블;과 같은 역할을 한다.

SELECT table_name FROM user_tables;, SELECT table_name, column_name, data_type, data_length FROM user_tab_columns [WHERE table_name = '테이블'];은 DICTIONARY를 이용해 정보를 확인하는 것이다.

데이터 딕셔너리(Data Dictionary)는 데이터베이스의 상태나 내부 운영과 관련된 정보를 저장해주는 테이블이다.

딕셔너리 이름의 접두어는 3가지가 있다.

DBA_ 는 관리자만 검색 가능한 딕셔너리에 붙는 접두어

ALL_은 일반사용자가 검색 가능한 딕셔너리, 접근 가능한 개체에 대한 정보를 담고있다

USER_는 일반사용자가 검색 가능한 딕셔너리, 소유하고 있는 개체에 대한 정보를 담고있다

데이터 타입은 다음과 같다.

문자	VARCHCAR2(n)	가변 길이 문자 타입 (1 < n <4000 byte)
문자	CHAR(n)	고정 길이 문자 타입 (1 < n <2000 byte)
숫자	NUMBER(n,p)	n은 전체 자리수 p 소수점 이하 자리수 전체 자리수를 초과할경우 입력 거부됨 소수점 이하 자리수 초과는 반올림
날짜	DATE	입출력 형식과 무관하게 YYYY/MM/DD:HH24:MI:SS형태로 저장
문자	LONG	2GB까지 저장가능한 가변 길이 문자 타입, 조건검색이 불가능하고, 테이블 하나당 하나의 컬럼에만 지정가능
문자	CLOB	4GB까지 저장가능한 가변 길이 문자 타입, 테이블 하나에서 여러 컬럼에 지정가능
이진	BLOB	4GB까지 저장가능한 가변 길이 이진 타입
	ROW(n)	가변길이 이진 타입 (n < 2000)
	BFILE	외부파일을 저장하기 위한 이진타입, 4G 이내 파일을 저장
ROWID	ROWID	ROWID를 저장하기 위한 타입, 64진수로 엔코딩되어있다