[MSSQL] 11.LOCK(락)과 동시성(병행)제어

동시성은 여러 Transaction이 동시에 동일한 Data에 접근하는 것을 말합니다.

Database에서는 이렇게 병행수행 되고 있는 Transaction들이 서로의 연산들을 간섭하지 않도록 하기 위해서 병행구행 제어기법(Concurrency Control)을 제공하고 있습니다. 이러한 문제들을 다루는 기본적인 기법이 바로 Lock입니다.

 

 Lock를 사용하지 않을 경우 발생할 수 있는 4가지 문제점에 대해서 알아보고, Lock이 적용되는 범위와 Lock

의 유형과 해당 Lock 유형들 사이의 호환성에 대해서 알아보도록 하겠습니다.

실질적으로 Lock이 SQL Server에서 어떻게 사용되고, Lock을 사용하면서 발생될 수 있는 Dead Lock에 대해서 알아보도록 하겠습니다. 

1.1 Concurrency Problems Prevented by Locks

▣ Lost Update

Lost Update는 말 그대로 갱신한 Data를 잃어버리는 것입니다.

 

▣ Dirty Read

말 그대로 잘못된, 즉 쓸모없는 Data를 읽어오는 경우를 말합니다. 

 

▣ Nonrepeatable Read

Nonrepeatable Read는 불일치 분석 문제(inconsistent analysis problem)라고도 합니다.

 

▣ Phantom Read

Phantom Read는 말 그대로 유령값이 나타나는 것을 말합니다.

 

사용자 A가 '김정환'이라는 고객의 정보를 갱신하는 동안, 다른 사용자가 '김정환' 고객의 Data를 사용하지 못하게 한다면, 이러한 문제가 발생되지 않을 것입니다. 이렇게 사용자 A가 특정 Data를 사용하는 동안 다른 사용자가 해당 Data를 변경하지 못하도록 '사용중' 이라는 표기를 하여 놓는 것이 바로 Lock입니다.

 

1.2 Lockable Resources

SQL Server의 Memory와 같이 System의 자원을 사용하게 되기 때문에, 무한정 Lock을 설정할 수 없습니다. 기본적으로 SQL Server에서 걸 수 있는 Lock 수는 2,147,483,647개 또는 SQL Server 메모리의 40%라고 정의를 하고 있습니다.

 

SQL Server에서 Lock을 걸리는 범위는 다음의 표와 같습니다.

개체

설명

RID(Row dentifier)

행식별자. Table 내에서 행(Row) 하나에 대해서 Lock을 걸때 사용합니다.

Key

Index에 있는 Row Lock입니다. 일련의 값으로 이루어진 Transaction에서 Key 값을 보호하기 위해 사용

Page(8kb)

8kb의 Data Page 또는 Index Page

Extent(64kb)

인접한 8개의 데이터 페이지 또는 인덱스 페이지 그룹

Table

모든 Data와 Index가 포함된 전체 Table

Database

전체 Database

 

SQL Server는 자동으로 Lock의 범위를 확장하기도 하는데, 이를 Auto Escalation(자동 상향)

 

1. Lock의 유형

DML 2가지특성:

우선 Data에 변경작업은 가하지 않고, 다만 Data,에 대한 반환작업만을 하는 SELELCT 구문과 Data에 대한 변경작업을 하는 INSERT, UPDATE, DELETE 등으로 나눌 수 있습니다. 

 

Transaction 역시 이러한 DML문장을 이용하므로 Transaction에 대해서 Lock을 거는 것 또한 비슷합니다.

 

종류

구문

특성

Shared Lock(공유 Lock) - Read Lock

SELECT

다른 Lock과 공유됨. 

읽기 전용 작업 

Data 변경은 불가

Exclusive Lock(단독 Lock) - Write Lock

INSERT, 

UPDATE, 

DELETE

다른 Lock과는 공유되지 않음 

쓰기 작업 

동일 Data에 대해서 반드시 하나의 Exclusive Lock만이 걸림

 

2. Managing Locks

 

 발생할 수 있는 문제점에 대해서 Lock을 사용하면, 어떻게 해결할 수 있는지. 그 종류와 어떻게 사용하면 해결되는지에 대해서 알아보고, Lock을 사용하면서 발생될 수 있는 문제

 

SQL Server에서 Lock은 4가지의 Option으로 제공이 됩니다. 이 4가지 Option은 그 Lock의 간섭의 정도(degree of interference)에 따라서 사용되어지게 됩니다.

이를 level of isolation 또는 isolation level(고립화 수준)이라고 합니다.

Session별로 정의가 가능합니다. Session은 사용자가 SQL Server로 연결되어 있는 일정 시간을 말하는 것입니다. 

ANSI - SQL Isolation Level

SQL Isolation Level

발생되는 문제점

Level 0

READ UNCOMMITTED

Dirty Read, 

Nonrepeatable Read, 

Phantom Read

Level 1

READ COMMITTED

Nonrepeatable Read, Phantom Read

Level 2

REPEATABLE

Phantom Read

Level 3

SERIALIZABLE

 

다음은 REPEATABLE READ입니다. REPEATABLE READ는 SELECT한 Data에 대해서 설정된 Shared Lock이 Transaction이 종료되는 시점까지 연장되는 것을 말합니다. 즉 Transaction 안에서 SELECT문을 이용하여 반환된 Data에 대해서 다른 Transaction에서 반환을 요구하는 SELECT문장을 실행하였을 경우에는 앞서 실행되었던 Transaction이 종료될 때까지 기다려야만 합니다. 물론 해당 Data에 대한 변경은 불가능합니다. 

 

즉, 이는 반복적으로 Data를 읽어도 같은 Data를 읽어 올 수 있도록 지원하는 Isolation Level입니다. 따라서 이는 Nonrepeatable Read라는 문제점을 해결하여 줍니다. 

 

마지막으로 SERIALIZABLE은 가장 강력한 Isolation Level입니다. SERIALIZABLE은 SELECT절에 의해서 반환된 Data에 대해서 SELECT나 변경은 물론 INSERT도 불가능하게 하는 Isolation Level입니다. 따라서 이는 Phantom Read의 문제점 까지도 보완을 할 수 있습니다.

 

Transaction이 끝나는 시간까지 기다려야만 합니다. Locking Timeout

 

1. Table-Level Locking Options

대부분의 경우 SQL Server가 자동으로 Lock에 대한 Option을 설정하여 주기 때문에 수동으로 Lock에 대한 Option을 지정할 필요는 없지만, 성능향상을 위해서 사용자가 수동으로 Lock에 대한 Option을 지정할 수 있습니다. 이를 Lock Hint라고 합니다. 

Lock Hint의 Option은 다음과 같이 정의할 수 있습니다.

 

Option

설 명

HOLDLOCK

필요한 테이블, 행 또는 데이터 페이지가 더 이상 필요 없게 되자마자 해제하지 않고 트랜잭션이 완료될 때까지 공유 잠금을 보유합니다. HOLDLOCK은 SERIALIZABLE과 같은 의미입니다.

NOHOLD

공유 잠금을 실행하거나 단독 잠금을 유지하지 않습니다. 이 옵션을 적용하면 커밋되지 않은 트랜잭션이나 읽는 중 롤백된 페이지 집합을 읽을 수 있습니다. 커밋되지 않은 읽기가 가능합니다. SELECT 명령문에만 적용됩니다.

PAGLOCK

주로 단일 테이블 잠금이 취해지는 곳에서 페이지 잠금을 사용합니다.

READCOMMITTED

READ COMMITTED 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법을 사용하여 스캔을 수행합니다.

READPAST

잠겨 있는 행을 건너뜁니다. 이 옵션을 사용하면 다른 트랜잭션이 이러한 행에 대해 잠금을 해제할 때까지 기다리지 않고 다른 트랜잭션에 의해 잠겨 있는 행을 건너뜁니다. 그렇지 않으면 일반적으로 결과 집합에 나타납니다. READPAST 잠금 참고는 READ COMMITTED 격리 수준에서 작동하는 트랜잭션에만 적용되며 행 수준 잠금 뒤만 읽습니다. SELECT 문에만 적용됩니다.

READUNCOMMITTED

NOLOCK과 같습니다.

REPEATABLEREAD

REPEATABLE READ 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법으로 스캔을 수행합니다.

ROWLOCK

행 수준 잠금을 사용합니다.

SERIALIZABLE

SERIALIZABLE 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법으로 스캔을 수행합니다. HOLDLOCK과 같습니다.

TABLOCK

세부적인 행 또는 페이지 수준 잠금 대신 테이블 잠금을 사용합니다. SQL Server는 명령문이 끝날 때까지 이 잠금을 보유합니다. 그러나 HOLDLOCK을 함께 지정했으면 트랜잭션이 끝날 때까지 잠금이 보유됩니다.

TABLOCKX

테이블에 대해 단독 잠금을 사용합니다. 이 잠금을 사용하면 다른 트랜잭션이 테이블을 읽거나 업데이트할 수 없고 명령문이나 트랜잭션이 끝날 때까지 보유됩니다.

UPDLOCK

테이블을 읽는 중 공유 잠금 대신 업데이트 잠금을 사용하며 명령문이나 트랜잭션이 끝날 때까지 보유됩니다. UPDLOCK을 사용하면 다른 트랜잭션이 읽는 것을 차단하지 않고 데이터를 읽을 수 있고 마지막으로 읽은 후 데이터가 변경되지 않으며 나중에 업데이트할 수 있습니다.

 

2. Deadlocks

다른 Transaction에 의해서 해당 Data가 Lock이 걸려서 다른 Transaction이 Lock을 획득하기 위해서 기다리는 것을 Blocking이라고 합니다.

 

SET LOCK_TIMEOUT 구문을 이용하여, 정의된 시간동안 Lock이 걸려서 기다린 경우 자동으로 해당 작업을 취소할 수 있도록 지원을 한다고 말씀을 드렸습니다. 이러한 경우에는 다행이지만, 만약 서로 다른 Transaction이 서로의 Data에 대해서 사용을 요구할 경우에는 서로 해당 Data에 대해서 Lock을 얻을 때까지 무한정 기다리게 됩니다. 이러한 경우를 Deadlock라고 합니다.

 







Deadlock의 예제

 

3. Displaying Locking Information

Transaction과 Lock에 정보는 다음의 4가지 방법

Enterprise Manager ? Management ? Current Activity
sp_lock, sp_who System Stored Procedure
SQL Server Profiler
Windows NT 성능모니터, 성능
DBCC OPENTRAN, DBCC USEROPTION
@@TRANCOUNT
 

spid는 Server Process를, dbid는 Database ID를 ObjID는 Object ID를 IndId는 index 번호를 나타내는 것입니다. 

다음의 Mode Column은 자원에 걸린 Lock의 종류를 나타냅니다.

S : Shared
X : Exclusive
I : Intent
U : Update
Sch : Schema
마지막으로 Status Column은 Lock의 상태를 보여주는 Column입니다.

GRANT : Lock을 얻어서 활성화된 상태
WAIT : Blocking을 당하여 Lock을 얻기 위해서 기다리고 있는 상태
CNVRT : 변환작업 중에 있는 상태
 



sp_lock의 예제