TX LOCK ⓐ 특정 행을 변경하고자 하는 경우

■ TX LOCK 
1. 특정 행을 변경하고자 하는 경우 
enq : TX - row lock contention

<hr session_1>

create table hr.emp as select * from hr.employees;
update hr.emp set salary = 2000 where employee_id = 200;

<hr session_2>

delete from hr.emp where employee_id = 200;

-- waiting 단계

<sys session>
모니터링

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

#) 현재 sql_id 확인해야 한다.
=> enq: TX - row lock contention 대기 이벤트 확인

select sql_text from v$sql where sql_id = '8suw0gu1fumpv';

#) 이전의 sql_id 확인

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

#) blocking_session 확인 : 24 
=> 여기서는 prev_sql_id 로 확인

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

#) 이 sql 문장으로 다른 session에서 대기 이벤트 발생한다.

select sql_text from v$sql where sql_id = '0xwwv6ba9d01c';

#) lock에 대한 정보 확인

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

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
49B54A68 49B54A94         18 TX    1245207       1547          0          6        741          0 =>  row lock  
F6EFD058 F6EFD088         24 TM      89143          0          3          0        760          0 => shared mode 
F6EFD058 F6EFD088         18 TM      89143          0          3          0        741          0 => shared mode 
48077CF0 48077D30         24 TX    1245207       1547          6          0        760          1 =>  row lock 

1#) TYPE : TM
ID1 : object id 

2#) TYPE : TX
ID1 : undo segment 번호 + transaction slot 번호 
ID2 : transaction slot sequence 번호 

#) undo 조각 번호 

SELECT
    sid, type, id1, id2, lmode, request,block,
    to_char(trunc(id1 / power(2, 16))) usn,
    bitand(id1, to_number('ffff', 'xxxx')) + 0 slot,
    id2 sqn
FROM  v$lock
WHERE sid in (18, 24) and type in ('TX', 'TM');

#) segment 확인

select segment_name from dba_rollback_segs where segment_id = 19;

#) object 번호 확인
tip) col object_name format a30

select object_name, object_type, data_object_id, object_id
from dba_objects
where object_id = 89143;

=> DATA_OBJECT_ID(rowid 생성시) = OBJECT_ID 값이 동일. / ==> 리오그 작업을 하지 않았다.

#) row 에 대기 이벤트 확인 

select sid, serial#, username, blocking_session, event, 
row_wait_obj# as obj_no, /* row object */
row_wait_file# as file_no, /* row file */
row_wait_block# as block_no, /* row block */
row_wait_row# as row_no /* row slot */
from v$session
where event like '%TX%';

#) 

select tablespace_name, file_name
from dba_data_files
where file_id = 4;

#) rowid 만들기 

select
    dbms_rowid.rowid_create(0, 89143, 4, 891, 2) as "restricted rowid",
    dbms_rowid.rowid_create(1, 89143, 4, 891, 2) as "extended rowid"
from dual;

restricted rowid(v7) 6byte : #block.#rowslot.#file
extended rowid(8v) 10btye : #object(6자리)+#file(3자리)+#block(6자리)+#rowslot(3자리)

#) 충돌된 rowid 확인

select * from hr.emp where rowid = 'AAAVw3AAEAAAAN7AAC';

#) 2개의 hr session에서 rollback; 하기

rollback;

---

<hr sess_1>

delete from hr.emp where employee_id = 200;

# emp 테이블에 대한 TM LOCK type이 걸리고 LOCK MODE :  RX, SX, 3 mode 걸린다.
# transaction 대상 행에 대해서는 TX lock type 걸리고 LOCK MODE : X, 6 걸린다.

<hr sess_2>

drop table hr.emp purge;

 

# emp 테이블에 대해 TX LOCK TYPE 이면서 LOCK MODE 는 RX, SX, 3 걸려 있어서 오류 발생.

<hr sess_1>

rollback;

<hr sess_2>

drop table hr.emp purge;