[Oracle] Lock 종류

  

 

 

 

Lock 이란?

락이란 멀티유저가 사용하는 데이터 베이스 환경해서 Concurrency와 Consistency, 그리고 무결성을 유지하기 위한 기능이다.

래치가 SGA를 보호 한다면, 락은 데이터베이스 전체를 보호한다고 할 수 있다. 테이블, 트랜잭션, 언두 세그먼트, 테이블 스페이스, 잡(Job)등 데이터베이스 단위에서 필요한 대부분의 리소소들은 락의 보호를 필요로 한다.

데이터베이스가 세션의 데이터 변경 작업을 허용하기 전에 먼저 세션은 변경 할 데이터에 대해 반드시 잠금을 설정해야 한다.

잠금은 세션에게 배타적 제어권을 부여하며, 잠금이 해소되기 전까지 다른 트랜잭션이 해당 데이터를 변경하지 못하도록 한다.

트랜잭션은 데이터의 개별 행, 복수 개의 행 또는 전체 테이블에 잠금을 설정 할 수 있다. Oracle Database 10g는 수동 및 자동 잠금을 모두 지원하며 , 자동으로 설정된 잠금은 다른 트랜잭션과의 잠재적 혼란을 최소하하기 위해 최소한의 수준의 락이 설정된다.

 

 

Types of Locks.

락은 매우 쉬운 개념이지만, 실제로는 상당히 혼란스러운 면이 있다. 첫째 락에 대한 정확한 분류가 존재하지 않는다는 것과

둘째 Enqueue 락과 일반 락과의 정확한 구분이 존재하지 않는다는 것에 기인한다. 아래의 분류는 상당히 논리적인 측면이 강하며 락의 활용도를 이해하는 데는 도움이 되지만 락의 실제 작동 메커니즘을 이해하는 데는 오히려 혼란을 야기하는 측면이 있다.

 

 

오라클 Concept 메뉴얼 에서의 락 분류

 

- DML locks (data locks)

Row Locks (TX)

Table Locks (TM) : None, Null, RX, RS, S, SRX, X

- DDL locks (dictionary locks)

Exclusive DDL Locks

Share DDL Locks

Breakable Parse Locks

 

- Internal locks and latches

Latches

Internal Locks

Dictionary Cache Locks

File and Log Management Locks

Tablespace and Rollback Segment Locks

 

 

OWI에서의 락의 분류

 

- Enqueue Lock

User Type Lock : TX, TM, UL

System Type Lock : CF, US, CI, TC, JS, ...

 

- Normal Lock

row cache lock

library cache lock, library cache pin

buffer lock

 

DATA LOCK

유저가 INSERT, DELETE, UDATE, SELECT .. FROM .. FOR UPDATE OF 문장을 실행하면, 변경되는 ROW에 대한 ROW LOCK 과 TABLE 에 대한 TABLE LOCK 이 생긴다

## ROW LOCK ( TX ) - INSERT INTO ... VALUE.. ;, DELETE FROM ...WHERE ...;, UPDATE ..SET ..WHERE ..;, SELECT .. FROM .. WHERE .. FOR UPDATE OF.. ; SQL 문장에서, WHERE 조건에 해당되는 ROW에 대하여 다른 유저들이 변경할 수 없도록 EXCLUSIVE LOCK 이 생긴다. TX LOCK 이 걸린 ROW 는 DML 문장을 실행한 유저가 COMMIT 이나 ROLLBACK을 할때 까지 걸리므로 다른 유저들이 변경할 수 없다.

## TABLE LOCK ( TM ) - TX LOCK 이 걸린 ROW 가 저장된 TABLE 에 대한 LOCK 이다. DML SQL 문장을 수행하는 중에, 해당 테이블이 ALTER 나 DROP 되는 것을 방지하기 위해서 TM LOCK 을 사용한다. 또 TM LOCK 에는 RS (ROW SHARE), RX (ROW EXCLUSIVE), S (SHARE), SRX (SHARE ROW EXCLUSIVE) X (EXCLUSIVE) 가 있다. 이런 TABLE LOCK MODE 는 같은 테이블에서 실행할 수 있는 SQL 문장과 실행할 수 없는 SQL 문장을 구분하기 위해서다. 예를 들어서 한 유저가 사원 테이블을 UPDATE 할때, 다른 유저가 사원 테이블을 DROP 하려고 한다고 가정하자.

TABLE LOCK MODE

RS ( ROW SHARE LOCK ) - SELECT .. FROM .. WHERE .. FOR UPDATE OF .. ; 이나 LOCK TABLE .. IN ROW SHARE MODE; 명령에 의해 해당 테이블에는 RS LOCK 이 생긴다. RS LOCK 이 걸린 테이블에는 RS, RX, S, SRX LOCK 을 걸 수 있고, X LOCK 은 걸 수 없다. 단, SELECT .. FROM FOR UPDATE OF 명령에 의해 WHERE 조건에 걸린 ROW 에 대해서는 TX LOCK 이 생기므로 이 ROW 에 대해서 UPDATE, DELETE 를 실행할때는 테이블에 대해서는 RX LOCK 이 생기므로 에러는 안 나지만, COMMIT 이나 ROLLBACK 할때까지 WAITING 을 한다. SELECT .. FOR UPDATE OF; 문장은 테이블에는 RS LOCK 이므로 에러는 안나지만, ROW 에 대해서는 TX LOCK 이 걸리므로 WAITING 한다.

RX ( ROW EXCLUSIVE LOCK ) - UPDATE ..;, INSERT INTO ..;, DELETE FROM ..; 이나 LOCK .. IN ROW EXCLUSIVE MODE ; 명령에 의해 테이블에 걸리는 LOCK 이다. RX LOCK 도 RS LOCK 과 비슷한 내용이고, 단지 S, SRX, X LOCK 을 걸 수 없다.

S (SHARE LOCK ) - LOCK .. IN SHARE MODE; 에 의해 테이블에 생긴 LOCK 이다. S LOCK 은 같은 테이블에 대해서 RS, S LOCK 만 가능하고, RX, SRX, X LOCK 을 걸 수는 없다.
SQL> LOCK TABLE EMP IN SHARE MODE;
<- 사원 테이블에는 INSERT, UPDATE,DELETE 할 수 없다.

SRX ( SHARE ROW EXCLUSIVE ) - LOCK TABLE .. IN SHARE ROW EXCLUSIVE MODE; 에 의해 테이블에 생긴 LOCK 이다. SRX LOCK 은 같은 테이블에 대해서 RS LOCK 만 가능하고 RX, SRX, S, X, LOCK 을 걸 수 없다.

SQL> LOCK TABLE 사원 IN SHARE ROW EXCLUSIVE MODE;
<- 사원 테이블에 SELECT .. FOR UPDATE OF;는 가능하지만 INSERT, DELETE, UPDATE 는 할 수 없다.

X ( EXCLUSIVE ) - LOCK TABLE .. IN EXCLUSIVE MODE; 에 의해 테이블에 생긴 LOCK 이다. X LOCK 은 같은 테이블에서는 어떠한 LOCK 도 걸 수 없다. 즉, DROP TABLE ..;, ALTER TABLE ..; 등의 DDL 문장에 의해 테이블에 생기는 LOCK 이다.

 

 

 

 

 

 

DML LOCK

S 테이블 전체에 락을 건다. INDEX해결책

SRX : DML금지 S락도 금지 INDEX해결책

 

 

 

 

 

SQL Statement	Mode of Table Lock	Lock Modes Permitted?
		RS	RX	S	SRX	X
SELECT...FROM table...	none	Y	Y	Y	Y	Y
INSERT INTO table ...	RX	Y	Y	N	N	N
UPDATE table ...	RX	Y*	Y*	N	N	N
DELETE FROM table ...	RX	Y*	Y*	N	N	N
SELECT ... FROM table FOR UPDATE OF ...	RS	Y*	Y*	Y*	Y*	N
LOCK TABLE table IN ROW SHARE MODE	RS	Y	Y	Y	Y	N
LOCK TABLE table IN ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N
LOCK TABLE table IN SHARE MODE	S	Y	N	Y	N	N
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE	SRX	Y	N	N	N	N
LOCK TABLE table IN EXCLUSIVE MODE	X	N	N	N	N	N

 

RS: row share

RX: row exclusive

S: share

SRX: share row exclusive

X: exclusive

*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

 

 

Data Dictionary Lock

 

 

딕셔너리 잠금을 개체의 구조를 보호함. 딕셔너리 잠금은 테이블이나 뷰를 정의(스키마 정의)한 부분을 보호함 DDl잠금은 프로시저에서 참조된 개체가 이 프로시저의 컴파일이 완료되기 전에 변경되거나 삭제되는 것을 방지

 

 

Type

 

Exclusive DDL Locks : 특정 DDL작업은 동일한 개체를 수정하거나 참조할 수 있는 다른 DDL작업의 파괴를 막기 위해 자원에 대해서 LOCK.

Share DDL Locks : 서로 충돌하는 DDL작업의 파괴적인 방해 작용을 막기 위해 자원에 대한 공유 DDL Lock.
( 이 트랜잭션이 진행되는 동안에는 참조된 개체 정의가 일정하게 됨)

Breakable Parse Locks : 공유 풀에 있는 SQL명령문 또는 PL/SQL프로그램 단위는 자신이 참조하는 각 개체에 대한 구문 분석 잠금. ( 참조된 개체가 변경되거나 삭제되면 관련된 공유 SQL 영역이 무효화될 수 있도록 하기 위해)

 

 

 

DDL LOCK

 

Exclusive(write) :I have it and you can't have it

Share(read) : I have it and you can share it with me.

But, if you try to get it exclusively

I will not let you

Breakable palse :

 

 

- 출처 : Enjoy the D&B

- URL : http://blog.naver.com/kmymk/110079623589