Oracle의 Lock에 관하여
Oracle이 채택한 유일한 Data Lock은 row-level Lock이다.
row lock의 갯수는 제한이 없으며, Oracle은 row-livel부터 그이상의 Table Lock으로 escalate하지
않습니다. Row Locking은 최대로 가능한 미세한 Locking을 제공하며 , 가장 가능한 concurrency와
throughput을 제공한다.
Multiversion concurrency control과 row-level locking의 결합은 동일한 row를 access할때에
data에 대해서 user들이 경쟁할때 의미가 있다.
특히:
* data의 reader들이 동일한 data row들의 writer들을 기다리지 않는다.
* data의 writer들은 동일한 data row들의 reader들을 기다리지 않는다.
(reader에 대해서 특별히 Lock을 요구하는, select ...for update를 사용하지 않는다면)
* Writer들이 동시에 동일한 row를 갱신하려고 한다면 다른 Writer들은 기다려야 한다.
참고:data의 reader들은 분산 Trasaction들을 pending하는 일부 특별한 경우에 동일한 Data Block들
의 writer들을 기다려야 할수도 있다.
모든 경우에 Oracle은 SQL수행시에 자동으로 Lock을 획득하며, User는 자세한 부분을 생각하지 않아
도 된다. Oracle은 또한 User가 data를 Manual로 Locking하는것을 허용한다.
1.Trnasaction과 Data Concurrency
- Oracle은 locking 메커니즘을 사용하는 트랜잭션사이에 data concurrency와 integrity를 제공할
수 있다. Oracle의 lock 메커니즘은 transaction control과 밀접하기에, application Designer들
은 적당히 트랜잭션을 정의하기만 하면 되고, Oracle은 자동으로 Lock을 관리해 준다.
2. Duration of Locks
- Transaction에 의해 걸린 Lock은 다른 동시수행 Transaction으로 부터의 파괴적인 간섭(dirty
reads,lost update, 파괴적인 DDL Operation)을 막기위해, Transaction기간 동안 유지된다. 하나
의 Transaction의 SQL문장에 의해서 변경된 data는 그 트랜잭션이 Commit된후 시작된 다른
Transaction에 의해서 볼수있다.
Oracle의 트랜잭션내의 문장에 의해 걸린 Lock들은 해당 트랜잭션이 커밋되거나 롤백된후에 해제된
다. Oracle은 또한 SavePoint까지 RollBack되었을때 savepoint후에 걸린 Lock들을 해제한다.
3.DeadLock
- Oracle은 자동으로 DeadLock을 발견하고 deadlock과 관련된 문장중의 하나를 rollback하며, 충돌
되는 row lock들의 집합중의 하나를 해제한다.또한 Rollback된 트랜잭션은 statement-level
rollback을 수행했다는 message를 받는다. statement rollback은 transaction이 deadlock에 걸렸다
는 것을 나타낸다. 일반적으로 명백하게 rollbacl되었다는 메시지를 받은 transaction은 기다린후에
rolled-back statement를 수행한다. deadlock은 대부분 트랜잭션이 명백하게 default lock을 무시한
때 자주 발생한다. Oracle 자체가 lock escalate하지않고 query들에 대해서 read lock을 사용하지
만 row-level locking을 사용함으로서 deadlock은 드물게 발생한다.
4. avoid deadlock
- multi-table deadlock들은 만약 동일한 Table을 Access하는 트랜잭션이 서로 동일한 순서로 이
Table에 묵시적으로 명백한 lock을 건다면 일반적으로 피할수 있다. 예를들면 개발자들이 Master
Table과 Detail Table모두를 변경한다면 ,먼저 master쪽에 lock을 걸고, 그다음에 detail쪽에 lock
을 걸어야 할것이다.
1. Locks의 형태
- Oracle은 자동으로 data에 대한 concurrent access하기위해서 여러종류의 lock을 사용하며,user
들 사이에 파괴적인 상호작용을 방지한다. 또한 Oracle은 다른 트랜잭션들이 동일한 자원에대한
exclusive access를 요구하는것을 방지하기 위해 transaction과 관련된 자원들을 자동으로 lock을
건다.이 lock은 어떤 event가 발생하면 , 그리고 그 transaction이 더이상 자원을 요구하디 않을때
자동으로 해제한다. 참고로 프로그래머들이 명심햐야할 사항은 모든 SQL에 대해서 묵시적인 lock이 발
생하므로 , 어떠한 자원에 대해서고 명백한 locking이 필요하지 않다. Operation 전체에 걸쳐서,
Oracle은 lock이 걸린 자원과 수행된 operation에 따라서 여러 level의 restrictiveness에 여러형태
의 lock을 자동으로 건다. 일반적으로 Oracle lock은 다음의 분류중의 하나가 걸린다.
a. data locks(DML locks)
data보호를 위한 locking, 예를들면 table lock은 전체 table에대해 lock을 걸지만 row locks은 선
택된 row들에 lock을 건다.
b.dictionary locks(DDL locks)
Object들의 Structure를 보호하기위한 lock, 즉 table과 row들의 정의를 보관한다.
c. internal locks and latches
Internal lock과 latch들은 datafile들과 같은 internal data structure를 보호하며 internal lock과 latches는 완전히 자동이다.
d.distributed locks
distributed locks은 여러 다양한 Oracle Parallel Server사이의 분산된 data와 또다른 자원이
consistent를 유지하는것을 보존한다. distributed locks은 트랜잭션보다는 Instance에 의해서 걸린
다. Oracle Parellel Server사이의 Instance는 자원의 현재 상태를 서로 전달한다.
e.Parallel cache management(PCM) locks
Parallel cache management(PCM) locks은 buffer cache내에서 하나 이상의 data block(table, index block)들을 보호하는 distrbuted lock이다. PCM lock은 트랜잭션에 대한 어떠한 row에 대해서도 lock을 걸지 않는다.
Data Locks
Data lock(DML lock)의 목적은 여러 user에 의해 동시에 access되는 data의 integrity를 보장하는
것이다. Data locks은 동시에 충돌하는 DML 과/또는 DDL operation의 파괴적인 간섭을 방지한다.
DML operation은 서로 다른 두 level에 대해 data lock을 걸 수 있다: 특정 row들에 대해서 그리고
전체 table들에 대해서
Row Locks(TX)
Transaction은 다음 문장 중 하나에 의해서 변경되는 각각의 개별적인 row에 대해 exclusive data
lock을 건다: INSERT, UPDATE, DELETE, 그리고 SELECT ... FOR UPDATE
수정된 row는 lock이 걸린 transaction이 commit되거나 rollback될 때까지 다른 user가 해당 row
를 변경할 수 없도록 항상 exclusive하게 lock을 건다. Row locks은 위에 나열된 문장의 결과처럼
Oracle에 의해서 자동으로 항상 lock을 건다.
Rows Locks and Table Locks 만약 transaction이 하나의 row에 대해서 row lock을 걸면, 그
transaction은 또한 해당 table에 대해서 table lock도 걸 수 있다. 또한 table lock은 현재의
transaction으로 data를 변경하는 것을 무시하는 DDL operation과의 충돌을 방지하기 위하여 걸려야
만 한다.
Table Locks(TM)
하나의 transaction은 다음의 DML 문장에 의해서 table이 변경될 때에 table lock을 건다: INSERT,
UPDATE, DELETE, SELECT ... FOR UPDATE, 그리고 LOCK TABLE. 이러한 DML operation들은 두 가
지 목적으로 table lock을 요구한다: transaction에 대해 DML access를 유지하기 위해서 그리고 그
transaction과 충돌하는 DDL operation을 방지하기 위해서 어떠한 table lock도 동일한 table에 대
해 exclusive DDL lock의 획득을 막음으로써 그러한 lock을 요구하는 DDL operation을 수행할 수 없
게 한다.
Table lock은 몇 가지 모드로 걸릴 수가 있다: row share(RS), row exclusive(RX), share lock
(S), share row exclusive(SRX), 그리고 exclusive(X). Table lock mode의 restriveness가 다른
transaction이 동일한 table에 걸린 또 다른 table lock들이 걸릴 수 있고 유지될 수 있는 lock mode들을 결정한다.
다음에 각각의 table lock에 대해 가장 덜 제한적인 것부터 가장 제한적인 것 순서대로
설명한다.
Row Share Table Locks(RS) Row share table lock(내부적으로 sub-share table lock,
SS라고 불리기도 함)은 table에 lock을 걸려는 transaction이 table안에 lock된 row가
있고 그 row를 변경시키고자 하는 것을 가리킨다. Row share table lock은 다음 문장에
의해 table에 대해 자동으로 lock을 건다:
SELECT . . . FROM table . . . FOR UPDATE OF . . . ;
LOCK TABLE table IN ROW SHARE MODE;
Row share table lock은 하나의 table에 대해서 높은 concurrency의 degree를 제공하기
위한 table lock의 가장 낮은 수준의 restrictive mode이다.
Permitted Operations : Transaction에 의해서 걸리는 row share table lock은 동시에
동일한 table에 대한 query, insert, delete, update, lock row를 허용한다. 그러므로,
다른 transaction은 동일한 table에 대해 동시에 row share, row exclusive, share,
그리고 share row exclusive table lock을 걸 수가 있다.
Prohibited Operations : Transaction에 의해서 걸리는 row share table lock은 다른 transaction이 다음의 문장을 이용하여 동일한 table에 대해 exclusive write access를
수행하는 것을 방지한다:
LOCK TABLE table IN EXCLUSIVE MODE
Row Exclusive Table Locks(RX) Row exclusive table lock(내부적으로 sub-exclusive
table lock, SX라 불리기도 함)은 그 lock이 걸린transaction이 그 table에 있는 row들에
대해 하나 이상의 update를 수행하고자 하는 것을 가리킨다. 다음 문장에 의해서 row
exclusive table lock이 수정된 table에 대해서 자동으로 걸린다:
INSERT INTO table . . . ;
UPDATE table . . . ;
DELETE FROM table . . . ;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
Row exclusive table lock은 row share table lock보다 약간 더 제한적이다.
Permitted Operations : Transaction에 의해서 거리는 row exclusive table lock은
동시에 동일한 table에 대해서 다른 transaction들이 row들을 query, insert, delete,
update, lock 하는 것을 허용한다. 그러므로, row exclusive table lock들은 여러
transaction이 동일한 table에 대해 동시에 row exclusive, row share table lock을
거는 것을 허용한다.
Prohibited Operations : Transaction에 의해서 걸리는 row exclusive table lock은 다른 transaction들이 exclusive하게 읽고 쓰기 위해서 수동으로 table을 lock하는 것을 방지한다.
그러므로, 다음 문장을 사용하여 다른 transaction들은 동시에 그 table을 lock할 수 없다:
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
Share Table Locks(S) Share table lock은 다음 문장에서 지정된 table에 대해서 자동으로
lock을 건다.
LOCK TABLE table IN SHARE MODE;
Permitted Operations : Transaction에 의해서 걸리는 share table lock은 다른
transaction들이 단지, table에 대한 query, SELECT ... FOR UPDATE를 이용한 특정 row에 대한
lock, LOCK TABLE ... IN SHARE MODE문들을 성공적으로 수행하기 위해서 허용한다; 다른
transaction에 의한 갱신은 허용하지 않는다. 여러 transaction이 동일한 table에 대해 동시에
share table lock을 수행할 수 있다. 이 경우에 어떠한 transaction도 table을 update할 수 없다
(Transaction이 SELECT ... FOR UPDATE문장의 결과로써 row lock들을 유지할 지라도). 그러므로 만
약 다른 transaction이 동일한 table에 대해 share table lock을 또한 가지지 않을 때에만 share
table lock을 가지는 transaction이 update 할 수 있다.
Prohibited Operations : Transaction에 의해서 걸리는 Share table lock은 다른 transaction이
다음 문장으로 동일한 table을 변경하는 것을 방지한다:
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
Share Row Exclusive Table Locks(SRX) Share row exclusive table lock(내부적으로 share-sub-
exclusive table lock, SSX라고 불리기도 함)은 share table lock보다 좀 더 제한적이다. Share
row exclusive table lock은 다음처럼 하나의 table에 대해서 걸린다.
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
Permitted Operations : 한 시점에 주어진 table에 대해
하나의 share row exclusive table lock만이 걸릴 수 있다.
transaction에 의해 걸린 share row exclusive table lock은
다른 transaction이 query을 하거나 SELECT ... FOR UPDATE로
특정 row를 lock하는 것을 허용하나 table의 갱신은 허용하지 않는다.
Prohibited Operations : Transaction에 의해서 걸리는
share row exclusive table lock은 다른 transaction이
동일한 table에 대해 row exclusive table lock을 걸어
table을 변경하는 것을 허용하지 않는다. Share row
exclusive table lock은 다른 transaction이 다음 문장을
이용하여 share, share row exclusive, exclusive table lock
을 수행하는 것을 방지한다.
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
Exclusive Table Locks(X) Exclusive table lock은
lock을 건 transaction이 table에 대한 access를
exclusive write로 허용하는table lock의 가장 제한적인
모드이다. Exclusive table lock은 다음 문w장에 의해 걸린다:
LOCK TABLE table IN EXCLUSIVE MODE;
Permitted Operations : 오직 하나의 transaction이 table에
대해 exclusive table lock을 걸 수 있다.
Prohibited Operations : Exclusive table lock은 다른
transaction이 그 table을 query하는 것만 허용한다. Exclusive
table lock은 어떤 종류의 DML문이나 어떤 종류의 lock도 금지한다.
DEFAULT LOCKING FOR INSERT, UPDATE, DELETE AND
SELECT ... FOR UPDATE STATEMENT INSERT, UPDATE, DELETE,
그리고 SELECT ... FOR UPDATE 문장의 특성은 다음과 같다.
DML문장을 포함하는 transaction은 문장에 의해서 변경되는
row들에 대해 exclusive row lock을 건다.그러므로 locking
transaction이 commit되거나 rollback될 때까지 다른 transaction이
lock된 row를 삭제하거나 변경할 수 없다.
DML문장을 포함하는 transaction은 subquery나 WHERE절의
query와 같은 묵시적인 query에 의해서 선택된 어떠한 row에
대하여 row lock을 걸 필요가 없다.
Transaction의 query은 동일한 transaction의 DML문장에
의한 변화는 볼 수 있지만, transaction이 시작된 이후의 다른
transaction에 의한 변화는 볼 수 없다.
요구되는 exclusive row lock이외에 추가로, DML문장을 포함하는
transaction은 영향받는 row를 포함하는 table에 대해 최소한 하나의
row exclusive table lock을 건다.
------------------------------------
Oracle에서 Lock 걸린 객체 조회 쿼리
------------------------------------
v$session, v$lock, v$sql등의 View를 조인하여 결과를 얻는다.
View에다한 상세설명은 Reference Manual을 참조하세요.
select s.sid as sid,s.serial# as serial#,s.username as username,s.logon_time as logon_time,s.machine as machine,
decode(l.type,'MR','Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX','Transaction',
'TM','DML',
'UL','P/L SQL User Block',
'DX','Distrbuted Xaction',
'CF','Control File',
'IS','Instance State',
'FS','File Set',
'IR','Instance Recovery',
'ST','Disk Space Transaction',
'TS','Temp Segment',
'IV','Library Cache Invalidation',
'LS','Log State or Switch',
'RW','Row Wait',
'TE','Extend Table',
'Tt','Temp Table','모름') as lock_type,
decode(l.lmode,0,'None',
1,'Null',
2,'Row-S(SS)',
3,'Row-X(SX)',
4,'Share',
5,'S/Row-X(SSX)',
6,'Exclusive','모름') as lock_mode,
decode(l.request,0,'None',
1,'Null',
2,'Row-S(SS)',
3,'Row-X(SX)',
4,'Share',
5,'S/Row-X(SSX)',
6,'Exclusive','모름') as request,
s.status as status,s.program as program,s.osuser as osuser,q.sql_text as sql_text
from v$session s, v$lock l, v$sql q
where l.sid = s.sid
and s.sql_address = q.address
and s.sql_hash_value = q.hash_value
order by s.sid;
댓글 없음:
댓글 쓰기