A question of Transactions

[Simon Harvey]

Hi everyone,

Can anyone tell me, if I use and ADO transaction object to execute say 10
stored procedures, and the stored procedures are themselve quite quite long
and multistaged, do I need to use transaction statements inside the
individual procedures to avoid potential concurrency issues, or am I
protected from this by virtue of the ADO transaction object.

The reason I ask is, it could be the case that the ADO.net transaction
simply ensures that the stored procedures operate in an all or nothing
manner. This may mean that within a complicated multi staged stored
procedure information could become corrupted because the relevent multi
staged code *inside* the procedure isn't transacted.

I hope that make sense. My query pertains to SQL Server but I'm guessing the
same would be true of any db that supports transactions and SProcs.

Thanks

Simon

 

[Mary Chipman]

If you have complex explicit transactions, your best bet is going to
be to implement them in your stored procedure(s) in T-SQL both in
terms of performance and simplicity. Implementing them in client code
may cause more round trips and not be as performant. Call a single
stored procedure that executes the transactions and returns
success/failure information in output parameters. See the BEGIN
TRANSACTION and related topics in SQL Books Online for more
information.

--Mary