Problem with SQL parameters in RecoveryStrategy for ActionProcedure

Posts   
 
    
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 13-May-2014 18:37:32   

Adapter, LLBL 4.1 Final March/12, MSSQL 2008 R2

I have problem to get working recovery strategy for action procedures, I see exception: "SqlParameter is already contained by another SqlParameterCollection" when attemptsTried > 1 (RecoveryStrategyBase.cs line 123)

I think somewhere in your code command.parameters.clear is missing and therefore if RecoveryStrategy does the second call

strategy.Execute(Function() MyBase.CallActionStoredProcedure(storedProcedureToCall, parameters))

then it fails due to that.

For example very simple sproc returning SQL server DateTime:

CREATE PROCEDURE [App].[SystemGetServerDatetime]
      @ServerDateTime datetime2 output
AS
BEGIN
    SET NOCOUNT ON; 
    SET @ServerDateTime = SYSDATETIME();
END

Calling code in WinForm app will look like

Dim serverDatetime As Date
Using adapter As New DataAccessAdapter
     DatabaseSpecific.ActionProcedures.SystemGetServerDatetime(serverDatetime, adapter)
End Using

In DataAdapter I override CallActionStoredProcedure to have there recovery strategy:

    Public Overrides Function CallActionStoredProcedure(storedProcedureToCall As String, parameters() As System.Data.Common.DbParameter) As Integer
        Dim valueToReturn As Integer
        Dim strategy As New SqlAzurePlus
        valueToReturn = strategy.Execute(Function() MyBase.CallActionStoredProcedure(storedProcedureToCall, parameters))
        Return valueToReturn
    End Function

The line

valueToReturn = strategy.Execute(Function() MyBase.CallActionStoredProcedure(storedProcedureToCall, parameters))

calls RecoveryStrategyBase.cs

public TResult Execute<TResult>(Func<TResult> toExecute)
...
var toReturn = toExecute();
...

This leads to DataAccessAdapterBase.cs line 144

public virtual int CallActionStoredProcedure(string storedProcedureToCall, DbParameter[] parameters)
        {
            DbCommand command = CreateStoredProcedureCallCommand(storedProcedureToCall, parameters);

Then DataAccessAdapterBase.cs line 3198

protected virtual DbCommand CreateStoredProcedureCallCommand(string storedProcedureToCall, DbParameter[] parameters)
...
            command.Parameters.AddRange(parameters);
            return command;
        }

There exception is raised on the line 3213 command.Parameters.AddRange(parameters);

At that line command.Parameters.Count=0 and parameters keeps parameter: @ServerDateTime , this looks fine But after command.Parameters.AddRange(parameters) I can see exception: "The SqlParameter is already contained by another SqlParameterCollection" StackTrace:

at System.Data.SqlClient.SqlParameterCollection.Validate(Int32 index, Object value) at System.Data.SqlClient.SqlParameterCollection.AddRange(Array values) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateStoredProcedureCallCommand(String storedProcedureToCall, DbParameter[] parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 3214 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CallActionStoredProcedure(String storedProcedureToCall, DbParameter[] parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 146 at FlexMARS.DAL.DataAccessAdapterEx.$VB$ClosureStub_CallActionStoredProcedure_MyBase(String p0, DbParameter[] p1) at FlexMARS.DAL.DataAccessAdapterEx._Closure$__17._Lambda$__17() in F:\DATA\MojeProgramy\NET10\Windows\NET40\Zakaznicke\FlexMARS\FlexMARS.DAL\DataAccessAdapterEx.vb:line 530 at SD.LLBLGen.Pro.ORMSupportClasses.RecoveryStrategyBase.Execute[TResult](Func`1 toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\TransientErrorRecovery\RecoveryStrategyBase.cs:line 123

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 14-May-2014 17:48:56   

strange! It of course shouldn't crash the second time. We'll look into this!

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-May-2014 14:29:16   

Ah I see what you did. The strategy has to be executed outside adapter: so the call to the ActionProcedures method, while passing in an adapter (or letting the call create the adapter) is required, the action has to be retried from start to finish, you can't retry a part of it, as the connection isn't recreated and the command isn't either.

So what you did runs into a current limitation of the strategies: they're not executable from within the adapter, you have to explicitly specify them when executing the query. One of the workitems still left for 4.2 is creating a way to specify a strategy to use for queries executed on adapter, though it's to be seen whether this is possible, as the whole action has to be retried from start to finish, which might not always be possible.

You added this to adapter to avoid having to specify the strategy call explicitly as stated in the docs?

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 15-May-2014 15:29:32   

I use own DataAccessAdapter derived from generated DataDBSpecific.DataAccessAdapter

I added there also "transparent"error recovery like for example:

    Public Overrides Function SaveEntity(entityToSave As IEntity2, refetchAfterSave As Boolean, updateRestriction As IPredicateExpression, recurse As Boolean) As Boolean
        Dim valueToReturn As Boolean
        Dim strategy As New RecoveryStrategyEx

        valueToReturn = strategy.Execute(Function() MyBase.SaveEntity(entityToSave, refetchAfterSave, updateRestriction, recurse))

        Return valueToReturn
    End Function
    Public Overrides Function CallActionStoredProcedure(storedProcedureToCall As String, parameters() As System.Data.Common.DbParameter) As Integer
        Dim valueToReturn As Integer
        Dim strategy As New RecoveryStrategyEx
        valueToReturn = strategy.Execute(Function() MyBase.CallActionStoredProcedure(storedProcedureToCall, parameters))

        Return valueToReturn    
    End Function

This way I don't need to care in my code about recovery strategies, simply adapter is taking care of that, in central place.

It works fine several months for SaveEntity, FetchEntity etc. Now I added to my DataAccessAdapter also "transparent" recovery strategy for CallActionStoredProcedure and I found reported problem.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-May-2014 14:26:04   

Rosacek wrote:

I use own DataAccessAdapter derived from generated DataDBSpecific.DataAccessAdapter

I added there also "transparent"error recovery like for example:

    Public Overrides Function SaveEntity(entityToSave As IEntity2, refetchAfterSave As Boolean, updateRestriction As IPredicateExpression, recurse As Boolean) As Boolean
        Dim valueToReturn As Boolean
        Dim strategy As New RecoveryStrategyEx

        valueToReturn = strategy.Execute(Function() MyBase.SaveEntity(entityToSave, refetchAfterSave, updateRestriction, recurse))

        Return valueToReturn
    End Function
    Public Overrides Function CallActionStoredProcedure(storedProcedureToCall As String, parameters() As System.Data.Common.DbParameter) As Integer
        Dim valueToReturn As Integer
        Dim strategy As New RecoveryStrategyEx
        valueToReturn = strategy.Execute(Function() MyBase.CallActionStoredProcedure(storedProcedureToCall, parameters))

        Return valueToReturn    
    End Function

This way I don't need to care in my code about recovery strategies, simply adapter is taking care of that, in central place.

ah ok. Not every method works with this however, hence my remark simple_smile There are a lot of fetch methods and they all need special code for this to work, hence the strategy placement outside the adapter.

It works fine several months for SaveEntity, FetchEntity etc. Now I added to my DataAccessAdapter also "transparent" recovery strategy for CallActionStoredProcedure and I found reported problem.

Ok, we'll have a look simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 16-May-2014 14:34:02   

Great, thanks simple_smile

I make software pro automotive industry, like traceability, process verification, efficiency etc and data comes also from mobile PC, barcode terminals connected via Wifi and some kind of recovery strategy is a must.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-May-2014 14:40:32   

Problem is that the parameters are passed in and added to a new command, which is disposed on exception, but that the dispose call isn't clearing the parameters first it seems.

I've now added a call to command.Parameters.Clear() in the finally block, before the dispose, so the parameters are cleared from that collection and thus can be re-added to the command. Not sure if this will work in all scenario's though.

I make software pro automotive industry, like traceability, process verification, efficiency etc and data comes also from mobile PC, barcode terminals connected via Wifi and some kind of recovery strategy is a must.

Nice! simple_smile Yes I can imagine it's mandatory and you want it to be as easy to use as possible.

The proc calls are the only places where parameter objects are passed into a method which creates a command, in all other cases a new command with parameters is created in the DQE so you won't run into this problem there.

I've attached a release build for 4.1 with the parameter fix. Please let us know if this doesn't fix your problem simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 16-May-2014 16:27:10   

I tried I now I can see user-unhandled exceptions on all strategy.Execute statements, for example

Public Overrides Function FetchEntity(ByVal entityToFetch As IEntity2, ByVal prefetchPath As IPrefetchPath2, ByVal contextToUse As Context, ByVal excludedIncludedFields As ExcludeIncludeFieldsList) As Boolean
    Dim valueToReturn As Boolean

    Dim strategy As New RecoveryStrategyEx
    valueToReturn = strategy.Execute(Function() MyBase.FetchEntity(entityToFetch, prefetchPath, contextToUse, excludedIncludedFields))
     Return valueToReturn
End Function
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-May-2014 18:19:34   

Ok! simple_smile This is good to know for our workitem to implement it in adapter itself. I still think there are scenarios which require an external call. thanks for the feedback simple_smile

I assume the fix worked out fine?

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 16-May-2014 18:34:47   

Yes it works.

Just when debugging I can see now user-unhandled exception for any strategy.execute as I wrote in my previous post.

Looks like:

System.Data.SqlClient.SqlException was unhandled by user code

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.OpenConnection() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PrepareQueryExecution(IQuery queryToExecute, Boolean forceConnectionSet) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFields2 fieldsToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfos, IRelationPredicateBucket filter) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) at FlexMARS.DAL.DataAccessAdapterEx.$VB$ClosureStub_FetchEntity_MyBase(IEntity2 p0, IPrefetchPath2 p1, Context p2, ExcludeIncludeFieldsList p3) at FlexMARS.DAL.DataAccessAdapterEx._Closure$__5._Lambda$__5() in F:\DATA\MojeProgramy\NET10\Windows\NET40\Zakaznicke\FlexMARS\FlexMARS.DAL\DataAccessAdapterEx.vb:line 274 at SD.LLBLGen.Pro.ORMSupportClasses.RecoveryStrategyBase.Execute[TResult](Func`1 toExecute)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-May-2014 10:17:18   

Ok, but the whole purpose of the strategy is that you don't see the exceptions: it will retry the action, so sql errors like the one you posted with opening a connection are caught and will trigger the strategy to retry the action. So that they bubble up is strange, or did I miss something?

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 17-May-2014 13:30:12   

Original SD.LLBLGen.Pro.ORMSupportClasses.dll 4.1.14.0327 doesn't have this problem, just the fix you sent me.

It is just debug exception, not appearing during runtime, as it is swallowed somewhere deep, anyway strange.

If you need more info, stack trace etc. just let me know

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-May-2014 15:18:48   

I think we misunderstand each other simple_smile

The strategy you use is meant to recover from connection issues. This means that if you use it to execute some code on the adapter, if a connection open fails, it will catch the exception and check whether the error is a transient error (i.e. related to a server being temporarily unavailable, what happens sometimes on azure) or a normal error. If it's a transient error, it will retry (based on the parameters specified on the strategy). If it's a normal error, it will bubble up the exception.

This thus means that if you get exceptions in your code which are transient errors, the strategy should have caught them and handled accordingly (i.e.: retry the action), and not bubble up the exception.

So when you said 'I now get unhandled exceptions', this is IMHO a bit strange as the strategy should have taken care of these exceptions, hence my question simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 17-May-2014 15:36:00   

True, we misunderstand little bit simple_smile I work with strategy since you released them. I know what it should do. And it worked perfectly. Recently I added recovery strategy also to ActionProcedures and faced the problem with parameters. Then I started this thread.

I my strategy derived from SqlAzure, I have also in IsTransientException:

Case 2
     'A network-related or instance-specific error occurred while establishing a connection to SQL Server.
     'The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server
     'is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
     Return True
Case 17142
     'SQL srv pause
     'SQL Server service has been paused. No new connections will be allowed.
     ' To resume the service, use SQL Computer Manager or the Services application in Control Panel.
     Return True

Again, everything worked fine.

But with your fix I can see new behavior, VS IDE shows now on any strategy.Execute command "green exception", means "user-unhandled" exception. I click continue and then IsTransientException processed PROPERLY. Then there is retry, again debugger stops at execute.strategy... I have to click to continue and then IsTransientException is processsed and finally ORMTransientRecoveryFailedException is raised.

It means you changed something in SD.LLBLGen.Pro.ORMSupportClasses.dll you provided as a fix, that causes this issue. Official DLL does not have this issue, code is running smoothly, without stop on startegy.execute line.

EDIT: StackTrace for FetchEntity (screenshot from VS2010 IDE attached):

SD.LLBLGen.Pro.ORMSupportClasses.dll!SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(System.Data.CommandBehavior behavior) + 0x217 bytes SD.LLBLGen.Pro.ORMSupportClasses.dll!SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase. PerformExecuteSingleRowRetrievalQuery(SD.LLBLGen.Pro.ORMSupportClasses.IRetrievalQuery queryToExecute, System.Data.CommandBehavior behavior) + 0x51 bytes SD.LLBLGen.Pro.ORMSupportClasses.dll!SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase. ExecuteSingleRowRetrievalQuery(SD.LLBLGen.Pro.ORMSupportClasses.IRetrievalQuery queryToExecute, SD.LLBLGen.Pro.ORMSupportClasses.IEntityFields2 fieldsToFill, SD.LLBLGen.Pro.ORMSupportClasses.IFieldPersistenceInfo[] fieldsPersistenceInfo) + 0xbd bytes SD.LLBLGen.Pro.ORMSupportClasses.dll!SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase. FetchEntityUsingFilter(SD.LLBLGen.Pro.ORMSupportClasses.IEntityFields2 fieldsToFetch, SD.LLBLGen.Pro.ORMSupportClasses.IFieldPersistenceInfo[] persistenceInfos, SD.LLBLGen.Pro.ORMSupportClasses.IRelationPredicateBucket filter) + 0x395 bytes SD.LLBLGen.Pro.ORMSupportClasses.dll!SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase .FetchEntityUsingFilter(SD.LLBLGen.Pro.ORMSupportClasses.IEntity2 entityToFetch, SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPath2 prefetchPath, SD.LLBLGen.Pro.ORMSupportClasses.Context contextToUse, SD.LLBLGen.Pro.ORMSupportClasses.IRelationPredicateBucket filter, SD.LLBLGen.Pro.ORMSupportClasses.ExcludeIncludeFieldsList excludedIncludedFields) + 0x5a6 bytes SD.LLBLGen.Pro.ORMSupportClasses.dll!SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase. FetchEntity(SD.LLBLGen.Pro.ORMSupportClasses.IEntity2 entityToFetch, SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPath2 prefetchPath, SD.LLBLGen.Pro.ORMSupportClasses.Context contextToUse, SD.LLBLGen.Pro.ORMSupportClasses.ExcludeIncludeFieldsList excludedIncludedFields) + 0x2d9 bytes FlexMARS.DAL.dll!FlexMARS.DAL.DataAccessAdapterEx.$VB$ClosureStub_FetchEntity_MyBase(SD.LLBLGen.Pro.ORMSupportClasses.IEntity2 p0, SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPath2 p1, SD.LLBLGen.Pro.ORMSupportClasses.Context p2, SD.LLBLGen.Pro.ORMSupportClasses.ExcludeIncludeFieldsList p3) + 0x55 bytes

FlexMARS.DAL.dll!FlexMARS.DAL.DataAccessAdapterEx.<closure>.<lambda5>() Line 274 + 0x37 bytes Basic SD.LLBLGen.Pro.ORMSupportClasses.dll!SD.LLBLGen.Pro.ORMSupportClasses.RecoveryStrategyBase. Execute<bool>(System.Func<bool> toExecute) + 0xe2 bytes FlexMARS.DAL.dll!FlexMARS.DAL.DataAccessAdapterEx.FetchEntity(SD.LLBLGen.Pro.ORMSupportClasses.Context contextToUse, SD.LLBLGen.Pro.ORMSupportClasses.IEntity2 entityToFetch, SD.LLBLGen.Pro.ORMSupportClasses.ExcludeIncludeFieldsList excludedIncludedFields, SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPath2 prefetchPath) Line 274 + 0x56 bytes Basic SD.LLBLGen.Pro.ORMSupportClasses.dll!SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase. FetchEntity(SD.LLBLGen.Pro.ORMSupportClasses.IEntity2 entityToFetch, SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPath2 prefetchPath) + 0x9f bytes

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-May-2014 11:22:09   

Nothing in that area changed, so I don't know what causes this...

Transient error testing is very hard btw, so we do our best with a deadlock we artificially create. Below are the two tests with both succeed, and I also display the trace which shows what is going on in the second query:


[Test]
[ExpectedException(typeof(ORMQueryExecutionException))]
public void TransientErrorRecoveryTest2()
{
    CreateDeadlock();

    var soh = new EntityCollection<SalesOrderHeaderEntity>();
    using(var adapter = new DataAccessAdapter())
    {
        adapter.CommandTimeOut = 1;
        adapter.FetchEntityCollection(soh, new RelationPredicateBucket(SalesOrderHeaderFields.CustomerId > 10000));
    }
    Assert.AreEqual(27659, soh.Count);
}


[Test]
public void TransientErrorRecoveryTest1()
{
    CreateDeadlock();

    var soh = new EntityCollection<SalesOrderHeaderEntity>();
    using(var adapter = new DataAccessAdapter())
    {
        adapter.CommandTimeOut = 1;
        var strategy = new SqlAzureRecoveryStrategy();
        strategy.Execute(()=>
                    adapter.FetchEntityCollection(soh, new RelationPredicateBucket(SalesOrderHeaderFields.CustomerId > 10000)));
    }
    Assert.AreEqual(27659, soh.Count);
}

helper code for deadlock (from Microsoft)


private void SetupThreadPrincipal(string userName)
{
    string[] rolesArray = new string[] { "Managers", "Executives" };
    Thread.CurrentPrincipal = new GenericPrincipal(new GenericIdentity(userName), rolesArray);
}

        
private void CreateDeadlock()
{
    _longTransactionThread = new Thread(LongTransactionThread);
    _longTransactionThread.Start();

    _deadlockImplThread = new Thread(DeadLockImplThread);
    _deadlockImplThread.Start();
}


private void DeadLockImplThread()
{
    Console.WriteLine("DeadLockImplThread started");
    var adapter = new DataAccessAdapter();
    try
    { 
        using(SqlConnection connection = new SqlConnection(adapter.ConnectionString))
        {
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
            try
            {

                SqlCommand command = new SqlCommand(
                    "UPDATE sales.SalesOrderDetail WITH (TABLOCKX) SET OrderQty = 2; " +
                    "SELECT * FROM sales.salesorderheader with (READCOMMITTEDLOCK)"
                    );
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandTimeout = 2;
                command.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                Console.WriteLine(string.Format("Exception caught in DeadLockImplThread: {0}", ex.Message));
            }
            finally
            {
                transaction.Rollback();
            }
        }
        Console.WriteLine("DeadLockImplThread ended");
    }
    catch(Exception ex)
    {
        Console.WriteLine("DeadLockImplThread ended with exception: {0}", ex.Message);
    }
}


private void LongTransactionThread()
{
    Console.WriteLine("LongTransactionThreadStarted");
    var adapter = new DataAccessAdapter();
    try
    {
        using(SqlConnection con = new SqlConnection(adapter.ConnectionString))
        {
            con.Open();

            SqlTransaction transaction = con.BeginTransaction();

            try
            {
                SqlCommand cmd = new SqlCommand("UPDATE sales.SalesOrderHeader WITH (TABLOCKX) SET status = 1");
                cmd.Connection = con;
                cmd.CommandTimeout = 3;
                cmd.Transaction = transaction;
                cmd.ExecuteNonQuery();

                Thread.Sleep(3000);

                cmd = new SqlCommand("SELECT * FROM sales.SalesOrderDetail with (READCOMMITTEDLOCK)");
                cmd.Connection = con;
                cmd.Transaction = transaction;
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                Console.WriteLine(string.Format("Exception caught in LontTransactionThread: {0}", ex.Message));
            }
            finally
            {
                System.Diagnostics.Debug.WriteLine("Roll back long transaction");
                transaction.Rollback();
            }
        }
        Console.WriteLine("LongTransactionThreadEnded");
    }
    catch(Exception ex)
    {
        Console.WriteLine("LongTransactionThread ended with exception: {0}", ex.Message);
    }
}

trace:


LongTransactionThreadStarted
DeadLockImplThread started
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[AccountNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[BillToAddressID] AS [BillToAddressId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Comment], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ContactID] AS [ContactId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardApprovalCode], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardID] AS [CreditCardId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CurrencyRateID] AS [CurrencyRateId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] AS [CustomerId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[DueDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Freight], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ModifiedDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OnlineOrderFlag], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OrderDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[PurchaseOrderNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[RevisionNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[rowguid] AS [Rowguid], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderID] AS [SalesOrderId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesPersonID] AS [SalesPersonId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipMethodID] AS [ShipMethodId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipToAddressID] AS [ShipToAddressId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Status], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SubTotal], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TaxAmt], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TerritoryID] AS [TerritoryId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TotalDue] FROM [AdventureWorksUnitTests].[Sales].[SalesOrderHeader]   WHERE ( [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] > @p1)
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10000.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Exception caught in DeadLockImplThread: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
DeadLockImplThread ended
Roll back long transaction
Transient Error Recovery: Query is retried after a delay of 2000ms, after 1 attempts after transient error
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[AccountNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[BillToAddressID] AS [BillToAddressId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Comment], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ContactID] AS [ContactId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardApprovalCode], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardID] AS [CreditCardId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CurrencyRateID] AS [CurrencyRateId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] AS [CustomerId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[DueDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Freight], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ModifiedDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OnlineOrderFlag], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OrderDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[PurchaseOrderNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[RevisionNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[rowguid] AS [Rowguid], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderID] AS [SalesOrderId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesPersonID] AS [SalesPersonId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipMethodID] AS [ShipMethodId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipToAddressID] AS [ShipToAddressId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Status], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SubTotal], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TaxAmt], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TerritoryID] AS [TerritoryId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TotalDue] FROM [AdventureWorksUnitTests].[Sales].[SalesOrderHeader]   WHERE ( [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] > @p1)
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10000.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
LongTransactionThreadEnded
Transient Error Recovery: Query is retried after a delay of 4000ms, after 2 attempts after transient error
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[AccountNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[BillToAddressID] AS [BillToAddressId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Comment], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ContactID] AS [ContactId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardApprovalCode], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardID] AS [CreditCardId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CurrencyRateID] AS [CurrencyRateId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] AS [CustomerId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[DueDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Freight], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ModifiedDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OnlineOrderFlag], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OrderDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[PurchaseOrderNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[RevisionNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[rowguid] AS [Rowguid], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderID] AS [SalesOrderId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesPersonID] AS [SalesPersonId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipMethodID] AS [ShipMethodId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipToAddressID] AS [ShipToAddressId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Status], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SubTotal], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TaxAmt], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TerritoryID] AS [TerritoryId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TotalDue] FROM [AdventureWorksUnitTests].[Sales].[SalesOrderHeader]   WHERE ( [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] > @p1)
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10000.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Executed Sql Query: 
    Query: SELECT [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[AccountNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[BillToAddressID] AS [BillToAddressId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Comment], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ContactID] AS [ContactId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardApprovalCode], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CreditCardID] AS [CreditCardId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CurrencyRateID] AS [CurrencyRateId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] AS [CustomerId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[DueDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Freight], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ModifiedDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OnlineOrderFlag], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[OrderDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[PurchaseOrderNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[RevisionNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[rowguid] AS [Rowguid], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderID] AS [SalesOrderId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesOrderNumber], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SalesPersonID] AS [SalesPersonId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipDate], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipMethodID] AS [ShipMethodId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[ShipToAddressID] AS [ShipToAddressId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[Status], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[SubTotal], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TaxAmt], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TerritoryID] AS [TerritoryId], [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[TotalDue] FROM [AdventureWorksUnitTests].[Sales].[SalesOrderHeader]   WHERE ( [AdventureWorksUnitTests].[Sales].[SalesOrderHeader].[CustomerID] > @p1)
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10000.

Transient Error Recovery: Query successfully recovered after 3 attempts after transient error

1 passed, 0 failed, 0 skipped, took 11,88 seconds (NUnit 2.5.10).

So the exception is always appearing but this was already the case, nothing in that area changed.

What I don't understand from your last post is whether the strategy works (run without debugger) or not. It looks like it lets the exception pop up regardless now (which makes it moot)...

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 18-May-2014 13:03:26   

Pls give me following versions of SD.LLBLGen.Pro.ORMSupportClasses.dll

Modified official 4.1.14.0327 with "...[b]added [/b]a call to command.Parameters.Clear() in the finally block, before the dispose"

Modified fix 4.1.14.516 with **removed **"call to command.Parameters.Clear() in the finally block, before the dispose"

This way I could test what causes the problem, if really just because added command.Parameters.Clear or if it caused by totaly different change in that DLL.

Crazy story, sorry for headache disappointed PS. I understand you are busy with 4.2, in the worst case I can live with missing command.Parameters.Clear() and try to solve this issue later on if you are ok to spend time with this.

**EDIT: **I think I found the problem. It is related to SD.LLBLGen.Pro.ORMSupportClasses.pdb If it is in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\CompiledRuntimeLibraries and is correct for version SD.LLBLGen.Pro.ORMSupportClasses.dll then it works smoothly

But if I delete SD.LLBLGen.Pro.ORMSupportClasses.pdb or if version if SD.LLBLGen.Pro.ORMSupportClasses.dll is different (like your fix you provided me) then I can see user-unhandled exception in VS IDE during debugging. Uf disappointed

I will continue to test

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-May-2014 09:21:52   

Ok. So it could be nothing.

If you simply run the test, the test succeeds? (as in: it does retry and succeed, no error/failing test) ?

Changes since 27th of march, the one which is the latest one downloadable from the website:

  • Postgresql: the DQE now strips out the catalog/database name of a stored procedure name, so it's no longer required to have the catalog name in the llblgen pro project equal to the catalog/database name in production: the proc in the database/catalog connected to in the connection string is now executed.
  • Linq: if the IQueryable<T> created by LinqMetaData was casted to IQueryable<U> where U was an interface implemented by T, it could be the query would fail, due to the lack of a source to query from in the generated SQL. This fix tries to fix this, however querying on an interface is still not supported so queries solely targeting interfaces can still fail as this isn't a feature our runtime supports. This fix will try to make interface usage in queries work in most situations however.
  • Fixed an issue with using an interface cast in a linq query where fields got mixed up due to obscurity of the real source to fetch from.
  • QuerySpec: If an operator was used other than Equal in the correlation predicate of a nested query, the query would succeed but results would be unreliable. The engine will now throw an ORMQueryConstructionException with the remark which operator was wrong so the query results are not used.
  • Added a parameters.Clear() before the command is disposed so re-entry proc calls will work.

I.o.w.: nothing that is in any way near anything exception handling related or strategy related.

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 19-May-2014 10:29:30   

1/ I confirm that recovery strategy for action procedures works fine in you fix. It follows maximumNumberOfRetries as well as RecoveryDelay settings.

Also ORMTransientRecoveryFailedException is raised after maximumNumberOfRetries, as expected. From my point this is closed issue. Thanks, Otis simple_smile

2/ The only reason of reported behavior was that you did not send me with fix also PDB file. If PDB file is missing or is not related to the version of DLL, then we face during debugging breaks on "user-unhadled exceptions". It doesn't affect released app. Pure debug behavior.

3/ To force debugger to skip those exceptions, DebuggerNonUserCodeAttribute could be used in LLBL code. See http://msdn.microsoft.com/en-us/library/h5e30exc(v=vs.100).aspx

4/ We can also change VisualStudio settings to skip such type of exceptions (but pls keep in mind it is dangerous, see example in the link to MSDN provided bellow) switch OFF in menu Tools->Options->Debugging->Enable Just My Code or switch OFF in menu Debug->Exceptions->Common Language Runtime Exceptions column "User-Unhandled" See more on: http://blogs.msdn.com/b/greggm/archive/2008/12/31/exceptions-dialog-explained-or-what-does-user-unhandled-mean.aspx http://msdn.microsoft.com/en-us/library/038tzxdw(v=vs.100).aspx

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 20-May-2014 11:04:42   

ahh, it's now clear simple_smile

We'll try to release a new 4.1 build today so you can get rid of this issue.

Frans Bouma | Lead developer LLBLGen Pro
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 20-May-2014 11:09:35   

It is OK. I can wait for next regular build or 4.2

BTW I am testing recovery strategy when SQL server mirroring (failover partner) is in game. If someone is interested I can post results.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 20-May-2014 15:53:06   

Sounds interesting simple_smile

Btw, we implemented automatic recovery strategy on Adapter now in v4.2, so you override 1 method and all actions which should be recoverable, like FetchEntity, call stored proc, but also unit of work Commit, are then covered by that strategy.

During that process we found that there were some methods which weren't virtual so you couldn't cover them with your custom code. These methods are now covered as well (e.g. fetch excluded fields). So that should make it much easier to use the recovery strategies simple_smile

Frans Bouma | Lead developer LLBLGen Pro