제 1절 Lock

1. Lock 기본

가. Lock 이란?

• 트랜잭션 처리능력이 핵심적인 요소.
• 같은 자원을 액세스하려는 다중 트랜잭션 환경에서 데이터베이스의 일관성, 무결성 유지를 위해 트랜잭션의 순차적 진행을 보장하는 직렬화(serialization) 장치가 필요하다.
• DBMS 마다 Lock 을 구현하는 세부 기능이 다르다.
• DBMS별 Lock 매커니즘과을 정확히 이해해야 한다.

나. 공유 Lock과 배타적 Lock

• DBMS는 각 트랜잭션의 오퍼레이션별로 적당한 수준의 Lock을 자동으로 설정한다.
• 일부 Lock 은 사용자가 직접 제어 방법도 제공한다.
• 공유 Lock과 배타적 Lock 이 있다.
  1. 공유 Lock
     • 공유(Shared) Lock 은 데이터를 읽고자 할 때 사용된다.
     • 다른 공유 Lock 과는 호환되나, 배타적 Lock 과는 호환되지 않는다.
       (호환 : 한 리소스에 두 개 이상의 Lock을 동시에 설정할 수 있음.)
     • 공유 Lock 을 설정한 리소스에 다른 트랜잭션이 추가로 Lock을 설정할 수 있으나, 배타적 Lock은 불가능하다.
     • 자신이 읽고있는 리소스를 다른 사용자가 동시에 읽을 수 있어도, 변경은 불가하다.
       다른 사용자가 읽고 있는 리소스를 동시에 읽을 수 있어도 변경 중인 리소스를 동시에 읽을 수 없다.
  2. 배타적 Lock
     • 데이터를 변경하고자 할 때 사용되며, 트랜잭션이 완료될 때 까지 유지된다.
     • Lock이 해제될 때까지 다른 트랜잭션은 해당 리소스에 접근할 수 없다. 변경 불가, 읽기 불가.
     • 다른 트랜잭션에 의해 Lock 이 설정된 리소스는, 공유 Lock이든 배타적 Lock 이든 배타적 Lock 을 동시에 설정할 수 없다.

다. 블로킹과 교착상태

1. 블로킹 (Blocking)
   • Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태.
   • 공유 Lock 끼리는 호환되므로 블로킹 발생하지 않는다.
   • 공유 Lock , 배타적 Lock 은 호환되지 않아 블로킹 발생 할 수 있다. ( 배타적 Lock 끼리도 호환 안됨 )
   • 블로킹 상태 해소 방법은 커밋(또는 롤백).
   • 먼저 Lock 설정한 트랜잭션이 완료될 때 까지 후행 트랜잭션은 대기해야 하므로 성능 저하가 발생한다.
     {info:title=Lock 에 의한 성능저하를 최소화 하는 방안}
2. 트랜잭션 원자성을 훼손하지 않는 선에서 트랜잭션을 가능한 짧게 정의해야 함.
   Oracle 은 select 시 공유 Lock 을 사용하지 않으므로 다른 DBMS에 비해 상대적으로 Lock 경합이 적다.
   배타적 Lock 경합은 피하지 못하므로 트랜잭션을 불필요하게 길게 정의해선 안된다.
3. 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계한다.
   트랜잭션이 활발한 주간에 대용량 갱신작업을 수행하면 안 된다.
4. 블로킹 현상에 의해 사용자가 무한정 기다리지 않도록 프로그래밍 기법을 도입한다.
   SQL Server 에서는 세션 레벨에서 LOCK_TIMEOUT 을 설정할 수 있다.

set lock_timeout 2000

Oracle 이라면 update/delete 문장 수행 전에 no wait, wait 옵션을 지정한 select ... for update 문을 먼저 수행해서 Lock 설정을 체크할 수 있다.

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

1. 트랜잭션 격리수준 (2절 2항 참조) 를 불필요하게 상향 조정하지 않는다.
2. 트랜잭션 설계를 잘 하여, SQL문장이 가장 빠른 시간 내에 처리를 완료하도록 하는 것이 Lock 튜닝의 기본이고 효과도 확실하다.
   {info}
3. 교착상태 (Deadlock)
   • 두 세션이 각각 Lock 을 설정한 리소스를 서로 액세스하려고 마주보며 진행하는 상황.
   • DBMS가 둘 중 한 세션에 에러를 발생시켜 문제를 해결한다.
   • 여러 테이블을 액세스하면서 발생하는 교착상태는 테이블 접근 순서를 같게 처리하면 피할 수 있다.
     예) 마스터 테이블과 상세 테이블을 둘 다 갱신할 때 마스터 테이블 다음에 상세 테이블 갱신하기로 규칙을 정한다.
     SQL Server 라면 아래 설명한 갱신(Update) Lock 을 사용함으로써 교착상태 발생 가능성을 줄일 수 있다.

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이 설정돼 있는지만 보고 테이블 진입여부를 확인한다.