Transaction & Isolation Level

Transaction

간단히 말해 일의 묶음 단위.

DB에서 한꺼번에 수행되어야하는 데이터 변경 등의 작업 단위

 

Transaction이 필수로 만족해야하는 성질 (ACID)

1. Atomicity (원자성)
   • 트랜잭션의 연산은 DB에 모두 반영 혹은 전혀 반영되지 않음. 하나라도 실패하면 트랜잭션 모두 취소.
2. Consistency (일관성)
   • 트랜잭션이 성공적으로 완료되면 언제나 일관성 있는 DB 데이터를 유지하는 것.
   • 무결성 제약과 같은 DB의 고정요소는 트랜잭션 수행 전과 후의 상태가 같아야 함.
3. Isolation (독립성, 격리성)
   • 둘 이상의 트랜잭션이 동시에 병행 실행되는 경우, 하나의 트랜잭션 실행중에 다른 트랜잭션이 끼어들지 못하도록 보장하는 것.
   • 수행중인 트랜잭션의 데이터 참조 가능 여부는 Isolation Level에 따라 다름.
4. Durablility (영속성, 지속성)
   • 성공적으로 완료된 트랜잭션의 결과는 영구적으로 반영되어야 함.

Transaction 종료

• COMMIT: 트랜잭션이 성공적으로 완료된 경우
• ROLLBACK: 트랜잭션에서 오류 발생 혹은 사용자의 트랜잭션 취소.
                 트랜잭션 시작할 때의 상태로 데이터 리턴, 트랜잭션에서 사용하던 리소스 잠금 해제

 

Lock

DB 리소스에 대한 잠금. SQL Server는 Lock을 사용하여 트랜잭션의 무결성 및 데이터베이스의 일관성을 유지한다.

동일한 리소스에 대해 여러 트랜잭션이 동시에 접근 가능할 때, 데이터의 일관성을 보장하기 위해 접근 허용범위를 제한하는 것이다. Lock을 통해 해당 리소스를 사용하고있다는것을 알리고, 다른 트랜잭션의 접근을 제한하거나 허용한다.

 

Lock의 종류

공유 잠금 (Shared Lock)	배타적 잠금 (Exclusive Lock)
- Select	- Insert - Update - Delete

1. 공유 잠금 (Shared Lock, S-Lock)
   • 데이터를 읽는(변경하지 않는) 작업에서 사용
   • 기본적으로 데이터를 읽자 마자 바로 잠금 해제
     • Isolation Level:REPEATABLE 이거나 HOLDLOCK 힌트를 주면 트랜잭션 종료까지 유지
   • 다른 공유 잠금과 호환 가능
2. 배타적 잠금 (Exclusive Lock, X-Lock)
   • 데이터를 변경하는 작업에 사용
   • 배타적 잠금이 설정되면 설정한 하나의 트랜잭션만이 리소스를 점유. 다른 트랜잭션은 읽기, 수정 모두 제한.
   • 다른 모든 잠금과 호환 불가능
3. 업데이트 잠금 (Update Lock, U-Lock)
   • 데이터 변경 시 배타적 잠금을 걸기 전 데이터 영역을 읽기 위해 사용. 리소스에 걸리 잠금을 미리 체크하여 데드락을 방지하기 위해 사용
   • 업데이트 잠금을 사용하는 이유 - 예제와 함께 설명
     1. 업데이트 작업 시 처음에 레코드를 읽음 = 리소스에 대한 공유 잠금
     2. 행을 수정하기 위해 배타적 잠금으로 변환
     3. 이를 두개의 트랜잭션에서 동시에 수행하면 공유 잠금은 서로 공유되고, 두 트랜잭션 모두 배타적 잠금으로 변환하기 위해 공유잠금이 해제되길 기다림. -> Deadlock 발생
     4. Deadlock 발생을 방지하기 위해 다른 업데이트 잠금과 공유되지 않는 업데이트 잠금을 사용해 하나의 리소스에 대해 하나의 트랜잭션만 수정을 시도할 수 있도록 함.
   • 공유 잠금과 호환 가능
4. 의도적 잠금 (INTENT Lock, I-Lock)
   • 공유 잠금 또는 배타적 잠금이 걸린 데이터 영역에 표시되는 알림 간판
   • 트랜잭션이 리소스에 대해 잠금을 얻을 수 있는지 상위 범위에서 확인 가능하므로 성능 향상.
   • 다른 의도적 잠금과 호환 가능

 

Lock의 호환성

Requested mode	IS	S	U	IX	SIX	X
Intent shared (IS)	Yes	Yes	Yes	Yes	Yes	No
Shared (S)	Yes	Yes	Yes	No	No	No
Update (U)	Yes	Yes	No	No	No	No
Intent exclusive (IX)	Yes	No	No	Yes	No	No
Shared with intent exclusive (SIX)	Yes	No	No	No	No	No
Exclusive (X)	No	No	No	No	No	No

 

Lock의 단위

• KEY(Key): 인덱스가 있을 때 행 잠금 단위
• PAG(Page): 데이터 페이지, 인덱스 페이지
• TAB(Table): 전체 테이블

 

 

 

Deadlock 개념과 예제

세션의 Lock 확인

Select 구문에서 전체가 아닌 인덱스에 걸린 열에 대한 조회

 

 

 

Isolation Level & Read Phenomena

잠금 수준에 따른 트랜잭션 연산과 발생 할 수 있는 읽기 이상 현상

아래로 내려갈수록 높은 잠금 수준.

각 트랜잭션 A, B가 아래의 잠금 수준이라 할 때, 

 

READ UNCOMMITTED

• A 트랜잭션에서 수정되었지만 아직 커밋되지 않은 행을 B 트랜잭션에서 수정된 데이터로 읽을 수 있음.
• 이 때 A 트랜잭션이 이후에 ROLLBACK 되면 B 트랜잭션에서 읽은 데이터는 잘못된 데이터가 됨. 이 현상을 'Dirty Read'라 함

READ COMMITTED (SQL Server Default)

• A 트랜잭션에서 수정되었지만 아직 커밋되지 않은 행을 B 트랜잭션에서 읽을 수 없음.

REPEATABLE READ

• A 트랜잭션에서 수정되었지만 아직 커밋되지 않은 행을 B 트랜잭션에서 UPDATE는 불가능하지만 INSERT는 가능.

SERIALIZABLE

• A 트랜잭션이 완료될 때 까지 B 트랜잭션은 해당 데이터에 대해 수정 및 입력 불가

 

 

출처

트랜잭션 잠금 및 행 버전 관리 안내서

Transaction Locking and Row Versioning Guide - SQL Server

트랜잭션 잠금 수준

SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - SQL Server

데이터베이스 Isolation Level

데이터베이스 Isolation Level