Recovery stategy and action procedures

Posts   
 
    
yowl
User
Posts: 266
Joined: 11-Feb-2008
# Posted on: 10-Feb-2015 18:25:06   

LLBLGen 4.2 Final December 4th, 2014 Adapter, not self servicing Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64) Jul 22 2014 15:26:36 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Hi,

I've read https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=22714&HighLight=1 which is the issue I'm having, but I'm not clear on whether this is meant to be fixed in 4.2 or I'm doing something wrong. I do something similar in that I use TransactionScopes and have a method to create the DataAccessAdapter which does this:

new DataAccessAdapter(ConnectionString)
                {
                    ActiveRecoveryStrategy = new SqlAzureRecoveryStrategy()
                }

I then have a method which creates the TransactionScope:

ts = new TransactionScope(TransactionScopeOption.Required, timeout);

and does a bunch of work, part of which is:

ActionProcedures.GetNextClaimSeq(claim.ClientAccountKey, ref seq, daa);

where daa is the dataaccessadapter. If this stored procedure hits a deadlock I get the same problem described in the previous post whereby the command is recreated but the parameters are reused causing an exception.

I have 2 questions:

  1. Should I be calling the stored procedure differently if I want to use the recovery strategy?
  2. Why is deadlock regarded as transient? Surely having picked this spid as the deadlock victim, it is rolled back and so just attempting a restart from this point is incorrect as the DB operations that preceded it will have been lost?

Thanks

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Feb-2015 23:35:58   

Transactions are not supported in Auto strategies (ones set in the adapter). The following is quoted from the docs.

Methods like StartTransaction aren't covered, as it's part of an action completed by another method.

You can test, using a manually specified strategy for this specific query/db call.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Feb-2015 17:36:20   

The list of errors marked as transient was copied from the official list of errors distributed by MS as being transient. Deadlock is transient, as it's not a fatal error: it is very likely if one gives up, the deadlock goes away and it can be retried. E.g. a UC violation is fatal, you can retry it again but it will fail again. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
yowl
User
Posts: 266
Joined: 11-Feb-2008
# Posted on: 11-Feb-2015 17:46:14   

That makes sense with the meaning of word transient. It does not mean however that "it is always safe to retry this operation and carry on" which is what happens with the recovery strategy. Not a big deal as I can just exclude deadlock by extending the SqlAzureRecoveryStrategy:

    public class AzureRecoveryStrategy : SqlAzureRecoveryStrategy
    {
        protected override bool IsTransientException(Exception toCheck)
        {
            // exclude deadlocks
            var toCheckAsSqlException = toCheck as SqlException;
            if (toCheckAsSqlException == null)
            {
                return false;
            }
            if ((from SqlError error in toCheckAsSqlException.Errors select error.Number).Any(number => number == 1205))
            {
                return false;
            }
            return base.IsTransientException(toCheck);
        }
    }

I've not tried what happens with timeout, which is safe to retry, on the action procedures, I guess it will fail in the same way if used with a recovery strategy on the adapter. I'll create a test when I have time.

yowl
User
Posts: 266
Joined: 11-Feb-2008
# Posted on: 11-Feb-2015 17:52:10   

Rambling on, you could break transient errors in to 2 groups:

  • Can be retried only at the start of a transaction
  • Can be retried at any point
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 16-Feb-2015 17:22:12   

yowl wrote:

That makes sense with the meaning of word transient. It does not mean however that "it is always safe to retry this operation and carry on" which is what happens with the recovery strategy. Not a big deal as I can just exclude deadlock by extending the SqlAzureRecoveryStrategy:

    public class AzureRecoveryStrategy : SqlAzureRecoveryStrategy
    {
        protected override bool IsTransientException(Exception toCheck)
        {
            // exclude deadlocks
            var toCheckAsSqlException = toCheck as SqlException;
            if (toCheckAsSqlException == null)
            {
                return false;
            }
            if ((from SqlError error in toCheckAsSqlException.Errors select error.Number).Any(number => number == 1205))
            {
                return false;
            }
            return base.IsTransientException(toCheck);
        }
    }

I've not tried what happens with timeout, which is safe to retry, on the action procedures, I guess it will fail in the same way if used with a recovery strategy on the adapter. I'll create a test when I have time.

Good point. I must say I have no opinion in this, the list was more or less a starting point and it might indeed very well be the list of errors detected today are in some contexts too broad and less errors should be detected at some points in the application.

With transaction scope it's a bit vague as well, as normally when an exception occurs the transaction rolls back and then is retried if the strategy allows it, but a transaction scope is cross-adapter in theory, so you have sub-transactions in play. I think it's hard to determine which errors might be allowed at these situations, as excluding some of them by default might hurt users who use an ado.net transaction (as that transaction is retried from the start on an error).

Proc calls are also a grey area, as a proc might call whatever it likes, e.g. unmanaged code, extended procs, and these actions might not be retryable, e.g. sending an email.

Not sure if this is a satisfying answer, I'm a bit undecided what to do, as there's no clear 'it's wrong, this has to change into that' scenario, it's very bound to the context the strategy is used in, IMHO.

Frans Bouma | Lead developer LLBLGen Pro
yowl
User
Posts: 266
Joined: 11-Feb-2008
# Posted on: 16-Feb-2015 17:59:14   

True, in which the current approach is fine, its simple and consistent.