1. Lock 기본

가. Lock이란?

같은 자원을 액세스하려는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하려면

트랜잭션의 순차적 진행을 보장할 수 있는 직렬화(serialization) 장치가 필요하다.

- 직렬화가 가능하도록 하기 위해 모든 DBMS가 공통적으로 사용하는 메커니즘이 Lock이다.

- 중요한 것은 DBMS마다 Lock을 구현하는 방식과 세부적인 기능이 많이 다르다는 사실이다.

 

나. 공유 Lock과 배타적 Lock

 

1) 공유 Lock

공유(Shared) Lock은 데이터를 읽고자 할 때 사용된다.

다른 공유 Lock과는 호환되지만 배타적 Lock과는 호환되지 않는다.

( ‘호환된다’는 말은 한 리소스에 두 개 이상의 Lock을 동시에 설정할 수 있음을 뜻한다.)

2) 배타적 Lock

배타적(Exclusive) Lock은 데이터를 변경하고자 할 때 사용되며, 트랜잭션이 완료될 때까지 유지된다.

Lock이 해제될 때까지 다른 트랜잭션은 해당 리소스에 접근할 수 없다.

변경이 불가능할 뿐만 아니라 읽기도 불가능하다.

다른 트랜잭션에 의해 Lock이 설정된 리소스는, 그것이 공유 Lock이든 배타적 Lock이든, 배타적 Lock을 동시에 설정할 수 없다.

다. 블로킹과 교착상태

1) 블로킹

블로킹(Blocking)은, Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태를 말한다.

- 공유 Lock끼리는 호환되기 때문에 블로킹이 발생하지 않는다.

- 공유 Lock과 배타적 Lock은 호환되지 않아 블로킹이 발생할 수 있다.

- 배타적 Lock끼리는 당연히 호환되지 않는다.

블로킹 상태를 해소하는 방법은 커밋(또는 롤백)뿐이다. (LOCK을 가지고 있는 트랜잭션이 끝나야한다.)

 

Lock에 의한 성능 저하를 최소화하는 방안을 살펴보자.

① 우선, 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션을 가능한 짧게 정의하려는 노력이 필요하다.

     - Oracle은 데이터를 읽을 때 공유 Lock을 사용하지 않기 때문에 다른 DBMS에 비해 상대적으로 Lock 경합이 적게 발생한다.

        그렇더라도 배타적 Lock끼리 발생하는 경합은 피하지 못하므로 ‘불필요하게’ 트랜잭션을 길게 정의해선 안 된다.

② 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계하는 것도 중요하다.

     특히, 트랜잭션이 활발한 주간에 대용량 갱신 작업을 수행해선 안 된다.

③ 주간에 대용량 갱신 작업이 불가피하다면, 블로킹 현상에 의해 사용자가 무한정 기다리지 않도록

      적절한 프로그래밍 기법을 도입

     - 예를 들어, SQL Server에서는 세션 레벨에서 LOCK_TIMEOUT을 설정할 수 있다.

       아래는 Lock에 의한 대기 시간이 최대 2초를 넘지 않도록 설정한 것이다.

     - Oracle이라면 update/delete 문장을 수행하기 전에 nowait이나 wait 옵션을 지정한

       select … for update 문을 먼저 수행해 봄으로써 Lock이 설정됐는지 체크할 수 있고,

       발생한 예외사항(exception)에 따라 적절한 조치를 취할 수 있다.

④ 트랜잭션 격리성 수준(2절 2항 참조)을 불필요하게 상향 조정하지 않는다.

⑤ 트랜잭션을 잘 설계하고 대기 현상을 피하는 프로그래밍 기법을 적용하기에 앞서,

     SQL 문장이 가장 빠른 시간 내에 처리를 완료하도록 하는 것이 Lock 튜닝의 기본이고 효과도 가장 확실하다.

 

2) 교착상태

교착상태(Deadlock)는, 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 마주보며 진행하는 상황을 말한다.

교착상태가 발생하면, DBMS가 둘 중 한 세션에 에러를 발생시킴으로써 문제를 해결한다.

이를 방지하려면 어떻게 해야 할까?

- 조금 전 설명한 Lock 튜닝 방안은 교착상태 발생 가능성을 줄이는 방안이기도 하다.

  여러 테이블을 액세스하면서 발생하는 교착상태는 테이블 접근 순서를 같게 처리하면 피할 수 있다.

  예를 들어, 마스터 테이블과 상세 테이블을 둘 다 갱신할 때 마스터 테이블 다음에 상세 테이블을 갱신하기로 규칙을 정하고,

  모든 애플리케이션 개발자가 이 규칙을 지킨다면 교착상태는 발생하지 않을 것이다.

- SQL Server라면 잠시 후 설명할 갱신(Update) Lock을 사용함으로써 교착상태 발생 가능성을 줄일 수 있다.

2. SQL Server Lock

가. Lock 종류

1) 공유 Lock

- SQL Server의 공유 Lock은 트랜잭션이나 쿼리 수행이 완료될 때까지 유지되는 것이 아니라

   다음 레코드가 읽히면 곧바로 해제된다.(단, 기본 트랜잭션 격리성 수준(Read Committed)에서만 그렇다.)

   (격리성 수준 참고자료 : 고도화원리와해법) - http://www.bysql.net/index.php?document_srl=5062

   격리성 수준을 변경하지 않고도 트랜잭션 내에서 공유 Lock이 유지되도록 하려면

   아래와 같이 테이블 힌트로 holdlock을 지정하면 된다.(트랜잭션 격리성 수준에 대해서는 다음 절에서 설명한다.)

(프로그램)

 

 2) 배타적 Lock

- 1항에서 설명한 내용과 같다.

 

3) 갱신 Lock (앞으로 갱신을 하겠다는 의사 표현으로 우선권을 가진다.....)

앞서 공유 Lock을 설명하면서 예시했던 적립포인트 변경 프로그램을 공교롭게도

두 트랜잭션이 같은 고객에 대하여 동시에 수행했다고 가정하자. 

 두 트랜잭션 모두 처음에는 공유 Lock을 설정했다가 적립포인트를 변경하기 직전에 배타적 Lock을 설정하려고 할 것이다.

그러면 두 트랜잭션은 상대편 트랜잭션에 의한 공유 Lock이 해제되기만을 기다리는 교착상태에 빠지게 된다.

이런 잠재적인 교착상태를 방지하려고 SQL Server는 갱신(Update) Lock을 두게 되었고,

이 기능을 사용하려면 아래와 같이 updlock 힌트를 지정하면 된다.

(프로그램)

한 자원에 대한 갱신 Lock은 한 트랜잭션만 설정할 수 있다.

따라서 첫 번째 트랜잭션이 고객 데이터를 읽을 때 갱신 Lock을 설정하면

두 번째 트랜잭션은 첫 번째 트랜잭션이 배타적 Lock으로 전환했다가 이를 다시 해제할 때까지 기다려야만 한다.

갱신 Lock끼리는 호환되지 않지만 공유 Lock과는 호환되므로

갱신 Lock이 설정된 데이터를 단순히 읽고자 할 때는 기다리지 않아도 된다.

 

4) 의도 Lock (Intent Lock)

특정 로우에 Lock을 설정하면 그와 동시에 상위 레벨 개체(페이지, 익스텐트, 테이블)에 내부적으로 의도(Intent) Lock이 설정된다.

Lock을 설정하려는 개체의 하위 레벨에서 선행 트랜잭션이 어떤 작업을 수행 중인지를 알리는 용도로 사용되며,

일종의 푯말(Flag)이라고 할 수 있다.

예를 들어, 구조를 변경하기 위해 테이블을 잠그려 할 때 그 하위의 모든 페이지나 익스텐트,

심지어 로우에 어떤 Lock이 설정돼 있는지를 일일이 검사해야 한다면 좀처럼 작업이 끝나지 않을 수 있다.

의도 Lock은 그런 현상을 방지해 준다.

즉, 해당 테이블에 어떤 모드의 의도 Lock이 설정돼 있는지만 보고도 작업을 진행할지 아니면 기다릴지를 결정할 수 있다.

 

5) 스키마 Lock

테이블 스키마에 의존적인 작업을 수행할 때 사용된다.

• Sch-M(Schema Modification) : 테이블 구조를 변경하는 DDL 문을 수행할 때 발생하며,
  수정 중인 스키마 정보를 다른 세션이 참조하지 못하도록 함

6) Bulk Update Lock

테이블 Lock의 일종으로, 테이블에 데이터를 Bulk Copy 할 때 발생한다.

병렬 데이터 로딩(Bulk Insert나 bcp 작업을 동시 수행)을 허용하지만 일반적인 트랜잭션 작업은 허용되지 않는다.

 

(MSSQL 로킹 참고 URL) - http://blog.naver.com/ssayagain?Redirect=Log&logNo=90031592976

(MSSQL 트랜잭션 분리수준 참고 URL) - http://blog.naver.com/ssayagain?Redirect=Log&logNo=90031592976

 

나. Lock 레벨과 Escalation

위 5가지 레벨 외에 인덱스 키(Key)에 로우 레벨 Lock을 거는 경우도 있다.

 

Lock Escalation

‘Lock Escalation’이란 관리할 Lock 리소스가 정해진 임계치를 넘으면서

   로우 레벨 락이 페이지, 익스텐트, 테이블 레벨 락으로 점점 확장되는 것을 말한다.

- 이는 SQL Server, DB2 UDB처럼 한정된 메모리 상에서 Lock 매니저를 통해

    Lock 정보를 관리하는 DBMS에서 공통적으로 발생할 수 있는 현상이다.

- Locking 레벨이 낮을수록 동시성은 좋지만 관리해야 할 Lock 개수가 증가하기 때문에 더 많은 리소스를 소비한다.

- 반대로, Locking 레벨이 높을수록 적은 양의 Lock 리소스를 사용하지만 하나의

   Lock으로 수많은 레코드를 한꺼번에 잠그기 때문에 동시성은 나빠진다.

 

(Lock Escalation  참고자료 URL) - http://strictlysql.blogspot.com/2010/11/lock-escalation-sql-server-2008.html

 

다. Lock 호환성

‘호환된다’는 말은 한 리소스에 두 개 이상의 Lock을 동시에 설정할 수 있음을 뜻한다.

앞서 설명한 Lock 종류별로 호환성을 요약하면 [표 Ⅲ-2-2]와 같다.(‘O’는 두 모드 간에 호환성이 있음을 의미함)

스키마 Lock의 호환성은 다음과 같다.

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

 

3. Oracle Lock

Oracle은 공유 리소스와 사용자 데이터를 보호할 목적으로 다양한 종류의 Lock을 사용한다.

- DML Lock, DDL Lock, 래치(Latch), 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등

이들 중 애플리케이션 개발 측면에서 가장 중요하게 다루어야 할 Lock은 무엇보다 DML Lock이다.

- DML Lock은, 다중 사용자에 의해 동시에 액세스되는 사용자 데이터의 무결성을 보호해 준다.

- DML Lock에는 로우 Lock과 테이블 Lock이 있다.

가. 로우 Lock

Oracle에서 로우 Lock은 항상 배타적이다.

- insert, update, delete문이나 select...for update문을 수행한 트랜잭션에 의해 설정되며,

  이 트랜잭션이 커밋 또는 롤백할 때까지 다른 트랜잭션은 해당 로우를 변경할 수 없다.

- Oracle에서 일반 select문에 의해 읽힌 레코드에는 어떤 Lock도 설정되지 않는다.

  다른 DBMS처럼 읽기 작업에 대한 공유 Lock을 사용하지 않기 때문에 Oracle에서 읽기와 갱신 작업은 서로 방해하지 않는다.

• 읽으려는 데이터를 다른 트랜잭션이 갱신 중이더라도 기다리지 않는다.
• 갱신하려는 데이터를 다른 트랜잭션이 읽는 중이더라도 기다리지 않는다.
       (select ... for update 구문으로 읽는 경우는 제외)
• 갱신하려는 데이터를 다른 트랜잭션이 갱신 중이면 기다린다.

Oracle이 공유 Lock을 사용하지 않고도 일관성을 유지할 수 있는 것은

Undo 데이터를 이용한 다중버전 동시성 제어 메커니즘(3절에서 설명함)을 사용하기 때문이다.

- 또한 별도의 Lock 매니저 없이 레코드의 속성으로서 로우 Lock을 구현했기 때문에

  아무리 많은 레코드를 갱신하더라도 절대 Lock Escalation은 발생하지 않는다.

나. 테이블 Lock

한 트랜잭션이 로우 Lock을 얻는 순간, 해당 테이블에 대한 테이블 Lock도 동시에 얻는다.

그럼으로써 현재 트랜잭션이 갱신 중인 테이블에 대한 호환되지 않는 DDL 오퍼레이션을 방지한다.

테이블 구조를 변경하지 못하도록 막는 것이다. 테이블 Lock 종류로는 아래 5가지가 있다.

• 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 table emp in row share mode; lock table emp in row exclusive mode; lock table emp in share mode; lock table emp in share row exclusive mode; lock table emp in exclusive mode;

 

- 테이블 Lock끼리의 호환성은 [표 Ⅲ-2-3]과 같다.

- ‘테이블 Lock’이라고 하면, 테이블 전체에 Lock이 걸린다고 생각하기 쉽다. 

       DML 수행 시 항상 테이블 Lock이 함께 설정된다고 했는데,

       만약 이것이 SQL Server의 테이블 레벨 Lock처럼 테이블 전체를 잠그는 기능이라면

       다른 트랜잭션이 더는 레코드를 추가하거나 갱신하지 못하도록 막게 될 것이다.

       하지만 [표 Ⅲ-2-3]에서 보듯, RX와 RX 간에 호환성이 있으므로 그런 일은 발생하지 않는다.

-  Oracle에서 말하는 테이블 Lock은, Lock을 획득한 선행 트랜잭션이 해당 테이블에서

       현재 어떤 작업을 수행 중인지를 알리는 일종의 푯말(Flag)이다.

   후행 트랜잭션은 어떤 테이블 Lock이 설정돼 있는지만 보고도 그 테이블로의 진입 여부를 결정할 수 있다.