Hi,

LLBLGen 4.2, Adapter.

What is the behaviour when using the SqlAzureRecoveryStrategy and TransactionScope? It seems that after a timeout which is treated as transient, the TransactionScope is completed and hence the next operation fails with something along the lines of "Transaction scope is completed but not disposed".

Not sure what you mean by timeout here, do you et a timeout error? isn't it covered by the recovery?

Could you provide more details about the problem? Maybe with a simple code snippet.

Sure,

1. create the attached table in some SQL Server database, I used 2012 SP1, but probably not that important.
2. Lock the table up with something like:

set transaction isolation level serializable
begin tran
select * from t

1. Run the app.
2. Wait for the first timeout, say 7 seconds
3. Release the db lock with commit, or rollback
4. You get a TransactionAbortedException not a successful retry.

Sorry, should have put all the attachments in one zip, Here is the code.

If I wait 7 seconds, I get:

ORMTransientRecoveryFailedException {"Recovery failed: Maximum number of retries of 1 exceeded."} ... {"An exception was caught during the execution of an action query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.\r\nThe statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}

Which means the strategy worked for 1 maximum try, as you indicate in the SqlAzureRecoveryStrategy, Right?

Here is my own working test:

set transaction isolation level serializable
begin tran
select * from Customers

--commit 

using (var adapter = new DataAccessAdapter())
{
    var customer = new CustomerEntity("ALFKI");
    adapter.FetchEntity(customer);

    customer.Address = "New address";

    adapter.SaveEntity(customer);
}

SaveEntity doesn't execute and pass the call, until I commit the trans in the SQL Management Studio. I'm using the same exact CreateRecoveryStrategyToUse override.

Setting the CommandTImeout to 5 seconds, and waiting for more time before committing, this throws ORMTransientRecoveryFailedException "Recovery failed: Maximum number of retries of 1 exceeded."

So everything is working as expected, either the recovery or the exception.

I think that there is a tiny time-frame when your behavior could take place: Between the success of the recovery strategy and the scope.Complete. Attached is the stacktrace. Here is the code:

using lltest.DatabaseSpecific;
using lltest.EntityClasses;
using lltest.HelperClasses;
using System;
using System.Threading;
using System.Transactions;

namespace retrytimeout
{
    class Program
    {
        static void Main(string[] args)
        {
            Thread outsider = new Thread(new ThreadStart(new Outsider().Run));
            outsider.Start();

            using (var ts = new TransactionScope())
            {
                var t = new TEntity {B = 1};
                var daa = new DataAccessAdapterRecovery(@"Server=.\sqlexpress;Database=lltest;Trusted_Connection=True;");
                daa.CommandTimeOut = 5;
                daa.SaveEntity(t);
                ts.Complete();
            }

            outsider.Join();
            Console.WriteLine("done.");
            Console.ReadLine();
        }

        public class Outsider
        {
            public Outsider()
            {
            }

            public void Run()
            {
                var adapter = new DataAccessAdapter(@"Server=.\sqlexpress;Database=lltest;Trusted_Connection=True;");
                adapter.StartTransaction(System.Data.IsolationLevel.Serializable, "outsider");
                
                var results = new EntityCollection<TEntity>();
                adapter.FetchEntityCollection(results, null);

                Thread.Sleep(6000);

                adapter.Commit();
            }
        }
    }
}

The DataAccessAdapter is a normal one. The DataAccessAdapterRecovery is:

public partial class DataAccessAdapterRecovery : DataAccessAdapter
{
    public DataAccessAdapterRecovery()
        : base()
    {

    }

    public DataAccessAdapterRecovery(string connectionString)
        : base(connectionString)
    {

    }

    protected override RecoveryStrategyBase CreateRecoveryStrategyToUse()
    {
        return new SqlAzureRecoveryStrategy(1, new RecoveryDelay(TimeSpan.FromSeconds(1), 1, RecoveryStrategyDelayType.Linear));
    }
}

The question is: Why the transactionScope aborted the exception?

@Walaa - This doesn't look like my code as there is no TransasctionScope.

@daelmo:

Thanks, that certainly makes it easier to test

I've modified it some more to make sure that the lock is taken by outsider before the insert can run:

        static ManualResetEvent lockTaken = new ManualResetEvent(false);
        static void Main(string[] args)
        {
            Thread outsider = new Thread(new ThreadStart(new Outsider().Run));
            outsider.Start();

            lockTaken.WaitOne();
            Console.WriteLine("ts starting.");
            using (var ts = new TransactionScope())
            {
                var t = new TEntity { B = 1 };
                var daa = new DataAccessAdapterRecovery(@"Server=localhost;Database=lltest;Trusted_Connection=True;");
                daa.CommandTimeOut = 5;
                daa.SaveEntity(t);
                ts.Complete();
            }
            Console.WriteLine("ts complete.");

            outsider.Join();
            Console.WriteLine("done.");
            Console.ReadLine();
        }

        public class Outsider
        {
            public void Run()
            {
                var adapter = new DataAccessAdapter(@"Server=localhost;Database=lltest;Trusted_Connection=True;");
                adapter.StartTransaction(System.Data.IsolationLevel.Serializable, "outsider");

                var results = new EntityCollection<TEntity>();
                adapter.FetchEntityCollection(results, null);
                Console.WriteLine("outsider lock taken.");
                lockTaken.Set();

                Thread.Sleep(7000);

                adapter.Commit();
                Console.WriteLine("outsider commit.");
            }
        }

I don't believe it is that time sensitive: I tried with sleeps of 5500, 6000, and 7000 and all throw the TransactionAbortedException.

Will try with EntityFramework to see what the behaviour is.

This might help: http://sankarsan.wordpress.com/2009/02/01/transaction-timeout-in-systemtransactions/

i.o.w.: the timeout of the scope itself kicks in.

Hi,

The default is 60 seconds and I'm not changing it so think that is unlikely.

Forget it, EF doesn't do what I thought it would do either when using TransactionScope.

I was hoping that for a command timeout it would retry that command, but it closes the connection and starts the whole thing again. In other words it doesn't discriminate between those transient errors that could be retried and those that can't.

So with TransactionScope and Azure you are basically on your own. It would seem that TransactionScope is not going to be a straightforward strategy when on Azure, and not the Microsoft recommendation.

Did you try to set the timeout to a higher value in the scope ctor? It might not be the root cause, but it could be.

It is possible that the transaction scope gets notified directly by the MS DTC manager that the transaction failed, however I'm not sure (it's pure guesswork as there's not really deep info about this with respect to the error you got )

Tried with :

            using (var ts = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromMinutes(1)))

And its the same, errors after the Thread.Sleep in the Outsider. I'm not surprised now having read :

http://msdn.microsoft.com/en-us/data/dn307226

Hope you are having fun recoding for .Net Core

I'll look at a different strategy at some point, as I think the biggest problem with Azure transient errors is the initial connection failing, and I think that might be ok as the Transaction hasn't started yet.

yowl wrote:

Tried with :

            using (var ts = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromMinutes(1)))

And its the same, errors after the Thread.Sleep in the Outsider. I'm not surprised now having read :

http://msdn.microsoft.com/en-us/data/dn307226

hmm...

Hope you are having fun recoding for .Net Core

I doubt it, but we'll see.

I'll look at a different strategy at some point, as I think the biggest problem with Azure transient errors is the initial connection failing, and I think that might be ok as the Transaction hasn't started yet.

The connection can fail at any time, sadly. so it will break at other moments as well. It might be you don't need transaction scope btw, if you don't use multiple database servers in your transaction, but only a single one, you can use an ado.net transaction and you don't need a transaction scope

Otis wrote:

The connection can fail at any time, sadly. so it will break at other moments as well. It might be you don't need transaction scope btw, if you don't use multiple database servers in your transaction, but only a single one, you can use an ado.net transaction and you don't need a transaction scope

I don't need any of the DTC support, but I like the ambient approach better as it simplifies the design in my opinion. You can write a method A which does some DB work in a transaction using a TransactionScope. You can then write a method B which also does some DB work and in addition calls A. If you were using ADO transactions then you would have to pass the transaction into A to get everything in the same transaction. With ambient transactions you don't have to worry about that and your method signatures don't need to have a transaction parameter on the off chance that they are being called from within an existing transaction. Some people I imagine prefer the explicit approach but I've been using ambient transactions since the spinning balls of COM+ and I'm quite happy with it.

Good point / argument!