* lock
---------------------
*lock 란 ?
- db환경에서 concumency, consistency, 무결성 유지하기 위한 기능
* 공유 lock , 배타적 lock
1) 공유 Lock
- 공유 Lock는 데이터를 읽고자 할 때 사용
- 다른 공유 Lock 와 호환되지만 배타적 Lock와 호환되지 않음
2) 배타적(Exclusive) Lock
- Exclusive Lock는 데이터를 변경하고자 할때 사용됨. 트랜젝션이 완료 될때까지 유지
- 해당 Lock 해제 될 떄까지 다른 트랜 젝션은 해당 Resource 접근할 수 없음.
* 블로킹, 교착상태
1) 블로킹
- Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춘 상태
- 공유 Lock, 배타적 Lock 함게 설정될 수 없으므로 Blocking
- Commit, Rollback 해제 가능
- query
-> for update wait 3 3초 대기 후 Exception을 던짐
2) 교착 상태
- 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스 하려고 마주보며 진행하는 상황.
=> 오라클은 Transaction을 Rollback 하고 Alert파일에 기록함
* 오라클 Lock
Lock
------ User lock (UL)
| | _______ DML lock
| | - TX
| | - TM
| | - RX (Row Exclusive)
| | - RS (Row Share)
| | - S (Share)
| | - SRX (Share Row Exclusive)
| |
| |_______ DDL lock
| - Exclusive
| - Share
| - Breaktable parse
|
|----- System lock
- ST
- HW
- .... etc
* lock 종류 ?
1) 래치(Latch) : SGA에 공유돼 있는 갖가지 자료구조를 보호할 목적으로 사용되는 lock
2) 버퍼 lock : 버퍼 블록에 대한 액세스 직렬화
3) 라이브러리 캐시 lock : 라이브러리 캐시 오브젝트에 대한 핸들을 보호
4) 라이브러리 캐시 pin : 라이브래시 캐시 오브젝트를 실제 내용이 담긴 힙(Heap) 보호
5) DML 테이블 lock : Enqueue lock 로 구현
6) DML 로우 lock : 로우 단위 lock 와 트랜젝션 lock(TX) 조합하여 구현
* 버퍼 lock
- 두개 이상의 프로세스가 동시에 버퍼내용을 일고 쓸때
- 이를 막기 위해 캐시된 버퍼 블록을 읽거나 버퍼 헤더로부터 버퍼 Lock 를 획득
- 버퍼 Lock 를 획득 하면 래치 Lock 은 풀림
=> 블록을 읽고 씀 ( 버퍼 Lock 획득 후 )
- 버퍼 Lock 모드
1) Shared 모드 : 버퍼를 읽음
-> 블록 클린 아웃을 필요로 하는 select 문일 경우
-> Exclusive 모드로 Lock 설정
* 블록 클린 아웃
트랜잭션에 의해 설정된 로우 Lock 해제 -> 블록 헤더에 커밋 정보를 기록하는 오퍼레이션
2) Exclusive 모드 : 버퍼 내용 변경(10g 부터 wait event 로 등록됨. v$event_name)
해시 체인 래치 획득 후 버퍼를 찾았으나, 다른 프로세스가 버퍼 Lock을 Exclusive 점유했으면
(1) 버퍼 헤더의 버퍼 Lock Waiter List(대기자 목록) 에 자신을 등록하고 래치 Lock 해제
(2) Lock Waiter List(대기자 목록) 들어있는 동안 수면 상태(=buffer busy wait 대기 이벤트)
(3) 다른 프로세스의 버퍼 Lock 해제 되면 버퍼 Lock 획득 후 작업 진행
(4) 작업 완료 -> 래치 Lock 획득
=> 버퍼 헤더 엑세스 시 다른 프로세스와 충돌을 막기 위해
(5) 버퍼 Lock 해제 -> 래치 해제
* 로우 Lock
- Oracle 에서 로우 Lock는 항상 배타적
- Exclusive 모드
- 대체로 select 와 같은 읽는 과정은 어떠한 Lock 도 설정하지 않음
1) A 데이터에 대해 하나의 트랜잭션 갱신 중이여도 데이터를 읽을경우 잘됨
2) A 데이터에 대해 두개의 트랜잭션중 하나는 갱신, 하나는 읽는 중의 트랙잭션이여도 데이터 읽기 잘됨
-> 단 SELECT ... FOR UPDATE 구문 제외
3) A 데이터에 대해 두개의 트랜잭션이 모두 갱신 중이라면 기다림
- 공유 Lock 를 사용하지 않고 일관성을 유지할 수 있는 것은 UNDO 데이터를 이용한 다중 버전 동시성 제어 메커니즘을 사용하므로
* 테이블 Lock = TM Lock
- 로우 Lock + 테이블 Lock 얻음 ( 로우 Lock 얻은 후 테이블 Lock 를 얻음 .항상.)
=> 현재 트랜잭션이 갱신 중인 테이블에 대해 DDL 오퍼레이션을 방지
* insert, update, delete, merge 문을 위해 로우 Lock을 설정 하려면
해당 테이블에 RX(=SX) 모드 테이블 Lock을 먼저 획득해야 함
select for update 문을 위해 로우 Lock설정하려면 RS(=SS) 모드 테이블 Lock을 먼저 획득해야함.
테이블 Lock(TM) 종류
1) RS (Row Share)
- ROW Share Lock table에 lock을 걸려는 트랜잭션 테이블 안에 Lock된 row 가 있고
그 row를 변경 시키고자 하는 것을 말함
2) RX (ROW Exclusive Lock)
- Update ...; Insert into ...; Delete from ...;, Lock ...In Row Exclusive Mode; 명령어에 의해 걸리는 Lock
- RX Lock도 RS Lock 와 비슷
- S, SRX, X LOCK 걸수 없음.
3) S (Share Lock)
- Lock ... In Share Mode; 에 의한 테이블에 걸린 Lock.
- S Lock은 같은 테이블에 대해서 RS, S Lock 만 가능
RX, SRX, X Lock 걸수 없음
4) SRX (Share Row Exclusive)
- Lock Table... In Share Row Exclusive Mode; 에 의한 테이블에 걸린 Lock.
- SRX Lock 같은 테이블에 대해서 RS Lock 만 가능
RX, SRX, S, X Lock 걸수 없음.
5) X (Exclusive)
- Lock Table ... In Exclusive Mode; 에 의한 테이블 Lock.
- X Lock 같은 테이블에는 어떠한 Lock 걸수 없음.
- Alter table, Drop table 에 의해 걸리는 Lock.
* 명시적인 테이블 Lock
lock table emp in row share mode
lock table emp in row exclusive mode
lock table emp in share mode
lock table emp in share row exclusive mode
lock table emp in exclusive mode
* 조회
select *
from v$lock;
select *
from v$locked_object
select *
from v$session_wait
Null (N) ? Resource 에 대한 변경이 있을 경우 lock Holder에게 변경사실 전달
Sub-Shared(RS/SS) ? 리소스의 일부분에 대한 Shared lock이 필요한 경우
Sub-Exclusive(RX/SX) ? 리소스 일부분에 대한 Exclusive lock이 필요한 경우
Shared(S) ? 리소스에 대한 변경을 막는 경우
Shared-Sub-Exclusive(SRX/SSX) - 리소스의 일부분에 대해서는 exclusive lock이 리소스 전체에 대해서는 Shared lock이 필요한 경우
Exclusive(X) ? 리소스에 대한 변경이 필요한 경우
#TX Lock 트랜잭션 LOCK
- Enqueue Lock 구현
- 첫 번째 변경을 시작할때 얻고, 커밋/롤백시 해제
- 트랜잭션 시작 -> Undo 세그먼트 헤더에 위치한 트랜잭션 테이블로부터 슬롯을 하나 할당 받음
- 다른 트랜잭션이 Consistent 버전을 얻으려고 트랜잭션 슬롯에 기록된 정보를 확인.
-> 필요시 CR 블록을 생성하여 읽음
=> 레코드가 갱신 중이라도 읽기 작업 시 블로킹이 없음을 유지
- 동시레 레코드 변경작업이 일어날 경우 TX(트랜잭션 Lock) 사용
* insert, update, delete, merge 문을 위해 로우 Lock을 설정 하려면
# lock test
1) row - level lock
session 1>
UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844;
session 2>
UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844;
=> waiting
col oracle_username format a15
col object_name format a10
col object_type format a10
col type format a4
col event format a25
col state format a10
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> select sw.SID, lo.oracle_username, do.object_name, do.object_type, l.type, lo.locked_mode, sw.event, sw.state
from v$lock l, v$session_wait sw, v$locked_object lo, DBA_OBJECTS DO
where l.sid = sw.sid
and l.sid = lo.session_id
AND l.ID1=do.object_id
and l.type ='TM';
SID ORACLE_USERNAME OBJECT_NAM OBJECT_TYP TYPE LOCKED_MODE EVENT STATE
---------- --------------- ---------- ---------- ---- ----------- ------------------------- ----------
148 SCOTT EMP TABLE TM 3 SQL*Net message from client WAITING
151 SCOTT EMP TABLE TM 3 enq: TX - row lock contention WAITING
session 1>
commit ;
session 2>
UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844;
=> 실행
session 1>
SELECT * FROM emp WHERE empno = 7844 FOR UPDATE;
-> select 문이지만 lock 발생
-> where 조건에 의해 변경되어질 row 를 미리 lock 을 걸어 놓는 것
session 2>
SELECT * FROM emp WHERE empno = 7844 FOR UPDATE;
SYS@ORCL AS SYSDBA> select sw.SID, lo.oracle_username, do.object_name, do.object_type, l.type, lo.locked_mode, sw.event, sw.state
from v$lock l, v$session_wait sw, v$locked_object lo, DBA_OBJECTS DO
where l.sid = sw.sid
and l.sid = lo.session_id
AND l.ID1=do.object_id
and l.type ='TM';
SID ORACLE_USERNAME OBJECT_NAM OBJECT_TYP TYPE LOCKED_MODE EVENT STATE
---------- --------------- ---------- ---------- ---- ----------- ------------------------- ----------
148 SCOTT EMP TABLE TM 3 SQL*Net message from client WAITING
151 SCOTT EMP TABLE TM 3 enq: TX - row lock contention WAITING
session 1>
ROLLBACK;
2) TABLE-LEVEL의 LOCK
lock table emp in row share mode
lock table emp in row exclusive mode
lock table emp in share mode
lock table emp in share row exclusive mode
lock table emp in exclusive mode
- 변경중인 데이터가 특정 행들이 아닌
테이블 전체인 경우에 테이블-레벨 락 걸림
=> DML 실행되었지만 where 조건절이 없는 경우.
- 오라클은 3가지를 제공함
1) S Lock
- 마스터-디테일 관계, 부모-자식 관계를 가진 2개의 테이블에서
부모 테이블이 변경될 때 자식 테이블에 발생하는 테이블 락
- LOCK TABLE [테이블명] IN SHARE MODE;
부모 - DEPT
자식 - DEPT_2
SCOTT@ORCL > SELECT table_name, constraint_name, constraint_type
FROM USER_CONSTRAINTS
WHERE TABLE_NAME in ('DEPT','DEPT_2');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
DEPT PK_DEPT P
DEPT_2 FK_DEPTNO_2 R
제약 조건에 위배 되므로
자식 DEPT.DPTPNO 값을 변경할 수 없음
FK의 제약조건을 꺼보고 실행.
*
ENABLE VALIDATE -> 새로 들어오는 테이터 뿐만 아니라 기존 데이터도 CONSTRAINT를 체크
ENABLE NOVALIDATE -> 새로 들어오는 데이터는 체크하지만, 기존 데이터는 CONSTRAINT체크하지 않음
DISABLE VALIDATE -> 새로 들어오는 데이터에 대해서 CONSTRAINT 체크 안함, 기존 데이터 CONSTRAINT 체크
DISABLE NOVALIDATE -> 새로 들어오는 데이터 뿐만 아니라 기존 데이터도 CONSTRAINT체크 안함
session 1>
alter table DEPT_2 modify constraints FK_DEPTNO_2 disable novalidate;
alter table DEPT modify constraints PK_DEPT disable novalidate;
update dept set deptno='50' where DNAME='OPERATIONS'
session 2>
update dept set deptno='60' where DNAME='OPERATIONS'
select sw.SID, lo.oracle_username, do.object_name, do.object_type, l.type, lo.locked_mode, sw.event, sw.state
from v$lock l, v$session_wait sw, v$locked_object lo, DBA_OBJECTS DO
where l.sid = sw.sid
and l.sid = lo.session_id
AND l.ID1=do.object_id
and l.type ='TM';
SID ORACLE_USERNAME OBJECT_NAM OBJECT_TYP TYPE LOCKED_MODE EVENT STATE
---------- --------------- ---------- ---------- ---- ------------------------------------ ----------
151 SCOTT DEPT TABLE TM 3 enq: TX - row lock contention WAITING
148 SCOTT DEPT TABLE TM 3 SQL*Net message from client WAITING
2) SRX LOCK
- S Lock 같은 경우에 발생
- 차이점
부모테이블이 생성될 시 자식테이블의 foreign-key 컬럼에 대한 부모 테이블 컬럼이 on delete cascade 절에 의해 생성시
+ foreign-key 컬럼에 인덱스가 생성되지 않을 경우
- LOCK TABLE [테이블명] IN SHARE ROW EXCLUSIVE MODE;
CREATE TABLE JUMP
(
JUMPNO NUMBER(2),
JNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT FK_JUMPNO PRIMARY KEY(JUMPNO)
);
CREATE TABLE JUMP_2
(
JUMPNO NUMBER(2),
JNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT FK_JUMPNO FOREIGN KEY(JUMPNO)
REFERENCES JUMP(JUMPNO)
ON DELETE CASCADE
);
INSERT INTO JUMP VALUES(1,'ABC','ZZZ');
INSERT INTO JUMP VALUES(2,'BCD','XXX');
INSERT INTO JUMP VALUES(3,'DEF','CCC');
INSERT INTO JUMP_2 VALUES(1,'ABC','ZZZ');
INSERT INTO JUMP_2 VALUES(2,'BCD','XXX');
INSERT INTO JUMP_2 VALUES(3,'DEF','CCC');
SELECT table_name, constraint_name, constraint_type
FROM USER_CONSTRAINTS
WHERE TABLE_NAME in ('JUMP','JUMP_2');
SCOTT@ORCL > SELECT table_name, constraint_name, constraint_type
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME in ('JUMP','JUMP_2');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
JUMP PK_JUMPNO P
JUMP_2 FK_JUMPNO R
SCOTT@ORCL >
session 1>
alter table JUMP_2 modify constraints FK_JUMPNO disable novalidate;
update JUMP set JUMPNO='30' where JNAME='DEF'
update JUMP set JUMPno='30' where JNAME='DEF'
session 2>
update JUMP set JUMPno='30' where JNAME='DEF'
select sw.SID, lo.oracle_username, do.object_name, do.object_type, l.type, lo.locked_mode, sw.event, sw.state
from v$lock l, v$session_wait sw, v$locked_object lo, DBA_OBJECTS DO
where l.sid = sw.sid
and l.sid = lo.session_id
AND l.ID1=do.object_id
and l.type ='TM';
SID ORACLE_USERNAME OBJECT_NAM OBJECT_TYP TYPE LOCKED_MODE EVENT STATE
---------- --------------- ---------- ---------- ---- ------------------------------------ ----------
151 SCOTT JUMP TABLE TM 3 enq: TX - row lock contention WAITING
148 SCOTT JUMP TABLE TM 3 SQL*Net message from client WAITING
3) X LOCK
- 가장 강력한 범위의 테이블-레벨락
- X 락이 걸리면 다른 락을 발생시킬 수 없음.
- LOCK TABLE [테이블명] IN EXCLUSIVE MODE;
LOCKED_MODE :
1 SELECT ~ FOR UPDATE에 의한 Lock
2 INSERT에 의한 Lock
3 UPDATE에 의한 Lock
4 DELETE에 의한 Lock
lock mode
0 - none
1 - null(n)
2 - Sub-Shared(ss), Row-Shared(RS)
3 - Sub-Exclusive(SX), Row-Exclusive(RX)
4 - Shared(S)
5 - Shared-Sub-Exclusive(SSX), Shared-Row-Exclusive(SRX)
6 - Exclusive(X)
원인 대기 이벤트 LMODE
DML 로우 Lock ? enq : TX - row lock contention ?6 (Exclusive)
무결성 제약 위배 가능성 ? enq : TX - row lock contention ?4 (Shared)
비트맵 인덱스 엔트리 갱신 enq : TX - row lock contention ?4 (Shared)
ITL부족 ? enq : TX - allocate ITL entry ?4 (Shared)
인덱스분할 ? enq : TX - index contentiony ?4 (Shared)
읽기전용테이블스페이스, enq : TX - contention ? 4 (Shared)
Prepared TxN(2PC), Free Lists 등등
* Select ... From for update of
- 위의 명령어에 의해 where 조건에 걸린 row 테이블은 TX LOCK 생김.
- where 조건 row 에 대하여 update, delete 실행할 때 테이블에 대해 RX lock 생김. -> 에러 X
- commit, rollback 하기 전 까지, update, delete 외 다른 명령어 실행시 Waiting 을 함
* 여기서 TM 은 SESSION 의 상태를 말하는 것.
LOCK 상태는 V$LOCK 의
BLOCK 수가 늘어난 것을 봐야함
BLOCK 1 이면 해당 트랜잭션으로 인해 뒤에 대기 타고 있는 트랜잭션이 하나라는 것.
select et.*, dt.*
from emptest et, depttest dt
where et.empno = 23251
and et.empno = dt.deptno
for update of dt.deptno wait 3;
위 쿼리의 경우 WHERE 조건이 들어가므로 TX, TM 이 모두 걸림
TX - 테이블락
TM - where 조건에 걸려 변경되는 row 에 대한 락
TX - 6 : Exclusive(X)
TM - 3 : Sub-Exclusive(SX), Row-Exclusive(RX)
SYS@ORCL AS SYSDBA> select sid, type, lmode from v$lock
2 where sid in (148,151,22)
3 and TYPE != 'AE';
SID TY LMODE
---------- -- ----------
22 TM 3
22 TX 6
151 TM 3
151 TX 6
update emptest set RANDOM='ASDSS' where empno=1111
SCOTT@ORCL > select * from emptest where empno=1111;
EMPNO DEPTNO ENAME ADDR RANDOM
---------- ---------- ---------- ---------- ----------
########## ########## 홍길동1111 서울1111 NPDOR
=> 해당 row 를 제외하고 update 가능
* Select .. for update of
- 위의 명령어에 의해 해당 테이블에는 RS Lock 가 생김
- ROW 에는 TX Lock 걸림
하지만,
select et.*, dt.*
from emptest et, depttest dt
for update of dt.deptno wait 3;
위 쿼리의 경우 테이블 전체의 변동이 이루어지므로 TM, TX 걸림
SYS@ORCL AS SYSDBA> select sid, type, lmode from v$lock
2 where sid in (148,151,22)
3 and TYPE != 'AE';
SID TY LMODE
---------- -- ----------
151 TM 3
151 TX 6
---> TM 이 2번으로 보여야하는데 왜 3번으로 보이는지 모르겠음.
Oracle 에서 Lock 걸린 객체 조회 쿼리
select s.sid as sid, S.serial# as serial#, s.username , s.logon_time, s.machine,
DECODE(L.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table','모름') as lock_type,
DECODE(l.LMODE,
0, 'None', /* MON LOCK EQUIVALENT */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive' /* X */
) as lock_mode,
DECODE(l.REQUEST,
0, 'None', /* MON LOCK EQUIVALENT */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive' /* X */
)as request,
s.status, s.program, s.osuser, q.sql_text
from v$session s, v$lock l, v$sql q
where l.sid=s.sid
and s.sql_address = q.address
and sql_hash_value = q.hash_value
| 래치 | 락 | |
| 목적 | 하나의 목적을 수행 메모리 구조에 대한 배타적인 접근을 위함 ( 9i 부터 cache buffer chains latch 들은 읽기 전용시에 공유가 가능) |
두 가지 목적 수행. 1) 락 모드가 호환 가능하다면 다수의 프로세스가 동일한 리소스를 공유하는것을 허용. 2) 락 모드가 호환 되지 않으면 리소스에 대한 배타적인 접근만 허용 |
| 사용 범위 | SGA 내부의 데이터 구조에만 적용 메모리 오브젝트를 임시적으로 보호 단일 오퍼레이션으로 메모리 구조에 대한 접근제어 트랜잭션 단위가 아님 |
테이블, 데이터 블록 및 state object 와 같은 오브젝트를 보호함. 데이터베이스의 데이터 또는 베타 데이터 접근제어, 트랜잭션 단위 |
| 획득 방법 | 두가지 모드로 가능 1) willing-to-wait 2) no-wait |
6가지 모드로 요청 가능. 1) NULL 2) row share 3) row exclusive 4) share 5) share row exclusive 6) exclusive |
| 범위 | SGA 내부에 정보가 존재하며 로컬 인스턴스에서만 볼수 있음. 래치는 인스턴스 레벨로 작동. |
데이터베이스 내부에 정보가 존재. 모든 인스턴스에서 볼수 있음. 락은 데이터베이스 레벨에서 작동. |
| 복잡도 | 단순한 명령어를 사용하여 구현 일반적으로 test-and-set, compare-and-swap cpu 명령어 |
문맥 교환(context switch)를 포함한 일련의 명령어들을 사용하여 구현. |
| 지속시간 | 아주 짧은 순간만 지속 | 일정 시간 동안 지속됨(트랜잭션 동안) |
| 큐 | 프로세스가 래치 획득을 실패한 후 슬립(sleep) 상태로 들어갈 때, 해당 요청은 큐(queue)로 관리 되지 않으며, 요청한 순서대로 서비스되지 않음 (latch wait list를 이용하여 큐 방식으로 사용되는 래치는 예외) |
일정 시간 동안 지속됨(트랜잭션 동안) 프로세스가 락 획득을 실패 한 후, 해당 요청 큐(queue)로 관리 됨. 요청한 순서대로 서비스됨 (no-wait 모드 예외) |
| 데드락 | 래치는 데드락(deadlock)이 발생되지 않도록 구현 | 락은 큐(queue)방식을 사용하며, 데드락이 발생될 가능성이 높음 데드락 발생시 마다 트레이스 파일이 생김 |