SQL Server - вложенные транзакции в хранимой процедуре
Допустим, такова ситуация:
[Stored Proc 1]
BEGIN
BEGIN TRANSACTION
...
exec sp 2
COMMIT
END
Теперь, если SP 2 - откатывается по какой-либо причине, SP 1 - фиксирует или откатывает или выдает исключение?
Благодарю.
7 ответов
В SQL Server нет автономных транзакций. Вы можете увидеть @@TRANCOUNT больше, чем 1, но откат влияет на все это.
РЕДАКТИРОВАТЬ попросил указать на документацию. Не знаю, в какой теме это задокументировано, но я могу показать это вам в действии.
USE tempdb;
GO
Внутренний процесс:
CREATE PROCEDURE dbo.sp2
@trip BIT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
PRINT @@TRANCOUNT;
IF @trip = 1
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
PRINT @@TRANCOUNT;
END
GO
Внешний процесс:
CREATE PROCEDURE dbo.sp1
@trip BIT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
PRINT @@TRANCOUNT;
BEGIN TRY
EXEC dbo.sp2 @trip = @trip;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
PRINT @@TRANCOUNT;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
PRINT @@TRANCOUNT;
END
GO
Итак, теперь давайте назовем это и позволим всему зафиксировать:
EXEC dbo.sp1 @trip = 0;
Результаты:
1
2
1
1
0
Теперь давайте его назовем и откатим внутреннюю процедуру:
EXEC dbo.sp1 @trip = 1;
Результаты:
1
2
0 <- обратите внимание, что откат здесь откатил оба
Число транзакций после EXECUTE указывает на несовпадающее количество операторов BEGIN и COMMIT. Предыдущий счетчик = 1, текущий счетчик = 0.
0
0
Можно выполнить откат SP2 и не потерять работу, выполненную SP1. Но чтобы это произошло, вы должны написать свои хранимые процедуры, используя очень специфический шаблон, как описано в разделе Обработка исключений и вложенные транзакции:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
end catch
end
Не все ошибки могут быть исправлены, существует ряд состояний ошибок, из которых транзакция не может быть восстановлена, наиболее очевидным примером является взаимоблокировка (вы получаете уведомление об исключении взаимоблокировки после того, как транзакция уже откатилась). И SP1, и SP@ должны быть написаны с использованием этого шаблона. Если у вас мошеннический SP, или вы хотите просто использовать существующие хранимые процедуры, которые невольно выдают ROLLBACK Заявления тогда ваше дело потеряно.
Если SP2 откатит транзакцию, SP1 также откатится.
См. http://msdn.microsoft.com/en-US/library/ms187844(v=sql.105).aspx для получения подробной информации.
Во вложенных транзакциях, если откатывается какой-либо из внутренних переходов, выполняется откат всех его внешних транзакций.
Каждая хранимая процедура должна заканчиваться тем же счетчиком транзакций, с которым она была введена. Если количество не совпадает, SQL Server выдает ошибку 266: «Счетчик транзакций после EXECUTE указывает на отсутствие инструкции COMMIT или ROLLBACK TRANSACTION».
Если хранимая процедура не инициирует самую внешнюю транзакцию, она не должна выполнять ROLLBACK.
Если вложенная процедура начинает новую транзакцию; но если он обнаруживает необходимость отката, а значение @@TRANSACTION больше 1, он вызывает ошибку, возвращает сообщение об ошибке вызывающей стороне через выходной параметр или возвращаемое значение и выдает COMMIT вместо ROLLBACK.
CREATE PROCEDURE [dbo].[Pinner]
-- Add the parameters for the stored procedure here
@ErrorMessage varchar(max) out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
begin tran
begin try
throw 51000, 'error occured', 1
commit tran
set @ErrorMessage = ''
end try
begin catch
set @ErrorMessage = ERROR_MESSAGE();
if @@TRANCOUNT = 1
rollback tran
if @@TRANCOUNT > 1
commit tran
end catch
END
create PROCEDURE [dbo].[Pouter]
-- Add the parameters for the stored procedure here
@ErrorMessage varchar(max) out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
begin tran
begin try
EXECUTE [dbo].[Pinner]
@ErrorMessage OUTPUT
if @ErrorMessage <> '' begin
throw 51000, @ErrorMessage, 1
end
commit tran
set @ErrorMessage = ''
end try
begin catch
set @ErrorMessage = ERROR_MESSAGE();
if @@TRANCOUNT = 1
rollback tran
if @@TRANCOUNT > 1
commit tran
end catch
END
DECLARE @ErrorMessage varchar(max)
EXEC [dbo].[Pouter]
@ErrorMessage = @ErrorMessage OUTPUT
SELECT @ErrorMessage as N'@ErrorMessage'
https://www.codemag.com/article/0305111/handling-sql-server-errors-in-nested-procedures
Вот быстрый и грязный способ вложения транзакций в хранимые процедуры (используя код из ответа Аарона), который иногда может быть полезен. Он использует параметр по умолчанию, чтобы указать внутренней процедуре, является ли он вложенным вызовом, и возвращает результат успеха / неудачи внешней процедуре.
CREATE PROCEDURE dbo.sp2
@trip BIT,
@nested BIT = 0
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON
IF @nested = 0 BEGIN TRAN
PRINT @@TRANCOUNT
IF @trip = 1
BEGIN
IF @nested = 0 ROLLBACK
RETURN 1
END
ELSE
BEGIN
IF @nested = 0 COMMIT
END
PRINT @@TRANCOUNT
RETURN 0
END
GO
Внешняя процедура проверяет успех / неудачу, а при необходимости откатывает транзакцию.
CREATE PROCEDURE dbo.sp1
@trip BIT
AS
BEGIN
DECLARE @result INT
SET NOCOUNT, XACT_ABORT ON
BEGIN TRAN
PRINT @@TRANCOUNT
BEGIN TRY
EXEC @result = dbo.sp2 @trip = @trip, @nested = 1
IF @result <> 0
BEGIN
ROLLBACK
RETURN 1
END
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT
RETURN 0
END
GO
USE [DemoProject]
GO
/****** Object: StoredProcedure [dbo].[Customers_CRUD] Script Date: 11-Jan-17 2:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Customers_CRUD]
@Action VARCHAR(10)
,@BId INT = NULL
,@Username VARCHAR(50) = NULL
,@Provincename VARCHAR(50) = NULL
,@Cityname VARCHAR(50) = NULL
,@Number VARCHAR(50) = NULL
,@Name VARCHAR(50) = NULL
,@ContentType VARCHAR(50) = NULL
,@Data VARBINARY(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON;
--SELECT
IF @Action = 'SELECT'
BEGIN
SELECT BId , Username,Provincename,Cityname,Number,Name,ContentType, Data
FROM tblbooking
END
--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO tblbooking(Username,Provincename,Cityname,Number,Name,ContentType, Data)
VALUES (@Username ,@Provincename ,@Cityname ,@Number ,@Name ,@ContentType ,@Data)
END
--UPDATE
IF @Action = 'UPDATE'
BEGIN
UPDATE tblbooking
SET Username = @Username,Provincename = @Provincename,Cityname = @Cityname,Number = @Number,Name = @Name,ContentType = @ContentType,Data = @Data
WHERE BId = @BId
END
--DELETE
IF @Action = 'DELETE'
BEGIN
DELETE FROM tblbooking
WHERE BId = @BId
END
END
GO