12장 데이터 읽기 일관성과 락

목 차 데이터 읽기 일관성과 락 데드락 테이블 갱신시 발생한 락을 해결하는 방법 DDL명령의 롤백 Truncate 와 Delete의 차이

데이터 읽기 일관성과 락 (- 주간) 사용자가 동일한 데이터를 읽도록 하기 위하여 수정하기 전 자동으로 롤백세그먼트에 before image(복사본) 저장 Commit 이나 rollback에 의해 정보 수정가능 rollback에 의해 롤백세그먼트에 있던 복사본이 원상복귀 됨. 준비 사항 Scott의 emp, dept 테이블의 조회 권한을 scott_j 에게 부여 SQL> conn system/soft SQL> GRANT select on scott.emp to scott_j; SQL> GRANT select on scott.dept to scott_j;

데이터 읽기 일관성과 락 (- 주간) 1. SELECT * FROM em1 2.DELETE em1 SQL> conn scott_j/tiger SQL> CREATE TABLE em1 AS SELECT * FROM scott.emp; 로그인을 두 번 함 A : SQL> conn scott_j/tiger B : SQL> conn scott_j/tiger A(사용자:서울본사직원) B(사용자:대전지사직원)   1. SELECT *  FROM  em1       WHERE   empno=7788;                      3. UPDATE em1        SET sal=200      WHERE empno=7788;     Locking(무한대기상태)......    5.1 행이 갱신되었습니다. SQL> 2.DELETE  em1      WHERE empno=7788;   1행이 삭제되었습니다.       4.ROLLBACK;  Locking 해제

데이터 읽기 일관성과 락 사용자가 동일한 데이터를 읽도록 하기 위하여 수정하기 전 자동으로 롤백세그먼트에 before image(복사본) 저장 Commit 이나 rollback에 의해 정보 수정가능 rollback에 의해 롤백세그먼트에 있던 복사본이 원상복귀 됨. 준비 사항 Scott의 emp, dept 테이블의 조회 권한을 scott_y 에게 부여 SQL> conn system/soft SQL> GRANT select on scott.emp to scott_y; SQL> GRANT select on scott.dept to scott_y; 5

데이터 읽기 일관성과 락 1. SELECT * FROM em1 2.DELETE em1 WHERE empno=7788; SQL> conn scott_y/tiger SQL> CREATE TABLE em1 AS SELECT * FROM scott.emp; 로그인을 두 번 함 A : SQL> conn scott_y/tiger B : SQL> conn scott_y/tiger A(사용자:서울본사직원) B(사용자:대전지사직원)   1. SELECT *  FROM  em1       WHERE   empno=7788;                      3. UPDATE em1        SET sal=200      WHERE empno=7788;     Locking(무한대기상태)......    5.1 행이 갱신되었습니다. SQL> 2.DELETE  em1      WHERE empno=7788;   1행이 삭제되었습니다.       4.ROLLBACK;  Locking 해제 6

데드 락(dead lock, 교착상태 ) Locking(무한대기상태).... Locking(무한대기상태)... 데드락 교차로에서 자동차들이 교착상태에 빠진 것. A 와 B가 서로 lock 에 걸림으로써 아무 작업도 할 수 없는 상태 A(사용자:서울본사직원) B(사용자:대전지사직원) 1. UPDATE em1 SET  sal=100     WHERE  ename='SCOTT';                               3. UPDATE em1 SET  sal=300     WHERE ename='SMITH' ; Locking(무한대기상태)....                    2. UPDATE em1 SET  sal=20      WHERE  ename=‘SMITH’;     4. UPDATE em1 SET  sal=400      WHERE  ename='SCOTT';   Locking(무한대기상태)... ‘ORA-00060: 자원대기중 교착상태가 검출.’ 메시지와 함께 비정상 종료로 자동 롤백  B 는 여전히 물려있으므로 rollback으로 locking 해제해야 함.

락과 TCL 커밋(Commit) 롤백(Rollback) 트랜잭션을 데이터베이스에 반영. 트랜잭션을 데이터베이스에서 취소. 락 해제. 트랜잭션이 종료. 정상 종료시 자동 커밋(Auto Commit) 비정상 종료시 자동 롤백(Auto Rollback) 자동 커밋 :- DDL 사용시 - DDL 오류시 자동 롤백 :- 비정상 종료시 - deadlock 검출시

테이블 갱신 시 발생한 락 해결 방법 문제 제시 ALTER TABLE 테이블명 DROP COLUMN 컬럼명; delete(DML)는 undo segment 에 before image 저장후 commit, rollback에 의해 트랜잭션 종료 삭제가 진행되는 시점에서 다른 사용자가 DML명령어로 접근한다면 락걸림 SET UNUSED 다른 사용자가 접근하지 못하도록 제한만 둔다. 복구 안됨. ALTER TABLE 테이블명 SET UNUSED(컬럼명); 시스템 사용 빈도가 적을 때 실질적인 삭제를 한다. ALTER TABLE 테이블명 DROP UNUSED COLUMNS;

테이블 갱신 시 발생한 락 해결 방법 SQL> CREATE TABLE em3 AS SELECT * FROM emp; 사원테이블과 동일한 테이블 생성 SQL> CREATE TABLE em3 AS SELECT * FROM emp; em3 테이블에서 empname 컬럼 삭제 SQL> ALTER TABLE em3 DROP COLUMN empname; 접속 : >conn scott_y/tiger 삭제중 DML이 발생하면 lock 걸림 사원테이블과 동일한 테이블 생성 SQL> CREATE TABLE em4 AS SELECT * FROM emp; em4 테이블에서 empname 컬럼 사용하지 못하도록 함 SQL> ALTER TABLE em4 SET UNUSED (empname); SQL> SELECT * FROM em4; unused 컬럼 삭제 SQL> ALTER TABLE em4 DROP UNUSED COLUMNS; Empname 컬럼이 나타나지 않음.

DDL의 롤백은 불가 자동 커밋 DDL 문은 자동 커밋이 발생 -> 롤백 불가. 테이블을 복사해두었다가 변경후 잘 못 되었을 경우를 대비, rename 으로 이름 변경후 사용.

DDL의 롤백 SQL> CREATE TABLE em2 AS SELECT * FROM emp; 사원테이블과 동일한 테이블 생성 SQL> CREATE TABLE em2 AS SELECT * FROM emp; SQL> CREATE TABLE copy_em2 사원명 삭제 (DＤＬ로　자동커밋） SQL> ALTER TABLE em2 DROP COLUMN empname; SQL> ROLLBACK; SQL> SELECT * FROM em2; em2 테이블 삭제 SQL> DROP TABLE em2; SQL> RENAME copy_em2 TO em2; DDL은 롤백할 수 없으므로 복사해 둠. DDL은 롤백 불가 Empname 컬럼은 이미 제거됨.

TRUＮCATE와 DELETE 차이 TRUNCATE DDL 문 자동 커밋 발생, 삭제 이전으로 롤백 불가. 자동 커밋 발생, 삭제 이전으로 롤백 불가. 테이블 구조만 남고 모든 컬럼 삭제. DELETE DML 문 삭제 이전으로 롤백 가능. 삭제하고자 하는 컬럼과 로우 선택 가능 . (조건에 따른 삭제 가능)

TRUNCATE와 DELETE 차이 –cont’d 사원테이블과 동일한 테이블 생성 SQL> CREATE TABLE em5 AS SELECT * FROM emp; SQL> CREATE TABLE em6 AS SELECT * FROM em5; SQL> SELECT * FROM em6; SQL> TRUNCATE TABLE em6; SQL> ROLLBACK; Truncate 은 DDL이므로 롤백 불가. >>선택된 레코드가 없음

TRUNCATE와 DELETE 차이 –cont’d em5 사원테이블 삭제 SQL> DELETE em5; SQL> SELECT * FROM em5; SQL> ROLLBACK; SQL> DELETE em5 WHERE deptid=11 or deptid=21; SQL> COMMIT; DELETE는 롤백 가능. Commit 후 rollback은 이전상태로 복구불가.