중첩 트랜잭션(Nested Transaction)

먼저 중첩 Transaction에 대해서 알아보도록 하겠습니다. 중첩 Transaction은 말 그대로 Transaction 안에 Transaction을 또 다시 정의하는 것을 말합니다.

예제의 그림을 보면서 설명을 드리도록 하겠습니다.

그림을 보시면 상당히 복잡한 문장 같은 데요. 하나하나 살펴보도록 하겠습니다.

이 문장은 기존에 임대되었던 임대내역을 갱신하는 문장입니다. 즉 고객이 테이프를 빌려가면서, 이미 임대에 대한 지불을 끝내고 나서, 테이프를 하나 더 빌리는 경우, 임대정보를 기존에 입력한 내역에 대해서 추가를 하는 업무입니다. 따라서 임대정보 Table에 있는 총계 정보를 갱신하고, 임대품목에는 하나의 임대내역을 추가하여야 합니다.

먼저 BEGIN TRAN TEST_1은 Transaction에 대해서 TEST_1이라는 명칭을 부여하였습니다. 이 Transaction은 모든 전체적인 문장의 Transaction입니다. 먼저 임대정보 Table에 있는 임대번호가 '2000010602'인 Data에 대해서 총계를 1500으로 갱신을 하는 문장입니다. 그리고 그 다음 문장을 보시면, BEGIN TRAN TEST_2로 또 하나의 BEGIN TRANSACTION 문장을 사용하고 있습니다. 이것이 바로 중첩 Transaction입니다. 자세한 설명은 잠시 후 그림을 통해서 드리도록 하겠습니다.

그 다음은 임대품목에 하나의 임대내역을 추가하는 문장과 해당 테이프의 대여여부를 갱신하는 문장입니다.

다음의 문장이 좀 중요한데요 보시는 바와 같이, Error에 대한 검사를 하여, 에러가 발생된 경우에는 ROLLBACK TRAN 문장을 수행하고, 그렇지 않은 경우, 즉 에러가 발생하지 않은 경우에는 두 개의 Transaction을 모두 COMMIT하여 Database에 적용을 하는 문장입니다. Transaction은 두 개이기 때문에 COMMIT문장은 두 번 쓰인 것은 이해할 수 있지만, ROLLBACK 구문이 한 번 쓰인 이유는 무엇일까요?

우선 중첩 Transaction에 대해서 설명을 드리고 ROLLBACK 구문이 한 번 사용된 이유에 대해서 설명을 드리도록 하겠습니다.

아래의 그림은 앞선 문장의 Transaction을 간략하게 정리한 것입니다.

보시는 바와 같이 BEGIN TRANSACTION 구문을 이용하여 Transaction을 시작을 한 경우에는 반드시 COMMIT을 이용하여 Transaction을 마무리 하여야 합니다.

또한 처음 시작한 Transaction 안에 여러 개의 Transaction을 중복하여 사용을 할 수 있습니다. 즉 Transaction 안에 또 다른 Transaction을 만들 수 있다는 것입니다.

앞선 예제에서는 Transaction 마다 이름을 주었지만, 이들은 생략이 가능합니다.

만약 이들 이름을 생략하게 된다면 COMMIT TRAN TEST_2는 바로 앞서서 BEGIN TRANSACTION TEST_2를 이용하여 시작하였던 Transaction에 대해서 COMMIT를 실행합니다. 즉 COMMIT 구문에서 명칭을 부여하지 않은 경우에는 가장 가까이 위치한 Transaction에 대해서 COMMIT 구문을 적용하게 된다는 것입니다.

그럼 ROLLBACK 구문은 어떨까요?

물론 Transaction이 하나의 BEGIN TRANSACTION의 문장으로 시작한 경우, ROLLBACK구문은 해당 Transaction의 모든 연산을 취소하게 됩니다. 하지만 하나 이상의 Transaction. 즉 방금 전 살펴보았던, 중복 Transaction일 경우 어떻게 될까요?

ROLLBACK의 경우에는 중복된 Transaction의 개수와는 상관없이, 가장 먼저 시작한 Transaction에 대해서 Rollback을 수행하게 됩니다. 즉 해당 Transaction 안에서 몇 개의 Transaction이 있는지에 상관없이 전체 Transaction에 대한 실행취소를 하게 됩니다.

즉 BEGIN TRANSACTION에 의해서 시작된 수 만큼 COMMIT 문장을 실행하는 것과는 조금 다른데요. 앞선 예제에서 ROLLBACK 구문이 한 번 사용된 이유는 여기에 있습니다. 따라서 앞서 예제 문장에서 만약 에러가 발생되었다면, 모든 문장에 대한 연산을 취소하고 맨 처음 시작한 TEST_1 Transaction이 시작되기 전의 상태로 되돌아가게 됩니다.

2. SAVEPOINT

조금은 불편하죠? 예제는 몇 개 되지않은 연산이었지만, 만약 여러 연산을 한 번에 실행하여야 하는 경우에, 마지막 연산에서 에러가 발생하여 Rollback으로 Transaction을 취소하였다면, 서버자원의 상당한 낭비도 초래하게 됩니다. 앞선 예제에서 만약 TEST_2 Transaction에 대한 내역만 취소하고 나머지 연산은 성공할 수 있도록 한다면 이러한 낭비는 줄일 수 있을 것입니다.

이렇게 특정 시점까지만 ROLLBACK을 할 수 있도록 지원하는 것이 바로 SAVEPOINT입니다.

우선 SAVEPOINT의 기본적인 구문을 살펴보도록 하겠습니다.

SAVE TRAN[SACTION] {savepoint_name | @savepoint_variable}

보시는 바와 같이 COMMIT 구문이나 ROLLBACK 구문과 사용방법은 같습니다.

예제를 통해서 설명을 드리도록 하겠습니다.

그림을 보시면 우편번호 Table에 Data를 입력하는 3개의 입력연산이 있습니다.

보시는 바와 같이 두 번째 Line을 보시면, SAVE TRAN 구문 다음에 SAVEPOINT의 명칭을 명시하여 SAVEPOINT를 정의하고 있습니다.

이렇게 SAVEPOINT를 정의하고, 다시 ROLLBACK을 하기 위해서는 ROLLBACK TRANSACTION 문 다음에 SAVEPOINT로 지정된 명칭을 명시하여 주면 됩니다.

자세한 내용은 실습을 통해서 설명을 드리도록 하겠습니다.

3. Transaction의 처리순서

이렇게 SQL Server는 Transaction을 이용하여 Data의 일관성을 유지할 수 있도록 지원하고 있습니다. 그럼 SQL Server가 어떻게 내부적으로 Transaction을 처리하는지에 대해서 알아보도록 하겠습니다.

그림에서 보시는 것과 같은 문장을 실행하였을 경우, MSSQL Server는 어떠한 처리과정을 거치는지 알아보도록 하겠습니다. 앞선 강좌에서 미루었던 Log에 대한 부분도 여기서 설명을 드리도록 하겠습니다.

설명을 드리기 전에 우선, Cache에 대한 부분에 대해서 알아보도록 하겠습니다. 앞서서 MSSQL Server는 Data에 대한 부분은 Data 파일에, Log에 대한 부분은 Log 파일에 저장을 한다고 하였습니다.

이렇게 Data는 SQL Server가 설치된 Computer의 Hard Disk 상에 존재하게 됩니다. 그렇다면 사용자에 의해서 Data에 대한 반환요구가 있을 경우, 매번 Hard Disk에서 해당 Data를 읽어와서 반환을 하는 작업을 거쳐야 하는데요. 이는 사용자가 느끼는 SQL Server의 속도와도 관계가 있기 때문에 성능상의 문제로 발전하게 됩니다.

이미 알고 계시듯이 Hard Disk에서 Data를 읽는 속도는 RAM의 속도와는 비교가 안될 정도로 차이가 나게 됩니다. MSSQL Server는 이렇게 성능향상을 위해서 Computer에 있는 RAM에 일부 Data를 저장하여 놓게 되는데요. 이를 Cache라고 합니다. Data와 Log에 대한 부분이 Hard Disk 상에 다른 파일로 존재하듯이, Cache에도 Data와 Log를 따로 저장하도록 하고 있습니다. 이를 Data Cache와 Log Cache로 나누어서 RAM을 사용하고 있습니다.

다시 앞선 예제문장으로 돌아가도록 하겠습니다.

1) 먼저 Transaction을 시작하는 BEGIN TRAN 문장이 수행되면, MSSQL Server는 Log Cache에 Transaction이 시작되었음을 알리는 내용을 기록합니다.

2) 두 번째로 UPDATE문이 실행이 되면, MSSQL Server는 갱신할 Data가 Data Cache에 있는지를 보고서, 만약 있다면 이를 사용하게 되고, 그렇지 않은 경우에는 Hard Disk로부터 해당 Data를 읽어와 해당 Data의 복사본을 Data Cache에 기록을 하고, 갱신문장을 수행하여 Data를 갱신하게 됩니다. 이때 Data에 대한 갱신하는 작업은 Log Cache에 기록되어 집니다. 이때 Dirty Page라는 것이 발생하게 되는데요. 이는 Cache 상의 복사본 Data와 Hard Disk 상에 있는 Data의 내용이 틀린 것을 말합니다. 앞서서 UPDATE 문장에서 기존에 있던 주소 Data(즉, Hard Disk에 저장되어 있는 Data)와 UPDATE 문장에 의해서 변경된 복사본 Data(즉, Cache에 저장되어 있는 Data)의 내용이 다른 Page를 말하는 것입니다.

3) 마지막으로 COMMIT TRANSACTION 문에 의해서 Transaction이 성공적으로 마치게 되면, Dirty Page의 내용은 Hard Disk 상의 Log 파일에 기록이 됩니다.

이렇게 SQL Server는 복사본의 내용을 Data 파일에 기록하기 전에, Log Cache를 Hard Disk의 Log 파일에 기록을 하게 됩니다. 이를 Database에서는 먼저 쓰기 로그(Write Ahead Log)라고 부릅니다.

Log만 기록하면 Data Cache에 기록된 Data는 언제 Hard Disk로 기록을 할까요? 다른 사용자가 해당 Data를 Hard Disk에서 읽어가면 갱신된 Data가 아니라 갱신되기 이전의 Data를 읽어가게 되는데, 그럼 Data의 일관성을 해치는 결과를 낳게 되는데. 이 문제들은 어떻게 할까요?

우선 첫번째로 Data Cache에 있는 변경된 Data는 Checkpoint나 Lazy Writer에 의해서 Hard Disk로 변경된 Data를 옮기게 됩니다. 바로 뒤에서 설명을 드리겠습니다.

두 번째, 다른 사용자가 Hard Disk 상에 있는 갱신되기 전의 Data를 읽어가면 어떻게 할까요? 이 문제는 걱정을 하지 않으셔도 됩니다. 앞서서 Data Cache에 대한 부분을 말씀을 드렸는데요. 만약 해당 Data에 대해서 다른 사용자가 사용을 하려 한다면, MSSQL Server는 Data Cache에 해당 Data가 있는지를 찾아보고 있다면, 그 Data를 사용하기 때문에, 다른 사용자가 갱신되지 않은 Data를 Hard Disk로부터 읽어가는 불상사는 일어나지 않습니다.

자, 그럼 방금 전 말씀을 드렸던 Checkpoint와 Lazy Writer는 무엇일까요? 하나하나 설명을 드리도록 하겠습니다.

- Checkpoint

우선 Checkpoint는 말 그대로 '검사점'입니다. 이 검사점 명령이 발생되면, SQL Server는 Cache에 있는 Data와 Log를 Hard Disk에 기록하게 됩니다. 즉, Hard Disk에 있는 내용과 사용자의 변경에 의해서 달라진 내용. 즉 Dirty Page의 내용을 Hard Disk로 넘겨주는 것입니다. 이는 언제 발생되는지는 알 수 없으며, 다만 Transaction의 사용정도와 Memory의 cache의 크기에 따라서 비주기적으로 발생됩니다.

- Lazy Writer

Computer 상의 RAM은 그 크기가 한정되어 있기 때문에, Cache 역시 그 크기가 한정되어 있습니다. 만약 Cache가 꽉 찬다면 어떻게 될까요? 이러한 경우에도 걱정하실 필요가 없습니다. Lazy Writer라는 것이 바로 Cache에 있는 내용을 바로 Hard Disk로 옮겨 적어주기 때문입니다.

이렇게 Cache에 있는 내용을 물리적인 Disk로 적어주는 기능은 Checkpoint와 Lazy Writer에 의해서 이루어지게 됩니다.

앞서서는 BEGIN TRANSACTION, COMMIT, ROLLBACK 등의 구문을 이용하여 Data의 일관성을 유지하는 것에 대해서 알아보았습니다. 이 외에도 MSSQL Server는 정전 또는 기타 다른 문제에 의해서 오류가 발생했을 경우에도 Data의 일관성을 유지할 수 있도록 지원을 하고 있습니다.

4. Transaction의 Recovery(복구)

Database에서 System 실패가 발생하는 경우는 여러 가지가 있습니다. 우선 Transaction 안에서 Overflow가 발생된다든지 하는 지역적인(Local) 고장과 정전이나 CPU의 고장, Hard Disk의 고장 등의 전역적인(Global) 고장 등이 있습니다. 지역적인 고장의 경우에는 해당 Transaction에만 영향을 미치지만, 전역적인 고장의 경우에는 고장 시 진행 중이던 Transaction에 치명적인 영향을 미치게 됩니다. 이런 경우 Database의 Data의 일관성이 깨어지게 되기 때문에, 이전의 일관된 상태로 되돌리기 위한 복구(Recovery) 작업이 필요로 하게 됩니다.

이러한 시스템 복구(Recovery)작업에 대해서 고장유형별로 알아보도록 하겠습니다. 먼저 시스템 고장일 경우의 시스템 복구에 대해서 알아보도록 하겠습니다.

▣ System 고장

시스템 고장은 예를 들어 정전과 같은 경우가 발생되는 경우, 현재 진행 중인 Transaction에 치명적인 영향을 미치게 됩니다. 이러한 경우에는 운영체제가 다시 시작될 때, MSSQL Server가 자동적으로 Transaction에 대한 내용을 다시 시작하거나(redo, roll forward), Transaction에 대한 내용을 취소하든지(undo, rollback) 하도록 지원을 합니다. 이러한 경우 시스템이 실패하였기 때문에, Cache에 기록된 내용은 이미 알 수가 없으므로, Log 파일의 기록에 의존할 수밖에 없습니다. 따라서 Log 파일에 기록된 내용을 읽어서 Database의 내용을 바꾸게 됩니다.

그럼 어떻게 Transaction의 내용을 복구하는지에 대해서 알아보도록 하겠습니다.

우선 그림을 보시면 5개의 Transaction이 시간별로 실행되고 종료되는 것을 표로 작성을 하였습니다. 보시는 바와 같이 일부는 Checkpoint가 발생되기 전에 이미 Commit이 되었고, 또 다른 Transaction은 Commit이 되기 전에 정전으로 인해서 Transaction에 대한 정보를 잃어버리게 되었습니다. 이들 각각의 Recovery(복구)작업은 다른 형태로 진행되게 됩니다.

자세한 내용은 실습을 통해서 설명을 드리도록 하겠습니다.

▣ 장치고장

다음은 장치고장의 경우입니다. 장치고장은 예를 들어 Hard Disk의 고장 또는 Disk Controller와 같은 Hardware의 고장 등이 있을 수 있습니다. 이러한 경우는 시스템 고장보다 더욱 심각한 경우인데요. 이 경우에는 앞서 시스템 고장과 같이 SQL Server가 자체적으로 복구를 할 수 없는 경우입니다. 따라서 관리자에 의해서 Data에 대한 복구가 이루어져야만 합니다. 장치고장에 대한 복구는 Database의 Backup과 Restore와 관련된 부분에서 자세하게 설명을 드리도록 하겠습니다.

5. Consideration for Using Transactions

Data의 일관성을 위해서 Transaction을 사용하는 것은 좋은 습관입니다. 하지만 무분별하게 Transaction을 오랫동안 사용하고 있다든지, 중첩 Transaction을 남용하는 것은 상당히 위험합니다. 이렇게 Transaction을 사용하는데 있어서 몇 가지 주의해야 할 점이 있습니다.

▣ Transaction Guidelines

우선 Transaction에 대한 주의할 점에 대해서 알아보도록 하겠습니다.

• 가능하면 Transaction은 짧게 사용하여야만 합니다. 특히 WHILE문과 같이 Loop를 수행하는 문장이나 Data Definition Language와 같은 구문은 짧게 사용할수록 좋습니다. 이는 Lock과도 연관이 되는데요. Lock에 대해서는 뒤에서 설명을 드리도록 하겠습니다.
• Transaction을 시작하기 전에, 필요한 사용자의 정보는 미리 받아 놓는 것이 좋습니다. 만약 Transaction이 정의된 상태에서 사용자의 정보를 얻거나 사용자에 의해서 입력을 받아야 하는 경우에는 Transaction이 수행되고 있는 동안에, 사용자가 해당 Data를 입력하지 않고 있다면, Transaction은 종료되지 않고 사용자의 입력을 계속해서 기다리게 됩니다.
• Transaction 내에서 INSERT, UPDATE, DELETE 구문이 주가 되어야만 합니다. 또한 이들 구문에 의해서 영향을 받는 Row는 적어야만 합니다.

▣ Issues in Nesting Transactions

다음은 중첩 Transaction에 대한 내용입니다.

중첩 Transaction의 사용은 제한이 없지만, 가능하면 사용하지 않는 것이 좋습니다. 앞서 중첩 Transaction에서 설명을 드린 것과 같이 중첩된 Transaction과는 상관없이 가장 먼저 정의된 Transaction이 Commit 되었는지, 아니면 Rollback 되었는지에 따라서 Transaction의 성공여부가 결정되기 때문에 중첩 Transaction은 의미를 가지지 못합니다.

@@trancount라는 전역변수를 사용하여 현재 정의되어 있는 Transaction의 수를 반환 받을 수 있습니다. 만약 @@trancount가 0인 경우에는 어떠한 Transaction도 정의되어 있지 않다는 것을 뜻합니다. 또한 DBCC OPENTRAN 구문을 이용하여도 Transaction에 대한 정보를 얻을 수 있습니다.

6. Setting the Implicit Transactions Option

앞서서 묵시적 Transaction에 대해서 설명을 드렸습니다. 묵시적 Transaction 역시 나름대로 장점을 가지고 있지만, 항상 BEGIN TRAN으로 Transaction을 시작하고, COMMIT이나 ROLLBACK 구문을 이용하여 Transaction을 종료하는 것이 잘못된 입력을 막아서 Data의 일관성을 지킬 수 있도록 할 수 있기 때문입니다.

묵시적 Transaction Option은 이렇게 Transaction을 정의하는 것을 좀 더 쉽게 할 수 있도록 지원을 하고 있습니다. 묵시적 Transaction을 설정하는 기본적인 문장은 다음과 같습니다.

SET IMPLICIT_TRANSACTIONS {ON | OFF}

만약 IMPLICIT_TRANSACTION의 Option을 ON으로 하였다면, 특정 문장이 시작되면, 그 전에 BEGIN TRAN이라는 구문을 SQL Server가 자동으로 실행하여 줍니다. BEGIN TRAN 구문을 사용자가 직접 정의하지는 않았지만, SQL Server에서 정의를 해주었기 때문에 이 문장은 반드시 COMMIT 또는 ROLLBACK 구문을 이용하여 종료하여야 합니다.이렇게 MSSQL Server가 자동으로 BEGIN TRAN 문장을 실행하여 주기 때문에, 중첩 Transaction에 대한 정의는 불가능합니다.

또한 다음의 문장이 실행되면, MSSQL Server는 암시적으로 BEGIN TRAN 구문을 실행하여 Transaction을 시작하게 됩니다.

ALTER TABLE CREATE DELETE DROP FETCH GRANT INSERT OPEN REVOKE SELECT TRUNCATE TABLE UPDATE

묵시적 Transaction Option의 기본값은 OFF이기 때문에 이를 사용하기 위해서는 반드시 SET 구문을 이용하여 암시적 Transaction의 Option을 ON으로 변경하여 주어야 합니다.

자세한 사용 예는 실습을 통해서 설명을 드리도록 하겠습니다

여기서 주의하여야 할 점은 묵시적 Transaction으로 정의된 문장은 반드시 COMMIT나 ROOLBACK 구문을 이용하여 Transaction을 종료하여야만 합니다.

7. Restrictions on User-Defined Transactions

사용자 지정 Transaction에는 몇 가지 제약이 있습니다. 몇몇 System Stored Procedure는 사용자 지정 Transaction에서 사용될 수 없습니다. 그 이유는 임시 Table을 생성하기 때문입니다. 예를 들어 sp_dboption과 같은 System Stored Procedure는 사용될 수 없습니다.

또한 다음의 문장들 역시 명시적 Transaction에서는 사용될 수 없습니다.

ALTER DATABASE BACKUP LOG CREATE DATABASE DROP DATABASE RECONFIGURE RESTORE DATABASE RESTORE LOG UPDATE STATISTICS