July 10, 2015 at 1:55 am
Hi
I have a number of procs that run within an ETL system.
I have wrapped the code in each proc in transactions but some procs run other procs within the code
so
create proc proc1
as
begin transaction t1
code
code
code
exec proc 2
commit transaction t1
and proc2 looks something like
create proc proc2
as
begin transaction t1
code
code
code
commit transaction t1
will there be an issue with 2 transactions with the same name, one running in the other in a different proc?
i hope ive explained that well
thanks in advance
July 10, 2015 at 2:18 am
Firstly, don't name your transactions. Doing so can lead to errors later on. Naming transactions is pretty much for documentation purposes.
Second, SQL doesn't actually have nested transactions. The syntax looks like it does, but there's still only one transaction actually there. A rollback anywhere will roll back to the outermost BEGIN TRANSACTION.
It also looks like you don't have error handling, which means that the transactions aren't protecting you against user errors. If any one piece of code fails, all the others will run and the transaction will commit, having done part of the work.
Long-winded way of saying you probably need to reconsider and redesign the transaction part of your code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2015 at 2:50 am
hi, thanks a lot for the reply
i do have error handling
my code actually looks something like this
Begin Try
Begin Transaction T1
Commit Transaction T1
ENd TRy
Begin Catch
Rollback Transaction T1
insert into error table
end catch
so if i have "nested" transactions any rollback will always go right back to the outer most transaction?
July 10, 2015 at 2:52 am
In this case, no, because you're naming transactions. Instead the rollback will *fail* and you will be left with an uncommitted transaction that will cause huge problems later.
Stop naming your transactions. Definitely never name a rollback. SQL does not actually have nested transactions, just some syntax which looks like it does
If you take the names off all your transactions, then yes, a rollback anywhere will roll back to the outermost begin transaction.
You probably want to re-design this so that you're only ever starting a transaction in one place and ending it in the same scope.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2015 at 3:40 am
thanks agina for the reply,
Rather than this
Begin Try
Begin Transaction T1
--exec Proc2
Commit Transaction T1
ENd TRy
Begin Catch
--Rollback Transaction T1
--insert into error table
end catch
i should perhaps look at this
Begin Try
Begin Transaction
--Commit Transaction
ENd TRy
Begin Catch
--Rollback Transaction
--insert into error table
end catch
exec Proc2
?
July 10, 2015 at 3:43 am
No, why would you only want proc 2 to be executed if there's an error? Doesn't make much sense and isn't the same as the previous ones.
You need to decide where you're going to put the transactions, in the outer proc or the inner proc, then change all procs so that they work that way
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2015 at 3:49 am
ok thanks a lot
proc2 is set outside the catch block, i did put it inside by mistake and editted my post, you probably saw that
i understand now anyway, thanks a lot for your help
July 10, 2015 at 3:57 am
erics44 (7/10/2015)
proc2 is set outside the catch block
In that case, if anything goes wrong in the statements in proc 1, proc 2 will still execute and commit. If that's what you want, fine. If you want a failure in the earlier statements in proc 1 to result in proc 2 not running, then it's not. You need to look at your requirements to decide.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply