0

I have the following method in my DAL:

public void SavePlan()
{
    using (TransactionScope scope =
           new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        CallSaveDataProc();
        CallLogMsgProc();
        scope.Complete();
    }
}

I have deliberately put a COMMIT Transaction in the CallLogMsgProc without creating a Transaction. This results in a SQLException being thrown from CallLogMsgProc procedure and scope.Complete() never executes.

However, in my database, I'm still seeing records saved by the first method, CallSaveDataProc. Am I doing something wrong?

Dave M
  • 1,302
  • 1
  • 16
  • 28
Iftikhar Ali
  • 369
  • 3
  • 12
  • Could you put the code from CallSaveDataProc(), CallLogMsgProc() and any stored procedure you have used? – Fabio Nov 30 '11 at 19:22

1 Answers1

0

Starting/committing transactions have to be paired, and preferably each pair should ideally be in the same scope (though each pair doesn't have to be in the same scope as another pair).

So you have a case of starting a transaction via the new TransactionScope, followed by Commit in your stored procedure (which will save the work... as you are seeing), followed by an attempt to commit the transaction "seen" by TransactionScope, which has now become invalid.

Community
  • 1
  • 1
Kit
  • 20,354
  • 4
  • 60
  • 103
  • So a commit in the child stored procedure would save the data even if the parent do end up rolling back? I thought stored procedures in SQL always honoured the parent transaction. – Iftikhar Ali Nov 30 '11 at 19:42
  • @IftikharAli - SQL Server does not support nested transactions, so whatever you do, (e.g. commit/rollback) it will affect the single transaction. – Kit Nov 30 '11 at 20:47