[MSSQL] Transaction & Lock

1. 트랜잭션과 테이블 잠금(Lock)

 

잠금에 대한 전반적인 내용은 MSDN의 잠금 및 행 버전 관리 페이지로부터 찾아볼 수 있다.

 

 

트랜잭션 중일 때 테이블 잠금(Lock)이 발생한다.

잠금(Lock)은 사용자가 데이터를 사용하고 있으면 다른 사용자가 그 데이터를 변형하지 못하도록 한다.

 

따라서, 테이블이 잠기면 이 잠금이 풀릴 때까지 다른 쿼리 수행은 잠금이 풀릴 때까지 대기하게 된다.

 

아래 예제를 살펴보자.

 

-- 트랜잭션이 시작되어 잠금(Lock)이 발생했다
BEGIN TRAN

UPDATE UserTable SET Addr = N'서울' WHERE ID = 'KHD'
UPDATE UserTable SET Addr = N'경기' WHERE ID = 'KKJ'
UPDATE UserTable SET Addr = N'강원' WHERE ID = 'KYM'

-- 아래와 같이 트랜잭션을 종료시키지 않으면, 잠금이 해제되지 않는다
-- COMMIT TRAN

 

COMMIT TRAN이 수행되지 않았기에, 발생한 잠금이 계속 유지되게 된다.

이 후 COMMIT TRAN이 수행되면 잠금이 풀려 다른 쿼리를 수행할 수 있게 된다.

 

참고로, 현재 테이블에서 진행중인 트랜잭션의 수는 아래와 같이 얻을 수 있다.

-- 아래와 같이 @@TRANCOUNT 시스템 함수를 사용한다
SELECT @@TRANCOUNT

 

잠금이 사용되어야 하는 근본적인 이유 중 하나는 "일관성(Consistency)"이다.

 

데이터베이스의 중요한 사항 중의 하나인 일관성은 

"동시에 여러 사용자가 같은 데이터에 접근하려고 할 때" 발생하는 상황을 뜻한다.

 

그렇다면, 같은 사용자가 동시에 데이터에 접근하려고 할 때 어떻게 정보를 제공해 주어야 할까?

 

예를 들어, A라는 데이터를 B로 변경 중이라고 생각해 보자.(즉, 트랜잭션이 진행중...)

이 때 다른 사용자가 그 데이터에 접근하려고 할 때 어떻게 데이터가 보여져야 할까?

또 다른 사용자가 A라는 데이터를 C로 변경하려 한다면, 어떻게 처리해야 할까?

 

이에 대해 논의하려면 격리 수준에 대한 이야기가 필요하다.

 

 

2. 데이터베이스 엔진 격리 수준

 

SQLServer 2008은 아래와 같은 데이터베이스 엔진 격리 수준을 지원한다.

(데이터베이스 엔진 격리 수준은 트랜잭션 격리 수준이라고도 한다)

• READ UNCOMMITTED
• READ COMMITTED : SQLServer 2008 기본 격리 수준
• REPEATABLE READ
• SNAPSHOT : 비표준, MSSQLServer만의 격리 수준
• SERIALIZABLE

 

각 항목들의 자세한 내용에 대해선 아래에서 설명할 것이고,

우선 알아 두어야 할 것은 격리 수준이 위에서부터 아래로 강해진다는 것이다.

 

즉, READ UNCOMMITTED가 가장 약한 격리 수준이고, SERIALIZABLE이 가장 강한 격리 수준이다.

여기서 약한 격리 수준이라는 것은 잠금의 강도가 약함을 의미한다.

따라서, 약하게 잠기기 때문에 데이터 접근에 대한 유연성은 좋아지지만, 데이터의 일관성은 그만큼 떨어진다 할 수 있다.

 

이 격리 수준에 대해 먼저 이야기하는 이유는 격리 수준에 따라 트랜잭션이 다르게 동작하기 때문이다.

트랜잭션 격리 수준을 설정하기 위해서는 아래와 같은 형식을 사용한다.

SET TRANSACTION ISOLATION LEVEL
{
    -- 아래 것들중 하나만 선택이 가능하다
    READ UNCOMMITTED
    READ COMMITTED
    REPEATABLE READ
    SNAPSHOT
    SERIALIZABLE
}

 

여러 사용자가 동시에 하나의 데이터에 접근할 때 발생하는 문제는 다음 세 가지로 분류할 수 있다.

• Dirty read : 커밋되지 않은 데이터 읽기
• Unrepeatable read : 반복되지 않은 데이터 읽기
• Phantom read : 가상 읽기

 

지금부터 각각의 상황이 무엇인지, 

그리고 이러한 상황에 대처하기 위해 어떠한 격리 수준을 사용해야 하는지에 대해 알아보도록 하자.

 

1. Dirty read

 

 

트랜잭션 문서의 챕터4에서 설명하였듯이, 

"데이터 캐시에는 변경이 되었지만, 아직 디스크에는 변경되지 않은 데이터(페이지)"를 더티 페이지라고 한다.

 

이 더티 페이지를 읽는 행위를 Dirty read(더티 리드)라고 한다.

 

이 더티 리드의 문제점은 아직 커밋되지 않은 데이터를 읽어오기 때문에,

더티 페이지를 읽어온 후에 더티 페이지의 데이터가 롤백된다면 이미 읽어온 데이터는 잘못된 데이터가 된다는 점이다.

 

이 더티 리드는 동시성이 좋아서 커밋을 기다리지 않고 값을 읽어올 수 있는 장점은 있지만,

(즉, SELECT문이 TRAN LOCK을 대기하지 않도록 하여 응답성을 높일 수 있지만)

확실히 데이터의 일관성은 떨어지게 된다.

 

이를 수행하려면 아래와 같이 격리 수준을 READ UNCOMMITTED로 설정하여야 한다.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

트랜잭션이 변경한 임시 값(=더티 페이지)이 확실히 커밋될 것이라는 전제가 있을 때만 사용하여야 한다.

 

2. Unrepeatable read

 

반복되지 않은 읽기(Unrepeatable read)란 트랜잭션 내에서 한 번 읽은 데이터가

트랜잭션이 끝나기 전에 변경되었다면, 다시 읽었을 때 새로운 값이 읽히는 것을 의미한다.

 

즉, 두번째 트랜잭션이 같은 행에 여러번 엑세스하며, 이때마다 다른 데이터를 읽을 경우를 생각하면 된다.

 

잠금은 트랜잭션이 발생하게 되면 걸리게 되는데,

SELECT를 수행할 경우에는 공유 잠금(Shared Lock)이 걸리고,

INSERT/UPDATE/DELETE를 수행할 경우에는 배타적 잠금(Exclusive Lock)이 걸리게 된다.

 

현재 SELECT에 의해 공유 잠금이 걸린 상태에서도

다른 사용자의 데이터의 접근을 꼭 막아야 하는 경우가 있다면, REPEATABLE READ 격리 수준을 사용해야 한다.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 

다시 정리하면, "REPEATABLEREAD"는 SELECT에 의한 공유 잠금이 걸려도 데이터의 변경을 막아 줘서,

트랜잭션 중에는 일관되게 데이터를 읽을 수 있게 해 준다.

 

이렇게 하면 누군가 데이터를 읽기(공유 잠금)만 하고 있어도 그 데이터는 변경할 수 없게 되므로,

READ COMMITTED에 비해 일관성이 조금 좋아졌지만, 동시성은 그 만큼 더 나빠졌다고 할 수 있다.

(READ COMMITTED는 변경중인 데이터를 읽을 수 없음을 의미한다)

 

3. Phantom read

 

REPEATABLE_READ 격리 수준에서는 공유 잠금인 상태의 데이터에 대해 변경 불가가 보장되었다.

하지만, 그 데이터를 변경시키지 못할 뿐 새로운 데이터를 추가/삭제하는 것은 가능하다.

이것을 팬텀 읽기(Phantom read, 가상 읽기)라고 부르는 것이다.

 

정리하면, 트랜잭션 중에 없던 행이 추가되어 새로 입력된 데이터를 읽는 것또는

트랜잭션 중에 데이터가 삭제되어 다음 읽기시 이전에 존재하던 행이 사라지는 것을 팬텀 읽기라고 한다.

 

이 팬텀 읽기를 방지하려면, 격리 수준을 SERIALIZABLE 또는 SNAPSHOT 으로 설정하면 된다.

 

1) SERIALIZABLE

 

SERIALIZABLE은 트랜잭션이 수행중일 때 INSERT/DELETE도 제한된다.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

 

REPETABLE_READ가 UPDATE만 금지했던 것이 비해 INSERT/DELETE까지 제한되도록 잠금이 강화된 것이다.

 

2) SNAPSHOT

 

SERIALIZABLE 격리 수준이 트랜잭션이 수행 중일 때 INSERT/DELETE를 원천적으로 봉쇄시킨 방법이라면,

SNAPSHOT은 INSERT/DELETE 수행을 블록시키진 않고, 우선 수행은 하게 해 준다.

 

SNAPSHOT은 트랜잭션이 진행 중인 테이블에 새 데이터를 추가하면, 

그 데이터를 실제 테이블에 적용하는 것이 아니라 우선 tempDB에 적용을 시켜놓고, 

원래 테이블의 트랜잭션이 종료되면 이 tempDB에 적용시켰던 데이터를 다시 원래 테이블로 입력하는 것이다.

 

이것이 SNAPSHOT의 동작 방식이다.

 

또한, SNAPSHOT은 격리 수준 설정 방식이 다른 격리 수준들과는 조금 다르다.

USE tranDB
GO

ALTER DATABASE tranDB SET ALLOW_SNAPSHOT_ISOLATION ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

 

 

지금까지 알아본 격리 수준과 동시성 부작용을 테이블로 정리하면 아래와 같다.

격리 수준	커밋되지 않은 읽기	반복되지 않는 읽기	가상
READ UNCOMMITTED	예	예	예
READ COMMITTED	아니요	예	예
REPEATABLE READ	아니요	아니요	예
SNAPSHOT	아니요	아니요	아니요
SERIALIZABLE	아니요	아니요	아니요

 

READ UNCOMMITTED는 모든 동시성 부작용 문제가 있으며, SERIALIZABLE은 모든 동시성 부작용에서 자유롭다.

하지만, 동시성 부작용이 없을수록 동시 접근성이 떨어지는 문제는 간과하면 안 될 것이다.

 

 

트랜잭션 격리 수준 및 행 관리에 대해 잘 정리되어 있는 글이 있어 공유합니다. 

(출처 : http://egloos.zum.com/sweeper/v/3005129)