SQL Server Performance Forum – Threads Archive
Nested and autonomous transaction
Hi, I have a requirement for nested and autonomous transaction, I am sure nested transaction is supported by sql server, but, I am not sure about autonomous transaction. I havegiven below my sample script for your reference. create table tmpT1(
f1 int
) create table tmpT2(
f2 int
) — Regarding autonomous transaction
— I want to update data into this table independently, apart from the transaction proc. status, keep it away from the transaction, I mean autonomous. create proc tmpTest12
as begin transaction insert into tmpT1
values(101) — insertion here should be autonomous
insert into tmpT2
values(102) commit transaction
go — Regarding nested transaction
create proc tmpTest11
as begin transaction t1 insert into tmpT1
values(101) begin transaction t2 insert into tmpT2
values(102) rollback transaction t2 commit transaction t1
go tmpTest11 procedure gives run time error, please tell me what could be the problem, in BOL, it is given rollback transaction will consider the outer most transaction. Please provide me more details/links regarding this, thank you. Regards,
Deva
What is the runtime error that is produced?
If you are going to commit T1 no matter what happens in T2, why not commit it before doing the begin for T2?
Is there a trigger perhaps on t2 that ends up in some way requiring access to T1? It could be that perhaps the begin of a second transaction is resolved by another process that is then locked out by the fact that T1 hasn’t been committed. Just some guesses,
Dalton
]]>