2024년 2월 8일 1교시

★ TX LOCK

 

1. 특정 행을 변경하고자 할 때 거는 LOCK

    enq: TX - row lock contention

 

<<hr>>

HR@ora11g> drop table hr.emp purge;
Table dropped.

 

<<sys>>

SYS@ora11g> create table hr.emp as select * from hr.employees;
Table created.

 

<<hr 1>>

HR@ora11g> update hr.emp set salary = 2000 where employee_id = 200;
1 row updated.

 

<<hr 2>>

HR@ora11g> delete from hr.emp where employee_id = 200;

세션이 대기 상태로 빠진다.

아래 상태로 꿈쩍도 안함.

 

 

<<sys>>

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

대기 원인 모니터링

 

23번 세션에서 enq: TX - row lock contention 대기이벤트 발생 중

이럴 때는 prev_sql_id 말고 sql_id 로 문장을 찾아내야 한다. (이전에 수행한 문장이 없기 때문)

 

 

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

대기 이벤트를 발생시킨 원인이 된 문장 찾기: delete from hr.emp where employee_id = 200

 

 

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

얘는 의미가 없음, 오라클이 내부적으로 던진 문장이 나온다.

 

 

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

블록을 걸고 있는 창 (191번 창) 에서 문제되는 sql 문 찾기

문제되는 문장은 sql_id 로 봐야 하고 지나간 sql 문을 보려면 prev_sql_id 로 봐야 한다.

 

 

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

191 세션의 update hr.emp set salary = 2000 where employee_id = 200

(23번 세션을 대기에 빠뜨린 원인이 된 문장)

 

 

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

현재 TX 이벤트가 일어나는 세션 정보 검색

 

 

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

23번 세션과 191번 세션이 걸어놓은 락 정보 보기

 

 

★ 잠깐 LMODE 복습

  Row eXclusive (RX) = Sub exclusive = 3

  row share 와 동일하지만 shared mode 에서도 락을 금지한다.

  row exclusive lock 은 데이터 갱신, 입력, 삭제시 (DML 작업시) 자동으로 획득한다.

  (조회는 가능하지만 같은 row 에 대해 배타적)

 

- TM LOCK은 서로 충돌되지 않는다. LMODE가 3번 shared 모드이기 때문

- LMODE 6 : ROW 에 대해 exclusive (같은 row 에 DML 작업 절대 불가)

  191번 세션에서 이미 200번 사원의 행에 TX 를 걸어놓았기 때문에 23번 세션에서는 기다려야 한다. 

- REQUEST: 나에게 걸려있는 LOCK (LMODE) 정보를 나타냄

- BLOCK = 1: 해당 LOCK 이 다른 세션에 의해 차단되어 있다는 뜻

 

TYPE : TM

ID 1 : Object ID - 89975

 

TYPE : TX

ID 1 : 어떤 언두 세그먼트를 이용하는지에 대한 정보 + transaction slot 번호 조합 - 393220

ID 2 : transaction slot sequence 번호 - 3335

 

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 spn
from v$lock where sid in (23,191) and type in ('TX', 'TM');

23번 세션과 191번 세션의 TX LOCK 과 TM LOCK 정보 보기

USN: 언두 세그먼트 번호

 

 

select segment_name from dba_rollback_segs where segment_id = 2;

_SYSSMU2_2598796883$

 

 

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

테이블 재배치를 하면 (re-org 작업하면) 뒤에 있는 데이터들을 다 앞으로 가져온다.

DATA_OBJECT_ID 와 OBJECT_ID 가 서로 같으면 한번도 재배치를 한 적이 없다는 뜻

ROWID 는 data_object_id 로 만든다. (data_obejct_id 가 현재 object 번호)

 

 

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

충돌된 row 정보 찾아내기

 

 

select sid, serial#, username, blocking_session, event, row_wait_obj# as obj_no, row_wait_file# as file_no,
row_wait_block# as block_no, row_wait_row# as row_no
from v$session where event like '%TX%';

 

 

select tablespace_name, file_name from dba_data_files where file_id = 4;

파일번호로 어떤 테이블스페이스에 있는지 살펴보기:

/u01/app/oracle/oradata/ora11g/users01.dbf

유저 테이블스페이스 안에 있다.

 

 

저 정보를 가지고 로아이디를 만들 수 있다.

 

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

 

     restricted rowid                    extended rowid

-----------------------------------------------------------------------

00000433.0002.0004       AAAV93AAEAAAAQzAAC

 

 

restricted rowid (v7) 6byte: #block.#rowslot.#file

extended rowid (v8) 10byte: #object(6자리) + #file(3자리) + #block(6자리) + #rowslot(3자리)

 

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

 

 

hr 세션 두개에서 rollback 하기

 

<<hr1>>

 

HR@ora11g> rollback;
Rollback complete.

 

<<hr2>>

 

HR@ora11g> delete from hr.emp where employee_id = 200;
1 row deleted.

 

hr1 세션에서 롤백하는 순간 hr2 세션에서 던진 delete 문이 수행된다.

여기서도 rollback 하기

 

<<hr2>>

 

HR@ora11g> rollback;
Rollback complete.