MSSQL : 트랜잭션 격리수준

 

현재 다니고 있는 회사에서는 대부분의 비즈니스 로직을 프로시저로 처리한다.

 

그때 각각의 프로시저마다 항상 삽입되는 두 가지의 구문이 있는데 바로

1) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
2) SET NOCOUNT ON;

이다.

프로시저마다 쓰길래 사용하긴 하는데, 왜 저렇게 해야하는지 알아야 하기에.. 정리해보았다.

 

본론으로 들어가기 전 간단하게 말하자면 ,
1)은 트랜잭션의 ACID  중 격리성(Isolation)에 대한 내용

2)는 프로시저 성능향상에 대한 내용이다. 

길어질 것 같아 1과 2를 분리해 두개의 글로 적을 예정이다 .

---

트랜잭션 격리 수준(Transaction Isolation Level) ?

SQL Server에서 잠금(Lock)을 어떤 식으로 적용할 것인가에 관한 얘기이다.

주로 SELECT시 Lock을 어떻게 걸 것이냐에 따라 격리 수준이 구분지어진다.

 

트랜잭션 격리 수준의 필요성 ?

• 데이터베이스는 ACID 같이 트랜잭션이 원자적이면서도 독립적인 수행을 하도록 한다.
• 그래서 Locking 이라는 개념이 등장한다.
  
  (트랜잭션이 DB를 다루는 동안 다른 트랜잭션이 관여하지 못하게 막는 것)
• 하지만 무조건적인 Locking으로 동시에 수행되는 많은 트랜잭션들을 순서대로 처리하는 방식으로 구현되면 DB의 성능은 떨어지게 된다.
• 반대로 응답성을 높이기 위해 Locking 범위를 줄인다면 잘못된 값이 처리 될 여지가 있다.
• 그래서 최대한 효율적인 Locking 방법이 필요하다.

---

트랜잭션 격리 수준의 종류
1. READ COMMITTED 
2. READ UNCOMMITTED

3. REPEATABLE READ

4. READ COMMITTED SNAPSHOT

5. SNAPSHOT 

 

1. READ COMMITTED
다른 사용자의 작업이 완료된 데이터만 조회하거나 변경할 수 있다.
다른 사용자가 변경 중이라면 SELECT를 포함한 어떤 작업도 할 수 없고, 대기 하다가 다른 사용자가 비로소 작업이 완료 되면 변경된 결과로 데이터를 조회 하거나 변경 작업이 진행된다.
① 일반적으로 SQL Server 설치 시 기본 설정이며, 다른 사용자가 작업이 완료될 때까지 계속 대기 상태에 있다.
② ORACLE의 경우, 다른 사용자가 데이터를 수정하고 있다 하더라도 변경 전의 데이터로 대기 없이 조회된다.
③ 정확한 데이터를 제공할 수 있지만 대기시간이 길어지는 단점이 있다.

 

2. READ UNCOMMITTED
트랜잭션이 완료되지 않은 데이터도 조회 가능하다.
다른 세션에서 해당 데이터가 UPDATE중이어도 세션2는 대기 없이 SELECT 명령이 실행되는 대신
트랜잭션 내에서 UPDATE작업이 완료되었다면 아직 트랜잭션이 완료되지 않았다고 하더라도 그 당시의 데이터가 출력 된다. (데이터의 정확도는 낮지만 대기 없이 빠른 속도로 조회됨 )

3. REPEATABLE READ
트랜잭션 내에서 SELECT 된 데이터는 내부적으로 LOCK이 설정 되어 다른 세션에서 SELECT는 가능하지만 UPDATE나 DELETE해야 할 경우 대기 상태가 된다. 하지만 INSERT 작업은 언제든지 수행할 수 있고 그 결과는 모든 트랜잭션에서 바로 확인 가능하다.

4. SERIALIZABLE
SERIALIZABLE은 가장 강력한 격리수준을 갖고 있으며,
한 세션에서 SELECT 된 데이터는 다른 세션에서 SELECT 할 수는 있지만 UPDATE, DELETE 뿐만 아니라 관련된 키 값의 INSERT작업을 할 경우 대기가 발생된다.
(현 세션과 관련 없는 데이터는 아무런 문제없이 SELECT, INSERT, UPDATE, DELETE 등 모든 작업이 정상적으로 수행된다.)

5. READ COMMITTED SNAPSHOP
ORACLE DB의 기본 트랜잭션 모드와 매우 유사하다.
트랜잭션 중에 변경된 정보는 다른 사용자가 변경된 내용이 조회 되지 않고 별도의 대기 현상도 없기 때문이다.
READ COMMITTED SNAPSHOP 격리 수준은 데이터베이스 시스템 전체에 적용 하는 것이 아니라 생성한 데이터베이스 단위로 변경해야 한다. 만약 변경 하고자 하는 데이터베이스에 접속된 사용자가 있다면 접속이 중단 될 수 있도록 단일 사용자 모드에서 변경 후 속성을 변경하여야 한다.
READ COMMITTED SNAPSHOP 격리 수준의 변경은 데이터베이스의 속성 정보에서 변경 가능 하지만 쿼리 창에서 명령어를 실행할 수도 있다.

6. SNAPSHOP
처음 조회된 데이터가 다른 세션에서 이미 변경 또는 삭제 처리가 되고 트랜잭션이 완료되었다고 하더라도 다시 조회 하였을 경우 동일하게 데이터가 조회되는 특성이 있다. 하지만 이 정보를 변경 할 경우에는 “업데이트 충돌로 인해 스냅숏 격리 트랜잭션이 중단 되었습니다”라는 메시지와 함께 오류가 발생된다. SNAPSHOP 격리 수준은 데이터베이스 시스템 전체에 적용 하는 것이 아니라 생성한 데이터베이스 단위로 변경해야 한다. 만약 변경 하고자 하는 데이터베이스에 접속된 사용자가 있다면 접속을 중단할 수 있도록 단일 사용자 모드에서 변경 후 속성을 변경하여야 한다.
SNAPSHOP 격리 수준의 변경은 READ COMMITTED SNAPSHOP 격리수준과 마찬가지로 데이터베이스의 속성 정보에서 변경 가능 하지만 쿼리 창에서 명령어를 실행 할 수도 있다.