Monday, March 26, 2012

escaping a transaction

The code is
proc1
begin tgan
insert
exec proc2
commit tran
end
proc2
begin tran
commit tran
exec proc3 -- do not want to it be in the proc1's tran
end
The desire is to escape the execution of proc3 from the transaction started
by proc1.
If you like, the apology for the decision is: The proc3 does some complex
things (a couple of records are inserted, deleted, updated). This must be
done 1) atomically (all or nothing) but the operations are 2) expensive and
3) must always commit. So, for performance reasons, I have decieded to do it
transceding the transaction. In case of failure, which may happen only
because of system shut down, the state can be restored manually.I'm not sure I completely understand your question, however you may want to
take a look at the following stuff.
Check out ROLLBACK TRAN
http://msdn2.microsoft.com/en-us/library/ms181299.aspx
and TRY ... CATCH (SQL 2005 only)
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
--
Ekrem Önsoy
"valentin tihomirov" <V_tihomirov@.best.ee> wrote in message
news:eNi74gCFIHA.3400@.TK2MSFTNGP03.phx.gbl...
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction
> started by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive
> and 3) must always commit. So, for performance reasons, I have decieded to
> do it transceding the transaction. In case of failure, which may happen
> only because of system shut down, the state can be restored manually.
>|||Hi
I am not sure why you would want to do this as it would prolong the length
of the transaction without participating in it!
You may want to look at service broker, you could call xp_cmdshell to run a
query.
John
"valentin tihomirov" wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
>
>|||On 21 Oct, 22:21, "valentin tihomirov" <V_tihomi...@.best.ee> wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
proc1
begin tran
insert
commit tran
exec proc2
end
--
David Portas

No comments:

Post a Comment