작업의 처리 단위인 Transaction과 병행성 제어를 위한 Lock에 대해서 설명을 드리도록 하겠습니다.

1. Batch & Script

Batch Database Server에서 실행되는 작업의 단위, 하나의 Batch GO라는 Batch 마지막을 알리는 문장에 의해

구분된다, Batch단위로 구문을 점검 실행하게된다. 만약 Batch 포함된 문장중 하나가 에러났다면 Batch 포함된 모든 문장은 실행되지 않는다.

 

2. Introductions to Transaction('처리가 끝난 또는 처리 중인 업무') and Locks

Transaction과 Lock은 DBMS에서 동시성 제어, 일관성, 회복과 밀접한 관련이 있습니다.

Transaction은 BEGIN TRANSACTION 문장과 COMIIT 또는 ROLLBACK을 이용하여 Transaction 이라는 것을 사용하였습니다. 이 문장들이 하나의 Transaction을 묶어주는 역할을 하게 되는 것입니다.

Transaction은 작업의 논리적인 단위로서 Database System의 근본적인 목적이 이 Transaction 수행에 있다고 할 수 있습니다.

Primary Key의 중복이나 NULL 값의 입력이상 등의 불가피한 입력이상이 발생될 수 있기 때문이 성공적인 입력연산의 수행을 보장할 수 없습니다. 따라서 3개의 입력구문이 하나의 입력구문이 실행되는 것처럼, 전체적인 실행에 대한 성공 또는 실패가 보장되도록 하여야만 합니다. 이러한 기능을 지원하는 것을 앞서 말씀을 드린 것처럼 Transaction의 원자성(Atomic)이라고 합니다.

 

Transaction 자체가 일련의 작업을 수행하고 있는 Data에 대해서, 작업을 수행하고 있는 동안 예측할 수 없는 방식으로 변환되지 않도록 보증을 필요로 하게 됩니다. 이러한 경우 Transaction은 해당 Data에 대해서 Lock이라는 것을 획득하게 됩니다. 해당 Data가 특정 Transaction에 의해서 사용되고 있다는 인증서와 같은 역할을 합니다. Lock는 해당 Data가 다른 Transaction에 의해서 사용이 되어서, 일관된 상태가 아니다는 것을 알려주어 다른 Transaction이 사용하지 못하도록 하는 역할을 합니다.

 

3. SQL Server Transactions

 

Implicit(묵시적) Transaction: BEGIN TRANSACTION, COMMIT, ROLLBACK 생략

 

Explicit(암시적,명시적) Transaction :BEGIN TRANSACTION, COMMIT, ROLLBACK 표시

 

 

1. Nested(중첩) Transaction

출처즉 COMMIT 구문에서 명칭을 부여하지 않은 경우에는 가장 가까이 위치한 Transaction에 대해서 COMMIT 구문을 적용하게 된다는 것입니다.  그럼 ROLLBACK 구문은 어떨까요?

ROLLBACK의 경우에는 중복된 Transaction의 개수와는 상관없이, 가장 먼저 시작한 Transaction에 대해서 Rollback을 수행하게 됩니다. 

 

2. SAVEPOINT

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

 


3. Transaction의 처리순서

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 파일에 기록이 됩니다.

 

Checkpoint

검사점 명령이 발생되면, SQL Server는 Cache에 있는 Data와 Log를 Hard Disk에 기록하게 됩니다

즉 Dirty Page의 내용을 Hard Disk로 넘겨주는 것입니다. 

Lazy Writer

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

 

4. Transaction Recovery(복구)

 

System 고장

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

 

장치고장

관리자에 의해서 Data에 대한 복구가 이루어져야만 합니다.

 

5. Consideration for Using Transactions

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

Transaction Guidelines

가능하면 Transaction은 짧게 사용하여야만 합니다. 

Transaction을 시작하기 전에, 필요한 사용자의 정보는 미리 받아 놓는 것이 좋습니다. 

Transaction 내에서 INSERT, UPDATE, DELETE 구문이 주가 되어야만 합니다. 또한 이들 구문에 의해서 영향을 받는 Row는 적어야만 합니다.

Issues in Nesting Transactions

중첩된 Transaction과는 상관없이 가장 먼저 정의된 Transaction이 Commit 되었는지, 아니면 Rollback 되었는지에 따라서 Transaction의 성공여부가 결정되기 때문에 중첩 Transaction은 의미를 가지지 못합니다.

 

6. Setting the Implicit Transactions Option

SET IMPLICIT_TRANSACTIONS {ON | OFF}

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

 

7. Restrictions on User-Defined Transactions

 

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

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

 

실습:

 

Transaction의 처리순서

SQL Server가 물리적으로 정보를 저장하는 것은 Log와 Data 파일 두 가지.

이외에도 Database의 성능향상을 위해서 MSSQL은 Memory 상에 Cache라는 부분을 만들어 사용한다고 하였습니다. 이 역시 Data Cache와 Log Cache를 만들어서 사용하게 됩니다. 이렇게 SQL Server는 Hard Disk와 Memory 상에 Data와 Log에 대한 부분을 따로 저장하여 사용을 하고 있습니다.

먼저 Transaction이 BEGIN TRANSACTION에 의해서 시작되면 SQL Server는 Transaction이 시작되었음을 Log Cache에 기록하게 됩니다. 두 번째로 UPDATE 구문이 실행되면,  먼저 SQL Server는 해당 Data가 Data Cache에 있는지 검사를 하게 됩니다. 만약 있는 경우에는 Data에 대해서 UPDATE문을 수행하여 갱신을 하게 되지만,  없는 경우에는 Hard Disk에 있는 Data 파일로부터, 해당 Data를 읽어와 Data Cache에 기록을 하게 됩니다.  Data를 읽어온 다음에는  UPDATE문을 실행하여 갱신을 하게 됩니다. UPDATE문을 이용하여 갱신한 작업에 대해서는 Log Cache에 기록을 하게 됩니다. 이 때, Data 파일의 내용과 Data Cache의 내용이 다른 부분이 발생되게 됩니다. 즉, UPDATE문에 의해서 변경된 주소 부분이 바로 내용이 다른 부분인데요. 이를 Dirty Page라고 합니다. 마지막으로 COMMIT 문장이 실행되면,SQL Server는 아직 Hard Disk상에 옮겨 적지 않은 Dirty Page를 Log 파일로 옮겨 적게 됩니다. 이는 Log Writer에 의해서 작동됩니다.

 

//

Transaction의 처리순서

그럼 Transaction은 해당 구문을 어떻게 처리를 할까요? 그림을 보면서 설명을 드리도록 하겠습니다. Transaction의 처리순서에 대해서는 앞서서 간략하게 말씀을 드렸는데요. 같은 예제 문장을 통해서 다시 한번 더 설명을 드리도록 하겠습니다.

1. 앞서 설명을 드리면서 SQL Server가 물리적으로 정보를 저장하는 것은 Log Data 파일 두 가지라고 말씀을 드렸습니다. 이에 대해서는 앞서 Database 강좌에서 충분히 설명을 드린 부분입니다.

이외에도 Database의 성능향상을 위해서 MSSQL Memory 상에 Cache라는 부분을 만들어 사용한다고 하였습니다. 이 역시 Data Cache Log Cache를 만들어서 사용하게 됩니다. 이렇게 SQL Server Hard Disk Memory 상에 Data Log에 대한 부분을 따로 저장하여 사용을 하고 있습니다.

2. 이제 예제 문장을 보도록 하겠습니다. 예제 문장을 보시면, 간단히 우편번호 Table에 있는 하나의 Data에 대해서 갱신연산을 하는 구문입니다.

먼저 Transaction BEGIN TRANSACTION에 의해서 시작되면 SQL Server Transaction이 시작되었음을 Log Cache에 기록하게 됩니다.

3. 두 번째로 UPDATE 구문이 실행되면,

4. 먼저 SQL Server는 해당 Data Data Cache에 있는지 검사를 하게 됩니다. 만약 있는 경우에는 Data에 대해서 UPDATE문을 수행하여 갱신을 하게 되지만,

5. 없는 경우에는 Hard Disk에 있는 Data 파일로부터, 해당 Data를 읽어와 Data Cache에 기록을 하게 됩니다.

6. Data를 읽어온 다음에는 

7. UPDATE문을 실행하여 갱신을 하게 됩니다.

8. UPDATE문을 이용하여 갱신한 작업에 대해서는 Log Cache에 기록을 하게 됩니다.

9. 이 때, Data 파일의 내용과 Data Cache의 내용이 다른 부분이 발생되게 됩니다. , UPDATE문에 의해서 변경된 주소 부분이 바로 내용이 다른 부분인데요. 이를 Dirty Page라고 합니다.

10. 마지막으로 COMMIT 문장이 실행되면,

11. SQL Server는 아직 Hard Disk상에 옮겨 적지 않은 Dirty Page Log 파일로 옮겨 적게 됩니다. 이는 Log Writer에 의해서 작동됩니다.

 

 

 

Transaction Recovery(복구)

 

 

 

 

 

 

 

+ Recent posts