[아이티윌 오라클 DBA 과정 91기] 260128 TIL

2026. 1. 29. 09:30ㆍCourses/아이티윌 오라클 DBA 과정

Data Buffer Cache(Cont.)

Index

Buffer pinning

Index Range Scna 시 동일한 Leaf Block을 반복적으로 읽어야 함
buffer pinning을 통해 반복적으로 읽어야 하는 버퍼에 대해 Cache Buffers Chains Latch를 매번 획득할 필요 없이 읽을 수 있음

Root Block이 변경되는 경우

인덱스 삭제, 테이블 삭제
데이터 삭제
인덱스가 걸려 있는 컬럼을 수정
- 일반 컬럼을 수정하는 경우 인덱스 블록은 shared 모드로 읽음
- 인덱스가 걸려 있는 컬럼을 수정하는 경우 인덱스 블록을 exclusive 모드로 읽음
- 인덱스는 정렬을 유지해야 하므로 수정 발생 시 해당 로우를 삭제 후 순서에 맞는 블록에 로우를 추가해줘야 함

DML 시 Data Buffer Cache 탐색 과정

Lock

다중 세션에서 동일한 데이터를 동시에 변경하는 것을 방지하기 위한 목적으로 사용
데이터베이스와 관련된 객체를 보호하는 동기화 객체
enqueue lock(대기 행렬)

동일한 행에 대해 DML 수행 시 lock 충돌

enq: TX - row lock contention 대기 이벤트 발생

HR 1 : 테이블 생성 후 변경 작업

HR@ora19c> drop table hr.emp purge;

Table dropped.

HR@ora19c> create table hr.emp tablespace users as select * from hr.employees;

Table created.

HR@ora19c> update hr.emp set salary = salary * 1.1 where employee_id = 100;

1 row updated.

테이블에 대해서 TM LOCK(SX MODE(3) 획득), 트랜잭션 대상 행에 대해서는 TX LOCK(X MODE(6) 획득)

HR 2 : 테이블 drop → 실패

HR@ora19c> drop table hr.emp purge;
drop table hr.emp purge
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

테이블에 대해서 TM LOCK(X MODE(6) 획득) 해야 하는데 누군가 먼저 대상 테이블에 대해서 LOCK을 걸어 놓았으면 충돌 때문에 오류 발생

트랜잭션 조회

SYS@ora19c> select s.sid, s.username, t.xidusn, xidslot, xidsqn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
and s.username = 'HR';

       SID USERNAME                           XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK  USED_UBLK
---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
       158 HR                                      7         25       2121          4      16325          1

lock 조회

SYS@ora19c> select *
from v$lock
where type in('TX', 'TM') and sid = 158;  2    3

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00007F2601A28C50 00007F2601A28C78        158 TM      75323          0          3          0        486          0      0
0000000070F57080 0000000070F570B8        158 TX     458777       2121          6          0        486          0      0

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid = 158;  2    3    4

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       158 TM      75323          0          3          0       1233          0 1                9787          0
       158 TX     458777       2121          6          0       1233          0 7                  25       2121

TYPE : lock 종류
- TM : 테이블 레벨 lock
- TX : row 레벨 lock, 트랜잭션 대상 행에 대해 걸음
ID1
- TM일 경우 object id
- TX일 경우 undo segment + transaction slot

# ID1 값으로 object 조회
SYS@ora19c> select object_name from dba_objects where object_id = 75323;

OBJECT_NAME
------------------------------
EMP

ID2 : transaction slot sequence
LMODE : Lock Mode, 0보다 크면 lock을 보유 중인 세션
REQUEST : 0보다 크면 lock을 요청 중인 세션
CTIME : 현재 lock mode가 허용된 이후의 시간(second), 즉 lock을 보유하거나 요청한 이후부터의 초시간
BLOCK : 현재 lock이 다른 lock을 블로킹하고 있는지 여부
- 0 : lock을 블로킹하지 않음
- 1 : lock을 블로킹하고 있음

HR3

HR@ora19c> update hr.emp set salary = salary * 1.2 where employee_id = 100;
# 대기 중

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;  5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       158 TM      75323          0          3          0       2063          0 1                9787          0
       158 TX     458777       2121          6          0       2063          1 7                  25       2121
       172 TM      75323          0          3          0        216          0 1                9787          0
       172 TX     458777       2121          0          6        216          0 7                  25       2121

세션 모니터링

# HR 유저 세션 정보 조회
SYS@ora19c> select sid, event, blocking_session from v$session where username = 'HR';

       SID EVENT                                    BLOCKING_SESSION
---------- ---------------------------------------- ----------------
       139 SQL*Net message from client
       158 SQL*Net message from client
       172 enq: TX - row lock contention                         158

lock이 걸린 세션 및 sql문 조회

# TX lock이 걸린 세션 정보 조회
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id from v$session where event like '%TX%';

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       172      43424 HR                                          158 enq: TX - row lock contention            8sjbnzfxs5w8x g4y6nw3tts7cc

# TX lock이 걸린 SQL문 조회
SYS@ora19c> select sql_text from v$sql where sql_id = '8sjbnzfxs5w8x';

SQL_TEXT
----------------------------------------------------------------------------------------------------
update hr.emp set salary = salary * 1.2 where employee_id = 100

blocking 중인 세션 및 sql문 조회

# blocking 중인 세션 정보 조회
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where sid in (select blocking_session from v$session where event like '%TX%');  2    3

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       158       6101 HR                                              SQL*Net message from client                        43qd0u7n4j2nk

# 위에서 조회한 prev_sql_id로 blocking 중인 sql문 조회
SYS@ora19c> select sql_text from v$sql where sql_id = '43qd0u7n4j2nk';

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

lock이 발생한 rowid 조회

SYS@ora19c> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where event like '%TX%';

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        75323              7             475             0

SYS@ora19c> select object_name, object_type from dba_objects where data_object_id = 75323;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
EMP                            TABLE

SYS@ora19c> select file_name, tablespace_name from dba_data_files where file_id = 7;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf         USERS

SYS@ora19c> select extent_id, file_id, block_id, blocks from dba_extents where owner = 'HR' and segment_name = 'EMP';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          7        472          8        

# rowid 형태로 조회
SYS@ora19c> select
    dbms_rowid.rowid_create(0,75323,7,475,0) as "restricted rowid",
    dbms_rowid.rowid_create(1,75323,7,475,0) as "extended rowid"
from dual;  2    3    4

restricted rowid   extended rowid
------------------ ------------------
000001DB.0000.0007 AAASY7AAHAAAAHbAAA

restricted(제한된) rowid(v7)
- 6byte : #block.#rowslot.#file
extended(확장된) rowid(v8)
- 10 byte : #data_object_id(6) #file(3) #block(6) #rowslot(3)

HR 1 Rollback

HR@ora19c> rollback;

Rollback complete.

Lock 조회 : HR 3 세션 TX lock ID1 변경됨

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;  2    3    4    5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       172 TX     655389       2091          6          0         52          0 10                 29       2091
       172 TM      75323          0          3          0       3161          0 1                9787          0

undo segment는 세션 별로 할당 받음
HR 3 세션이 TX lock 획득 후update문을 execute하면서 undo segment를 할당 받음
- USN 7 → 10
- SLOT 25 → 29
- SQN 2121 → 2091

HR1 : delete 작업

HR@ora19c> delete from hr.emp where employee_id = 100;
# 대기 중

# lock 조회
SYS@ora19c> /

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       158 TX     655389       2091          0          6          3          0 10                 29       2091
       158 TM      75323          0          3          0          3          0 1                9787          0
       172 TM      75323          0          3          0       4067          0 1                9787          0
       172 TX     655389       2091          6          0        958          1 10                 29       2091

# blocking 된 세션 조회
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id from v$session where event like '%TX%';

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       158       6101 HR                                          172 enq: TX - row lock contention            5prc84jkw6yku 43qd0u7n4j2nk

# blocking된 sql 조회
SYS@ora19c> select sql_text from v$sql where sql_id = '5prc84jkw6yku';

SQL_TEXT
----------------------------------------------------------------------------------------------------
delete from hr.emp where employee_id = 100

# blockiong 중인 세션 조회
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where sid in (select blocking_session from v$session where event like '%TX%');  2    3

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       172      43424 HR                                              SQL*Net message from client                        8sjbnzfxs5w8x

# blocking 중인 sql 조회
SYS@ora19c> select sql_text from v$sql where sql_id = '8sjbnzfxs5w8x';

SQL_TEXT
----------------------------------------------------------------------------------------------------
update hr.emp set salary = salary * 1.2 where employee_id = 100

# blocking된 rowid 조회
SYS@ora19c> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where event like '%TX%';

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        75323              7             475             0

SYS@ora19c> select
    dbms_rowid.rowid_create(0,75323,7,475,0) as "restricted rowid",
    dbms_rowid.rowid_create(1,75323,7,475,0) as "extended rowid"
from dual;  2    3    4

restricted rowid   extended rowid
------------------ ------------------
000001DB.0000.0007 AAASY7AAHAAAAHbAAA

blocking 중인 세션 정보와 kill 명령어 함께 출력

SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id, 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ' immediate;' kill_sql
from v$session
where sid in (select blocking_session from v$session where event like '%TX%');  2    3

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID        PREV_SQL_ID   KILL_SQL
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- ------------- --------------------------------------------------
       172      43424 HR                                              SQL*Net message from client                            8sjbnzfxs5w8x alter system kill session '172,43424' immediate;

blocking 중이던 세션을 kill 시키면 HR 1세션의 delete 작업 수행됨

Lock Mode

Null(N), 1

모든 모드의 lock과 호환 가능

Row Share(RS), Sub Share(SS), 2

lock이 걸린 테이블에 동시 액세스를 허용하지만 다른 세션이 exclusive mode로 lock을 거는 것을 금지
lock table hr.emp in row share mode;

# HR 1 : RS 모드로 tm lock
HR@ora19c> lock table hr.emp in row share mode;

Table(s) Locked.

# SYS
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;  2    3    4    5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       158 TM      75323          0          2          0         11          0 1                9787          0

# HR 2 : 조회 및 dml 가능
HR@ora19c> select salary from hr.emp where employee_id = 100;

    SALARY
----------
     20000

HR@ora19c> delete from hr.emp where employee_id = 100;

1 row deleted.

# SYS
SYS@ora19c> /

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       139 TM      75323          0          3          0          3          0 1                9787          0
       139 TX     262152       2100          6          0          3          0 4                   8       2100
       158 TM      75323          0          2          0        260          0 1                9787          0

Row Exclusive(RX), Sub Exclusive(SX), 3

Row Share와 동일하지만, Share 모드에서도 lock을 금지
lock table hr.emp in row exclusive mode;

Share(S), 4

동시 쿼리는 허용하지만, Lock이 걸린 테이블에 대한 변경은 금지
테이블에 인덱스를 생성하려면 share lock이 필요하며 자동으로 요청
primary key, unique 충돌 시 발생
lock table hr.emp in share mode;

Share Row Exclusive(SRX), Share Sub Exclusive(SSX), 5

테이블 쿼리하는데 사용되며 다른 세션에서 같은 테이블 행을 쿼리하는 것은 허용하지만 해당 테이블 share mode lock하거나 행을 갱신하는 것은 금지
row cache lock(sequence nocache 속성에서 nextval 호출할 때마다 dictionary 테이블 변경 작업 시)
lock table hr.emp in share row exclusive mode;

Exclusive(X), 6

lock 테이블에 대해서 쿼리는 허용하지만 해당 테이블에서 다른 작업은 금지(DDL)
같은 행에 대해서 동시에 변경할 수 없도록 발생하는 LOCK
lock table hr.emp in exclusive mode;

특정 컬럼에 primary key, unique key에 해당하는 데이터를 입력, 수정하는 경우

enq: TX - row lock contention

테이블 및 인덱스 생성

create table hr.unique_test(id number) tablespace users;

create unique index hr.unique_test_idx on hr.unique_test(id);

SYS@ora19c> select index_name, uniqueness from dba_indexes where table_name = 'UNIQUE_TEST';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
UNIQUE_TEST_IDX                UNIQUE

HR1

HR@ora19c> insert into hr.unique_test(id) values(1);

1 row created.

HR@ora19c> select * from hr.unique_test;

        ID
----------
         1

HR2

HR@ora19c> insert into hr.unique_test(id) values(1);
# 대기 중

Lock 조회

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;   2    3    4    5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       139 TX     196609       2564          0          4         16          0 3                   1       2564
       139 TM      75326          0          3          0         16          0 1                9790          0
       139 TX      65564       2133          6          0         16          0 1                  28       2133
       171 TX     196609       2564          6          0        160          1 3                   1       2564
       171 TM      75326          0          3          0        160          0 1                9790          0

인덱스 때문에 TX를 Share(4)모드로 요청 중
아직 HR 1 세션이 insert 작업을 끝내지 않았기 때문에 rollback될 경우에는 작업 수행 가능 → HR1 세션이 트랜잭션을 끝낼 때까지 대기

blocking 세션 및 sql 조회

# blocking 된 세션
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id from v$session where event like '%TX%';

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       139      23294 HR                                          171 enq: TX - row lock contention            cuz65hgum0qns 6k7r72uujnxvr

# blocking 된 SQL
SYS@ora19c> select sql_text from v$sql where sql_id = 'cuz65hgum0qns';

SQL_TEXT
----------------------------------------------------------------------------------------------------
insert into hr.unique_test(id) values(1)

# blocking 중인 세션
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where sid in (select blocking_session from v$session where event like '%TX%');  2    3

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       171      15427 HR                                              SQL*Net message from client                        6k7r72uujnxvr

# blocking 중인 SQL
SYS@ora19c> select sql_text from v$sql where sql_id = '6k7r72uujnxvr';

SQL_TEXT
----------------------------------------------------------------------------------------------------
select * from hr.unique_test

HR 1 commit 수행 시 HR2 오류 발생

HR@ora19c> commit;

Commit complete.

HR@ora19c> insert into hr.unique_test(id) values(1);
insert into hr.unique_test(id) values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.UNIQUE_TEST_IDX) violated

HR1 세션 트랜잭션에 의해 1번 데이터가 입력되었고, 유니크 인덱스 제약 조건 때문에 HR2 세션의 1번 데이터 입력은 실패함

select 중인 테이블에 대해서 다른 세션에서 drop table 수행됨

대용량 테이블 생성

SYS@ora19c> drop table hr.emp purge;

Table dropped.

SYS@ora19c> create table hr.emp
nologging
tablespace users
as
select rownum  emp_id, last_name, first_name, salary, department_id
from hr.employees, (select rownum emp_id from dual connect by level < = 100000)
order by dbms_random.value;  2    3    4    5    6    7

Table created.

SYS@ora19c> select blocks, bytes/1024/1024 mb from dba_segments where segment_name = 'EMP';

    BLOCKS         MB
---------- ----------
     46720        365
         8      .0625

SYS@ora19c> select blocks, bytes/1024/1024 mb from dba_segments where owner = 'HR' and segment_name = 'EMP';

    BLOCKS         MB
---------- ----------
     46720        365

HR1 : select문 수행

HR@ora19c> select * from hr.emp;
...
   5229840 King                      Steven                    20000            90
   7043195 Mikkilineni               Irene                      2700            50
    334309 Patel                     Joshua                     2500            50
  10510476 Johnson                   Charles                    6200            80
   8244350 Gietz                     William                    8300           110
   3278212 Hall                      Peter                      9000            80
ERROR:
ORA-08103: object no longer exists

TM : NULL(1)

HR2 : drop table 수행

HR@ora19c> drop table hr.emp purge;

Table dropped.

TM : Exclusive(6)

select for udpate

대용량 테이블 생성

SYS@ora19c> create table hr.emp
nologging
tablespace users
as
select rownum  emp_id, last_name, first_name, salary, department_id
from hr.employees, (select rownum emp_id from dual connect by level < = 100000)
order by dbms_random.value;  2    3    4    5    6    7

Table created.

HR 1 : 기본 select

HR@ora19c> select * from hr.emp where emp_id = 100;

    EMP_ID LAST_NAME                 FIRST_NAME               SALARY DEPARTMENT_ID
---------- ------------------------- -------------------- ---------- -------------
       100 Grant                     Douglas                    2600            50

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;   4    5

no rows selected

lock 발생 X

HR 1 : select for update

HR@ora19c> select * from hr.emp where emp_id = 100 for update;

    EMP_ID LAST_NAME                 FIRST_NAME               SALARY DEPARTMENT_ID
---------- ------------------------- -------------------- ---------- -------------
       100 Grant                     Douglas                    2600            50

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;   2    3    4    5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       171 TX     327710       2598          6          0          7          0 5                  30       2598
       171 TM      75338          0          3          0          7          0 1                9802          0

TM : Row Exclusive(3)
TX : Exclusive(6)

HR 2 : update

HR@ora19c> update hr.emp set salary = salary * 1.1 where emp_id = 100;

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;  2    3    4    5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       139 TM      75338          0          3          0          8          0 1                9802          0
       139 TX     327710       2598          0          6          8          0 5                  30       2598
       171 TX     327710       2598          6          0        102          1 5                  30       2598
       171 TM      75338          0          3          0        102          0 1                9802          0

TM : Row Exclusive(3) → RX 끼리는 호환 가능하므로 획득함
TX : Exclusive(6) 요청 중

blocking 세션 및 sql 조회

# blocking 된 세션
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id from v$session where event like '%TX%';

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       139      23294 HR                                          171 enq: TX - row lock contention            afm91tjsmrqsn c84ynr1u8hbs2

# blocking 된 SQL
SYS@ora19c> select sql_text from v$sql where sql_id = 'afm91tjsmrqsn';

SQL_TEXT
----------------------------------------------------------------------------------------------------
update hr.emp set salary = salary * 1.1 where emp_id = 100

# blocking 중인 세션
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where sid in (select blocking_session from v$session where event like '%TX%');  2    3

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       171      15427 HR                                              SQL*Net message from client                        7w5ga3044ckm7

# blocking 중인 SQL
SYS@ora19c> select sql_text from v$sql where sql_id = '7w5ga3044ckm7';

SQL_TEXT
----------------------------------------------------------------------------------------------------
select * from hr.emp where emp_id = 100 for update

HR 1 : select for update

HR@ora19c> rollback;

Rollback complete.

HR@ora19c> select * from hr.emp where emp_id = 100 for update;

    EMP_ID LAST_NAME                 FIRST_NAME               SALARY DEPARTMENT_ID
---------- ------------------------- -------------------- ---------- -------------
       100 Grant                     Douglas                    2600            50

HR 2 : select for update

HR@ora19c> rollback;

Rollback complete.

HR@ora19c> select * from hr.emp where emp_id = 100 for update;
# 대기 중

for udpate는 wait 옵션이 기본 값
wait : 이미 lock이 걸려 있으면 waiting 단계로 빠지는 옵션

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;   3    4    5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       139 TM      75338          0          3          0         62          0 1                9802          0
       139 TX     131077       2539          0          6         62          0 2                   5       2539
       171 TX     131077       2539          6          0         62          1 2                   5       2539
       171 TM      75338          0          3          0         62          0 1                9802          0

HR 1 롤백 시 HR 2 결과 출력됨

HR@ora19c> rollback;

Rollback complete.

    EMP_ID LAST_NAME                 FIRST_NAME               SALARY DEPARTMENT_ID
---------- ------------------------- -------------------- ---------- -------------
       100 Grant                     Douglas                    2600            50

HR 2 세션이 lock 점유 중

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;   2    3    4    5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       139 TX     196638       2569          6          0         38          0 3                  30       2569
       139 TM      75338          0          3          0        119          0 1                9802          0

HR 1 : select for update nowait

HR@ora19c> select * from hr.emp where emp_id = 100 for update nowait;
select * from hr.emp where emp_id = 100 for update nowait
                 *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

nowait : 대상 행에서 이미 lock이 걸려 있으면 오류 발생해서 waiting 단계로 빠지지 않도록 하는 옵션

HR 1 : select for update wait n

HR@ora19c> select * from hr.emp where emp_id = 100 for update wait 5;
select * from hr.emp where emp_id = 100 for update wait 5
                 *
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired

wait n : n초동안 대기한 후 그 사이에 lock 풀리면 다시 수행하고 n초가 되었지만 lock이 거려있으면 오류 발생해서 waiting 단계로 빠지지 않도록 하는 옵션

인덱스 생성 중에 delete 수행하는 경우

enq: TM - contention 대기 이벤트 발생

HR 1 : create index

create index hr.emp_name_idx on hr.emp(last_name, first_name) tablespace users;

HR 2 : delete

delete from hr.emp where emp_id = 100;
# 대기하다가 인덱스 생성이 끝나면 삭제

Lock 조회

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;   2    3    4    5

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       139 TM      75338          0          0          3          6          0 1                9802          0
       171 TX     524319       2575          6          0         20          0 8                  31       2575
       171 TM      75338          0          4          0         20          1 1                9802          0
       171 TM         18          0          3          0         20          0 0                  18          0

ID1 = 18인 object는 obj$
인덱스 객체 정보를 입력하기 위해 obj$ 딕셔너리 테이블에 대해 TM(RX) Lock이 걸림
인덱스를 생성하는 대상 테이블에 대해 TM(S) Lock을 사용하는 중에 대상 테이블에 대해서 DML 작업을 수행하기 위해 테이블에 TM(RX) Lock을 걸려고 하지만 호환이 안되기 때문에 기다려야 함

blocking된 세션 조회

SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id from v$session where event like '%TM%';

       SID    SERIAL# USERNAME                       BLOCKING_SESSION EVENT                                    SQL_ID     PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------- ------------- -------------
       171      20513 HR                                          139 enq: TM - contention                     6zq3b95sw9agv 6zq3b95sw9agv

enq: TM - contention 대기 이벤트 발생

blocking 풀린 후 lock 조회

SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK USN              SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       139 TX     393249       2565          6          0         44          0 6                  33       2565
       139 TM      75338          0          3          0         45          0 1                9802          0

인덱스가 생성된 후 DML 작업이 TM(RX) Lock과 TX(X) Lock을 획득

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

[아이티윌 오라클 DBA 과정 91기] 260130 TIL (0)	2026.01.30
[아이티윌 오라클 DBA 과정 91기] 260129 TIL (0)	2026.01.30
[아이티윌 오라클 DBA 과정 91기] 260127 TIL (0)	2026.01.29
[아이티윌 오라클 DBA 과정 91기] 260126 TIL (0)	2026.01.29
[아이티윌 오라클 DBA 과정 91기] 260122 TIL (0)	2026.01.29

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

Data Buffer Cache(Cont.)

Index

Buffer pinning

Root Block이 변경되는 경우

DML 시 Data Buffer Cache 탐색 과정

Lock

동일한 행에 대해 DML 수행 시 lock 충돌

HR 1 : 테이블 생성 후 변경 작업

HR 2 : 테이블 drop → 실패

트랜잭션 조회

lock 조회

HR3

세션 모니터링

lock이 걸린 세션 및 sql문 조회

blocking 중인 세션 및 sql문 조회

lock이 발생한 rowid 조회

HR 1 Rollback

Lock 조회 : HR 3 세션 TX lock ID1 변경됨

HR1 : delete 작업

blocking 중인 세션 정보와 kill 명령어 함께 출력

Lock Mode

Null(N), 1

Row Share(RS), Sub Share(SS), 2

Row Exclusive(RX), Sub Exclusive(SX), 3

Share(S), 4

Share Row Exclusive(SRX), Share Sub Exclusive(SSX), 5

Exclusive(X), 6

특정 컬럼에 primary key, unique key에 해당하는 데이터를 입력, 수정하는 경우

테이블 및 인덱스 생성

HR1

HR2

Lock 조회

blocking 세션 및 sql 조회

HR 1 commit 수행 시 HR2 오류 발생

select 중인 테이블에 대해서 다른 세션에서 drop table 수행됨

대용량 테이블 생성

HR1 : select문 수행

HR2 : drop table 수행

select for udpate

대용량 테이블 생성

HR 1 : 기본 select

HR 1 : select for update

HR 2 : update

blocking 세션 및 sql 조회

HR 1 : select for update

HR 2 : select for update

HR 1 롤백 시 HR 2 결과 출력됨

HR 2 세션이 lock 점유 중

HR 1 : select for update nowait

HR 1 : select for update wait n

인덱스 생성 중에 delete 수행하는 경우

HR 1 : create index

HR 2 : delete

Lock 조회

blocking된 세션 조회

blocking 풀린 후 lock 조회

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바