제 1절 Lock

set lock_timeout 2000

select * from t where no = 1 for update no wait    : 대기없이 Exception을 던짐
select * from t where no = 1 for update wait 3      : 3초 대기 후 Exception을 던짐

2. SQL Server Lock

가. Lock 종류

1. 공유 Lock
   • SQL Server 의 공유 Lock은 다음 레코드가 읽히면 곧바로 해제된다. ( 기본 트랜잭션 격리성 수준 - Read Committed) 에서만 그렇다.
   • 격리성 수준을 변경하지 않고 트랜잭션 내에서 공유 Lock이 유지되도록 하려면 아래와 같이 테이블 힌트로 holdlock을 지정하면 된다.
     
   • 데이터 변경할 목적으로 레코드 읽을 경우, 위와 같은 패턴으로 트랜잭션을 처리해야 한다.
   • 고객 데이터를 읽고 적립 포인트를 변경하기 전에 다른 트랜잭션이 고객 데이터를 변경하면, 적립포인트가 비일관된 상태가 될 수 있기 때문이다.
2. 배타적 Lock
   • 1항에서 설명한 내용과 같다.
3. 갱신 Lock
   • 위 예제에서 같은 고객에 대해서 두 트랜잭션이 동시에 수행되었다고 가정하자.
     처음에는 공유 Lock을 설정했다가 적립포인트를 변경하기 직전에 배타 Lock을 설정하려고 할 것이다.
     상대편 트랜잭션에 의한 공유 Lock이 해제되기만 기다리는 교착상태에 빠지게 된다.
   • 이러한 잠재적 교착상태를 방지하려고 SQL Server 는 갱신(Update) Lock 을 두게 되었고 아래와 같이 updlock 힌트를 지정하면 된다.
     
   • 한 자원에 대한 갱신 Lock 은 한 트랜잭션만 설정할 수 있다.
   • 첫 번째 트랜잭션이 고객테이터를 읽을 때 갱신 Lock을 설정하면,
     두 번째 트랜잭션은 첫 번째 트랜잭션이 배타 Lock으로 전환했다가 이를 해제할 때까지 기다려야 한다.
   • 갱신 Lock 끼리는 호환 안되나, 공유 Lock 과는 호환되므로 갱신 Lock이 설정된 데이터를 단순 읽기만 하면 대기하지 않아도 된다.
4. 의도 Lock
   • 특정 로우에 Lock을 설정하면 그와 동시에 상위 레벨 개체(페이지,익스텐트,테이블) 에 내부적으로 의도(Intent) Lock 이 설정된다.
   • Lock 설정하려는 개체의 하위 레벨에서 선행 트랜잭션이 어떤 작업을 수행 중인지 알리는 용도로 사용. 일종의 Flag.
   • 구조를 변경하기 위해 테이블 lock 하려할 때 하위의 모든 페이지, 익스텐트, 로우에 어떤 Lock 이 설정되어있는지 검사하는 것은 작업이 끝나지 않을 수 있다.
     의도 Lock 은 그런 현상을 방지한다. 해당 테이블에 어떤 모드의 의도 Lock 이 설정 되어 있는지 보고 작업 진행/대기 여부를 정한다.
5. 스키마 Lock
   • 테이블 스키마에 의존적인 작업을 수행할 때 사용된다.
     • Sch-S (Schema Stability) : SQL 컴파일 중 오브젝트 스키마를 참조할 때 발생, 읽는 스키마 정보 수정/삭제 안되도록 함.
     • Sch-M(Schema Modification) : 테이블 구조 변경하는 DDL문 수행 시 발행, 수정 중인 스키마 정보를 다른 세션이 참조하지 못하도록 함.
6. Bulk Update Lock
   • 테이블 Lock 의 일종. 테이블에 데이터를 Bulk Copy 할 때 발생.
     병렬 데이터 로딩 (Bulk Insert 나 bcp작업 동시수행)을 허용하나 일반 트랜잭션 작업 허용되지 않는다.

나. Lock 레벨과 Escalation

• 위 5가지 레벨 외에 인덱스 키(Key)에 로우 레벨 Lock 을 거는 경우도 있다.
• Lock Escalation
  • 관리할 Lock 리소스가 정해진 임계치를 넘으면서 로우 락 -> 페이지, 익스텐트, 테이블 레벨 락으로 확장되는 것.
  • 한정된 메모리 상에서 Lock 매니저를 통해 Lock 정보를 관리하는 DBMS 에서 공통적으로 발생할 수 있는 현상이다.
  • Lock 레벨이 낮을수록 동시성은 좋으나, 관리할 Lock 개수가 증가하여 더 많은 리소스를 소비한다.
  • Locking 레벨이 높을수록 적은 Lock 리소스를 사용하나, 하나의 Lock 으로 수많은 레코드를 한번에 잠그기 때문에 동시성은 나빠진다.

다. Lock 호환성

[ 스키마 Lock 호환성 ]

• Sch-S 는 Sch-M 을 제외한 모든 Lock 과 호환된다.
• Sch-M 은 어떤 Lock 과도 호환되지 않는다.

3. Oracle Lock

• Oracle 은 공유 리소스, 사용자 데이터 보호를 목적으로 DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등 다양한 종류의 Lock을 사용한다.
• DML Lock 은 다중 사용자에 의해 동시에 액세스되는 사용자 데이터의 무결성을 보호해준다.
  DML Lock 에는 로우 Lock, 테이블 Lock 이 있다.

가. 로우 Lock

• 항상 배타적이다. insert, update, delete 문이나 select ... for update 문을 실행한 트랜잭션에 의해 설정되며, 이 트랜잭션이 커밋 또는 롤백할 때 까지 다른 트랜잭션은 해당 로우를 변경할 수 없다.
• 일반 select 문에 의해 읽힌 레코드에는 어떤 Lock도 설정되지 않는다.
  읽기 작업에 대한 공유 Lock 을 사용하지 않기 때문에, 읽기와 갱신작업은 서로 방해되지 않는다.

• Oracle이 공유 Lock을 사용하지 않고도 일관성을 유지할 수 있는 것은 Undo 데이터를 이용한 다중 버전 동시성 제어 메커니즘을 사용하기 때문이다.
• 별도의 Lock 매니저 없이 레코드 속성으로 로우 Lock 을 구현했기 때문에 많은 레코드 갱신에도 절대 Lock Escalation 은 발생하지 않는다.

나. 테이블 Lock

• 한 트랜잭션이 로우 Lock 을 얻는 순간, 해당 테이블에 대한 테이블도 Lock을 얻는다.
• 테이블 구조를 변경하지 못하도록 현재 트랜잭션이 갱신 중인 테이블에 대한 호환되지 않는 DDL 오퍼레이션을 방지한다.
• 테이블 Lock 종류
  • Row Share(RS)
  • Row Exclusive(RX)
  • Share(S)
  • Share Row Exclusive(SRX)
  • Exclusive(X)

• select ... for update 문 수행 시 RS 모드 테이블 Lock을 얻고, insert, update, delete 문을 수행 시 RX 모드 테이블 Lock 을 얻는다.
• DML 로우 Lock을 처음 얻는 순간 묵시적으로 테이블 Lock을 얻지만, Lock Table 명령을 이용해 명시적으로 테이블 Lock 을 얻을 수도 있다.

• 테이블 Lock 끼리의 호환성은 아래 표와 같다.
  
• DML 수행 시 항상 테이블 Lock이 설정되는데, SQL Server 의 테이블 레벨 Lock 처럼 테이블 전체를 잠그는 기능. 그렇다면 다른 트랜잭션이 레코드 추가, 갱신이 불가할텐데 위 표에서 RX - RX 간 호환성이 있으므로 그런 일은 발생하지 않는다.
• Oracle 의 테이블 Lock 은 테이블 전체에 Lock 이 걸리는 것이 아니라, Lock을 획득한 선행 트랜잭션이 해당 테이블에서 현재 어떤 작업을 수행 중인지 알려주는 일종의 Flag 이다.
  후행 트랜잭션은 어떤 테이블 Lock이 설정돼 있는지만 보고 테이블 진입여부를 확인한다.