잠금(LOCK) 종류와 잠금수준

1. LOCK의 종류

 

공유잠금(Shared Lock)	Select
배타적잠금(Exclusive Lock)	Insert , Update , Delete

 

 

2. LOCK 단위

 

 

RID	행 식별자. 하나의 행을 잠글 때 사용
KEY	인덱스가 있을 때의 행 잠금단위.
PAGE	8KB 데이터 페이지 또는 인덱스 페이지
EXTENT	인접한 8개의 데이터 페이지 또는 인덱스 페이지
TABLE	데이터와 인덱스가 포함된 전체 테이블
DB	데이터베이스

 

 

3. LOCK 종류

1) SHARED(S)

읽기 동안에만 일어나며 , 다른 S 락에 대해서는 공유하난 X에 대해서는

배타적이다. 데이터를 변경하거나 업데이트하지 않는 작업(읽기전용작업)에

사용한다.

공유잠금(S)를 사용하면 , 여러 트랜잭션이 동시이에 하나의 개체를 읽을 수 있다.

즉 , 공유잠금끼리는 서로 충돌되지 않는다.

그러나 , 공유잠금이 설정되어 있는 동안에는 , 다른 트랜잭션이 데이터를

변경할 수 없다.즉, 공유 잠금은 다른 배타적 잠금을 허용하지 않으며 ,

호환되지 않는다.리소스에 대한 공유 잠금은 다음 경우를 제외하고는 ,

데이터를 읽자마자 해제된다.

예외)

      - 트랜잭션 고립수준을 REPEATABLE 레벨 이상으로 설정

      - LOCK HINT 를 써서 , 해당 공유잠금을 트랜잭션 끝까지 유지한다.

 

SELECT * FROM EMP(HOLDLOCK)

 

2) EXCLUSIVE(X)

배타적 잠금이 설정되면 동시에 여러 연결이 한 리소스에 액세스할 수 없게 된다.

오직 하나의 연결만이 해당 리소스를 점유한다. 이 잠금은 수정 시에 , 행단위에

설정 되게 된다.

배타적 잠금은 다른 트랜잭션이 읽거나 수정할 수 없다. 즉  공유잠금(S)에도 적용된

다는 뜻이다. 배타적 잠금은 공유잠금을 포함한 대부분의 락에 대해서 배타적이

고 호환되지 않는다.

 

3) 업데이트(U)

수정 시에 X락을 걸기전 데이터영역을 읽기 위해 거는 락이다. 해당 리소스에 대한

잠금을 미리 체크하여 데드락을 방지하기 위해서 사용된다. 공유잠금과 호환된다.

 

업데이트잠금(U)를 사용하면 일반적인 형태의 교착상태가 방지된다.

일반적인 업데이트패턴은 레코드를 읽고 , 리소스(페이지 혹은 행)에 대한 공유(S)

잠금을 얻은 다음 , 행을 수정하는 트랜잭션으로 구성되는데 , 행을 수정할 때는 먼

저 배타적 잠금으로 잠금을 변환해야한다.

두 트랜잭션에서 리소스에 대해 공유 모드 잠금을 얻은 다음 데이터를 동시에 업데

이트하려고 하면 한 트랜잭션이 배타적 잠금으로 잠금을 변환하려 할 것이다. 이 때,

한 트랜잭션의 배타적 잠금은 다른 트랜잭션의 공유 모드 잠금과 호환되지 않으므

로, 공유 모드를 단독 모드로 변환할 때는 잠금 대기가 발생하게 된다. 그리고 두 번

째 트랜잭션도 해당 업데이트에 대해 배타적 잠금을 얻으려고 할 것이다. 이 경우 두

트랜잭션 모두 배타적 잠금으로 변환 중이고 각각 상대 트랜잭션이 공유 모드 잠금

을 해제하기를 기다리므로 교착 상태가 발생하는 것이다.

 

이러한 교착 상태를 방지하기 위해서 SQL Server는 업데이트 잠금을 사용한다. 이

업데이트 잠금은 한 번에 한 트랜잭션만 리소스에 대한 업데이트 잠금을 얻을 수 있

게 하기 때문에 교착상태가 방지될 수 있다. 트랜잭션이 리소스를 수정하면 업데이

트 잠금이 배타적 잠금으로 변환되고 그렇지 않으면 잠금이 공유 모드 잠금으로 변

환된다. 그러나 업데이트 잠금을 사용하더라도 교착상태를 완전히 방지할 수는 없

다. 그것은 모든 RDBMS 에서도 마찬가지이다.

 

4)  INTENT(I)

공유 잠금 OR 배타적 잠금이 걸린 특정 데이터영역에 표시하는 알림간판이라고 생

각하면 된다. 의도적 잠금이라고 하며 다른 INTENT 잠금 들과 호환된다.

 

의도적 잠금은 SQL Server가 리소스에 대해 공유 잠금 또는 배타적 잠금을 얻으려

할 때 같이 발생한다.

예를 들어, 의도적 공유 잠금(IS)을 테이블 수준에서 설정하려고 한다면, 이것은 해

당 트랜잭션이 해당 테이블의 페이지 또는 행에 대해 공유 잠금을 설정하려고 한다

는 것을 의미한다. 이렇게 테이블 수준에서 의도적 잠금을 걸면, 이후에 다른 트랜잭

션이 해당 페이지를 포함하는 테이블에 대해 배타적 잠금을 얻을 수 없게 된다.

 

SQL Server는 테이블 수준에서만 의도적 잠금을 확인하여, 트랜잭션이 해당 테이블

에 대해 잠금을 얻을 수 있는지 확인하므로 의도적 잠금을 사용하면 성능이 향상된

다. 왜냐하면, 직접 테이블의 모든 행 또는 페이지 잠금을 확인할 필요가 없기 때문

이다.

 

- 의도적 배타적 잠금(IX)

잠금을 걸려는 트랜잭션이 각 리소스계층(테이블,페이지,행등...)에 대해 X 잠금을

설정하여 계층의 아래쪽에 있는 일부 리소스를 수정하려 하는 것을 말한다. IX는 IS

의 상위 집합이 된다.

 

- 공유 및 의도적 배타적 잠금(SIX)

잠금을 걸려는 트랜잭션이 각 리소스계층에 대해 IX 잠금을 설정하여 계층의 아래쪽

에 있는 모든 리소스에 대해서는 읽기 작업을 하고, 일부 리소스에 대해서는 수정작

업을 하려고 하는 것을 말한다.

최상위 수준 리소스에서는 동시 IS 잠금이 허용된다.

예를 들어, 테이블에 대한 SIX 잠금은 테이블에 대해 SIX 잠금을 설정하여 동시 IS

잠금을 허용하고, 수정 중인 페이지에 IX 잠금을 설정하고 수정된 행에 대해서는 X

잠금을 설정한다. 각 리소스 당 한 번에 하나의 SIX 잠금을 설정할 수 있으므로 다른

트랜잭션이 테이블 수준에서 IS 잠금을 얻어 계층 아래쪽에 있는 리소스를 읽을 수

있게 된다. 그라나, 그 상황에서 다른 트랜잭션이 리소스를 수정할 수는 없게 된다

 

5) SCHEMA((Sch)

- Sch-M (Schema Manipulation)

스키마를 변경하는 작업 즉, DDL 문 실행 시에 SQL Server 가 SCHEMA 자체에 대

해서 건다. 이 잠금은 모든 잠금에 대해서 배타적이며, 어떤 작업도 허락하지 않는

다. 그도 그럴것이 데이터베이스의 구조를 변경하는 누군가 그 데이터베이스 내의

데이터를 엑세스해서는 안될 것이기 때문이다. 이것을 스키마 변경 잠금이라고 한

다.

- Sch-S (Schema Stability)

쿼리문 컴파일 시에만 발생한다. S or X 와 호환된다. 스키마 안정성(Sch-S) 잠금은

의도적 잠금 등 다른 트랜잭션 잠금을 차단하지 않는다. 따라서 쿼리가 컴파일되는

동안에도, 테이블에 대한 의도적 잠금을 포함하여 다른 트랜잭션을 계속 실행할 수

있다. 그러나 해당 테이블에서의 DDL 작업은 수행할 수는 없다.

 

4. TRANSACTION ISOLATION LEVEL (잠금수준)

 

잠금은 그 레벨에 대해서 SQL-92 표준을 가지고 있다. 이것은 SQL Server 에만 적용되
는 것이 아니고, 모든 RDBMS에서 자신들의 제품에 맞도록 적용하는 기준이다.

잠금수준의 표준은 네 가지가 있으며 SQL Server 2000 은 네 가지 모두를 지원한다.

오라클은 그 중 두 수준만을 지원한다.

이러한 잠금 수준은 트랜잭션 고립수준, 혹은 트랜잭션 격리수준 혹은

TRANSACTION ISOLATION LEVEL 이라고 부른다.

 

다음은 잠금 수준에 대한 내용을 설명한 것인데, 그 수준은 다음과 같이 레벨을 가진다.

오른쪽으로 갈수록 잠금의 정도가 심함을 나타낸다.

READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ <

SERIALIZABLE

 

실제로 , 사용자가 제어할 일은 그렇게 많지 않다.

 

1) READ UNCOMMITTED

READ UNCOMMITTED 수준은 잠금의 4가지 레벨 중 가장 낮은, 즉 가장 느슨한 단

계의 잠금 수준이다. 이 수준은 특정 트랜잭션에서 배타적 잠금을 설정하고 있어도,

해당 데이터를 볼 수는 있게 한다.

물론 해당 리소스에 대한 수정작업은 모든 잠금 수준에서 불가능하다.

READ UNCOMMITTED는 배타적 잠금이 설정된 리소스(데이터)라고 하더라도, 볼

수(SELECT) 있게 하는, 잠금 중 가장 느슨한 단계이다. 배타적 잠금을 공유 잠금과

호환되게 하는 것이라고 보면 되겠다.

 

2) READ COMMITTED

 

READ COMMITTED 는 말그대로, COMMIT 된 트랜잭션만을 읽자는 것이다.

이것은 잠금을 건 트랜잭션에서의 입장이 아니다. 잠금을 건 트랜잭션에 대해서

SELECT 하려는 또 다른 트랜잭션에서의 입장이다. 현재는 해당 트랜잭션에 대해

잠금이 걸려 있으면 해당 잠금의 종류에 따라 볼 수도 있고 보지 못할 수도 있다.

공유 잠금에 대해서는 볼 수 있으며, 배타적 잠금이 걸려 있으면 볼 수 없는것이

현재 상태이다. SELECT 한다는 것은 공유 잠금이고, 공유 잠금은 공유 잠금과는

호환되지만 배타적 잠금에 대해서는 호환되지 않기 때문이다.

이것이 READ COMMITTED 의 뜻이다. 배타적 잠금에 대해서 공유 잠금을 허용하지

않겠다는 뜻이다.

 

3) REPEATABLE READ

 

읽는 데이터의 일관성에 관한 얘기이다. 잠금이 설정되어 있을 때, 트랜잭션 시작 전

과 시작 후의 데이터를 동일하게 읽게 함으로써, 읽는 데이터의 일관성을 보장하는

방법이 된다.

REPEATABLE READ 수준은 트랜잭션 진행 중인 개체에 엑세스해서 데이터를 읽으

려고 할 때, 읽고자 하는 데이터에 일어나고 있는 변경이 성공될지 취소될지 불확실

한 상태에서, 확실하게 트랜잭션이 완료되기 전까지는, 변경전의 데이터를 읽게 함

으로써 데이터의 일관성을 보장해 주는 기법이다.

 

4) SERIALIZABLE

 

데이터의 범위에 대한 입력작업에 관한 이야기이다.

한 세션에서 특정 범위의 데이터를 읽고 있는 경우에 , 다른 세션에서 읽고 있는  특

정 범위내에 포함되는 데이터를 입력하는 경우에 입력을 하지 못하도록 한다.