Oracle 데이터 동시성 관리, Lock

목차

1. 오라클에서 사용하는 Lock과 Lock 메커니즘

2. Lock 충돌 발생했을 때 해결방법

---

1. 오라클에서 사용하는 Lock과 Lock 메커니즘

 

 

⦁ Lock 

- 데이터를 보호하는 장치

- 다른 세션에서 동일한 데이터를 동시에 변경하는 것을 방지한다.

  ㄴ- > 해당 세션이 데이터를 수정하고 있는 데이터를 Lock 한다.  다른 세션은 수정할 수 없다.

- 트랜잭션을 통해 전체테이블 까지 Lock 할 수 있음

 

⦁ Lock 메커니즘

 - Lock 메커니즘의 기본값은 하위단계의 행 레벨 Lock 모드이다. 다른 트랙잭션은 서로 간섭하지 않고 같은 테이블  내에      서 다른 행을 갱신할 수 있다. 즉, 행 레벨 락을 걸 수 있다. 

 - Select 명령어에는 Lock을 걸지 않는다.

 

⦁ 락의 종류

1. DML Lock

- 데이터를 보호하기 위해 락을 건다.

- Table 락은 전체 테이블을 잠그는 반면 컬럼 락은 선택한 행을 잠근다.

- DML락은  여러 사용자가 동시에 액세스하는 데이터의 무결성을 보장한다.

   ex) 두 고객이 온라인 서점에서 구할 수 있는 마지막 책을 구매하는 것을 방지한다.

- DML문 (insert, update, delete)와 관련있음.

 

1) Row Locks (TX)  : 행단위 Lock

- 로우락은 테이블의 단일 행에 대한 잠금

- 트랜잭션은 insert, update, delete, merge 또는 select.. for update 문에 의해 수정된 각 행에 대해 행 잠금 획득

- 로우락은 커밋 / 롤백 될 때 까지 존재

- 로우 락은 두 트랜잭션이 동일한 행을 수정하지 못하도록 하는 대기열 메커니즘 역할

- 다른 트랜잭션이 행을 수정할 수 없음

 

2) Table Locks(TM)

- insert, Update, Delete, Merge, for update 절이 있는 select 문에 의해 획득

- 트랜잭션과 충돌할 DDL 작업을 방지하기 위해 Table Lock 필요

┗━> 현재 트랜잭션이 갱신 중인 테이블에 대한 테이블 구조 변경을 방지한다.

┗━> DML 수행 중 해당 테이블이 ALTER나 DROP되는 것을 방지하기 위해 사용한다.

- 여러 트랜잭션에서 동시 수행이 불가능하지만, 테이블 하나만 잠그면 돼서 관리가 쉽다.

 

2_1) Row share(RS)

 - 테이블에 대한 lock을 보유한 트랜잭션이 lock을 건 행을 업데이트할 것임을 나타낸다.

 - 한가지 행을 잠궈놓고 있어도 테이블 내의 다른 행은 다른 트랜잭션이 업데이트 할 수 있기 때문에 테이블 잠금의 최소 제한 모드로 테이블에 대해 가장 높은 수준의 동시성

-  commit, rollback시 까지 waiting한다.

 

2_2) Row Exclusive Table Rock (Rx)

 - 다른 트랜잭션이 동일한 테이블에서 동시에 행을 쿼리, 삽입,업데이트,삭제 또는 잠글 수 있다.

 - lock을 보유한 트랜잭션이 해당 테이블 행을 업데이트 했음을 알린다.

 

2_3) Share table lock(s)

  - 트랜잭션에 의해 유지되는 공유 테이블 잠금이다

  -  다른 트랜잭션의 query를 허용하지만 트랜잭션이 share lock을 유지하는 경우만 허용

    ┗━> 동시 query는 허용하지만 lock된 테이블에 대한 갱신을 금지한다.

  - RS, RX에 비해 S를 보유하는 것으로는 테이블을 수정할 수 있는지 확실하지 않다.

 

2_4) share Row Exclusive table rock(SRX)

  - 한번에 하나의 트랜잭션만 주어진 테이블에서 이 잠금 획득가능

  - 다른 트랜잭션이 테이블을 쿼리할 수는 있지만 테이블을 업데이트 불가능

 

2_5) Exclusive table lock(x)

 - 아무것도 못함. 모든 유형의 잠금을 설정함

 

2. DDL LOCK

- 테이블 및 뷰의 사전 정의와 같은 스키마 개체의 구조를 보호한다.

 

1) Shared Lock (공유 락)

- 데이터를 읽을 때 사용하는 lock

- 같은 shared lock끼리는 동시 접근이 가능하다

┗━> 데이터 일관성과 무결성을 해치지 않기 때문이다. (데이터 변경 X)

- Exclusive lock의 접근은 막는다.

 

2) Exclusive Lock (배타 락)

- drop, alter 등

- 구조를 변경할 때 사용하는 lock

- 트랜잭션이 완료될 때까지 유지되며 배타 락이 끝날 때까지 어떠한 접근도 허용하지 않    음

- 다른 트랜잭션이 수행되고 있는 데이터에 대해서는 lock을 걸 수 없다.

 

---

2. Lock 충돌 발생했을 때 해결방법

⦁  락 충돌

- Lock 충돌이 자주 발생하지만 대개 시간이 지나면서 enqueue 매커니즘으로 해결.

- 트랜잭션 하나가 update명령어를 수행하며 커밋하지 않아 락을 반환하지 않게되면,

   다른 트랜잭션이 테이블을 갱신하려고 락을 요청할 때 차단된다.

 

* Enqueue 메커니즘 * 

 -  요청하는 순서에 따라 lock 모드를 추적하고 lock을 부여받는다.

- 다중 트랜잭션에서 같은 리소스를 lock 해야 할 경우 첫 번째로 lock을 요청한 트랜잭션이 lock을 획득한다. 다른 트랜잭션은 첫 번째 트랜잭션 완료를 대기

- 트랜잭션 완료시 (Commit, rollback) 모든 락 해제.

 

⦁ Lock 충돌 유발 원인

- 커밋되지 않은 변경사항

- 장기 실행 트랜잭션

   ㄴㅡ > 대량갱신을 수행할 때, 단기간에 끝나지 않는다.

- 필요이상으로 높은 Lock 레벨

 

⦁ Lock 충돌 해결

- 락을 보유하는 세션을 커밋 또는 롤백한다.

- 락을 보유하는 세션을 종료한다.

 

⦁ SQL을 사용하여 Lock 충돌 해결

 

1.  Lock을 걸어 wating을 일으킨 세션을 확인하는 명령어

SQL> select sid, serial#, username

           from v$session

           where sid IN

           (select blocking_session from v$session);

 

 

2. Kill

alter system kill session ‘sid, serial#’ immediate;

 

 

⦁ DeadLock

- 서로가 서로를 기다리는 교착상태.

- 오라클이 Detection 해서 에러메시지를 보낸다.

- 데드락 에러는 alert.log file에 기록된다. 어떤 블록에서 데드락이 발생했는지 나오게되고, 그것을 보고 데드락을 해제시킨다.