[Database] DB Transaction Isolation Level(트랜잭션 격리 수준) 완전타파

이번 시간에는 DB Transaction Isolation Level에 대해서 알아보겠습니다.

 

DB Transaction Isolation Level이란 특정한 트랜잭션이 다른 트랜잭션에 변경한 데이터를 보게 할 지 결정하는 것입니다.

 

예를 들어, 같은 DB를 사용하는(DBA, Software Engineer, 시스템 사용자..) 사람이 특정 데이터를 조회하려고 하는데, DB Transaction Isolation Level의 설정때문에 데이터를 조회하는데 특정 데이터에 Lock이 걸려 한 없이 기다리고 있을 수도 있고, 커밋되지 않은 데이터를 읽게 설정해 잘못된 데이터를 읽을 수도 있습니다.

 

따라서! 실무에서는 DB Transaction Isolation Level을 올바르게 설정하고 이해하는 것이 무엇보다 중요합니다.

 

이제부터 본격적으로 알아보겠습니다. 

 

DB Transaction Isolation Level에는 4단계로 나뉩니다.

 

1. ReadUncommitted (커밋되지않은 읽기)

2. ReadCommitted (커밋된 읽기)

3. Repeatable Read (반복 가능한 읽기)

4. Serializable (직렬화 가능)

 

1번으로 갈 수록 고립레벨이 낮아지고, 4번으로 갈 수록 고립레벨이 높아집니다. 고립레벨이 낮아진다는 건 동시성(DB 사용성)을 높일 수 있지만 무결성은 낮아집니다. 반대로 고립레벨이 높아진다는 건 동시성(DB 사용성)은 낮아지지만 무결성은 높아집니다.

 

그렇다면 왜 동시성과 무결성에 영향을 주는지 알아보겠습니다.

 

1. ReadUncommitted (커밋되지않은 읽기), 커밋되지 않은 데이터도 읽을 수 있는 격리 수준, ReadUncommitted단계에서 데이터 select시 공유 잠금을 걸지 않는다.

 

공유 잠금을 걸지 않는다는 건, 업데이트 되지 않은 데이터도 읽을 수 있다는 것이고 무결성을 깨트릴 수 있다는 의미입니다.

 

MS-SQL DB 문법으로 알아보겠습니다.

 

-------------------------------------- ex) 세션2에서 commit되지 않은 데이터도 가져오는 경우

-- 세션1 

begin tran

update tb_vp_plan_order

set DELI_PLAN_CFN_YN = '0'

where DELI_DY = '20220827'

and DELI_SEQNC = '6'

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='1'

 

-- 세션2

select * from tb_vp_plan_order

with (READUNCOMMITTED)

where DELI_DY = '20220827'

and DELI_SEQNC = '6'

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='0'

 

이렇게 되면 세션2에서 커밋되지 않은 데이터도 읽어들여, 잘못된 조회를 할 수 있습니다. 

MS-SQL에서는 begin tran키워드를 작성 후 commit이나 rollback을 하지 않으면 트랜잭션이 반영되지 않기 때문입니다. 

 

2. ReadCommitted, 커밋된 데이터만 읽을 수 있는 격리 수준, ReadCommitted단계에서 데이터 select시 공유 잠금을 검.

 

- 공유 잠금을 걸게 되면, 공유 잠금을 건 데이터에 다른 공유 잠금은 허용이 되지만 배타적 잠금은 허용이 되지 않음. 

- 공유 잠금이 걸려 있는 데이터에 배타적 잠금이 걸리지 않기 때문에, 

- 쉽게 말해 조회하고 있는 데이터를 다른 세션에서 (insert, update, delete)하는 경우 데이터의 일관성이 없어지기 때문에 

- 전 DB isolation 단계에 비해 동시성은 줄어들지만 무결성은 높일 수 있음.

 

-------------------------------------- ex) 세션2에서 block

-- 세션1 

begin tran

update tb_vp_plan_order

set DELI_PLAN_CFN_YN = '1'

where DELI_DY = '20220827'

and DELI_SEQNC = '6'

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='1'

 

-- 세션2

select * from tb_vp_plan_order

where DELI_DY = '20220827'

and DELI_SEQNC = '6'

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='1'

 

세션1에서 begin tran으로 update하고 commit하거나 rollback하지 않으면(트랜잭션을 끝내지 않으면) 배타적 잠금(쓰기 잠금)이 걸리고, 세션2에서 배타적 잠금이 걸린 데이터를 select하면 공유 잠금(읽기 잠금)을 걸려고 하기 때문에 블럭됨.

블럭되고 난 후 commit이나 rollback(트랜잭션을 끝내는 것)을 하게 되면 block상태에서 block이 풀리고 데이터 select이 자동으로 됨.

 

3. RepeatableRead 반복가능한 읽기 격리 수준, 한 번 select 됐던 row에 대해서는 update나 delete가 불가능하다. (insert는 가능)

 

select 했을 때 데이터에 걸리는 공유 잠금이 트랜잭션이 끝날 때 까지 유지된다는 의미이다. 

 

-- 세션1

begin tran

select * from tb_vp_plan_order

with (REPEATABLEREAD)

where DELI_DY = '20220827'

and DELI_SEQNC = '6'

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='0'

 

-- 세션2

update tb_vp_plan_order

set DELI_PLAN_CFN_YN = '1'

where DELI_DY = '20220827'

and DELI_SEQNC = '6'

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='1'

 

세션1에서 begin tran으로 select 하고 세션2에서 update하면 해당 데이터는 block된다.  begin tran으로 select 했을 때 해당 데이터에 공유 잠금이 걸리고, update를 하려고 하면 배타적 잠금을 걸려고 하는데, 공유 잠금에는 배타적 잠금이 걸리지 않으므로 block이 걸린다. 

 

4. Serializable 직렬화 격리 수준, select 했을 때 select된 row에 ranged 공유 잠금이 걸리고 트랜잭션 종료시까지 유지되기 때문에, 트랜잭션 종료시까지 해당 범위에 대해서 일체의 update, insert, delete가 차단되고, 따라서 동일한 select 결과 재현이 보장된다.

- select 했을 때 데이터에 걸리는 공유 잠금이 트랜잭션이 끝날 때 까지 유지된다는 의미이다. 

 

-- 세션1

begin tran

select * from tb_vp_plan_order

with (SERIALIZABLE)

where DELI_DY = '20220827'

and DELI_SEQNC <= 6

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='1'

 

select * from tb_vp_plan_order

with (SERIALIZABLE)

where DELI_DY = '20220827'

and DELI_SEQNC = 8

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='1'

 

-- 세션2

update tb_vp_plan_order

set DELI_PLAN_CFN_YN = '1'

where DELI_DY = '20220827'

--and DELI_SEQNC > '6'

and CENTER_CD = '985'

and DELI_PLAN_CFN_YN ='0'

 

 

추가로 MS-SQL에서 사용되는 유틸 쿼리들입니다.

 

-- DB의 세션 확인 쿼리

 

SELECT A.spid

, A.login_time

, A.loginame

, A.last_batch

, A.status

, A.program_name

, A.cmd

, B.client_net_address

FROM sys.sysprocesses           A 

JOIN sys.dm_exec_connections B

ON A.spid = B.session_id

 

-- DB의 DB Isolation Level 확인 쿼리

SELECT

    CASE transaction_isolation_level 

        WHEN 0 THEN 'Unspecified' 

        WHEN 1 THEN 'ReadUncommitted' 

        WHEN 2 THEN 'ReadCommitted'  

        WHEN 3 THEN 'Repeatable'  

        WHEN 4 THEN 'Serializable' 

        WHEN 5 THEN 'Snapshot' 

    END AS TRANSACTION_ISOLATION_LEVEL

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID

 

DB Isolation Level은 실제 운영 환경에서 중요한 개념이므로 알아두고 넘어가도록 합니다!