TX LOCK ⓒ 변경하고자 하는 블록의 ITL에 트랜잭션 엔트리를 등록하고자 하는 경우

■ TX LOCK 
3. 변경하고자 하는 블록의 ITL에 (자신에 해당하는) 트랜잭션 엔트리(=: TX)를 등록하고자 하는 경우

 

--> 트랜잭션 슬랏 생각하자!
enq : TX - allocate ITL entry | 트랜잭션 슬랏을 잡지 못해서 생기는 대기 이벤트

 

 

---

<sys sess>
#) 테이블 생성 시, maxtrans 설정.

create table hr.itl_table(id number, l_name varchar2(1000), f_name varchar2(1000)) 
initrans 1 maxtrans 2 pctfree 0;

#) maxtrans 설정의 무의미 확인

select ini_trans, max_trans, pct_free from dba_tables where table_name = 'ITL_TABLE';

#1) initrans : block header 에 transaction layer 에 생성된 transaction slot의 수
#2) maxtrans : data가 저장되어 있는 free 공간에 생성될 수 있는 transaction slot의 최대 수
       필요할 때 free 공강이 있으면 생성해서 사용하다가 transaction이 종료되면 자동으로 해제된다.
#3) pctfree : 최초로 블록에 데이터가 저장될 때 pctfree 값을 제외시키고 입력한다.
-why? (이유)
1) 기존 행의 값들의 증가분때문에 남겨 놓은 free 영역 
만약에 free 공간이 없으면 다른 블록이 이전을 해야 하는 문제가 발생. row migration 이 발생할 때
2) maxtrans 값을 보장하기 위해서 
만약에 transaction slot 생성하지 못하면 트랜잭션을 수행한 세션에서는 대기 하는 일이 발생한다.
-> enq : TX - allocate ITL entry 대기 이벤트 발생.

#) insert 문 실행 후 commit
=> 열에 꽉 채워지게 insert문 수행

insert into hr.itl_table (id, l_name, f_name)
select level, rpad('x', 1000, 'x'), rpad('z', 1000, 'z')
from dual
connect by level <= 10;

#) rowid 확인

select id, rowid, dbms_rowid.rowid_block_number(rowid) from hr.itl_table order by 3;

[HR 계정]
<hr_sess_1>

update hr.itl_table
set l_name = rpad('y', 1000, 'y') , f_name = rpad('a', 1000, 'a')
where id = 5;

<hr_sess_2>

update hr.itl_table
set l_name = rpad('y', 1000, 'y') , f_name = rpad('a', 1000, 'a')
where id = 6;

<hr_sess_3>

update hr.itl_table
set l_name = rpad('y', 1000, 'y') , f_name = rpad('a', 1000, 'a')
where id = 7;

--> waiting

 

<sys sess>
=> row lock이 아닌 트랜잭션 슬랏의 중요성 

select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TX%';

--> 트랜잭션 걸려있는 sql문장 : sql_id
--> BLOCKING_SESSION - prev_sql_id

 

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

#) 수행을 못하고 대기하고 있는 sql문장

select sql_text from v$sql where sql_id = 'fnxxranhzqd2n';

#) 트랜잭션을 못하고 있는 원인 문장 확인

select sid, serial#, username, sql_id, prev_sql_id
from v$session
where sid in (select blocking_session from v$session);

#) sess_1,2,3 모두 rollback;

rollback;

#) enq: TX - allocate ITL entry 대기 이벤트의 해결방안
<sys sess>
#) 값 확인

select ini_trans, max_trans, pct_free from dba_tables where table_name = 'ITL_TABLE';

#) 테이블 수정 후 확인

alter table hr.itl_table initrans 2 pctfree 10;

--> block parameter 값을 수정한 경우 새로운 block 부터 적용된다. 
--> reorg 작업(테이블 재배치 작업 해야 한다.)

select ini_trans, max_trans, pct_free from dba_tables where table_name = 'ITL_TABLE';

# 기본 block에도 새로운 block parameter 를 적용하려면 테이블을 재구성해야 한다. 
# 테이블을 재구성하면 기존 행들의 rowid가 변경된다.  index는 꼭 재구성해야 한다.

#) 테이블 재배치
--> rowid 변경 , index 다시 꼭 구성해야 한다!

alter table hr.itl_table move;

#) index 재구성
--> rebuild online : 운영중에 변경하겠다.

alter index hr.itl_table_id rebuild online;

#) 변경되었는지 rowid 확인

select id, rowid, dbms_rowid.rowid_block_number(rowid) from hr.itl_table order by 3;

☆ [이후에 update 문 실행 해보기!] ☆ 
<hr sess_1>

update hr.itl_table
set l_name = rpad('y', 1000, 'y') , f_name = rpad('a', 1000, 'a')
where id = 5;

<hr sess_2>

update hr.itl_table
set l_name = rpad('y', 1000, 'y') , f_name = rpad('a', 1000, 'a')
where id = 6;

<hr sess_3>

update hr.itl_table
set l_name = rpad('y', 1000, 'y') , f_name = rpad('a', 1000, 'a')
where id = 7;