XACT_ABORT and Custom DynamicQueryEngine

Posts   
 
    
Isz
User
Posts: 108
Joined: 26-Jan-2006
# Posted on: 07-May-2011 19:03:55   

Hello,

I may have found myself on a slippery slope, so if there is alternate design wisdom I should consider please advise.

Problem: There are multiple SQL Server 2008 R2 instances in the organization I work for, and the application I am writing, which uses LLBLGen 3.1, and .NET 3.5, needs to write data to database tables located in separate instances. I've found that synonyms will assist with this problem by allowing me to target a single instance, yet write and retrieve data from another instance. Using LLBLGen designer to add objects from both instances works well, but I've found that writing data through a synonym within a transaction requires SET XACT_ABORT ON.

At first I received this error:

MSDTC on server 'XXX' is unavailable

To overcome the above, I needed to start the Distributed Transaction Coordinator service.

Then I received this error:

An exception was caught during the execution of an action query: Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server ".\SQL2". A nested transaction was required because the XACT_ABORT option was set to OFF. OLE DB provider "SQLNCLI10" for linked server ".\SQL2" returned message "Cannot start more transactions on this session.".. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

My synonyms are in an instance called .\SQL1 that point to .\SQL2.

Solution: Based upon some research, it appears that writing through a synonym within a transaction causes a nested transaction, and falls within the topic of Distributed Transactions, and setting SET XACT_ABORT ON makes this all possible. But it did not appear that LLBLGen supports this. Some additional research pointed me to the possibility of writing a custom DataAccessAdapter and a custom DynamicQueryEngine. So I've done this using the same convention for AppendArithAbortIfRequired.

The new adapter class is called XActAbortDataAccessAdapter and the new DynamicQueryEngine class is called XActAbortDynamicQueryEngine. In the DQE, I have code per the following:


        private void AppendXActAbortIfRequired(QueryFragments fragments, bool startOfQuery)
        {
            if (!this.IsCe() && XActAbortOn)
            {
                if (startOfQuery)
                {
                    fragments.AddFragment("SET XACT_ABORT ON;");
                }
                else
                {
                    fragments.AddFragment(";SET XACT_ABORT OFF");
                }
            }
        }

 ...

        protected override void CreateSingleTargetInsertDQ(IEntityFieldCore[] fields, IFieldPersistenceInfo[] fieldsPersistenceInfo, IActionQuery query, Dictionary<IEntityFieldCore, DbParameter> fieldToParameter)
        {
            TraceHelper.WriteLineIf(DynamicQueryEngineBase.Switch.TraceInfo, "CreateSingleTargetInsertDQ", "Method Enter");
            QueryFragments fragments = new QueryFragments();
            this.AppendXActAbortIfRequired(fragments, true);
            this.AppendArithAbortIfRequired(fragments, true);
            DelimitedStringList list = fragments.AddSemiColonFragmentList(false, true);
            fragments.AddFormatted("INSERT INTO {0}", new object[] { base.Creator.CreateObjectName(fieldsPersistenceInfo[0]) });
            DelimitedStringList list2 = fragments.AddCommaFragmentList(true);
            StringPlaceHolder holder = fragments.AddPlaceHolder();
            fragments.AddFragment("VALUES");
            DelimitedStringList list3 = fragments.AddCommaFragmentList(true);
            QueryFragments fragments2 = fragments.AddQueryFragments();
            this.AppendArithAbortIfRequired(fragments, false);
            this.AppendXActAbortIfRequired(fragments, false);

  ...

And from a client, I can instantiate a _XActAbortDataAccessAdapter _using something like:



        public static IDataAccessAdapter CreateXActAbortAdapter()
        {
            XActAbortDataAccessAdapter.SetXActAbortFlag(true);

            IDataAccessAdapter adapter = new XActAbortDataAccessAdapter();
            adapter.StartTransaction(System.Data.IsolationLevel.ReadUncommitted, "DISTRIBUTED_READUNCOMMITED_ADAPTER");
            return adapter;
        }

  ...

        bool refetch = true;
        bool success = xactabortAdapter.SaveEntity(this.Entity, refetch, recurse);


Results: When I perform the above, I notice that my entity is actually saved to .\SQL2 through the synonym defined in .\SQL1; however I must refetch the entity, and this is where I am stuck. Even though the entity is persisted, success returns false, and I believe this is because the refetch fails.

Output

Method Enter: DataAccessAdapterBase.SaveEntity(4) Active Entity Description: Entity: Vmgr.Plugins.Data.Generic.EntityClasses.PluginCleanLogConfigurationEntity. ObjectID: 001af543-eb83-4a19-a8c9-9db26c6fbdb9 PrimaryKey field: CleanLogId. Type: System.Int32. Value: 0 Method Enter: DataAccessAdapterBase.DetermineActionQueues(7) Active Entity Description: Entity: Vmgr.Plugins.Data.Generic.EntityClasses.PluginCleanLogConfigurationEntity. ObjectID: 001af543-eb83-4a19-a8c9-9db26c6fbdb9 PrimaryKey field: CleanLogId. Type: System.Int32. Value: 0 Entity added to insert queue: Entity: Vmgr.Plugins.Data.Generic.EntityClasses.PluginCleanLogConfigurationEntity. ObjectID: 001af543-eb83-4a19-a8c9-9db26c6fbdb9 PrimaryKey field: CleanLogId. Type: System.Int32. Value: 0 Method Exit: DataAccessAdapterBase.DetermineActionQueues(7) Method Enter: DataAccessAdapterBase.PersistQueue Persistence action info: Action: Insert. Queue length: 1 Current persisted entity info: Entity: Vmgr.Plugins.Data.Generic.EntityClasses.PluginCleanLogConfigurationEntity. ObjectID: 001af543-eb83-4a19-a8c9-9db26c6fbdb9 PrimaryKey field: CleanLogId. Type: System.Int32. Value: 0 Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: SET XACT_ABORT ON; INSERT INTO [DOM_Config].[dbo].[PluginCleanLogConfiguration] ([CleandateRangeEnd], [CleandateRangeStart], [CleanolderThanDays], [CleanTypeId], [CreateUser], [UpdateDate]) VALUES (@p1, @p2, @p4, @p5, @p6, @p7) ;SELECT @p3=SCOPE_IDENTITY() ;SET XACT_ABORT OFF Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 0. Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1. Parameter: @p5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2. Parameter: @p6 : String. Length: 255. Precision: 0. Scale: 0. Direction: Input. Value: "DOMAIN\isz". Parameter: @p7 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.

Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: Query.ReflectOutputValuesInRelatedFields

Syncing field CleanLogId with parameter @p3. Method Exit: Query.ReflectOutputValuesInRelatedFields Executed Sql Query: Query: SET XACT_ABORT ON; INSERT INTO [DOM_Config].[dbo].[PluginCleanLogConfiguration] ([CleandateRangeEnd], [CleandateRangeStart], [CleanolderThanDays], [CleanTypeId], [CreateUser], [UpdateDate]) VALUES (@p1, @p2, @p4, @p5, @p6, @p7) ;SELECT @p3=SCOPE_IDENTITY() ;SET XACT_ABORT OFF Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>. Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1. Parameter: @p5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2. Parameter: @p6 : String. Length: 255. Precision: 0. Scale: 0. Direction: Input. Value: "DOMAIN\isz". Parameter: @p7 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.

Method Exit: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.FetchEntity(4) Method Enter: DataAccessAdapterBase.FetchEntityUsingFilter(5) Active Entity Description: Entity: Vmgr.Plugins.Data.Generic.EntityClasses.PluginCleanLogConfigurationEntity. ObjectID: 001af543-eb83-4a19-a8c9-9db26c6fbdb9 PrimaryKey field: CleanLogId. Type: System.Int32. Value:

Method Enter: DataAccessAdapterBase.FetchEntityUsingFilter(3) Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleandateRangeEnd] AS [CleanDateRangeEnd], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleandateRangeStart] AS [CleanDateRangeStart], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleanLogId], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleanolderThanDays] AS [CleanOlderThanDays], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleanTypeId], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CreateDate], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CreateUser], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[UpdateDate], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[UpdateUser] FROM [DOM_Config].[dbo].[PluginCleanLogConfiguration] WHERE ( ( [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleanLogId] IS NULL)) Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: DataAccessAdapterBase.ExecuteSingleRowRetrievalQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection Executed Sql Query: Query: SELECT [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleandateRangeEnd] AS [CleanDateRangeEnd], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleandateRangeStart] AS [CleanDateRangeStart], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleanLogId], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleanolderThanDays] AS [CleanOlderThanDays], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleanTypeId], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CreateDate], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CreateUser], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[UpdateDate], [DOM_Config].[dbo].[PluginCleanLogConfiguration].[UpdateUser] FROM [DOM_Config].[dbo].[PluginCleanLogConfiguration] WHERE ( ( [DOM_Config].[dbo].[PluginCleanLogConfiguration].[CleanLogId] IS NULL))

Method Exit: DataAccessAdapterBase.ExecuteSingleRowRetrievalQuery Method Exit: DataAccessAdapterBase.FetchEntityUsingFilter Method Exit: DataAccessAdapterBase.FetchEntityUsingFilter(5) Method Exit: DataAccessAdapterBase.FetchEntity(4) PersistQueue method result: queuePersisted result: False Method Exit: DataAccessAdapterBase.PersistQueue Method Enter: DataAccessAdapterBase.PersistQueue Persistence action info: Action: Update. Queue length: 0 PersistQueue method result: queuePersisted result: True Method Exit: DataAccessAdapterBase.PersistQueue Method Exit: DataAccessAdapterBase.SaveEntity(4)

Questions - How can I get refetch to work and return true when SaveEntity() is called? - Is refetch being executed separately under a different transaction that is locking a subsequent read? - Is there an alternate design consideration that might allow writing to two instances I might explore? - Could XACT_ABORT be a supported feature in LLBLGen?

Thank you for your support and interest!

Attachements provided.

Attachments
Filename File size Added on Approval
Vmgr.Plugins.Biz.zip 2,802 07-May-2011 19:04.51 Approved
Isz
User
Posts: 108
Joined: 26-Jan-2006
# Posted on: 07-May-2011 19:57:04   

Hmmm... I just profiled the output, and here is what I got:

declare @p5 int set @p5=NULL exec sp_executesql N'SET XACT_ABORT ON; INSERT INTO [DOM_Config].[dbo].[PluginCleanLogConfiguration] ( [CleandateRangeEnd] , [CleandateRangeStart] , [CleanolderThanDays] , [CleanTypeId] , [CreateUser] , [UpdateDate] ) VALUES ( @p1 , @p2 , @p4 , @p5 , @p6 , @p7 ); SELECT @p3=SCOPE_IDENTITY(); SET XACT_ABORT OFF ' , N'@p1 datetime ,@p2 datetime ,@p3 int output ,@p4 int ,@p5 int ,@p6 nvarchar(255), @p7 datetime', @p1=NULL ,@p2=NULL ,@p3=@p5 output ,@p4=1 ,@p5=2 ,@p6=N'DOMAIN\isz' ,@p7=NULL select @p5

The result was NULL.

Perhaps SCOPE_IDENTITY doesn't work for XACT_ABORT, or that the nested query is in a different scope?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 08-May-2011 19:40:05   

So you run the query in your server script editor, and SCOPE_IDENTITY() returns NULL?

David Elizondo | LLBLGen Support Team
Isz
User
Posts: 108
Joined: 26-Jan-2006
# Posted on: 08-May-2011 21:11:38   

That is correct.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-May-2011 10:45:59   

Unfortunatly that's expected.

As the identity inserted in the remote server is out of scope of the local server. i.e. SCOPE_IDENTITY(), @@IDENTITY or IDENT_CURRENT can't obtain the identity key of the record inserted in the remote(linked) server.

IMHO, if you have 2 different databases(schemas) in 2 different servers, then it would be better if you use 2 different DALs, one for each database.

If the schemas are similar, then you can use one DAL to access both, and all you need to do is change the connectionString at runtime.