내부에 3 개의 저장 프로 시저 만 실행하는 저장 프로 시저가 있습니다. 마스터 SP가 성공하면 1 개의 매개 변수 만 사용하여 저장합니다.
첫 번째 저장 프로 시저가 마스터 저장 프로 시저에서 제대로 작동하지만 두 번째 저장 프로 시저가 실패하면 마스터 SP의 모든 SP를 자동으로 롤백하거나 명령을 수행해야합니까?
내 절차는 다음과 같습니다.
CREATE PROCEDURE [dbo].[spSavesomename]
-- Add the parameters for the stored procedure here
@successful bit = null output
AS
BEGIN
begin transaction createSavebillinginvoice
begin Try
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
EXEC [dbo].[spNewBilling1]
END
BEGIN
EXEC [dbo].[spNewBilling2]
END
BEGIN
EXEC [dbo].[spNewBilling3]
END
set @successful = 1
end Try
begin Catch
rollback transaction createSavesomename
insert into dbo.tblErrorMessage(spName, errorMessage, systemDate)
values ('spSavesomename', ERROR_MESSAGE(), getdate())
return
end Catch
commit transaction createSavesomename
return
END
GO
답변
질문에 표시된 코드 만 주어지고 세 가지 하위 프로세스 중 어느 것도 명시 적 트랜잭션 처리가 없다고 가정하면 세 가지 하위 프로세스 중 하나에서 오류가 발생 ROLLBACK
하고 CATCH
블록의 모든 롤백이 모두 롤백됩니다. 일의.
그러나 다음은 트랜잭션에 대해 알아야 할 사항입니다 (적어도 SQL Server에서는).
-
오직 하나 없습니다 실제 거래 (첫 번째)에 상관없이 전화를 몇 번,
BEGIN TRAN
- 트랜잭션 이름을 지정할 수 있으며 (여기에서 한 것처럼) 해당 이름이 로그에 나타나지만 이름 지정은 첫 번째 / 가장 바깥 쪽 트랜잭션에만 적용됩니다 (다시 말하면 첫 번째 트랜잭션 은 트랜잭션 임).
BEGIN TRAN
이름을 지정했는지 여부에 관계없이 호출 할 때마다 트랜잭션 카운터는 1 씩 증가합니다.- 당신은 현재 레벨을 볼 수 있습니다
SELECT @@TRANCOUNT;
- 모든
COMMIT
경우에 실행 한 명령@@TRANCOUNT
위의 2에서 또는 더, 한 번에 하나의 트랜잭션 카운터를 감소보다 아무것도하지 않습니다. - 에
COMMIT
있을 때 발행 될 때 까지 아무것도 커밋되지 않습니다@@TRANCOUNT
.1
- 위의 정보가 명확하게 표시되지 않은 경우를 대비하여 : 트랜잭션 레벨에 관계없이 실제 트랜잭션 중첩이 없습니다.
-
저장 점을 사용하면 취소 할 수있는 트랜잭션 내 에서 작업 서브 세트를 작성할 수 있습니다.
- 저장 점은
SAVE TRAN {save_point_name}
명령을 통해 생성 / 표시됩니다 - 저장 점 은 전체 트랜잭션을 롤백하지 않고 취소 할 수있는 작업 서브 세트의 시작 을 표시합니다 .
- 저장 점 이름은 고유 할 필요는 없지만 동일한 이름을 두 번 이상 사용하면 여전히 고유 한 저장 점이 생성됩니다.
- 저장 점 은 중첩 될 수 있습니다.
- 세이브 포인트는 커밋 할 수 없습니다.
- 를 통해 저장 포인트를 취소 할 수 있습니다
ROLLBACK {save_point_name}
. (아래에 더 자세히 설명되어 있습니다) - 저장 점을 롤백하면 롤백 된 파일이 생성 된 후 생성 된 저장 점 ( “중첩”)을 포함 하여 가장 최근에 호출 한 후 발생한 모든 작업이 취소됩니다
SAVE TRAN {save_point_name}
. - 저장 점 롤백은 트랜잭션 수 / 레벨에 영향을 미치지 않습니다.
- 전체 트랜잭션
SAVE TRAN
전체ROLLBACK
를 발행하는 경우를 제외하고 는 초기 이전에 수행 한 모든 작업을 취소 할 수 없습니다 . - 명확하게 말하면,
COMMIT
when@@TRANCOUNT
가 2 이상인 경우 발행 포인트에 영향을 미치지 않습니다 (다시 말하면 1 이상의 트랜잭션 레벨은 해당 카운터 외부에 존재하지 않기 때문).
- 저장 점은
-
특정 명명 된 트랜잭션을 커밋 할 수 없습니다. 와 함께 제공되는 경우 트랜잭션 “name”
COMMIT
은 무시되며 읽기 쉽도록 존재합니다. -
ROLLBACK
이름없이 실행은 항상 모든 트랜잭션을 롤백합니다. -
ROLLBACK
중 하나에 이름 필수의 대응 발급 :- 첫 번째 트랜잭션의 이름은 다음 과 같습니다. 동일한 트랜잭션 이름으로
noSAVE TRAN
가 호출 되었다고 가정하면 모든 트랜잭션이 롤백됩니다. - “저장 지점”(위 설명) :
이 동작은 가장 최근SAVE TRAN {save_point_name}
에 호출 된 이후에 변경된 모든 것을 “실행 취소”합니다 . - 첫 번째 트랜잭션의 이름이 a)이고 b)
SAVE TRAN
해당 이름으로 명령을 발행 한 경우 해당 트랜잭션 이름의 각 ROLLBACK은 해당 이름의 왼쪽이 없을 때까지 각 저장 점을 취소합니다. 그 후, 해당 이름으로 발행 된 ROLLBACK은 모든 트랜잭션을 롤백합니다. -
예를 들어, 다음 명령이 표시된 순서대로 실행되었다고 가정하십시오.
BEGIN TRAN A -- @@TRANCOUNT is now 1 -- DML Query 1 SAVE TRAN A -- DML Query 2 SAVE TRAN A -- DML Query 3 BEGIN TRAN B -- @@TRANCOUNT is now 2 SAVE TRAN B -- DML Query 4
이제 발행하면 (다음 시나리오 각각은 서로 독립적입니다) :
ROLLBACK TRAN B
한 번 : “DML 쿼리 4″를 실행 취소합니다.@@TRANCOUNT
여전히 2입니다.ROLLBACK TRAN B
두 번 : “DML 쿼리 4″를 실행 취소 한 다음 “B”에 해당하는 저장 점이 없으므로 오류가 발생합니다.@@TRANCOUNT
여전히 2입니다.ROLLBACK TRAN A
한 번 : “DML 쿼리 4″및 “DML 쿼리 3″을 실행 취소합니다.@@TRANCOUNT
여전히 2입니다.ROLLBACK TRAN A
두 번 : “DML 쿼리 4”, “DML 쿼리 3″및 “DML 쿼리 2″가 실행 취소됩니다.@@TRANCOUNT
여전히 2입니다.ROLLBACK TRAN A
세 번 : “DML 쿼리 4”, “DML 쿼리 3″및 “DML 쿼리 2″가 실행 취소됩니다. 그런 다음 전체 트랜잭션을 롤백합니다 ( “DML 쿼리 1″만 남음).@@TRANCOUNT
이제 0입니다.COMMIT
한 번 :@@TRANCOUNT
1로 내려갑니다.COMMIT
한 번, 한ROLLBACK TRAN B
번 : 1@@TRANCOUNT
로 내려갑니다. 그러면 “DML 쿼리 4″가 실행 취소됩니다 (COMMIT가 아무 것도하지 않았다는 것을 증명).@@TRANCOUNT
여전히 1입니다.
- 첫 번째 트랜잭션의 이름은 다음 과 같습니다. 동일한 트랜잭션 이름으로
-
거래 이름 및 저장 점 이름 :
- 최대 32 자까지 가능
- 인스턴스 수준 또는 데이터베이스 수준 데이터 정렬에 관계없이 이진 데이터 정렬이있는 것으로 간주됩니다 (현재 문서에서는 대소 문자를 구분하지 않음).
- 자세한 내용 은 다음 게시물 의 거래 이름 섹션을 참조하십시오. 이름의 내용 : T-SQL 식별자의 엉뚱한 세계 내부
-
저장 프로 시저 자체는 암시 적 트랜잭션이 아닙니다. 명시 적 트랜잭션이 시작되지 않은 경우 각 쿼리 는 암시 적 트랜잭션입니다. 그렇기 때문에 프로그래밍상의 이유가없는 한 단일 쿼리에 대한 명시 적 트랜잭션이 필요하지 않은 경우가 있습니다
ROLLBACK
. -
저장 프로 시저를 호출 할 때는 호출 될 때와 같은 값으로 종료해야합니다
@@TRANCOUNT
. 의미는, 당신은 할 수 없습니다 :BEGIN TRAN
호출 / 부모 프로세스에서 커밋을 기대하면서 커밋하지 않고 proc에서 시작하십시오 .ROLLBACK
proc가 호출되기 전에 명시 적 트랜잭션이 시작@@TRANCOUNT
되면 0 을 반환 하므로 a를 발행 할 수 없습니다 .
스타트했을 때보 다 높거나 낮은 트랜잭션 수로 저장 프로 시저를 종료하면 다음과 유사한 오류가 발생합니다.
메시지 266, 수준 16, 상태 2, 프로 시저 YourProcName, 줄 0
EXECUTE 이후의 트랜잭션 수는 일치하지 않는 BEGIN 및 COMMIT 문의 수를 나타냅니다. 이전 카운트 = X, 현재 카운트 = Y -
일반 변수와 마찬가지로 테이블 변수는 트랜잭션에 의해 바인딩되지 않습니다.
독립적으로 호출 될 수 있고 (따라서 트랜잭션 처리가 필요할 수있는) proc에서 트랜잭션 처리를하는 것과 관련하여 (따라서 트랜잭션 처리가 필요하지 않은) 다른 proc에서 호출하는 경우 : 몇 가지 다른 방식으로 수행 할 수 있습니다.
잘 작동하는 것 같다 내가 지금 몇 년 동안 그것을 처리되었는지 방법은 단지이다 BEGIN
/ COMMIT
/ ROLLBACK
의 가장 바깥 층. 서브 프로세스 호출은 트랜잭션 명령을 건너 뜁니다. 아래에 각 proc에 넣은 내용을 설명했습니다 (물론 트랜잭션 처리가 필요한 각각).
- 각 절차의 상단에서
DECLARE @InNestedTransaction BIT;
-
simple 대신 다음을
BEGIN TRAN
수행하십시오.IF (@@TRANCOUNT = 0) BEGIN SET @InNestedTransaction = 0; BEGIN TRAN; -- only start a transaction if not already in one END; ELSE BEGIN SET @InNestedTransaction = 1; END;
-
simple 대신 다음을
COMMIT
수행하십시오.IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0) BEGIN COMMIT; END;
-
simple 대신 다음을
ROLLBACK
수행하십시오.IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0) BEGIN ROLLBACK; END;
이 방법은 트랜잭션이 SQL Server 내에서 시작되었는지 또는 앱 계층에서 시작되었는지에 관계없이 동일하게 작동해야합니다.
내에서 취급이 트랜잭션의 전체 템플릿 TRY...CATCH
구조, 다음 DBA.SE 질문에 대한 내 대답을 참조하십시오 : 우리는뿐만 아니라 저장 프로 시저에 C # 코드에서 트랜잭션을 처리해야합니다 .
“기본”을 넘어 서면 알아야 할 몇 가지 추가 트랜잭션 뉘앙스가 있습니다.
-
기본적으로 트랜잭션은 대부분 오류가 발생할 때 자동으로 롤백 / 취소되지 않습니다. 적절한 오류 처리를하고
ROLLBACK
자신을 호출하는 한 일반적으로 문제가되지 않습니다 . 그러나 배치 중단 오류 또는 사용OPENQUERY
(또는 일반적으로 링크 된 서버)의 경우와 같이 원격 시스템에서 오류가 발생 하는 경우와 같이 상황이 복잡 해지는 경우가 있습니다. 대부분의 오류는을 사용하여 잡힐 수 있지만TRY...CATCH
두 가지 방법으로 잡을 수 없습니다 (현재 어떤 오류를 기억할 수는 없습니까?). 이 경우SET XACT_ABORT ON
트랜잭션을 올바르게 롤백하는 데 사용해야 합니다.SET XACT_ABORT ON은 에 SQL 서버를 발생 즉시 (하나가 활성화 된 경우) 롤백 트랜잭션 과 배치 중단하면 어떤 오류가 발생합니다. 이 설정은
TRY...CATCH
구문 을 도입 한 SQL Server 2005 이전에 존재했습니다 . 대부분의 경우,TRY...CATCH
대부분의 상황을 처리하고 그래서 대부분의 필요성을 쓸모 없게XACT_ABORT ON
. 그러나OPENQUERY
(그리고 현재 기억할 수없는 다른 시나리오)를 사용할 때 여전히을 사용해야SET XACT_ABORT ON;
합니다. -
트리거 내부
XACT_ABORT
는 암시 적으로로 설정됩니다ON
. 이 원인이 어떤 트리거를 해고 전체 DML 문을 취소하려면 트리거 내에서 오류가 발생했습니다. -
특히 트랜잭션을 사용할 때 항상 적절한 오류 처리가 있어야합니다.
TRY...CATCH
SQL Server 2005에 도입 된 이 구문은 거의 모든 상황을 처리 할 수있는 수단을 제공합니다.@@ERROR
각 명령문 이후의 테스트에 비해 개선 된 결과 로 배치 중단 오류에 큰 도움이되지 않았습니다.TRY...CATCH
그러나 새로운 “상태”를 도입했습니다. 구문을 사용 하지 않을 때TRY...CATCH
활성화 된 트랜잭션이 있고 오류가 발생하면 수행 할 수있는 몇 가지 경로가 있습니다.XACT_ABORT OFF
및 명령문 중단 오류 : 트랜잭션이 여전히 활성 상태이며 다음 명령문 (있는 경우)으로 처리가 계속됩니다 .XACT_ABORT OFF
대부분의 배치 중단 오류 : 트랜잭션이 여전히 활성 상태이며 다음 배치 (있는 경우)로 처리가 계속됩니다 .XACT_ABORT OFF
특정 배치 중단 오류 : 트랜잭션이 롤백되고 다음 배치 (있는 경우)로 처리가 계속됩니다 .XACT_ABORT ON
및 모든 오류 : 트랜잭션이 압연 처리되고, 다음으로 계속 배치 있는 경우.
그러나을 사용할 때
TRY...CATCH
배치 중단 오류는 배치를 중단하지 않고 대신 제어를CATCH
블록으로 전송합니다 . 이 (XACT_ABORT
가)OFF
인 경우 거래는 여전히 대부분의 시간 동안 활성화되며 귀하는COMMIT
또는 대부분 가능성이ROLLBACK
있습니다. 그러나와 같은 특정 배치 중단 오류가 발생OPENQUERY
하거나 (가있는 경우XACT_ABORT
)ON
트랜잭션은 “커밋 할 수없는”새로운 상태에있게됩니다. 이 상태에서는COMMIT
DML 작업을 수행 할 수 없으며 수행 할 수도 없습니다 . 당신이 할 수있는 모든이다ROLLBACK
과SELECT
문. 그러나이 “comittable”상태에서 트랜잭션은 오류 발생시 롤백되었으며 발행은ROLLBACK
형식 일 뿐이지 만 반드시 수행되어야합니다.XACT_STATE 함수를 사용하여 트랜잭션이 활성인지, 커밋 할 수 없는지 또는 존재하지 않는지 확인할 수 있습니다.
CATCH
if에서-1
테스트하는 대신 결과가 (즉, 커밋 할 수 없는지) 결정하기 위해 블록 에서이 기능을 확인하는 것이 좋습니다@@TRANCOUNT > 0
. 그러나와 함께XACT_ABORT ON
있을 수있는 유일한 상태 여야하므로 테스트@@TRANCOUNT > 0
및XACT_STATE() <> 0
이에 해당 하는 것으로 보입니다 . 때 다른 한편으로는,XACT_ABORT
인OFF
및 활성 트랜잭션이, 다음 중 하나의 상태를 가질 수 있습니다1
또는-1
에서CATCH
발행의 가능성을 허용 블록,COMMIT
대신ROLLBACK
나는 경우 생각할 수 없다,하지만 때 사람을 ( 하고 싶다COMMIT
트랜잭션이 커밋 가능한 경우). 블록XACT_STATE()
내 에서의 사용에 대한 자세한 정보와 연구 는 다음 DBA.SE 질문에 대한 답변에서 찾을 수 있습니다. XACT_ABORT가 ON으로 설정된 경우 CATCH 블록 내부에서 트랜잭션을 커밋 할 수있는 경우는 무엇입니까? . 특정 시나리오에서 잘못 반환 하는 사소한 버그가 있습니다. XACT_STATE ()는 일부 시스템 변수와 함께 SELECT에 사용될 때 FROM 절없이 1을 반환합니다.CATCH
XACT_ABORT ON
XACT_STATE()
1
원래 코드에 대한 참고 사항 :
- 도움이되지 않으므로 트랜잭션에 지정된 이름을 제거 할 수 있습니다.
- 당신은 필요하지 않습니다
BEGIN
및END
각 주위에EXEC
전화를
답변
예, 마스터 스토어드 프로 시저의 catch 명령문에 오류 롤백 코드가 실행되면 직접 명령문 또는 중첩 된 스토어드 프로 시저를 통해 수행 된 모든 조작이 롤백됩니다.
중첩 저장 프로 시저에 명시 적 트랜잭션을 적용하지 않은 경우에도 이러한 저장 프로시 저는 암시 적 트랜잭션을 사용하고 완료시 커밋됩니다. 그러나 중첩 저장 저장 프로 시저의 명시 적 또는 암시 적 트랜잭션을 통해 커밋 한 SQL Server 엔진은이를 무시하고 마스터 저장 프로 시저가 실패하고 트랜잭션이 롤백되면 이러한 중첩 저장 프로 시저로 모든 작업을 롤백하십시오.
가장 바깥 쪽 트랜잭션 끝에서 수행 된 조치를 기반으로 트랜잭션이 커미트되거나 롤백 될 때마다. 외부 트랜잭션이 커밋되면 내부 중첩 트랜잭션도 커밋됩니다. 외부 트랜잭션이 롤백되면 내부 트랜잭션이 개별적으로 커밋되었는지 여부에 관계없이 모든 내부 트랜잭션도 롤백됩니다.
참조 http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx