[Oracle DBA]데이터 동시성 관리

데이터 동시성 관리

Lock

• 여러 세션이 동시에 같은 데이터를 변경할 수 없도록 하는 장치
• row level
• X
• table level
• RX
• Mode
• 0 : NONE
• 2 : RS
• 3 : RX
• 4 : S
• 5 : SRX
• 6 : X
• RS(select)  :  RS, RX, S, SRX : select for update => table lock
• RX(insert, update, delete) : RS, RX
• S : RS, S
• SRX : RS
• X

#terminal 1

SCOTT@orcl2> lock table emp in exclusive mode;

Table(s) Locked.

-- 테이블에 아무런 lock이 걸려있지 않으므로 테이블 전체에 lock이 된다.

-- 아주 강한 lock이다. (나혼자 작업)

-- X lock

#terminal 2

SCOTT@orcl2> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        880                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1584        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1375        500         30

      7566 JONES      MANAGER         7839 02-APR-81     3272.5                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1375       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       3135                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2695                    10

      7788 SCOTT      ANALYST         7566 19-APR-87        500                    20

      7839 KING       PRESIDENT            17-NOV-81       5500                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1650          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1210                    20

      7900 JAMES      CLERK           7698 03-DEC-81       2000                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3300                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1430                    10

14 rows selected.

SCOTT@orcl2> update emp set sal = 0;

Wating......

SCOTT@orcl2> select * from emp for update;

Wating......

-- 모든 세션 재접속

#terminal 1

SCOTT@orcl2> lock table emp in share mode;

Table(s) Locked.

-- S lock

#terminal 2

SCOTT@orcl2> lock table emp in share mode;

Table(s) Locked.

-- lock이 걸린다

#terminal 1

SCOTT@orcl2> update emp set sal=1000 where empno=7788;

Wating......

-- S lock은 테이블 전체를 share하겠다는 것이므로, x가 허용되지 않는다.

#terminal 2

• table lock
• RS  : Select ... where empno = 7788 for update    
•  session A : 7788 행에 lock 을 걸면 table에는 RS 락이 걸린다.
•  session B : 7900 행에 lock 을 걸면 table 에 RS lock 이 걸린다.
• RX : DML을 하면 자동으로 테이블에 걸리는 락
•  session A: update emp 1번 행   
• 1번 행에는 exclusive row lock,  table에는 RX 락이 걸린다.
• session B : update emp 2번 행
• 2번 행에는 exclusive row lock,  table에는 RX 락이 걸린다.
• 만약 session C : drop table emp 한다면 ?
• DML Lock과 DDL Lock이 따로 존재하는데,  DML은 enqueue에 들어가는데, DDL은 nowait상태이기 때문에 실패하게 된다.

#terminal 1

SCOTT@orcl2> update emp set sal=1000 where empno=7788;

1 row updated.

#terminal 2

SCOTT@orcl2> update emp set sal=2000 where empno=7900;

1 row updated.

#termninal 3

SYS@orcl2> drop table scott.emp;

drop table scott.emp

                 *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

-- DDL drop을 하려면 테이블 전체를 lock을 해제 해야하는데, 드랍하려는 세션이 RX이 걸려있으면 즉시 실패하게 된다.

SYS@orcl2> select username, osuser,s.sid, serial#, s.machine

           from v$session s;

#terminal 1

SCOTT@orcl2> update emp set sal = 1000 where deptno=20;

5 rows updated.

-- 현재 행에대한 베타적 Xlock을 걸었다.

#terminal 2

SCOTT@orcl2> select * from emp where job = 'CLERK' for update;

Wating......

SCOTT@orcl2> select * from emp where job = 'CLERK' for update wait;

-- lock이 해제될때까지 대기

SCOTT@orcl2> select * from emp where job = 'CLERK' for update wait 5;

-- 5초 기다리고 실행

lock  대기의 원인과 조치

• rowid
• oracle7
• Restricted ROWID (제한 rowid) 6 bytes
• oracle8
• Extended ROWID (확장 rowid) 10 bytes
• Extend ROWID = presentation format 18자
• 000000FFFBBBBBBRRR
• 000000: object id
• FFF : relative file no (tablespace의 상대적 파일번호)
• 000000FFF : Absolute File number
• BBBBBB : block number
• RRR : 그 블럭내에서 row의 id

현재 두개의 터미널에서 SCOTT계정으로 접속 후에

===================================================================================================#terminal1

ALLEN@orcl2> update emp

             set sal= sal * 1;

14 row updated.

===================================================================================================

===================================================================================================

#terminal2

SCOTT@orcl2> update emp

             set sal= sal * 1

             where empno = 7788;

wating...

===================================================================================================

의 명령어를 실행 시키면, 한개의 터미널은

한개의 터미널은

트랜잭션이 끝나는 것을 기다리는 상태가 된다.

-- Waiting session 확인

SYS@orcl2> SELECT SID, SERIAL#, USERNAME, BLOCKING_SESSION

           FROM V$SESSION

           WHERE BLOCKING_SESSION IS NOT NULL;

       SID    SERIAL# USERNAME                       BLOCKING_SESSION

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

        19         145 SCOTT                                        27

-- BLOCKING_SESSION이 NULL이 아닌 것이 Waiting session이다.

-- Blocking session 확인

SYS@orcl2> SELECT SID, SERIAL#, USERNAME

           FROM V$SESSION

           WHERE SID IN (SELECT BLOCKING_SESSION

                         FROM V$SESSION

                         WHERE BLOCKING_SESSION IS NOT NULL);

       SID    SERIAL# USERNAME

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

        27        91 SCOTT

SYS@orcl2> select *

           from v$session

           where sid in(27, 145);

-- 자세하게 볼 수 있다.

-- lock된 object를 확인

SYS@orcl2> SELECT session_id, object_id, oracle_username, locked_mode

           FROM v$locked_object ;

SESSION_ID  OBJECT_ID ORACLE_USERNAME                LOCKED_MODE

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

        19      73181 ALLEN                                    3

        27      73181 SCOTT                                    3

SYS@orcl2> SELECT session_id, owner, name, mode_held

                  FROM dba_dml_locks ;

SESSION_ID OWNER                          NAME                           MODE_HELD

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

        27 SCOTT                          EMP                            Row-X (SX)

        19 SCOTT                          EMP                            Row-X (SX)

SYS@orcl2> SELECT sid, serial#, username, blocking_session, event, seconds_in_wait

           FROM v$session

           WHERE username in ('SCOTT','ALLEN') ;

SID SERIAL# USERNAME BLOCKING_SESSION    EVENT                           SECONDS_IN_WAIT

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

19     145 ALLEN           27            enq: TX - row lock contention   298

27      91 SCOTT                         SQL*Net message from client     1431

SYS@orcl2> SELECT username, row_wait_obj#, row_wait_file#, row_wait_block#,

                          row_wait_row#, sql_id

        FROM v$session

        WHERE blocking_session IS NOT NULL ;

USERNAME                  ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# SQL_ID

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

ALLEN                             73181              4             151             1 8fnkr5kmx5xmv

OWNER OBJECT_NAME

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

SCOTT EMP

SYS@orcl2> SELECT empno, ename, sal

        FROM scott.emp

        WHERE rowid = DBMS_ROWID.ROWID_CREATE(1,73181,4,151,8) ;

     EMPNO ENAME             SAL

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

      7839 KING             5500

SYS@orcl2> SELECT sql_text

FROM v$sql

WHERE sql_id = '8fnkr5kmx5xmv' ;

SQL_TEXT

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

update emp set sal = sal *1

SYS@orcl2> alter system kill session '27, 145' immediate;

System altered.

-- 세션을 강제로 종료시키면, 트랜잭션이 끝나는 것을 기다리는 터미널은 업데이트가 완료되고, 원래 트랜잭션이 실행되고 있던 터미널은 연결이 끊킨다.

-- 상대적 파일번호를 확인

SYS@orcl2> select tablespace_name, file_id, relative_fno, file_name

           from dba_data_files;

TABLESPACE_NAME              FILE_ID RELATIVE_FNO FILE_NAME

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

USERS                              4            4 /u01/app/oracle/oradata/orcl2/users01.dbf

UNDOTBS1                           3            3 /u01/app/oracle/oradata/orcl2/undotbs01.dbf

SYSAUX                             2            2 /u01/app/oracle/oradata/orcl2/sysaux01.dbf

SYSTEM                             1            1 /u01/app/oracle/oradata/orcl2/system01.dbf

EXAMPLE                            5            5 /u01/app/oracle/oradata/orcl2/example01.dbf

SYS@orcl2> select decode(status,'INACTIVE',username || ' ' || sid || ',' || serial#,'lock') as Holder, decode(status,'ACTIVE',  username || ' ' || sid || ',' || serial#,'lock') as waiter, sid, serial#, status

    from( select level as le, NVL(s.username,'(oracle)') AS username,

    s.osuser,

    s.sid,

    s.serial#,

    s.lockwait,

    s.module,

    s.machine,

    s.status,

    s.program,

    to_char(s.logon_TIME, 'DD-MON-YYYY HH24:MI:SS') as logon_time

       from v$session s

      where level>1

                or EXISTS( select 1

    from v$session

    where blocking_session = s.sid)

      CONNECT by PRIOR s.sid = s.blocking_session

  START WITH s.blocking_session is null );

-- 현재 lock 정보를 보여준다.

HOLDER                    WAITER                           SID    SERIAL# STATUS

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

SCOTT 27,91               lock                              27         91 INACTIVE

lock                      ALLEN 19,145                      19        145 ACTIVE

• lock session 들의 정보를 blocker, waiter로 graphical 하게 보여주는 utility
• $ORACLE_HOME/rdbms/admin/utllockt.sql

Dead Lock

• 서로가 서로를 기다리는 교착 상태.
• ora-60 deadlock detected
• v$diag_info
• 현재 세션의 server process의 trace file의 위치와 이름 정보
• user_dump_dest
• deadlock 이 발생하지 않도록 하려면 일괄 작업(batch) 을 수행할 때 작업하는 행을 같은 순서로 처리한다.
• show parameter user_dump_dest : user 가 발생시키는 trace file의 위치
• dead lock
•  sql_trace 를 만들었을 때
• alter session set sql_trace = true;
• select...
• alter session set sql_trace = false;

SCOTT@orcl2> select empno, sal

             from emp

             where empno in (7788, 7900)

SCOTT@orcl2> save sel

#terminal 1

SCOTT@orcl2> @sel

SCOTT@orcl2> select name, value from v$diag_info

             where name like 'Default%';

NAME                      VALUE

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

Default Trace File        /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_21702.trc

SCOTT@orcl2> update emp set sal = 2000 where empno=7788;

1 row updated.

#terminal 2

SCOTT@orcl2> update emp set sal = 5000 where empno = 7900;

1 row updated.

#terminal 1

SCOTT@orcl2> update emp set sal = 500 where empno = 7900;

Wating......

#terminal 2

SCOTT@orcl2> update emp set sal = 500 where empno = 7788;

Wating......

#terminal 1

update emp set sal = 500 where empno = 7900

       *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

-- 락이 해제가된다.

SCOTT@orcl2> !vi /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_21702.trc

/deadlock

.............

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-000a000f-00000277        30      19     X             33     142           X

TX-0008001f-0000041d        33     142     X             30      19           X

session 19: DID 0001-001E-000000E3      session 142: DID 0001-0021-000001A5

session 142: DID 0001-0021-000001A5     session 19: DID 0001-001E-000000E3

Rows waited on:

  Session 19: obj - rowid = 00011DDD - AAAR3dAAEAAAACXAAL

  (dictionary objn - 73181, file - 4, block - 151, slot - 11)

.............

#terminal 3

SYS@orcl2> select owner, object_name from dba_objects where object_id = 73181

OWNER       OBJECT_NAME

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

SCOTT       EMP

SYS@orcl2>

<<<<복습>>>>

ALLEN 세션에서  EMP 테이블의 사원 'ALLEN'의 급여를 10퍼센트 인상하고 SCOTT 세션에서 'ALLEN'의 급여를 10% 인하하세요.

# Terminal 1

SYS@orcl2> grant update on scott.emp to allen;

ALLEN@orcl2> update emp set sal = sal * 1.1;

14 rows updated.

# Terminal 2

SCOTT@orcl2> update emp set sal = sal*0.9 where ename='ALLEN';

waiting..

반응 없음(ALLEN 계정에서 해당 테이블의 트랜잭션이 실행중이다)

-- BLOCING SESSION 조치 후

1 row updated.

위 상황을 DBA가 모니터링하고  BLOCKING SESSION 을 찾아 조치하세요.

# Terminal 3

-- Wating session 확인

SYS@orcl2> SELECT SID, SERIAL#, USERNAME, BLOCKING_SESSION

           FROM V$SESSION

           WHERE BLOCKING_SESSION IS NOT NULL;

       SID    SERIAL# USERNAME                       BLOCKING_SESSION

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

       136         95 SCOTT                                        17

-- Blocking session 확인

SYS@orcl2> SELECT SID, SERIAL#, USERNAME

           FROM V$SESSION

           WHERE SID IN (SELECT BLOCKING_SESSION

                         FROM V$SESSION

                         WHERE BLOCKING_SESSION IS NOT NULL);

       SID    SERIAL# USERNAME

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

        17         60 ALLEN

SYS@orcl2> alter system kill session '17, 60' immediate;

System altered.

그와 동시에 allen 과 scott.emp테이블의 트랜잭션이 강제로 종료되므로, 위의 scott의 update가 실행된다.

SCOTT@orcl2> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        880                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1584        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1375        500         30

      7566 JONES      MANAGER         7839 02-APR-81     3272.5                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1375       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       3135                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2695                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3300                    20

      7839 KING       PRESIDENT            17-NOV-81       5500                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1650          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1210                    20

      7900 JAMES      CLERK           7698 03-DEC-81       1045                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3300                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1430                    10

14 rows selected.

1.lock 경합 상태를 만든 후 다음 정보를 확인하시오.

• SCOTT : update emp set sal = 1000 where deptno = 20;
• ALLEN : update emp set sal = 2000 where empno=7788;

#terminal 1

SCOTT@orcl2> update emp set sal = 1000 where deptno = 20;

5 rows updated.

#terminal 2

ALLEN@orcl2> update emp set sal = 2000 where empno=7788;

Wating......

2.wating session과 blocking session 정보

#terminal 3

-- Wating session 확인

SYS@orcl2> SELECT SID, SERIAL#, USERNAME, BLOCKING_SESSION

           FROM V$SESSION

           WHERE BLOCKING_SESSION IS NOT NULL;

       SID    SERIAL# USERNAME                       BLOCKING_SESSION

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

        17          9 ALLEN                                        15

-- Blocking session 확인

SYS@orcl2> SELECT SID, SERIAL#, USERNAME

           FROM V$SESSION

           WHERE SID IN (SELECT BLOCKING_SESSION

                         FROM V$SESSION

                         WHERE BLOCKING_SESSION IS NOT NULL);

       SID    SERIAL# USERNAME

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

        15         23 SCOTT

--lock의 정보를 확인 할 수 있다.

SYS@orcl2> @$ORACLE_HOME/rdbms/adming/utllockt.sql

3.lock 경합을 일으킨 행을 찾으시오.

SYS@orcl2> SELECT username, sql_id

           FROM v$session

           WHERE blocking_session IS NOT NULL

USERNAME  ROW_WAIT_OBJ#    ROW_WAIT_FILE#    ROW_WAIT_BLOCK#    ROW_WAIT_ROW#          SQL_ID    

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

ALLEN      73181            4                 151                7              5h5wamkz2h7yv

SYS@orcl2> SELECT *

        FROM scott.emp

        WHERE rowid = DBMS_ROWID.ROWID_CREATE(1,73181,4,151,7) ;

     EMPNO ENAME             SAL

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

      7788 SCOTT            1000

SYS@orcl2> select sid, type, id1, id2, lmode, request, ctime

           from v$lock

           where sid in (15,23)

           and type <> 'AE'

           order by 1,2;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME

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

        15 TM      73181          0          3          0         51

        15 TX     655366        647          6          0         51

-- lock의 목록을 확인

-- TM : 테이블에 걸린 lock

-- TX : 행에 걸린 lock

-- LMODE : lock의 모드

SYS@orcl2>

select * from scott.emp

where rowid = (select dbms_rowid.rowid_create(1, obj, file_id, block_id, row_id)

                          from ( select row_wait_obj# obj, 

                                               row_wait_file# file_id, 

                        row_wait_block# block_id, 

                        row_wait_row# row_id

                   from v$session

                   where blocking_session IS NOT NULL));

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7788 SCOTT      ANALYST         7566 19-APR-87       1000                    20

4.현재 waiting 하고 있는 session의 SQL문을 찾으시오

SYS@orcl2> SELECT sql_text

           FROM v$sql

           WHERE sql_id = (SELECT sql_id

                           FROM v$session

                           WHERE blocking_session IS NOT NULL) ;

SQL_TEXT

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

update emp set sal = 2000 where empno=7788

5.lock 경합을 해결하는 조치를 취하시오

SYS@orcl2> alter system kill session '15, 23' immediate;

System altered.

#terminal 2

ALLEN@orcl2> update emp set sal = 2000 where empno=7788;

1 row updated.

-- 완료됨