Transaction 처리 순서

■ Transaction 처리 순서
1. Undo Segment 할당
   - 트랜잭션이 시작되면 undo segment를 할당받는다.
   - 만약 할당받지 못한다면 "enq: US - contention" 대기 이벤트가 발생한다.

2. Undo Segment Header에 Transaction Table Slot 생성
   - Undo segment header에 트랜잭션 테이블 슬롯(Transaction Table Slot)을 생성.
   - 트랜잭션 정보, 즉 TXID(Transaction ID)를 생성.

3. Data Buffer Cache에서 대상 블록 찾기
   - 트랜잭션의 대상이 되는 블록을 실행 계획을 통해 data buffer cache에서 찾는다.
   - 블록 헤더에 ITL(Interested Transaction List)에 트랜잭션 엔트리를 등록.
   - ITL에 entry를 등록할 공간이 없다면 "enq: TX - allocate ITL entry" 대기 이벤트가 발생.

4. Change Vector 생성 및 Redo Entry 작성
   - PGA(Program Global Area)에서 트랜잭션의 변경 정보를 담은 change vector(이력 정보)를 생성.
   - Change vector 정보를 redo entry로 작성하여 "redo log buffer"에 복사한다.
   - 복사하는 동안 "latch: redo copy", "latch: redo allocation", "latch: redo writing" 래치를 획득해야 한다.
   - 만약 래치 경합이 발생하면 "latch: redo copy" -> "latch: redo allocation" -> "latch: redo writing" 순서로 해결된다.
   - 발생하는 대기 이벤트는 "log buffer space" 및 "log file switch completion"이다

5. Undo Block에 이전 이미지 기록 및 Data Block 변경
   - Undo block에 이전 이미지(값)에 대한 정보를 기록하고, 데이터 블록을 변경한다.
   - 변경된 블록은 dirty 상태가 된다.
   - 변경된 블록 헤더에 CR(Consistent Read, 읽기 일관성) 블록 정보를 작성.
   - 변경하는 row에 대해 row level locking을 생성.
   - 다른 세션이 해당 row에 대한 lock을 걸고 있으면 대기하며 "enq: TX - row lock contention" 대기 이벤트가 발생.

■  commit 처리과정
1. SCN 할당
   - Commit이 시작되면 시스템 커밋 번호(SCN)를 할당받는다.

2. Commit 정보 Redo Log Buffer에 저장
   - Commit 정보를 redo log buffer에 저장한다.

3. Transaction Table에 Commit 완료 정보 저장
   - Undo segment header의 transaction table에 Commit이 완료되었다는 정보를 저장한다.

4. Transaction Entry 및 Lock 해제
   - Transaction entry와 해당하는 lock을 해제한다.

5. LGWR 작동 및 Redo Log File에 기록
   - LGWR이 동작하여 redo log buffer의 내용을 redo log file에 기록한다.

■ Latch
- 가벼운 lock(light-weight lock)으로서, 공유 메모리 영역(SGA) 을 보호하기 위한 동기화 객체이다.

■ Lock
- latch 보다는 무거운 동기화 객체로서, database와 관련된 객체(object) 를 보호하는 동기화 객체이다.
- enquene lock : 순서대로 lock을 획득하는 lock 이다.

---

<hr session_1>

drop table hr.emp purge;

 

create table hr.emp as select * from hr.employees;

 

update hr.emp set salary = salary * 1.1 where employee_id = 100;

#1) table 에 대해서 TM lock 
#2) 트랜잭션 대상 row 에 대해서는 TX lock

<hr session_2>
=> lock 이라는 매커니즘 으로 인해서 drop 실행이 되지 않음.
1. update문 실행으로 인한 lock
2. table level 으로 인한 tx lock 

drop table hr.emp purge;

<sys session>

select * from dba_tables where table_name = 'EMPLOYEES' and owner = 'HR';
select * from v$session where username = 'HR';
select sid from v$session where username = 'HR';

select * from v$lock where sid in (10, 23) and type in ('TM','TX');

TYPE : TM => ID1 : OBJECT

select * from dba_objects where object_id = '89121';

TYPE : TX => ID1 : undo segment 번호 + transaction slot 번호 / ID2 : transaction slot sequence 번호  

LMODE > 0 이면 LOCK 을 보유중인 세션
REQUEST > 0 이면 LOCK 을 요청중인 세션
CTIME : 현재 LOCK 모드가 허용된 이후의 초단위 시간(second). 즉, lock 을 보유하거나 요청한 이후부터의 시간(초)
BLOCK : 현재 LOCK 이 다른 LOCK 을 블로킹하고 있는지 여부
해석) 
1 : 다른 LOCK 을 블로킹 중이다. 
0 : 다른 LOCK 을 블로킹하지 않는다.

<hr session_3>

update hr.emp set salary = 3000 where employee_id = 100;

watiting 단계

<sys session>

select * from v$lock where sid in (10, 125);

<SYS session>

select * from v$lock where sid in (10, 125);

#) LMODE
1) Row Share(RS), Sub Share(SS) = 2 로 표현 
-> 해석) LOCK 된 테이블에 대한 동시 엑세스를 허용하지만 세션이 배타적인(exclusive mode) 액세스를 위해 전체 테이블에 LOCK하는 것을 금지한다.
-> 수동 LOCK 하는 방법 : LOCK TABLE (테이블명) IN ROW SHARE MODE;

2) Row eXclusive(RX), Sub eXclusive(SX) = 3 으로 표현 
-> 해석) Row Share 와 동일하지만 shared mode 에서도 lock 을 금지한다. Row eXclusive lock은 데이터 갱신, 입력, 삭제(=:DML 작업) 자동으로 획득한다.
-> 수동 LOCK 하는 방법 : LOCK TABLE (테이블명) IN ROW EXCLUSIVE MODE;

3) Share(S) = 4 로 표현
-> 해석) 동식 query는 허용하지만 lock된 테이블에 대한 변경은 금지한다. 테이블에서 인덱스를 생성하려면 share lock이 필요하며 자동으로 요청한다.
-> unique, primary key 충돌시 발생
-> 수동 LOCK 하는 방법 : LOCK TABLE (테이블명) IN SHARE MODE;

4) Share Row eXclusive(SRX), Share Sub eXclusive(SSX) = 5 로 표현 
-> 해석) 전체 테이블을 query 하는데 사용되며 다른 유저가 테이블 행을 query 하는 것은 허용하지만 해당 테이블을 share mode에서 lock하거나 행을 갱신하는 것은 금지한다. 전체 테이블에 대해 조회는 가능+행에 대한 수정 작업 불가
-> 딕셔러리 table 에 해당 : sequence
-> row cache lock (sequence nocache 속성에서 nextval 호출할 때마다 dictionary 변경)
-> 수동 LOCK 하는 방법 : LOCK TABLE (테이블명) IN SHARE ROW EXCLUSIVE MODE;

5) eXclusive(X) = 6 으로 표현 
-> 해석) LOCK이 된 테이블에서의 query는 허용하지만 해당 테이블에서의 다른 작업은 금지한다. 즉, 해당 테이블에 DDL 작업 시에 exclsive lock 이 필요하다.
-> 수동 LOCK 하는 방법 : LOCK TABLE (테이블명) IN ROW EXCLUSIVE MODE;

 

※ 수동으로 lock 을 하는 명령어는 흔하지 않는 케이스이다.