본문 바로가기

Programming/Microsoft SQL Server

트랜잭션 (Transaction)

MS SQL Server에서 데이터베이스(DB)를 생성하면 실제 데이터베이스파일이 만들어 지는데 일부로 변경한 경우가 아니라면 아래와 같은 위치에 파일이 생성될것입니다.

 

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

 

실제 위와 같은 위치로 들어가면 확장자가 mdf인 파일과 ldf인 파일을 확인해 볼 수 있는데, 예를 들어 abc라는 데이터베이스를 생성했다면 abc.mdf와 abc_log.ldf파일이 존재함을 알 수 있습니다. 여기서 mdf는 실제 데이터가 들어가 있는 파일이며 ldf가 트랜잭션로그파일입니다. 이들 파일은 일반적으로는 한개만 존재할 경우가 많지만 필요에 따라 여러개로 나뉘어질 수 있는데 만약 데이터파일이 나뉘어진거라면 *.ndf형식으로 데이터파일이 생성되어 있을 것입니다.

 

이번에 주목해야할 것은 ldf즉 트랜잭션로그파일입니다. 이 파일은 Update나 Insert, Delete등의 데이터 변경작업을 기록하는 파일입니다. 만약 사용자가 Select와 같은 데이터조회 쿼리를 서버에 전달하면 서버는 mdf에서 원하는 데이터를 찾아 사용자에게 돌려주게 됩니다. 당연히 이 과정에서는 따로 트랜잭션로그가 남지 않습니다.(참고로 로그를 남기는 행위는 어떻게든 성능면에서는 불이익입니다. 트랜잭션로그도 마찬가지인데 로그를 남기지 않도록 하면 데이터의 무결성을 보증하기는 힘들어지지만 성능은 훨씬 좋아질것입니다.)

 

아래 쿼리는 데이터 변경 쿼리로 트랜잭션로그를 남기게 될 것인데 여기서 로그는 변경구문 그 자체를 기록하는것을 의미합니다.

 

Update [Person].[Person]
Set Title = NULL,
    FirstName = 'Ovidiu',
    MiddleName = 'V',
    LastName = 'Cracium'
Where BusinessEntityID = 11;

Update [Person].[Person]
Set Title = 'Ms.',
    FirstName = 'Janice',
    MiddleName = 'M',
    LastName = 'Galvin'
Where BusinessEntityID = 13;

 

그런데 위 구문은 Update가 2개이므로 2번의 트랜잭션을 발생시키게 됩니다. MS SQL은 기본적으로 자동 커밋이기에 사용자가 따로 트랜잭션시작과 종료시점을 명시하지 않는다면 변경 구문 하나하나마다 별도의 트랜잭션을 자동적으로 추가하게 됩니다.

 

Begin Tran

Update [Person].[Person]
Set Title = NULL,
     FirstName = 'Ovidiu',
     MiddleName = 'V',
     LastName = 'Cracium'
Where BusinessEntityID = 11;
Commit Tran;

Begin Tran
Update [Person].[Person]
Set Title = 'Ms.',
     FirstName = 'Janice',
     MiddleName = 'M',
     LastName = 'Galvin'
Where BusinessEntityID = 13;
Commit Tran;

 

그래서 만약 여러개의 변경구문을 묶어 단 한번만 트랜잭션을 유발시키려면 Begin Tran와 Commit Tran사이에 필요한 내용을 모두 명시해야 합니다.

 

Begin Tran

Update [Person].[Person]
Set Title = NULL,
     FirstName = 'Ovidiu',
     MiddleName = 'V',
     LastName = 'Cracium'
Where BusinessEntityID = 11;

Update [Person].[Person]
Set Title = 'Ms.',
     FirstName = 'Janice',
     MiddleName = 'M',
     LastName = 'Galvin'
Where BusinessEntityID = 13;
Commit Tran;

 

여기서 Begin Tran과 Commit Tran에서의 Tran은 Transaction의 줄임표현이며 원한다면 Begin Transaction처럼 풀어써주는것도 가능합니다.

 

트랜잭션은 위에서 처럼 Begin Tran과 Commit Tran을 명시적으로 해줘야 하는 경우도 있고 오라클처럼 Begin Tran은 자동으로 붙여주되 Commit Tran이나 Rollback Tran은 명시적으로 붙여줘야 하는 방식을 취할 수도 있습니다. 이러한 방식을 암시적 트랜잭션이라고 하는데 MS-SQL에서 이 방식을 사용하려면 다음과 같이 설정을 부여해야 합니다.

 

Set Implicit_Transactions On;

 

다만 일반적으로 트랜잭션은 중복으로 실행될 수 있지만 암시적 트랜잭션은 Commit이나 Rollback을 만나기 전까지는 중복실행이 불가능합니다.

 

위에서 처럼 MS-SQL서버에 트랜잭션이 발생하게 되면 그러니까 Begin Tran이 시작되면 ldf에 Begin Tran구문을 기록하고 변경 쿼리의 내용대로 데이터를 임시로 변경하게 됩니다. 임시로 변경한다는 것은 실제 데이터를 변경하는 것이 아니라 변경이 필요한 행 데이터(페이지)를 메모리캐시에 올려놓고 올려놓은 데이터를 대상으로 변경하는 것을 의미합니다. 그리고 나서 변경 쿼리에 일련번호를 부여하여 ldf파일에 로그로 기록하게 됩니다. 이 과정을 Commit이나 Rollback이 나올때까지 변경쿼리마다 하나씩 반복합니다.

 

만약 이 과정중 Commit Tran을 만나게 되면 ldf에 로그를 기록하고 캐시에 기록된 내용대로 실제 mdf데이터에 해당 내용을 적용(Commit)하게 됩니다. 그리고 나서 ldf의 Commit Tran부분에 체크포인트를 설정합니다. 체크포인트는 지금까지의 내용이 모두 실제 mdf에 적용되었음을 표시하는 것인데 만약 CheckPoint 문을 임의로 실행하면 캐시의 내용을 실제 데이터에 적용하고 그 시점에 체크포인트를 설정할 수 있도록 합니다.

 

Begin Tran부터 변경쿼리를 통해 캐시내용을 바꾸는 과정중 어떠한 이유로 인해서 서버나 혹은 서비스가 종료되는 상황이 발생하면 메모리의 내용이 모두 사라지게 될 것입니다. 변경내용이 모두 메모리에 존재하고 있었는데 이 캐시데이터를 모두 잃어버리게 되었고 로그상 아직 Begin Tran중이었으니 처음 Begin Tran이나 혹은 이전 체크포인트까지의 처리를 모두 취소하여 데이터의 무결성을 유지하게 됩니다.

 

그런데 처리과정이 ldf에 Commit Tran을 기록하는 것까지 도달하였으나 캐시데이터가 아직 실제 데이터에 적용되기 전에 사고가 발생한 경우라면 Begin Tran시점(또는 이전 체크포인트 이후)부터 모든 쿼리에 대한 트랜잭션을 다시 재 수행하여 변경절차를 마무리하게 됩니다.

 

Commit Tran은 캐시데이터를 실제 데이터에 적용하지만 Rollback Tran은 변경이전으로 되돌리는 작업을 수행합니다. 그런데 트랜잭션이 여러개 중첩되어 사용되는 경우 Rollback Tran을 사용할때 오해할 수 있는 부분이 하나 있는데 Rollback Tran은 이전의 트랜잭션을 되돌리는 것이 아니라 지금까지의 모든 트랜잭션을 되돌린다는 것입니다.

 

Begin Tran
     Update [Person].[Person]
     Set Title = NULL,
      FirstName = 'Ovidiu1',
      MiddleName = 'V1',
      LastName = 'Cracium1'
     Where BusinessEntityID = 11;

Begin Tran
      Update [Person].[Person]

Set EmailPromotion = 1
Where BusinessEntityID = 11;

RollBack Tran;

 

Begin Tran 이후 다시 Begin Tran으로 트랜잭션을 두번 호출하고 마지막에 RollBack Tran으로 변경 데이터를 되돌립니다. 얼핏봐서는 이전의 Begin Tran만 되돌릴것 같지만 사실은 걸려있는 모든 트랜잭션을 취소합니다. 따라서 만약 특정 트랜잭션만을 명시해 취소하고자 한다면 특정 지점에서 트랜잭션을 Save하고 Save한 트랜잭션까지 취소하는 방법을 써야 합니다.

 

Begin Tran
     Update [Person].[Person]
     Set Title = NULL,
     FirstName = 'Ovidiu1',
     MiddleName = 'V1',
     LastName = 'Cracium1'
     Where BusinessEntityID = 11;
Save Tran my_tran;
Begin Tran
     Update [Person].[Person]
     Set EmailPromotion = 1
     Where BusinessEntityID = 11;
RollBack Tran my_tran;

 

중간에 Save Tran으로 트랜잭션 지점을 생성하고 RollBack Tran에서 복원지점을 명시하면 명시한 지점까지만 트랜잭션을 복원하게 됩니다. 따라서 위 쿼리의 경우 EmailPromotion을 Update하는 부분만 적용되고 그 위로는 트랜잭션이 취소되지 않습니다.

 

참고로 특정 테이블에 트랜잭션이 걸려져 있는 상태라면 해당 테이블에는 잠금(Lock)이 발생할 수 있습니다. 이 현상을 확인하려면 Begin Tran을 발생시키고 다른 세션(별도의 쿼리창)에서 해당 테이블의 Select를 시도해 보면 쉽게 확인 할 수 있습니다. 따라서 임의로 트랜잭션을 구현할때는 주의해야 하며 필요한 경우 아래 쿼리를 실행(Select가 필요한 세션안에서)하여 테이블잠김상태라도 데이터를 가져올 수 있도록 설정할 수 있습니다.

 

Alter Database [대상DB] Set Allow_Snapshot_Isolation On;
Set Transaction Isolation Level Snapshot;

 

특정 세션에서 트랜잭션을 발생시킨뒤 데이터 변경 쿼리를 실행하고 테이블을 조회해 보면 변경된 상태의 데이터가 출력됨을 확인할 수 있는데 이것은 실제 테이블의 데이터를 가져오는 것이 아니라 캐시상태의 데이터를 가져와 보여주고 있기 때문입니다.

 

Snapshot을 취소하려면 다음과 같이 수행합니다.

 

Alter Database AdventureWorks Set Allow_Snapshot_Isolation Off;

 

위에서 알아본대로 트랜잭션은 데이터의 무결성을 유지하기 위한 아주 좋은 장치입니다. 그런데 트랜잭션은 장애발생에 대한 조치용이지 모든걸(모든 장애를) 해결해 주는 것으로 오해해서는 안됩니다. 예를 들어 아래 쿼리를 보면

 

Begin Tran

Update [Person].[Person]
Set Title = NULL,
     FirstName = 'Ovidiu1',
     MiddleName = 'V1',
     LastName = 'Cracium1'
Where BusinessEntityID = 11;

Update [Person].[Person]
Set EmailPromotion = 'ABC'
Where BusinessEntityID = 11;
Commit Tran;

 

두번째 Update문에서 EmailPromotion은 Int형인데 여기에 'ABC'라는 문자열을 처리하도록 하고 있습니다. 트랜잭션에 대해 오해를 하고 있으면 여기에서 실패가 발생해 자동적으로 모든 Update문이 RollBack되리라 생각할 수 있겠지만 사실은 그렇지 않습니다. 이것은 엄밀히 말해 장애가 아닌 오류상황이기 때문입니다. 따라서 이런 경우에는 운영자가 명시적으로 Rollback이 되도록 처리를 해줘야 하며 이 때 가장 많이 쓰이는 방식은 Try Catch 구문을 활용하는 것입니다.

 

Begin Try

Begin Tran
Update [Person].[Person]
Set Title = NULL,
FirstName = 'Ovidiu1',
MiddleName = 'V1',
LastName = 'Cracium1'
Where BusinessEntityID = 11;

Update [Person].[Person]
Set EmailPromotion = 'ABC'
Where BusinessEntityID = 11;
Commit Tran;

End Try
Begin Catch
     Rollback Tran;
End Catch;

 

Begin Tran안에서 오류가 발생하면 Begin Catch에서 잡아 RollBack Tran을 실행할 것입니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

인덱스(Index) - 1  (0) 2019.03.26
[SQL] 전체 텍스트 검색  (0) 2018.09.27
트랜잭션 (Transaction)  (0) 2018.09.12
분산 트랜잭션 설정  (0) 2018.07.11
[SQL] 기본언어확인및 변경  (0) 2018.01.30
[SQL] 스키마(Schema)  (0) 2018.01.23