'배치처리'에 해당되는 글 2건

Programming/Microsoft SQL Server
Batch처리내에서 Query문이 수행될때 오류가 발생하게 되면 다음 오류의 종류에 따라 약간씩 다른 방식으로 Query문이 종료됩니다.

(1) 문법에 맞지 않는 Query문 수행시

▶ Batch처리내에 있는 모든 Query문은 실행되지 않습니다.

(2) 잘못된 Table이나 변수등이 사용될시

▶ xact_abort Option에 따름

(3) 연산관련 오류 발생시

▶ xact_abort Option에 따름

(4) Table의 제약조건에 맞지 않는 동작수행시

▶ xact_abort Option에 따름

여기에서 xact_abort Option은 Batch처리내(Go)내에서 Query 수행시 오류가 발생하면 자동적으로 Rollback처리할지를 지정하는 Option으로서 On이면 오류가 발생한 Query문다음의 모든 Query실행을 취소하고 이전에 실행한 Query문에 대해서 자동 Rollback처리합니다. Off으로 설정시에는 오류가 발생한 해당 Query문만 실행되지 않고 다음에 나오는 Query문을 계속 실행하게 됩니다.

보통 Transaction을 걸어둔 상태에서 Query실행시 사용자가 Query 수행이 잘못되었다고 판단되면 임의적으로 Rollback하여 Table을 원래상태로 되돌렸으나 xact_abort는 Batch처리내에서 오류가 발생했을경우에 한해 명시적으로 Rollback하지 않아도 자동으로 Rollback을 수행하는 것입니다.
단, Rollback도 Transaction의 일부분이므로 Batch처리에서 Transaction이 같이 동반되어야만 자동적인 Rollback이 가능합니다.

Select *
From HumanResources.Department


Set xact_abort On

xact_abort Option을 On으로 설정합니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insertt Into humanResources.Department
Values('RES', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

Go부터 세개의 Insert 문을 Batch처리로 하여 Query를 실행합니다. 이때 두번째 Insert문에서는 원래 Insert라고 해야하지만 Insertt라고 하여 문법상 맞지 않는 문장을 사용합니다.


해당 Query실행으로 오류가 발생하였습니다. 어떻게 처리되었는지 결과를 알아보기 위해 Table을 확인해 보도록 하겠습니다.

Select *
From HumanResources.Department


Table에 어떠한 내용도 추가되지 않았습니다.

위 Query에서 잘못된것은 중간에 있는 Insertt뿐이었지만 다른 정상적인 Insert문의 실행도 같이 취소되었기 때문입니다.
문법상 오류가 발생한 부분에 대해서는 Batch처리내에 있는 모든 Query문을 실행하지 않는 것입니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Departmentt
Values('RES', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

이번에는 잘못된 Table 이름을 지정하였습니다.(중간에 있는 Insert문에서 Table이름을 humanResources.Department대신 humanResources.Departmentt로 하였습니다.)


처음의 Query문은 정상적으로 실행하였으나 이후 부터는 오류가 발생하였습니다.
처리된 내용을 확인하기 위해 Table을 확인하여 보겠습니다.

Select *
From HumanResources.Department


처음 Query문은 올바르게 실행되었다고 나왔지만 Table을 확인해보니 추가된 내용은 없습니다. 이는 오류가 발생한 시점의 Query문부터 Batch처리내의 모든 Query문이 실행되지 않았고 동시에 정상적으로 실행했던 Insert문에 대해서도 xact_abort옵션에 의해 자동 Rollback이 되었기 때문입니다.

이처럼 자동 Rollback이 되는 부분은 연산및 제약조건관련 에러에서도 동일하게 작용합니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Department
Values(100/0, 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

100을 0으로 나누는 작업을 수행하도록 합니다.


처음만 정상적으로 처리되고 이후에는 오류가 발생하였습니다.

Select *
From HumanResources.Department


Table에는 추가된 부분이 없습니다. 오류가 발생하여 Query문이 실행되지 않았고 이전에 실행한 Query문에 대해서는 자동적으로 Rollback되었습니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Department
Values('MAT', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

humanResources.Department Table의 Name열에는 동일한 내용의 문자열이 들어갈 수 없도록 제약이 설정되어 있으나 예제에서는 이전의 Insert문과 똑같은 이름의 'MAT'을 추가하도록 하였습니다.


Select *
From HumanResources.Department


오류가 발생한 시점부터 이후 Query문은 실행되지 않았으며 이전에 실행된 Insert문에 대해서는 자동 Rollback처리되었습니다.

HumanResources.Department Table에서 DepartmentID열은 자동증감이 설정된 열이기 때문에 임의적으로 Data를 추가할 수 없으며 Data가 추가될 때마다 자동적으로 열값이 1씩 증가하도록 되어 있습니다. 따라서 DepartmentID열을 제외한 다른열에 Insert문을 통하여 Data를 추가하도록 하였습니다. 이때 예제에서 시도한 Batch처리문에서 오류가 발생하였는데 다만 처음 한행은 올바르게 실행되었다는 점을 주목해 주십시오.

Table에 아무런 변화가 없는것은 올바르게 실행된 Insert에 대해 자동적으로 Rollback이 실행되었기 때문입니다. 하지만 이 Rollback은 이미 추가된 Data를 원상태로 되돌리기 위한 Rollback일뿐 Table의 변화까지도 Rollback되진 않습니다.

즉, 이전에 실행된 Insert문에 의해 HumanResources.Department Table의 DepartmentID값이 1이 증가된 상황에서 Rollback한다 하더라도 증가된 값까지 Rollback으로 되돌아 가진 않는 것입니다.
따라서 해당 Table에 Data가 정상적으로 추가되면 자동증가값이 순서대로 들어가지 않게 되는 문제가 발생할 수 있는 것입니다.

Insert Into humanResources.Department
Values('abcd', 'abcde', getdate())

humanResources.Department Table에 임의로 Data를 추가 합니다.


Select *
From HumanResources.Department

humanResources.Department Table을 확인합니다.


DepartmentID열값이 18로 들어가 있습니다. 이것은 이전에 실행한 Insert문에 의해 이미 DepartmentID값이 증가한 상태이며 이후에 실행한 Insert문에대해서도 값의 자동증가에 따라 1이 증가되었기 때문입니다.

지금까지 살펴본 Batch처리문에서 한가지 의문을 가질 수 있는건 'Batch처리를 실행할때 Transaction을 시작하고 Query를 실행하였다면 오류가 발생했을때 이후의 Query문이 실행되지 않았으므로 당연히 Commit Tran문도 실행되지 않은 것으로 봐야하지 않을까?'하는 것입니다. 결국 Data의 추가가 완료되지 않았을뿐 'Rollback되었다 라고는 보기 어렵다' 라고 하는 것인데 이러한 부분은 xact_abort Option을 Off로 한뒤에 같은 Query를 실행해 보면 확연히 다르다는 것을 알 수 있습니다.

Set xact_abort Off

xact_abort Option을 Off로 설정합니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Departmentt
Values('RES', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran


이전과 마찬가지로 같은 오류내용이 나타났습니다. 처리결과를 확인해 보기 위해 Table을 조회해 보도록 하겠습니다.

Select * From HumanResources.Department


오류가 발생한 이후의 Query문은 실행되지 않았지만 처음 정상적으로 실행되었던 Insert문에 대해서는 Rollback되지 않았습니다. 이것은 xact_abort가 Off로 되어 있기 때문입니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Department
Values(100/0, 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

중간 Insert문에서 0으로 나누는 작업을 시도합니다.


일부러 의도한 Query문에 대해서는 오류가 나타났으며 나머지 두 Insert문은 정상적으로 실행되었음을 나타내고 있습니다. 그럼 정말 그런지 Table을 확인해 보도록 하겠습니다.

Select *
From HumanResources.Department


중간 Insert문을 제외하고 나머지 쿼리문은 올바르게 실행되었습니다. xact_abort가 Off인 상태에서 연산관련 오류가 발생하면 해당 Query문만 실행되지 않을뿐 나머지 Query에 대한 실행은 계속하게 되는 것입니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Department
Values('MAT', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

두번째 Insert문에서 첫번재 Insert문과 똑같은 이름의 값을 추가하도록 합니다.


연산오류와 동일하게 문제가 되는 Query를 제외하고 다른 부분은 올바르게 실행되었음을 나타냅니다.

Select *
From HumanResources.Department


오류가 발생한 Insert문을 제외하고 다른 Query문은 정상적으로 수행되었습니다. Table의 제약조건을 위반하는 경우에도 xact_abort가 Off면 해당 Query문을 제외한 다른 Query문은 정상적으로 수행합니다.
0 0
Programming/Microsoft SQL Server
T-SQL에서의 Batch처리는 Go문부터 실행(혹은 또다시 Go가 나올때 까지)하는 전체를 의미합니다.

원래 SQL Server의 Query실행단위는 하나의 Batch처리단위로 실행하는 것을 원칙으로 합니다만 일반적인 상황에서는 Batch처리가 필요하지 않고 저장Procedure를 생성하여 Query를 작성하는 경우에 Batch처리의 개념으로 Go문이 사용됩니다.

또한 일괄처리 내에서 작성되는 Query중 다음 Create문은 같은 Batch처리단위에서 사용될 수 없으며 Go를 써서 처리를 분리해야 합니다.

Create Default
Create Function
Create Procedure
Create Trigger
Create View


Procedure의 Batch처리단위

참고:
(1) 위 Procedure의 끝부분에 있는 End; 에서 ; 문자는 종결자라고 합니다. 종결자는 Query가 실행되는 하나의 Query단위의 종료문자에 해당합니다.
(2) Go뒤에 숫자를 써주게 되면 해당 하는 숫자만큼 Go영역의 배치처리실행을 반복하게됩니다.

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

[SQL] 케이스(Case)  (0) 2010.09.07
[SQL] 예외처리(try ~ catch)  (0) 2010.09.06
[SQL] Batch처리(일괄처리)의 개념  (0) 2010.09.03
[SQL] Update  (0) 2010.09.01
[SQL] Replicate로 문자열 채워넣기  (0) 2010.08.26
[SQL] Delete  (0) 2010.08.25
0 0
1
블로그 이미지

클리엘