DB - 트랜잭션과 트랜잭션 격리성 수준

트랜잭션(이하 Tx)은 데이터 처리를 위한 하나의 논리적 단위이며 하나의 Tx 안에서 두개 이상의 여러 연산일 수 있다.
실제 사용시에는 일반쿼리를 사용하여 만들 수 있지만 주로 Stored Procedure(이하 SP)를 실행할때 길고 연속적인 트랜잭션이 사용되므로 실행시에 다음과 같은 형태로 나타난다.

 

아래의 Tx는 2줄에 불과하지만 2줄이든 100줄이든 3000줄이든 트랜잭션으로 묶여있다면 그것이 하나의 작업단위이다.

 

begin
    select *
    from dbo.player
end

 

 

---

 

 

Transaction의 특징

 

원자성(Atomicity)
Tx은 더 이상 분해가 불가능한 업무의 최소 단위이므로 전부 처리되거나 아예 하나도 처리되지 않아야 한다

 

일관성(Consistency)
일관된 상태의 데이터베이스에서 하나의 트랜잭션을 성공적으로 완료하고 나면 그 데이터베이스는 여전히 일관된 상태여야 한다. 즉, Tx 실행의 결과로 데이터베이스 상태가 모순되지 않아야 한다

 

격리성(Isolation)
실행 중인 Tx의 중간결과를 다른트랜잭션이 접근 할 수 없다

 

영속성(Durability)
Tx가 일단 그 실행을 성공적으로 완료하면 그 결과는 데이터베이스에 영속적으로 저장된다

 

 

---

 

 

Transaction의 격리성

 

아래의 Tx를 실행시 다른 Tx가 Update쿼리에 의해 WEIGHT값을 변경해서, 아래 쿼리의 Update실행문의 결과가 의도한 바와 다르게 나타날 수 있는데 이유는 Tx의 격리성 때문이다.

 

begin
 update dbo.player
 set weight = weight + 35
 where player_id = 2000001
 and weight > 40

 select player_id, player_name, team_id, weight
 from dbo.player
 where player_id = 2000001
end

 

 

낮은단계의 격리성 수준

 

트랜잭션의 격리성은 Lock을 강하게 오래 유지할수록 강화되고, Lock을 최소화 할수록 약화된다.

 

격리성 수준이 낮다는 얘기를 현실상황으로 풀어보자면 탈의실에 문을 잠그지 않아 다른사람에 의해 자신만의 일을 방해받을 상황이 생기는 것과 비슷한 현상이다.

 

낮은단계의 격리성 수준에서는 다음과 같은 현상이 발생한다

 

 

 

---

 

 

 

1. Dirty Read

 

다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것을 말한다.

 

변경 후 아직 커밋되지 않은 값을 읽었는데 변경을 가한 트랜잭션이 최종적으로 롤백된다면 그 값을 읽은 트랜잭션은 비일관 상태에 놓이게 된다

 

아래의 경우에는 weight의 값이 50일 때 Tx2수행중에 Rollback되었음에도 불구하고 Tx1를 실행했을 때의 결과값은 weight를 80으로 조회하게된다.

 

Tx2가 update를 마치고 Rollback문 할 예정이지만 Rollback을 수행하기 전에 Tx1이 select쿼리를 실행하여 롤백 처리될 weight값 80을 조회하게 되는 것이다.

 

그리고 Tx1이 다시 똑같은 쿼리문을 조회한다면 weight값은 50으로 조회될 것이다.

 

Tx1	Tx2
	1. update dbo.player set weight = 80 where player_id = 2000001
2. select weight from dbo.player where player_id = 2000001 조회된 weight : 80
	3. Rollback
weight: 50

 

 

 

---

 

 

 

2. Non-Repeatabla Read

 

한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상을 말한다

 

아래의 경우는 Tx1이 player_id가 2000001인 대상에 대해 weight값이 50인 것일 미리 확인 후, 그 값을 80으로 업데이트 하는 Tx1트랜잭션을 수행하려고 했다.

 

하지만 Tx1실행중 Tx2가 실행되어 weight가 10으로 변경되었기 때문에 Fail문구를 받게 되었다.

 

Tx1	Tx2
1. select weight from dbo.player where player_id = 2000001
	2. update dbo.player set weight = 10 where player_id = 2000001
	3. commit; weight가 10으로 변경
4. update dbo.player set weight = weight + 30 where player_id = 2000001 and weight = 50
5. if exists (     select weight     from dbo.player     where player_id = 2000001     and weight = 80 ) begin     print ‘Success’ end else begin     print ‘Fail’ end
6. commit;

 

 

 

---

 

 

 

3. Phantom Read

 

한 트랜잭션 내에서 같은 데이터를 집계하는 쿼리를 두번 실행했는데, 첫번째 쿼리앱에서 집계되지 않은 유령(Phantom) 레코드가 두번째 쿼리에서 나타나는 현상을 말한다.

 

Tx1이 집계하는 도중 Tx2가 집계중인 테이블에 Insert쿼리를 날렸다. Tx1은 같은 데이터를 집계하려고 했지만 중간에 Tx2로 인해 Tx1은 서로 Count값이 다른 데이터를 집계해서 의도하지 않은 결과가 나오게 된다

 

Tx1	Tx2
1. select count(*) from dbo.PLAYER group by TEAM_ID
	2. insert into dbo.PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION) values (2000099, ‘홍길동’, ‘K6’, ‘FW’)
3. select count(*)   from dbo.PLAYER   group by POSITION

 

 

 

---

 

 

 

Transaction격리성 수준

 

각 격리성마다 장단점이 있으며, 데이터의 쓰임새나 성능고려 등 여러가지 상황에 맞게 트랜잭션 격리성 수준 처리를 하면된다.

 

 

Read Uncommmitted
가장 낮은단계의 격리성 수준이며 트랜잭션에서 처리중인, 아직 Commit이나 Rollback이 되지않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.

 

Read Commmitted
트랜잭션이 커밋되어 확정된 데이터만 다른 트랜잭션이 읽도록 허용함으로써 Dirty Read가 일어날 경우를 방지해준다.

커밋된 데이터만 읽더라도 Non-Repeatable Read와 Phantom Read 현상을 막지는 못한다.

커밋된 데이터를 읽기때문에 트랜잭션 수행중 중간에 커밋된 데이터가 다음 쿼리에 의해 어떻게 바뀔지 모르지만 커밋되었기 때문에 읽어오기 때문이다.

 

Repeatable Read
트랜잭션 내에서 쿼리를 두번이상 수행할 때, 첫번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지해 준다.

쿼리에 있던 레코드가 사라지는 것을 방지할 뿐 트랜잭션 실행중 새 Insert가 일어나는 것을 방지해주지는 못한다.

 

Serializable Read
가장 엄격한 수준의 격리성 수준이며, 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때 첫번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않음은 물론 새로운 레코드가 나타나지 않는다.

즉 트랜잭션 자체에 잠금이 일어나서 다른트랜잭션이 이 레코드에 접근하지 못하며 동시처리능력이 떨어진다.

 

 

---

 

 

 

Transaction 격리성 수준과 비일관성 현상

 

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommmitted	가능	가능	가능
Read Commmitted	불가능	가능	가능
Repeatable Read	불가능	불가능	가능
Serializable Read	불가능	불가능	불가능

 

 

 

 

---

 

 

 

 

Transaction 격리성 사용법

 

격리성 수준은 level뒤에 격리성 수준을 정의해주면 되며 다음과 같이 쓸수 있다.

 

1.
set transaction isolation level read uncommitted
begin
    select *
    from dbo.PLAYER
    group by TEAM_ID
end

2.
set transaction isolation level read committed
begin
    select *
    from dbo.PLAYER
    group by TEAM_ID
end

3.
set transaction isolation level repeatable read
begin
    select *
    from dbo.PLAYER
    group by TEAM_ID
end


4.
set transaction isolation level read serializable
begin
    select *
    from dbo.PLAYER
    group by TEAM_ID
end