Isolated committing of transaction in SQL

Question

I have a n-tier C# ASP .Net application server which uses stored procedures to communicate with the database.

I have a service layer which rolls back all ADO .net transactions if an exception is thrown, using TransactionScope.requiresNew.

In my stored procedure, I want to track login attempt numbers, so we want to keep the transaction framework as is, but want to have an isolated transaction which we commit.

How do I do this?

I have tried using a new TransactionScope.RequiresNew in our data layer, but this has no effect.

Answer 1

Strange - RequiresNew in the inner (Logging) TransactionScope should work.

In the below nested transaction, TransactionScopeOption.Suppress or TransactionScopeOption.RequiresNew both work for me - the inner transaction is committed (Dal2.x), and the outer one aborted (Dal1.x).

    try
    {
        using (TransactionScope tsOuter = new TransactionScope(TransactionScopeOption.Required))
        {
            DAL1.Txn1();
            using (TransactionScope tsLogging = new TransactionScope(TransactionScopeOption.Suppress))
            {
                DAL2.Txn2();
                tsLogging.Complete();
            }
            throw new Exception("Big Hairy Exception");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

Edit : Mixing TransactionScope and explicit T-SQL transactions is to be avoided - this is stated in the same link you've referenced viz http://msdn.microsoft.com/en-us/library/ms973865.aspx, quoted below

TransactionScopes manage transaction escalation quite intelligently - they will use the (e.g. DTC will only be used if the transactions span multiple databases or resources - e.g. SQL and MSMQ). They also work with the SQL 2005+ Lightweight transactions, so multiple connections to the same database will also be managed within a transaction without the overheads of DTC.

IMHO the decision as to whether to use Suppress vs RequiresNew will depend on whether you need to do your auditing within a transaction at all - RequiresNew for an isolated txn, vs Suppress for none.

When using System.Transactions, applications should not directly utilize transactional programming interfaces on resource managers—for example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or the MessageQueueTransaction() object in System.Messaging namespace, when dealing with MSMQ. Those mechanisms would bypass the distributed transaction management handled by System.Transactions, and combining the use of System.Transactions with these resource manager "internal" transactions will lead to inconsistent results .... Never mix the two