210609_1(데이터베이스5, 락&데드락, 제약조건)

락 & 데드락

서로다른 유저가 동일객체를 동시 사용할때 발생

* 어느 시점이든지 오라클사용자들은 일관적으로 동일한 데이터를 읽을 수 있어야 한다.

하나의 테이블에 동시에 사용자 자가 접근하여 DML 문장을 실행하는 경우

특정 사용자가 자원을 독점하지 못하도록 오라클은 락을 발생시킨다.

이 락을 해제하기 위해서 우리가 배운것이 바로 commit과 rollback이다.

 

dml문장을 실행하면 commit하기 전까지 lock이 걸린다

 

lock의 발생

create table emp01 as select * from emp;

예) 서울본사                                                인천지사

1. select * from emp01 where empno = 7900;

  --락과 관련없음--

                                                            2. delete from emp01 where empno=7900;

                                                              --7900에 락을 발생시킴--

3. update emp01 set sal=200

 where empno=7900;

--락에 걸려 진행불가--

                                                            4. commit / rollback을 해서 락을 푼다

5. 3번이 실행된다

6. commit/rollback을해서 락을푼다

 

주의) "웹사이트 port1521"에서 접근할때는 일반적으로 auto commit이 true로 설정되어 있기 때문에

        DML문장을 실행하면 바로 적용된다.

SHOW AUTOCOMMIT; -- 오토커밋확인

set autocommit off; 

set autocommit on;

 

dead lock 발생 -- 교착상태

* 서로가 서로를 물고 있어서 commit이나 rollback을 더이상 할 수 없는 상태

 

서울본사                     인천지사

-----------------------------------------------smith allen

1.update emp01 set 

sal = 100 where  

ename ='SMITH';                               

                              2.update emp01 set

                                  sal = 200  where

                                 ename = 'ALLEN';

3. update emp01 set

  sal = 400 where

  ename ='ALLEN'; 

<2번때문 진행불가>

                              4.update emp01 set

                                   sal = 300 where

                                    ename='SMITH';

                                 <1번때문 진행불가>

-----------------------------------------------------------교착상태

오라클은 교착상태를 검출하면 어떤 한쪽을 강제로 풀어버린다

5.rollback / commit

                                  6. lock 해제

                                  7. rollback / commit

무결성 제약조건

기본키PRIMARY KEY,

외래키FOREIGN KEY,

유니크UNIQUE,

널NULL

체크 CHECK

 

데이터의 무결성이란 데이터베이스 내의 데이터의 정확성을 유지하는 것이다.

즉, 사용자가 원하지않는 데이터가 저장되는 것을 방지하는것.

 

무결성 제약 조건들

컬럼명 데이터형 [제약조건들]

• not null : null을 불허한다. 반드시 유효한 데이터가 입력되어야한다.
• unique : 유일성 - 동일한 데이터의 입력을 불허 합니다. (어떤 디비는 null도 유니크하다)
• primary key : 기본키 - 행을 구별하는 식별자 역할 : unique + not null 
• foreign key : 외래키 - 기본키를 참조하는 키, 기본키와 데이터형이 같다.
• check : 특정지정값(ex. 서울 , 부산, 인천,, 중1택)이나 , 범위(나이 0~100살)만 입력을 허용하는 것.

기본키 연습 primary key

1) dept를 구조만 복제하여 dept01을 생성하시오

create table dept01 as select * from dept where 1=0;

 

2) dept01 (1,'A','AA') 를 입력하시오

insert into dept01 values ( 1,'A','AA'); --ok

 

3)한번더  dept01 (1,'A','AA') 를 입력하시오

insert into dept01 values ( 1,'A','AA'); -- ok

 

4) dept01 (null,'B','BB') 를 입력하시오 . 결과는?

insert into dept01 values ( null,'B','BB'); --ok 제약이 없으니 정상입력된다

 

    DEPTNO DNAME                  LOC

---------- ------------------------ --------------------------

         1    A                            AA

         1    A                            AA

               B                            BB

 

 

create table dept02( deptno number(2) primary key, dname varchar2(15), loc varchar2(15));

 

1) dept02 (1,'A','AA') 를 입력하시오

insert into dept02 values ( 1,'A','AA');

 

2)한번더  dept02 (1,'A','AA') 를 입력하시오

insert into dept02 values ( 1,'A','AA'); --no

1행에 오류:

ORA-00001: 무결성 제약 조건(SCOTT.SYS_C003999)에 위배됩니다

 

3) dept02 (null,'B','BB') 를 입력하시오 . 결과는?

insert into dept02 values ( null,'B','BB'); --no

1행에 오류:

ORA-01400: NULL을 ("SCOTT"."DEPT02"."DEPTNO") 안에 삽입할 수 없습니다

 

SQL> select * from dept02;

    DEPTNO DNAME                 LOC
---------- ------------------------ ------------------------------
         1 A                              AA

 

제약조건을 설정하는 방법

 

1. 컬럼수준의 제약조건 : 테이블 생성시 컬럼 옆에 제약을 기술하는것

 ex) 컬럼명 데이타형 [제약조건들] -- 컬럼을 기술할때 제약을 같이 기술

2. 테이블수준의 제약조건 : 모든컬럼을 명기하고 별도의 컬럼처럼 제약조건을 기술

 ex) 컬럼명 데이타형 , 

      제약조건

3. 테이블을 만든 후 제약을 설정하는 경우 : alter ~

 

설정한 제약 조건들의 정보

user_constraints 에서 찾을 수 있다.

desc user_constraints; --data dictionary

 이름                                                  널?      유형
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                    --소유자                        NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME    --제약이름                     NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE      --형태 P기본키/R외래키/   VARCHAR2(1)

                                   U유니크/ C 체크
 TABLE_NAME              --테이블이름                  NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                            LONG
 R_OWNER                  --외래키소유자                  VARCHAR2(30)
 R_CONSTRAINT_NAME  --참조대상키명                  VARCHAR2(30)
 DELETE_RULE                                                    VARCHAR2(9)
 STATUS                                                         VARCHAR2(8)
 DEFERRABLE                                                     VARCHAR2(14)
 DEFERRED                                                       VARCHAR2(9)
 VALIDATED                                                      VARCHAR2(13)
 GENERATED                                                      VARCHAR2(14)
 BAD                                                            VARCHAR2(3)
 RELY                                                           VARCHAR2(4)
 LAST_CHANGE                                                    DATE
 INDEX_OWNER                                                    VARCHAR2(30)
 INDEX_NAME                                                     VARCHAR2(30)
 INVALID                                                        VARCHAR2(7)
 VIEW_RELATED                                                   VARCHAR2(14)

 

column owner format a15

column constraint_name format a15

column constraint_type format a15

column table_name format a15

column  R_OWNER format a15

column R_CONSTRAINT_NAME format a15

 

select owner, constraint_name,constraint_type,table_name,R_OWNER,R_CONSTRAINT_NAME from user_constraints;

 

OWNER           CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME      R_OWNER         R_CONSTRAINT_NA

--------------- --------------- --------------- --------------- --------------- ---------------

SCOTT           FK_DEPTNO                   R    --외래키           EMP             SCOTT           PK_DEPT

SCOTT           SYS_C003999                 P    --기본키           DEPT02

SCOTT           PK_DEPT                       P                          DEPT

SCOTT           PK_EMP                        P                           EMP

 

1. 컬럼수준 이름부여 기본키 만들기

constraint 제약명

create table dept03 (deptno number(2) constraint dept03_pk primary key, dname varchar2(15), loc varchar2(15));

 

2. 테이블 수준 이름부여 기본키 만들기

별도의 컬럼처럼 명기 : constraint 이름 primary key(해당컬럼)

create table dept04 (deptno number(2), dname varchar2(15), loc varchar2(15)

, constraint dept04_pk primary key(deptno));

 

3. 수정문으로 제약추가하기

테이블을 생성한 후에,

alter table 테이블명 add constraint 제약이름 제약(컬럼)

create table dept05 (deptno number(2), dname varchar2(15), loc varchar2(15));

alter table dept05 add constraint dept05_pk primary key(deptno);@cons

OWNER           CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME      R_OWNER         R_CONSTRAINT_NA
--------------- --------------- --------------- --------------- --------------- ---------------
SCOTT           FK_DEPTNO       R               EMP             SCOTT           PK_DEPT
SCOTT           SYS_C003999     P               DEPT02
SCOTT           DEPT03_PK       P               DEPT03
SCOTT           DEPT04_PK       P               DEPT04
SCOTT           DEPT05_PK       P               DEPT05

 

주의 사항

1. 제약의 이름을 부여하지 않으면 이름은 SYS_xxxx 로 자동부여된다.

2. 컬럼수준, 테이블수준의 제약은 테이블을 삭제하면 제약도 같이 제거된다.

     alter로 만든 제약은 테이블을 삭제하면 제약조건은 휴지통으로간다.

     purge recyclebin; 휴지통 비우기

    제약조건은 alter drop으로 제거할수있다.

     alter table dept05 drop constraint dept05_pk;

 

OWNER           CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME      R_OWNER         R_CONSTRAINT_NA
--------------- --------------- --------------- --------------- --------------- ---------------
SCOTT           FK_DEPTNO       R               EMP             SCOTT           PK_DEPT
SCOTT           SYS_C003999     P               DEPT02
SCOTT           DEPT03_PK       P               DEPT03
SCOTT           DEPT04_PK       P               DEPT04
SCOTT           PK_DEPT         P               DEPT

 

제약조건명 변경

alter table 테이블명 rename constraint 구 to 생;

 

기본키 + 외래키

1. dept06을 만드시오. deptno 컬럼수준, 기본키 이름은 dept06_pk로 설정하시오

create table dept06 (deptno number(2) constraint dept06_pk primary key, dname varchar2(15), loc varchar2(15));

 

2. emp06을 만드시오.  ------------- 외래키 테이블 수준

empno 컬럼수준, 기본키, 이름은 emp06_pk로 설정하시오

   deptno는 테이블 수준, 외래키, 이름은 emp06_fk , dept06의 deptno를 참조 constraint이름 foreign key(해당컬럼)

   references 타겟테이블(타겟컬럼)

 

create table emp06(

empno number(2) constraint emp06_pk primary key,

ename varchar2(15), deptno number(2),

 constraint emp06_fk foreign key (deptno) references dept06(deptno)

);

----------- 외래키 컬럼 수준 X

create table emp07(

empno number(2) constraint emp07_pk primary key,

ename varchar2(15), deptno number(2)

constraint emp07_fk foreign key (deptno) references dept06(deptno)

);

create table emp07(

empno number(2) constraint emp07_pk primary key,

ename varchar2(15), deptno number(2) 

constraint emp07_fk foreign key references dept06(deptno)

);

------------- 외래키 컬럼 수준 O

create table emp07(

empno number(2) constraint emp07_pk primary key,

ename varchar2(15), deptno number(2)  references dept06(deptno)

);

------------- alter 문

create table emp08(

empno number(2) constraint emp08_pk primary key,

ename varchar2(15), deptno number(2)

);

alter table emp08 add constraint emp08_fk foreign key (deptno) references dept06(deptno)

 

부서-------

부서번호

부서명

위치

create table buseo (

b_no number(2) primary key, 

b_name varchar2(20),

b_loc varchar2(20)

);

 

insert into buseo values (1,'기획부','서울');

insert into buseo values (2,'생산부','인천');

insert into buseo values (3,'영업부','서울');

insert into buseo values (5,'영업부','서울');

 

사원--------

사번

사원명

부서번호

create table sawon (

s_no number(4) primary key,

s_name varchar2(20),

b_no number(2) ,

constraint sawon_fk foreign key (b_no) references buseo(b_no)

); 

 

1. (1111,'홍길동',3) 을 입력해보자-----------------O

insert into sawon values(1111,'홍길동',3);

 

2. (2222,'홍길순',1) 을 입력해보자---------------O

insert into sawon values(2222,'홍길순',1);

 

3.(3333,'홍길당',4)                     -----------------X 기본키를 참조할수 없는 외래키는 입력불가***

insert into sawon values(3333,'홍길순',4);

1행에 오류:

ORA-02291: 무결성 제약조건(SCOTT.SAWON_FK)이 위배되었습니다- 부모 키가 없습니다

 

4. update buseo set b_no=5 where b_no=2; --------X 기본키는 중복될수없다

1행에 오류:

ORA-00001: 무결성 제약 조건(SCOTT.SYS_C004010)에 위배됩니다

 

5. update buseo set b_no=6 where b_no=3; --------X  외래키에 의하여 참조되는 기본키는 수정 불가***

1행에 오류:                                                        해결법 : cascade - 기본키 변경->외래키도 변경

ORA-02292: 무결성 제약조건(SCOTT.SAWON_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다

 

6. delete from buseo where  b_no = 5; -------------O 

      B_NO B_NAME                                   B_LOC

---------- ---------------------------------------- ----------------------------------------

         1 기획부                                   서울

         2 생산부                                   인천

         3 영업부                                   서울

 

7. delete from buseo where  b_no = 1; ---------------X 외래키에 의하여 참조되는 기본키는 삭제 불가***

1행에 오류:                                                         해결법 : cascade -기본키 삭제 - 외래키 삭제

ORA-02292: 무결성 제약조건(SCOTT.SAWON_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다

 

cf) cascade

alter table 테이블명 add constraint 외래키이름 foreign key자식속성 references 부모테이블명(자식속성이 참고할 부모속성)on delete cascade;

 

alter table feed add constraint foreign key (user_id)references user(id) on delete cascade;

---

유일성 unique

컬럼수준

컬럼 데이터형 unique ; -- 자동이름

컬럼 데이터형 constraint 이름 unique ; -- 이름부여

테이블수준

 , contraint 이름 unique (해당컬럼);

alter 문

alter table 테이블이름 add constraint 제약이름 unique (해당컬럼);

 

 

아래 테이블에 부서명에 unique를 적용하시오

create table dept09 (deptno number(2), dname varchar2(15), loc varchar2(15));

 

컬럼--create table dept10 (deptno number(2), dname varchar2(15) unique, loc varchar2(15)) ;

테이블--create table dept11 (deptno number(2), dname varchar2(15), loc varchar2(15),

            constraint dname_con unique (dname)) ;

alter--alter table dept09 add constraint dname_con02 unique (dname);

 

dept09 부서명 기획, 영업, 영업, null, null 등도 입력해보고 결과가 어떤지 살펴보자

 

insert into dept09 values(1, '기획', '서울');

insert into dept09 values(2, '영업', '대전');

insert into dept09 values(3, '영업', '대구'); --선택지에 대구가 없다

1행에 오류:

ORA-00001: 무결성 제약 조건(SCOTT.DNAME_CON02)에 위배됩니다

 

insert into dept09 values(4, 'null', '부산'); --'null'은 문자열이라 null이아니다

insert into dept09 values(5, '', '광주');

insert into dept09 values(6, null, '부산');  --널값은 unique에 영향이 없다

1행에 오류:

ORA-00001: 무결성 제약 조건(SCOTT.DNAME_CON02)에 위배됩니다

    DEPTNO DNAME                          LOC

---------- ------------------------------ ------------------------------

         1 기획                           서울

         2 영업                           대전

         4 null                           부산

         5                                광주

         6                                부산   --널값은 unique에 영향이 없다

 

---

not null

not null 추가하는 방법

ALTER TABLE 테이블명
MODIFY 컬럼명 유형 NOT NULL;

 

자세한 설명은 생략한다.

---

check

특정값이나 영역만 입력이 가능하도록 함

기본형 constraint 이름 check (컬럼 in())  

          constraint 이름 check (컬럼 between A and B)  

 

-------------------------------아래테이블의 loc에는 서울,인천,분당,일산,남양주 값중 하나가 들어가도록 한다.

create table dept12 (deptno number(2), dname varchar2(15),

 loc varchar2(15) constraint dept12_ck check (loc in('서울','인천','분당','일산','남양주') ));

 

insert into dept12 values(1, '회계', '서울');

insert into dept12 values(2, '영업', '부천'); --선택지에 없는 '부천'

1행에 오류:

ORA-02290: 체크 제약조건(SCOTT.DEPT12_CK)이 위배되었습니다

insert into dept12 values(3, '생산', '제주'); -- 선택지에 없는 '제주'

1행에 오류:

ORA-02290: 체크 제약조건(SCOTT.DEPT12_CK)이 위배되었습니다

insert into dept12 values(4, '무역', '인천');

insert into dept12 values(5, '인사', '남양주');

    DEPTNO DNAME                          LOC
---------- ------------------------------ ------------------------------
         1 회계                           서울
         4 무역                           인천
         5 인사                           남양주

 

-----------------------------아래테이블의 sal에는 1000~2000 사이에서만 입력되도록 하시오.

create table emp12(

empno number(2) constraint emp12_pk primary key,

ename varchar2(15), deptno number(2),

 sal number(10) constraint emp12_ck check (sal between 1000 and 2000 ));

 

insert into emp12 values (1,'홍길동',2,1000); 

insert into emp12 values (2,'아로미',3,900); --sal의 범위에서벗어났다

1행에 오류:

ORA-02290: 체크 제약조건(SCOTT.EMP12_CK)이 위배되었습니다

insert into emp12 values (3,'투투',4,2000);

insert into emp12 values (4,'영심이',5,2100); --sal의 범위에서벗어났다

1행에 오류:

ORA-02290: 체크 제약조건(SCOTT.EMP12_CK)이 위배되었습니다

     EMPNO ENAME                              DEPTNO        SAL
---------- ------------------------------ ---------- ----------
         1 홍길동                                  2       1000
         3 투투                                    4       2000

 

---

제약조건 중지 시키지

alter table 테이블명 disable constraint 제약이름; -- 중지

alter table 테이블명 enable constraint 제약이름; -- 재가동

---