Transactions Question RRS feed

  • Question

  • If you ask a .net developer he would likely say that he uses System.Transactions to manage transactions, a DBA on the other hand places transactions within the T-SQL of stored procedures.


    What rules do architects and others, use when determining if the transactions should be placed in:

    • .net Middle Tier Components using System.Transactions for example.
    • T-SQL Stored Procedures.
    • Both .net Middle Tier Components and T-SQL Stored Procedures.

    Thanks in advance,


    Doug Holland


    Tuesday, March 11, 2008 5:12 PM


  • Doug,


    I think that it's more about the scope that the transaction has than anything.


    If the transaction can be created and destroyed within a stored procedure for example then it probably belongs there.  If that transaction will be able to handle all problems within the stored procedure code, then that's where it should be.


    There could be a situation where multiple stored procedures are executed only in certain circumstances, and in that case you'd need to use a TransactionScope object in code.


    There may also be situations based around the manipulation of data in code that could cause a failure, in which case the code is the place to have the transaction, as that can cover all the problems.


    Then there's a step back from that, and failure within code that causes objects to roll back, using COM+ as well.


    Where the transaction is implemented is down to the requirements of the system, and the points that the transaction may fail. 


    I hope this helps,


    Martin Platt.

    Tuesday, March 11, 2008 11:28 PM