눈 처럼 내려와 이슬 되어 방울져 떨어 지다

2011. 10. 21. 11:31

잠금 모드

2011. 10. 21. 11:31 in SQL

MicrosoftSQL Server 데이터베이스 엔진은 동시 트랜잭션이 리소스에 액세스할 수 있는 방법을 결정하는 여러 가지 잠금 모드를 사용하여 리소스를 잠급니다.

여러 트랜잭션(Transaction)이 처리 되어질 때 어느 한 트랜잭션이 처리하는 테이블에 대하여 방해하지 않고 잘 처리하도록 다른 트랜잭션은 접근하지 않고 기다리게 됩니다. 자세히 이야기 하면 한 트랜잭션은 현재 자신이 처리하려고 하는 테이블에 이상없이 처리를 하기 위하여 잠금을 걸어 다른 트랜잭션이 방해하지 못하도록 합니다. 그렇게 되면 다른 트랜잭션은 현재 진행중인 트랜잭션이 처리를 완료하고 해당 테이블에 대하여 잠금을 풀어주면 그제서야 처리를 하게 됩니다. 대부분의 잠금과 해제는 순식간에 이루어집니다. 그래서 실제 사용자는 내부적인 잠금과 잠금 해제애 대하여 부감각해지게 됩니다. 하지만 어느 순간 이러한 잠금과 해제에 있어 문제가 생길 수 있는데, 이렇게 되면 처리가 엄청 늦어지고 심할 때는 시스템이 멈추어버린 듯한 사태가 발생하기도 합니다.

* 잠금이란(Lock).

잠금이란 현재의 트랜잭션이 사용하고 있는 데이터에 대해 다른 트랜잭션의 검색이나 변경을 막아 여러 트랜잭션이 동시에 같은 데이터를 사용 할 수 있도록 하는 것입니다. 즉 데이터의 신호등이라고 할 수 있습니다. 이렇게 보면 잠금이라는 것은 참으로 똑똑하고 유익한 것으로만 생각하게 되는데 잘못된 잠금은 트랜잭션 처리가 느려지는 원인이 될 수 있습니다

다음 표에서는 데이터베이스 엔진에서 사용하는 리소스 잠금 모드를 보여 줍니다.

공유 잠금 (Shared-Lock, S-Lock)

공유(S) 잠금을 사용하면 비관적 동시성 제어 하에서 동시 트랜잭션이 리소스를 읽을(SELECT) 수 있습니다. 리소스에 공유(S) 잠금이 설정되어 있는 동안에는 다른 트랜잭션이 데이터를 수정할 수 없습니다. 트랜잭션 격리 수준을 반복 읽기 이상으로 설정하거나 잠금 힌트를 사용하여 트랜잭션 기간에 대한 공유(S) 잠금을 보유하지 않는 한, 리소스에 대한 공유(S) 잠금은 읽기 작업이 완료되면 바로 해제됩니다. 일반적으로 Select 를 할때 공유 잠금이 발생하며, Select가 완료되는 즉시 공유 잠금은 해제 됩니다.

SELECT 문처럼 데이터를 변경하거나 업데이트하지 않는 작업(읽기 전용 작업)에 사용합니다. 공유 잠금을 형성하여 다른 트랜잭션에 의해 현재의 데이터가 데이터가 변경되지 못하도록 합니다.

업데이트 잠금 (Update-Lock, U-Lock)

업데이트(U) 잠금을 사용하면 일반적인 형태의 교착 상태가 방지됩니다. 반복 읽기 또는 직렬화 가능 트랜잭션의 경우 트랜잭션이 데이터를 읽고, 리소스(페이지 또는 행)에 대한 공유(S) 잠금을 얻은 다음 데이터를 수정하는데 행을 수정할 때는 배타(X) 잠금으로 잠금을 변환해야 합니다. 두 트랜잭션이 리소스에 대해 공유 모드 잠금을 얻은 다음 데이터를 동시에 업데이트하려고 하면 한 트랜잭션이 배타(X) 잠금으로 잠금을 변환하려고 합니다. 한 트랜잭션의 배타 잠금은 다른 트랜잭션의 공유 모드 잠금과 호환되지 않으므로 공유 모드를 배타 모드로 변환할 때는 잠금 대기가 발생합니다. 두 번째 트랜잭션이 해당 업데이트에 대해 배타(X) 잠금을 얻으려고 합니다. 이 경우 두 트랜잭션 모두 배타(X) 잠금으로 변환 중이고 각각 상대 트랜잭션이 공유 모드 잠금을 해제하기를 기다리므로 교착 상태가 발생합니다.

이러한 교착 상태를 방지하려면 업데이트(U) 잠금을 사용합니다. 한 번에 한 트랜잭션만 리소스에 대한 업데이트(U) 잠금을 얻을 수 있습니다. 트랜잭션이 리소스를 수정하면 업데이트(U) 잠금이 배타(X) 잠금으로 변환됩니다.

데이터를 UPDATE 하기 위해 Exclusive Lock을 형성하기 전에 미리 걸어주는 잠금입니다. 즉, "곧 변경할테니까 접근하지 마라!" 라는 의미로 보시면 됩니다.

배타 잠금 (Update-Lock, U-Lock)

배타(X) 잠금을 사용하면 동시 트랜잭션이 리소스에 액세스할 수 없습니다. 배타(X) 잠금을 사용하면 다른 트랜잭션이 데이터를 수정할 수 없습니다. 읽기 작업은 NOLOCK 힌트 또는 READ UNCOMMITED 격리 수준을 사용해서만 수행할 수 있습니다.

INSERT, UPDATE 및 DELETE 등의 데이터 수정 문은 데이터 수정과 읽기 작업을 함께 수행합니다. 해당 문은 먼저 읽기 작업을 수행하여 데이터를 확보한 후 필요한 수정 작업을 수행합니다. 따라서 데이터 수정 문은 대개 공유 잠금과 배타 잠금을 모두 필요로 합니다. 예를 들어 UPDATE 문은 다른 테이블과의 조인이 있는 테이블의 행을 수정할 수 있습니다. 이 경우 UPDATE 문은 조인 테이블에서 읽는 행에 대한 공유 잠금과 업데이트되는 행에 대한 배타 잠금을 함께 요청합니다.

의도 잠금

데이터베이스 엔진에서는 의도 잠금을 사용하여 잠금 계층 구조 아래쪽에 있는 하위 수준 리소스에 설정되는 공유(S) 잠금 또는 배타(X) 잠금을 보호합니다. 하위 수준의 잠금보다 먼저 확보되어 하위 수준에 잠금을 설정하려고 하는 의도를 나타내므로 의도 잠금이라고 합니다.

의도 잠금은 다음과 같은 두 가지 역할을 합니다.

다른 트랜잭션이 상위 수준 리소스를 수정하여 하위 수준 잠금을 무효화하는 것을 방지합니다.
데이터베이스 엔진에서 상위 수준 세분성에서 발생하는 잠금 충돌을 보다 효율적으로 발견할 수 있도록 해 줍니다.

예를 들어 테이블 내의 페이지 또는 행에 대한 공유(S) 잠금이 요청되기 전에 해당 테이블 수준에서 공유 의도 잠금이 요청됩니다. 테이블 수준에서 의도 잠금을 설정하면 이후에 다른 트랜잭션이 해당 페이지를 포함하는 테이블에 대해 배타(X) 잠금을 얻을 수 없습니다. 데이터베이스 엔진은 테이블 수준에서만 의도 잠금을 확인하여 트랜잭션이 해당 테이블에 대해 잠금을 얻을 수 있는지 확인하므로 의도 잠금을 사용하면 성능이 향상됩니다. 이 경우 테이블의 모든 행 또는 페이지 잠금을 확인하여 트랜잭션이 전체 테이블을 잠글 수 있는지 확인할 필요가 없습니다.

의도 잠금에는 내재된 공유(IS) 잠금, 의도 배타(IX) 잠금, 의도 배타 공유(SIX) 잠금이 있습니다.

- 단독(X) (Exclusive Lock)

INSERT, UPDATE, DELETE와 같은 데이터 수정 작업에 사용합니다. 여러 개의 업데이트 작업이 같은 리소스에 대해 동시에 이루어지지 못하게 합니다.데이터 읽기 조차도 못하게 됩니다

- 내재 (Intent Lock)

잠금 계층 구조를 만드는 데 사용합니다. 내재된 잠금의 종류에는 내재된 공유(IS) 잠금, 내재된 단독(IX) 잠금, 공유 및 내재된 단독(SIX) 잠금이 있습니다. 예를 들어 레코드에 공유잠금(S)이 형성되어 있으면 이 레코드를 포함하는 테이블은 내재된 공유잠금(IS)를 형성하여 "레코드에 공유 잠금이 형성되어 있으니 너희들은 나에게 단독 잠금(Exclusive Lock) 걸면 안돼!" 라고 알려주는 것입니다.

스키마 잠금 (Schema Lock)

데이터베이스 엔진은 열을 추가하거나 테이블을 삭제하는 등의 테이블 DDL(데이터 정의 언어) 작업 중에 스키마 수정(Sch-M) 잠금을 사용합니다. Sch-M 잠금이 유지되는 동안에는 테이블에 대한 동시 액세스가 방지됩니다. 즉, 잠금이 해제되기 전까지는 Sch-M 잠금이 모든 외부 작업을 차단합니다.

테이블 잘림 등의 일부 DML(데이터 조작 언어)은 Sch-M 잠금을 사용하여 영향을 받는 테이블에 대한 동시 작업의 액세스를 방지합니다.

데이터베이스 엔진은 쿼리를 컴파일하고 실행할 때 스키마 안정성(Sch-S) 잠금을 사용합니다. Sch-S 잠금은 배타(X) 잠금 등의 트랜잭션 잠금을 차단하지 않습니다. 따라서 쿼리가 컴파일되는 동안 테이블에 대한 X 잠금이 있는 트랜잭션을 포함하여 다른 트랜잭션이 계속 실행됩니다. 그러나 Sch-M 잠금을 획득하는 동시 DML 작업과 동시 DDL 작업은 테이블에서 수행할 수 없습니다.

테이블의 스키마에 종속되는 작업이 실행될 때 사용합니다. 스키마 잠금의 종류에는 스키마 수정(Sch-M)과 스키마 안전성(Sch-S) 두 가지가 있습니다.

대량 업데이트 잠금(BU)
(Bulk Update Lock)

데이터베이스 엔진은 테이블로 데이터를 대량 복사하고 TABLOCK 힌트를 지정하거나 sp_tableoption을 사용하여 table lock on bulk load 테이블 옵션을 설정할 때 대량 업데이트(BU) 잠금을 사용합니다. 대량 업데이트(BU) 잠금을 사용하면 여러 스레드가 데이터를 동시에 같은 테이블로 대량 로드하는 것은 허용하고, 데이터를 대량 로드하지 않는 다른 프로세스가 테이블에 액세스하는 것은 방지할 수 있습니다.

데이터를 테이블로 대량 복사하는 경우와 TABLOCK 참고가 지정된 경우 사용합니다.

키 범위 잠금

키 범위 잠금은 직렬화 가능 트랜잭션 격리 수준을 사용하는 동안 Transact-SQL 문에서 읽는 레코드 집합에 암시적으로 포함된 행 범위를 보호합니다. 키 범위 잠금은 가상 읽기를 방지합니다. 행 간에 키 범위를 보호하면 트랜잭션이 액세스하는 레코드 집합에 대한 가상 삽입이나 가상 삭제도 방지됩니다.

* 잠금의 단위

모든 트랜잭션이 항상 똑같은 잠금을 형성하지는 않습니다. 각각의 트랜잭션은 자신의 처리를 안전하게 할 수 있는 범위에서 가장 적은 잠금을 걸게 됩니다. 예를 들어 백만개의 레코드중에서 단 하나의 레코드만을 수정하는 트랜잭션이 테이블 전체에 잠금을 형성하는 것은 배보다 배꼽이 큰 경우가 되어 쓸데없는 낭비를 낳게 됩니다. 이런 경우 SQL Server는 대상이 되는 그 하나의 레코드에만 잠금을 형성하게 됩니다. 물론 해당 테이블에는 테이블의 일부인 한 레코드가 현재 잠금이 걸린 상태이므로 다른 트랜잭션이 잠금을 걸지 못하도록 Intent 잠금을 걸게됩니다.

트랜잭션은 다음과 같은 단위로 잠금을 형성하게 됩니다.

잠금단위	내용
RID	테이블에 있는 한 행을 잠그기 위한 행 ID입니다.
키(Key)	인덱스에 있는 행 잠금입니다.
페이지(Page)	8킬로바이트(KB) 데이터 페이지 또는 인덱스 페이지입니다.
익스텐트(Extent)	인접한 여덟 개의 데이터 페이지 또는 인덱스 페이지 그룹입니다.
테이블(Table)	모든 데이터와 인덱스가 포함된 전체 테이블입니다.
데이터베이스(DB)	데이터베이스입니다.