Since some days I'm facing problems with the nested transactions and I read the they are not fully supported in sql server 2005 so I'd need an help from a more experienced SS programming.
My outer SP must live inside a transaction. It calles an another SP but IT MUST NOT ROLLBACK in case the INNER SP fails.
My nested SP, let's call it INNERSP is quite complex because it involves INSERT-UPDATE-DELETE, mathematic calculation and calls other SP as well.
Of course I want to make the INNERSP error-proof and I'd like to wrap it inside a try..catch structure.
If my INNERSP is:
BEGIN TRY
Insert ... --this insert cause an error that is trapped in the BEGIN CATCH
BEGIN TRANSACTION
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
The Rollback statement roll back also the transaction of the caller, but for me it is not acceptable.
I was investigating on the SAVE TRANSACTION statement but if I use it the @.@.TRANCOUNT is not decremented and my OUTERSP ends with a @.@.TRANCOUNT that is not zero, but from what I know "Nothing is actually committed until @.@.TRANCOUNT = 0"
Any helps is more that welcome!!
Thank you
Marina B.
Try putting your begin transaction statement before the work that you want inside of the transaction.|||Interesting problem. It appears that even if an error in the called procedure is caught, the calling procedure's transaction still knows about it somehow:
if object_id('x') is not null drop table x
go
create table x(x1 int)
go
if object_id('px') is not null drop proc px
go
create proc px as
begin try
insert x values ('x')
end try
begin catch
print 'caught px'
end catch
go
if object_id('py') is not null drop proc py
go
create proc py as
begin tran
begin try
exec px
print @.@.error
insert x values (2)
commit
end try
begin catch
print 'caught py'
print error_message()
rollback
end catch
go
exec py
select * from x
go
/*
caught px
0
caught py
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
*/
|||Not surprisingly, this behavior is my design. More importantly, this only applies to "fatal" errors. Per BOL:
"A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block."
This script is changed from the one above: the error that occurs in px is not a "fatal" error (the insert of "x" into an integer column is considered a syntax error and is fatal), and after it is caught, the py procedure does complete the transaction, resulting in a new row being inserted into table x:
set nocount on
go
if object_id('x') is not null drop table x
go
create table x(x1 int primary key)
go
if object_id('px') is not null drop proc px
go
create proc px as
begin try
insert x values (1)
end try
begin catch
print 'caught px'
print error_message()
print xact_state()
end catch
go
if object_id('py') is not null drop proc py
go
create proc py as
begin tran
begin try
insert x values (1)
exec px
print @.@.error
commit
end try
begin catch
print 'caught py'
print error_message()
rollback
end catch
go
exec py
select * from x
go
/*
caught px
Violation of PRIMARY KEY constraint 'PK__x__177C9889'. Cannot insert duplicate key in object 'dbo.x'.
1
0
*/
|||SQL Server does not support autonomous transactions. Only way is to write an extended SP/SQLCLR SP to make a loopback connection to the database and call the inner SP without enlisting in same transaction. This technique of course has lot of disadvantages and side-effects. For one, you could create a distributed deadlock that is undetectable by SQL Server. You might end up blocking your own transaction consuming valuable resources on the server and so on.
Another technique is to use a table variable to store the results from the inner SP. Since table variables do not participate in user transactions, any subsequent COMMIT or ROLLBACK will not affect the data in the table variable. You can then retrieve the rows from the table variable after the transaction or in the CATCH block successfully. This will work from SQL Server 2000 onwards except there is no TRY...CATCH there.
|||Hello everybody,
thankx for the answers .
By the way I found another solution that solve my problem but because SQL server programming is not my main skill I would like to know what to you think about it.
select @.@.trancount, 'Entering'
begin transaction
begin try
-- Detect if the procedure was called from an active transaction and save that for later use.In the procedure, @.TranCounter = 0 means there was no active transaction and the procedure started one.
-- @.TranCounter > 0 means an active transaction was started before the procedure was called.
DECLARE @.TranCounter INT;
DECLARE @.i INT;
SET @.TranCounter = @.@.TRANCOUNT;
select @.TranCounter,'Entering s.p. 1st level from broker'
IF @.TranCounter > 0
BEGIN
-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.
SAVE TRANSACTION ProcedureSave;
select @.@.trancount, 'Save transaction SP 1st level'
END
ELSE
BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION;
select @.@.trancount, 'New transaction created on the s.p. 1st level from broker'
END
BEGIN TRY
set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE FIRST LEVEL S.P.
--this is an another nested SP
DECLARE @.TranCounterSP2 INT;
SET @.TranCounterSP2 = @.@.TRANCOUNT;
select @.TranCounterSP2,'Entering s.p. 2nd level from s.p. 1st level'
IF @.TranCounterSP2 > 0
BEGIN
-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.
SAVE TRANSACTION ProcedureSave2;
select @.@.trancount, 'Save transaction SP 2nd level'
END
ELSE
BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION;
select @.@.trancount, 'New transaction created on the s.p. 2nd level from sp 1st level'
END
BEGIN TRY
set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE SECOND LEVEL S.P.
IF @.TranCounterSP2 = 0
BEGIN
COMMIT TRANSACTION
END
select @.@.trancount, 'S.p. 2nd level was committed'
END TRY
BEGIN CATCH
select @.@.trancount, 'SP 2nd level transaction on going to be rolled back rolled back'
IF @.TranCounterSP2 = 0
-- Transaction started in procedure. Roll back complete transaction - included the outer one.
ROLLBACK TRANSACTION;
ELSE
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION ProcedureSave2;select @.@.trancount, 'SP 2nd level transaction was rolled back'
END CATCH
--this is the end of an another nested SP
-- Get here if no errors; must commit any transaction started in the procedure, but not commit a transaction started before the transaction was called.
IF @.TranCounter = 0
-- @.TranCounter = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
select @.@.trancount, 'SP 1st level transaction on going to be rolled back rolled back'
-- An error occurred; must determine which type of rollback will roll back only the work done in the procedure.
IF @.TranCounter = 0
-- Transaction started in procedure. Roll back complete transaction (INCLUDED the outer TRANSACTION).
ROLLBACK TRANSACTION;
ELSE
-- Transaction started before procedure called, do not roll back modifications made before the procedure was called.
IF XACT_STATE() <> -1
-- If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.
ROLLBACK TRANSACTION ProcedureSave;
select @.@.trancount, 'SP 1st level transaction was rolled back'
END CATCH
commit transaction -- for the outer transaction
select @.@.trancount, 'Outer transaction was committed'
end try
begin catch
if @.@.trancount>0
begin
rollback transaction
select @.@.trancount, 'Outer transaction was rolled back'
end
end catch
select @.@.trancount, 'Outer'
If I try this script everything works as I want, somebody can also confirm me that the transaction are also properly define and I'm not going to execute code in the wrong one?
Thank you
Marina B.
No comments:
Post a Comment