데이터 동시성 관리
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.
-- 완료됨
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]데이터베이스 유지 관리 (0) | 2019.04.08 |
|---|---|
| 복습문제 (0) | 2019.04.05 |
| [Oracle DBA]유저 보안 관리 (0) | 2019.04.01 |
| [Oracle DBA]데이터베이스 저장 영역 구조 관리 (0) | 2019.03.28 |
| [Oracle DBA]Oracle 네트워크 관리 (0) | 2019.03.27 |