A question of Transactions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Simon Harvey (sh856531_at_microsofts_free_email_service.com)
Date: 11/18/04 

Date: Thu, 18 Nov 2004 09:48:47 -0000

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

Relevant Pages

  • Re: Transaction Oriented Architecture (TOA)
    ... It had better be a transaction I already know about so I can record it properly and make sure your session gets credit. ... In that case the client-side Data Layer has the responsibility for mundane tasks like forming SQL queries and encoding/decoding SQL datasets. ... What I am talking about is analogous to that layer that decouples the Business Layer through an interface so the Business objects don't have to know about the specific mechanisms. ... TOA/TOP proposes the database and its application domain stored procedures are the only persistence mechanism necessary, and that the benefits of a focused, single, data-permeable gateway between application and database far exceed the benefits of O/R mappings--regardless of abstraction--and that its lightweight appearance shouldn't be dismissed as missing heavyweight kick. ...
    (comp.object)
  • Re: multiple SP within transaction
    ... >A transaction can span multiple batches, and multiple stored procedures. ... > Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • ADO.net or TSQL Transactions
    ... Should implement a transaction in both the stored procedure AND in ADO.net ... code or is doing it in one or the other good enough to protect against ... >>stored procedures, and the stored procedures are themselve quite quite ... >>protected from this by virtue of the ADO transaction object. ...
    (microsoft.public.sqlserver.programming)
  • ADO.net or TSQL Transactions
    ... Should implement a transaction in both the stored procedure AND in ADO.net ... code or is doing it in one or the other good enough to protect against ... >>stored procedures, and the stored procedures are themselve quite quite ... >>protected from this by virtue of the ADO transaction object. ...
    (microsoft.public.dotnet.framework.adonet)
  • ADO.net or TSQL Transactions
    ... Should implement a transaction in both the stored procedure AND in ADO.net ... code or is doing it in one or the other good enough to protect against ... >>stored procedures, and the stored procedures are themselve quite quite ... >>protected from this by virtue of the ADO transaction object. ...
    (microsoft.public.sqlserver.server)