[MSSQL] LOCK

 

 

1. 잠금(LOCK)

 

잠금(lock)이란 다른 트랜잭션에 의한 충돌이나 불완전한 접근을 방지하면서, 

 

데이터를 보호하기 위해 트랜잭션이 소유하는 관리 리소스를 말한다.
 

2. 잠금 모드와 호환성

트랜잭션과 동시성을 이해하기 위해서는 두 가지 잠금 모드인 

 

배타적(exclusive) 모드와 공유(shared) 모드에 익숙해져야 한다.
 
데이터를 변경하는 경우(INSERT,UPDATE,DELETE)

 

트랜잭션의 수준에 상관없이 데이터 리소스에 대해 배타적 잠금(X-LOCK)을 요청하게 된다.

 

요청이 승인되면 배타적 잠금은 트랜잭션이 끝날 때까지 유지된다.

단일 구문 트랜잭션에서는 잠금이 하나의 구문이 수행완료 될 때까지만 유지되며, 

 

여러 구문으로 된 트랜잭션에서는 모든 구문이 완료된 후 트랜잭션이 COMMIT TRAN 이나 

 

ROLLBACK TRAN 명령을 통해 끝나기 전까지 잠금이 유지된다.

즉, 데이터 변경 작업을 수행할 때에는 기본적으로 배타적 잠금방식(X-LOCK)이 적용되며,  

 

잠금모드의 변경도 불가능 하며, 

 

잠금이 지속되는 시간(트랜잭션이 종료될때까지)도 변경할 수 없다.

예를들어, 하나의 트랜잭션에 행들을 변경하는 작업이 존재한다면, 

 

트랜잭션이 완료될 때까지는 다른 트랜잭션에서 이 행들을 변경할 수 없다는 것을 의미한다. 

 

그러나 다른 트랜잭션에서 이 행들을 읽을 수 있을지의 여부는 격리수준에 따라 달라진다.
 

 

데이터 읽기 작업(SELECT)에서는 On-premises SQL Server와 

 

SQL 데이터베이스의 기본값이 다르다.

On-premises SQL Server 환경에서 기본 격리 수준은 READ COMMITTED다. 

 

이 격리 수준에서는 데이터를 읽고자 할때에 기본적으로 트랜잭션에서 

 

데이터 리소스에 공유 잠금을 요청하며,

읽기 작업이 완료되자마자 이 리소스에 대한 잠금을 해제하게 된다. 

 

이 잠금 모드를 "공유잠금(S-LOCK)"이라고도 한다. 

 

여러 트랜잭션이 동일한 데이터에 대해 동시에 공유 잠금을 유지할 수 있기 때문이다.

데이터를 변경할 때에는 잠금 모드와 지속 시간을 변경할 수 없었지만, 

 

데이터를 읽을 때에는 격리 수준을 조정함으로써 

 

잠금이 처리되는 방식을 조절할 수 있다.
 

 

SQL 데이터베이스의 기본 격리 수준은 READ COMMITED SNAPSHOT이다. 

 

잠금을 이용하는 대신, 이 격리 방식은 행 버전 기술을 이용한다. 

 

이 격리 수준에서는 읽기 작업에서 공유잠금을 필요로 하지 않는다.

따라서 데이터를 읽기 위해 기다리는 경우가 발생하지 않는다. 

 

실제 예로 설명하자면, READ COMMITTED 격리 수준 내에서는 

 

트랜잭션이 행들의 값을 바꾸는 경우, 

 

트랜잭션이 끝날 때까지는 다른 트랜잭션에서 이 행들을 읽을 수 없다.

이러한 동시성 관리 방식을 "비관적 동시성"(pessimistic concurrency) 방식이라고 한다.

 

이에 비해 READ COMMITED SNAPSHOT 격리 수준에서는 트랜잭션에서 행들을 변경하더라도,

다른 트랜잭션에서 데이터를 읽고자 할 때에는 

 

읽는 구문이 실행되는 시점에 사용 가능한 마지막 커밋된 상태의 행들을 읽을 수 있다. 

 

이러한 동시성 관리 방식을 "낙관적 동시성"(optimistic concurrency) 방식이라고 부른다.
 

 

 

3. 잠금을 설정할 수 있는 리소스 유형

SQL Server는 여러 유형의 리소스에 잠금을 설정할 수 있다. 

 

잠금을 설정할 수 있는 리소스 유형들로는 RID(데이터의 위치를 가리키는) 

 

또는 키(행), 페이지, 개체(테이블), 데이터베이스 등과 같은것이다.

특정 리소스 유형에 대해 잠금을 얻기 위해서는 트랜잭션에서는 

 

먼저 상위 수준의 리소스에 대한 동일한 모드의 의도된 잠금(intent lock)을 얻어야 한다.  

 

의도된 잠금을 먼저 얻어야 하는 이유는 서버에서 데이터 베이스나 테이블 내의

일부 데이터 영역에 이미 공유잠금이나 배타적잠금이 걸려있다는 것을 다른 커넥션에게 알리기 위함이다. 

 

이를 통해 상위 수준에 대한 잠금 요청이 호환되지 않는지를 효과적으로 감지하고, 

 

이러한 잠금이 바로 부여되는 것을 방지한다.
 

SQL Server에서는 우선 가장 낮은 수준의 잠금(행 또는 페이지 잠금 등)을 먼저 설정한다. 

그런다음, 어떤 수준이 되면 낮은 수준의 잠금을 좀 더 높은 수준의 잠금(테이블 잠금 등)으로 승격시킨다. 

예를들어 잠금 승격(lock escalation)은 단일 구문에서 최소 5,000개 이상의 잠금을 얻게 될 때 시도하며, 

이전의 잠금 승격 시도가 실패한 경우에는 매 1,250개의 새로운 잠금에 대해 다시 승격을 시도하게 된다.