A question of Transactions

  • Thread starter Thread starter Simon Harvey
  • Start date Start date
S

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
 
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
 
Back
Top