[오라클] 10.트랜잭션 처리(Commit, Rollback)

오늘은 트랜잭션 처리를 위한 Commit과 Rollback에 대해서 알아보도록 하자.

트랜잭션 필요성

트랜잭션이라는 키워드를 검색해보면 `업무 실행 단위`, `논리적인 명령 단위.`, `개념상의`라고 하는 말들이 많이 나올것이다. 이러한 설명들은 처음 접하는 사람들에게 와닿지 않는 설명이기에 더 자세하게 풀어서 알아보자.

 

트랜잭션이란? 업무 실행단위 or 논리 명령단위 or 개념상의

 

아래 그림의 업무적인 단위는 계좌 이체와 같은 업무를 말한다. 계좌 이체를 처리하려면 두 가지 이상의 SQL 명령어가 필요하다. 예를 들어, 한 사람의 계좌에서 금액을 출금하고 다른 사람의 계좌에 금액을 입금하는 두 개의 UPDATE 명령이 필요하다. 또한 이 두 작업은 반드시 함께 성공하거나 함께 실패해야 하며, 한쪽만 성공하고 다른 한쪽이 실패해서는 안 된다. 왜냐하면 만약 두 개의 `UPDATE`문이 각각 독립적으로 실행된다면 첫 번째 계좌에서는 금액이 출금되었는데 두 번째 계좌에서는 입금이 되지 않는것 문제가 발생할 수 있다.

 

그래서 여러 개의 명령을 통해서 실행되는 거라면 먼저 필요한 모든 명령이 다 만족이 되는지 확인한 후에 모든 사용자에게 적용되게 해야한다. 그전까지는 사용자 계좌에는 업데이트한 것이 반영이 돼서는 안된다.

 

따라서 업무적인 단위를 트랜잭션으로 묶어 처리함에 있어서 트랜잭션은 반드시 전체가 성공하거나, 전체가 실패해야 한다.

업무적인 단위 : 계좌이체

업무 단위에는 계좌 이체뿐만 아니라 다양한 종류가 존재하며 이벤트 게시글 등록을 예로 들 수 있다. 글을 등록함과 동시에 보너스 포인트를 받는 이벤트가 진행 중인 경우 게시글은 INSERT 하고 포인트는 UPDATE 해야한다.

업무적인 단위 : 이벤트 게시글

트랜잭션 안전 장치

그래서 이러한 업무적인 단위에서 여러 명령어가 하나의 논리적 실행 단위로 묶여서 실행하기 위해서는 모든 명령어가 안전하게 처리 완료되기 전까지는 사용자에게 변경 사항이 영구적으로 적용되어서는 안 된다. 그래서 데이터 변경은 현재 세션의 임시 저장소에서만 테스트하며 다른 사용자에게는 노출되지 않는다.

1.임시저장소에서 테스트

만약 트랜잭션을 처리하는 과정에서 다른 세션이 데이터에 접근해 수정하거나 삭제하면 문제가 발생할 것이다. 그래서 임시저장소에서 테스트하는것 외에 트랜잭션이 완료되기 전까지 다른 사용자가 그 데이터를 건드리거나 변경하지 않도록 막아야 한다.

 

그때 사용하는 게 Lock이다. 트랜잭션이 시작되면 해당 데이터에 LOCK을 걸어 다른 세션이나 사용자가 접근하지 못하도록 막는다. 예를 들어, UPDATE 명령을 실행하는 동안 LOCK이 걸려 있는 상태에서는 다른 세션이 이 데이터를 수정하거나 접근할 수 없다. LOCK이 걸리면 다른 세션은 해당 작업이 완료되고 UNLOCK(잠금 해제)될 때까지 기다려야 한다.

 

트랜잭션이 정상적으로 끝나서 모든 변경 사항을 영구적으로 저장하고자 할 때는 COMMIT 명령어를 사용한다. COMMIT을 실행하면 LOCK이 해제되고, 데이터가 모든 사용자에게 노출되며 영구적으로 저장된다. 반면, 문제가 발생하여 작업을 취소하고 이전 상태로 되돌리고자 할 때는 ROLLBACK 명령어를 사용하여 트랜잭션의 모든 변경을 취소하고 원래 상태로 복원한다.

2.LOCK 걸기

트랜잭션 처리 실습

아래 그림은 트랜잭션의 작동 방식을 확인하기 위해 동일한 사용자 계정으로 접속한 두 개의 세션이다. 동일 계정으로 접속을 해도 사용자 접속 상태에 따라서 세션이 나눠지게 된다.

 

좌측의 세션에서 INSERT 명령을 실행하였을 때 결과가 정상적으로 나오는 것을 확인할 수 있다. 하지만, 우측의 세션에서 동일한 SELECT 명령을 실행했을 때는 결과가 나오지 않는다.

 

이러한 현상이 발생하는 이유는 트랜잭션의 처리 방식 때문이다. INSERT나 UPDATE 명령이 실행되면 데이터는 임시 저장소(임시 테이블스페이스)에 먼저 저장되고, 아직 영구적으로 적용되지 않은 상태이다. 트랜잭션이 완료되어 COMMIT 명령을 실행하기 전까지, 이러한 변경 사항은 해당 세션에만 보이며 다른 세션에서는 볼 수 없다.

동일한 계정으로 접속한 다른 세션은 현 세션에서 COMMIT 될 때 까지 조회되지 않는다.

현재 좌측 세션의 데이터가 임시 저장소에 적용된 상태로 남아 있기 때문에 우측 세션에서는 해당 데이터를 조회할 수 없다. 변경 사항을 모든 세션에서 봐도 문제가 없을 것 같으면 COMMIT 명령을 실행하자 "커밋 완료" 메시지가 출력되었고 이제 우측 세션에서도 동일한 SELECT 명령을 통해 데이터를 정상적으로 조회할 수 있게 된다.

commit하니 다른 세션에서도 조회 가능

만약에 INSERT를 하고 Rollback을 하면 어떻게 될까?

 

'test' 데이터를 INSERT 하고 좌측 세션에서 조회를 해보면 정상 조회된다. 이것은 현재 임시 저장소에 있는것이다.  즉 아직 COMMIT되지 않은 상태이므로 우측에서 조회를 해도 'test' 데이터는 조회되지 않는다.

rollback 실습을 위한 test 데이터 삽입

이 상태에서 'test' 데이터를 취소해야 되겠다 하면 rollback을 사용하자. rollback을 실행하게 되면, 해당 데이터는 임시 저장소에서 제거되어 실제 데이터베이스에 반영되지 않는다. rollback이 완료되니 삽입했던 'test' 데이터가 사라진 것을 확인할 수 있다. 이렇게 해서 우린 쿼리에 대해서 완료를 시킬 것인지 말 것인지 결정할 수 있게 된다.

rollback 실행

이전에 트랜잭션이 시작되면 해당 데이터에 LOCK을 걸어 다른 세션이나 사용자가 접근하지 못하도록 막는다고 설명하였다. 즉 commit하기 전까지는 내가 다루고 있는 내용이 잠긴다는 뜻인데 이것을 실습해보자.

 

트랜잭션을 시작시키기 위해서 좌측 세션에서 name이 dragon인 데이터를 pwd를 111로 바꾸고 name은 오공으로 변경했다. update가 임시 저장소에서 잘 실행되었다.

 

이 상태에서 우측 세션에서도 똑같은 dragon이라고 하는 이름을 갖고 있는 레코드에 대해서 변경을 하는 작업을 하려고 한다. 우측 세션에서는 pwd를 222로 name은 다시 손오공으로 변경해보자. 하지만 쿼리를 실행해보면 우측 세션은 대기상태에 놓이게 된다.

 

왜냐하면 좌측 세션이 현재 commit 혹은 rollback이 안 된 상태이기 때문에 아직 lock이 유지되고 있는 상태이다. 그래서 우측 세션이 대기 상태에 놓이게 된다. 우측 세션의 로딩을 끝내려면 좌측 세션에서 commit이나 rollback을 실행해야 우측 세션의 작업이 진행될 수 있다.

lock이 걸린 좌측 세션

현재 좌측 세션에서 데이터 상태를 확인해보자. 아까 update를 통해 pwd는 111, name은 오공으로 변경된 것이 확인된다.

좌측 세션 데이터 조회

여기서 commit을 실행하면 실행하는 즉시 오른쪽 세션에서 업데이트가 된다.

좌측 세션 commit 실행

오른쪽 세션에서 업데이트가 적용이 돼서 pwd는 222, name은 손오공으로 변경되었다. 하지만 오른쪽 세션에서만 적용된 것이므로 왼쪽 세션에서는 오른쪽 세션에서 commit을 안 했기 때문에 마지막 업데이트됐던 내용이 나온다.

왼쪽 세션에서 commit을 하며 오른쪽 세션에서 업데이트가 실행되었지만 오른쪽 세션에서 commit을 하지않아 왼쪽 세션의 마지막 업데이트 데이터가 확인된다.

오른쪽 세션에서 commit을 해보자. commit을 하면 영구적으로 저장되기 때문에 왼쪽 세션에서도 오른쪽 세션에서 변경한 내용이 나오게 된다.

 

오늘 강조할 점은 트랜잭션의 중요성과 관리 방법이다. 트랜잭션 중에는 데이터가 임시로 저장되고, 다른 세션은 해당 데이터에 접근할 수 없도록 잠금 상태가 유지된다. 이 잠금은 트랜잭션의 일관성을 유지하고 데이터의 무결성을 보장하는 역할을 한다. 하지만 트랜잭션이 완료되지 않은 상태로 오래 유지되면 다른 사용자나 세션에서 무한루프에 빠질 수 있다. 그래서 lock 걸리는 작업이 있으면 동료나 다른 사용자에게 빠르게 commit이나 rollback을 해달라고 요청해야한다.

오른쪽 세션에서 commit 실행 후 왼쪽 세션에서 변경된 내용 확인 가능

 

참고자료

[1] 유튜브 채널 뉴렉처 - 트랜잭션 처리를 위한 COMMIT과 ROLLBACK