================ LOCK monitor 및 감지 =================
1. Lock 목표
- Lock contention을 적게 한다.
- Lock contention을 적게 한다.
2. Lock Type 및 Lock Mode 정의
1) DDL Lock (= Dictionary Lock )
- Nowaiting => error
- 현재 실행중인 DDL작업이 schema object에 대해 동작하거나 참조하는 동안 해당 schema object가
재정의 되거나 수정, 삭제되는 것을 막는다.
(1) Exclusive Lock
- drop table, create table, alter table
- 다른 사용자가 Table에 어떤 level이든 잠금을 보유한 경우 그 Table에 대해서 exclusive lock을
획득할 수 없음. 에로 alter table시 해당 table에 commit이 안된 tx이 있으면 실패.
* locally managed tablespace인 경우 공간할당을 요정할때 data dictionary를 갱신하지 않으므로
ST(Space Transaction) lock에 대한 경합이 일어나지 않습니다.
(2) Shared Lock
- create procedure, audit
- 다른 사용자가 참조하는 객체를 변경 또는 삭제하는 것을 막는다.
2) DML Lock (= Data Lock )
- Waiting. Ctrl+C
- 최소 2개의 lock을 획득함.
- lock을 대기열에 넣는 방식으로 관리함으로 lock을 대기하는 사용자, 요청된 lock mode, 사용자가
lock을 요정한 순서를 추적 가능.
- lock을 대기열에 넣는 방식으로 관리함으로 lock을 대기하는 사용자, 요청된 lock mode, 사용자가
lock을 요정한 순서를 추적 가능.
(1) Row-level Lock(TX)
- insert, update, delete, select for update에 대해 배타적 lock을 건다
해당 각 row에는 Tx에서 사용하는 Interested Transaction List(ITL) slot을 가르키는 lock byte
(row header에 있음)가 설정됨.
(2) Table-level Lock(TM)
- 해당 Table에 대한 DDL을 막는다.
A. Row Share(RS) : select for update
- 다른 tx이 배타적 쓰기를 위해서 table을 수동으로 잠그는 것을 막는다. <======
B. Row Exclusive(RX)
- insert, update, delete
- 다른 tx이 해당 table의 다른 rows 대해서 질의, 삽입, 삭제, 갱신을 하도록 허용.
- 다른 tx이 배타적 읽기/쓰기를 위해서 table을 수동으로 잠그는 것을 막는다. <======
C. Share(S)
- foreign key에 index가 없는 경우 상위 Table에 대한 delete 또는 상위 Table의
해당 column에 대한 update시 하위 table에 대해 TM(S) lock을 획득.
- 다른 Tx이 table에 대해서 질의만 하도록 한다. <======
- 모든 종속 Table에 하위행이 남이 있는 동안에는 상위행이 삭제되거나 상위행의
기본키가 갱신 되어서는 안되기 때문. 하위Table은 이 규칙을 위반하는 갱신과
삽입을 막기 위해서 lock됨.
D. Share Row Exclusive(SRX) : foreign key에 index가 없고 on delete cascede가 있는 경우.
- 다른 Tx이 table에 대해서 질의만 하도록 한다. <======
- 다른 tx이 배타적 쓰기를 위해서 table을 수동으로 잠그는 것을 막는다. <======
- on delete cascade가 포함
- FK에 index가 없다.
예) EMP table DEPT table
ID(PK) name deptno(FK) ID(PK) deptname
------ ----- ---------- ------ ---------
1 홍길동 10 10 인사
2 성춘향 20 20 총무
3 방자 30 30 영업 <--TX
4 월매 40 40 교육
5 향단 40 |
| |
-----------------------
A: delete from dept where id=30( EMP table의 deptno에 index가 없는 경우)
- 30 영업 <--TX lock이 걸린다.
- DEPT table에 TM(RX) lock이 걸린다.
- EMP table에 TM(S) lock이 걸린다.
B: delete from dept where id=30( EMP table의 deptno에 index가 없고 on delete cascade인 경우)
- 30 영업 <--TX lock이 걸린다.
- DEPT table에 TM(RX) lock이 걸린다.
- EMP table에 TM(SRX) lock이 걸린다. <--왜? S + RX( row를 delete해야 하므로 )
C: select * from dept where id=30 for update
- 30 영업 <--TX lock이 걸린다.
- DEPT table에 TM(RS) lock이 걸린다.
<=== Fk key에 index가 있으면 Table전체에 TM(S) 또는 TM(SRX) lock을 거는 대신에 해당 column(fk)
에만 lock을 건다.(해당 column만 조작 불가능)
3) 수동 Table Lock mode
- Lock Table t_name In mode_name Mode;
- EXCLUSICE lock
- 예: Lock Table s_emp In Exclusice Mode;
- 다른 Tx이 table에 대해서 질의만 하도록 한다. <======
- 다른 tx이 배타적 쓰기를 위해서 table을 수동으로 잠그는 것을 막는다. <======
- manually하게만 설정.
3. LOCK 경합원인
1) 필요이상의 높은 lock level
2) 긴 tx으로 아직 commit이 안된 경우
3) 높은 lock level을 요구하는 app을 oracle과 같이 사용시
4. 진단 도구
1) V$LOCK
ID1 : TX인 경우 rollback segment 번호 및 slot 번호
ID2 : TM인 경우 object_id
- 특정 Table에 걸려있는 lock확인
select owner, object_id, object_name, object_type
from dba_objects, v$lock
where object_id = v$lock.id1
and object_name = '&table_name';
2) V$LOCKED_OBJECT
- xidusn : rollback segment 번호( 0 인 경우 waiting... )
object_id : object id
session_id : object를 lock하고 있는 session id
locked_mode:
select xidusn, object_id, session_id, locked_mode
from v$locked_object;
3) Top sessions
- Diagnostic Pack
5. DEAD LOCK 인지 및 해결
- alert.log에 ORA-60기록 및 user_dump_dest에 trc file생성.
- dead lock을 유발시킨 session에서 ORA-60을 만나고 해당 statement만 Rollback된다.