why does System.Activities.DurableInstancing.SqlWorkflowInstanceStore create its stored procedures when they are already created?

Question

We are using appfabric wf services inside the IIS. They persist using the sqlworkflowinstancestore in a SQL Server.

We have some lock problems, and we have detected in our traces that the persistence related stored procedures are created a lot of times, maybe each time the pool is recycled.

Any way to avoid this?

---

http://pablocastilla.wordpress.com/

Answer 1

Do you have a stack trace for the type of locking you are seeing?

I am guessing you are referring to instance locking. Did you already look at the InstanceLockedExceptionAction?  http://msdn.microsoft.com/en-us/library/system.activities.durableinstancing.sqlworkflowinstancestore.instancelockedexceptionaction.aspx

Thanks,

---

If this answers your question, please use the "Answer" button to say so | Ben Cline

Answer 2

ummmmmmmm, my mistake talking about locks, that just give confussion to the question.

 

What I am talking about is that I see this continuously in the ddbb trace:

 create procedure [System.Activities.DurableInstancing].[LoadInstance]   @surrogateLockOwnerId bigint,   @operationType tinyint,   @handleInstanceVersion bigint,   @handleIsBoundToLock bit,   @keyToLoadBy uniqueidentifier = null,   @instanceId uniqueidentifier = null,   @keysToAssociate xml = null,   @encodingOption tinyint,   @concatenatedKeyProperties varbinary(max) = null,   @singleKeyId uniqueidentifier,   @operationTimeout int  as  begin   set nocount on   set transaction isolation level read committed    set xact_abort on;     set deadlock_priority low   begin transaction      declare @result int   declare @lockAcquired bigint   declare @isInitialized bit   declare @createKey bit   declare @createdInstance bit   declare @keyIsAssociated bit   declare @loadedByKey bit   declare @now datetime   declare @surrogateInstanceId bigint     set @createdInstance = 0   set @isInitialized = 0   set @keyIsAssociated = 0   set @result = 0   set @surrogateInstanceId = null      exec @lockAcquired = sp_getapplock @Resource = 'InstanceStoreLock', @LockMode = 'Shared', @LockTimeout = @operationTimeout      if (@lockAcquired < 0)   begin    set @result = 13    select @result as 'Result'   end      if (@result = 0)   begin    set @now = getutcdate()      if (@operationType = 0) or (@operationType = 2)    begin  MapKeyToInstanceId:     set @loadedByKey = 0     set @createKey = 0          select @surrogateInstanceId = [SurrogateInstanceId],         @keyIsAssociated = [IsAssociated]     from [KeysTable]     where [Id] = @keyToLoadBy          if (@@rowcount = 0)     begin      if (@operationType = 2)      begin       set @result = 4       select @result as 'Result', @keyToLoadBy       end       set @createKey = 1     end     else if (@keyIsAssociated = 0)     begin      set @result = 8      select @result as 'Result', @keyToLoadBy     end     else     begin      select @instanceId = [Id]      from [InstancesTable]      where [SurrogateInstanceId] = @surrogateInstanceId        if (@@rowcount = 0)       goto MapKeyToInstanceId        set @loadedByKey = 1     end    end   end     if (@result = 0)   begin  LockOrCreateInstance:    exec [System.Activities.DurableInstancing].[LockInstance] @instanceId, @surrogateLockOwnerId, @handleInstanceVersion, @handleIsBoundToLock, @surrogateInstanceId output, null, @result output                      if (@result = 0 and @surrogateInstanceId = 0)    begin     if (@loadedByKey = 1)      goto MapKeyToInstanceId          if (@operationType > 1)     begin      set @result = 1      select @result as 'Result', @instanceId as 'InstanceId'     end     else     begin          exec [System.Activities.DurableInstancing].[CreateInstance] @instanceId, @surrogateLockOwnerId, null, null, @surrogateInstanceId output, @result output           if (@result = 0 and @surrogateInstanceId = 0)       goto LockOrCreateInstance      else if (@surrogateInstanceId > 0)       set @createdInstance = 1     end    end    else if (@result = 0)    begin     delete from [RunnableInstancesTable]     where [SurrogateInstanceId] = @surrogateInstanceId    end   end       if (@result = 0)   begin    if (@createKey = 1)     begin     select @isInitialized = [IsInitialized]     from [InstancesTable]     where [SurrogateInstanceId] = @surrogateInstanceId          if (@isInitialized = 1)     begin      set @result = 5      select @result as 'Result', @instanceId     end     else     begin                   insert into [KeysTable] ([Id], [SurrogateInstanceId], [IsAssociated])      values (@keyToLoadBy, @surrogateInstanceId, 1)            if (@@rowcount = 0)      begin       if (@createdInstance = 1)       begin        delete [InstancesTable]        where [SurrogateInstanceId] = @surrogateInstanceId       end       else       begin        update [InstancesTable]        set [SurrogateLockOwnerId] = null        where [SurrogateInstanceId] = @surrogateInstanceId       end              goto MapKeyToInstanceId      end     end    end    else if (@loadedByKey = 1 and not exists(select [Id] from [KeysTable] where ([Id] = @keyToLoadBy) and ([IsAssociated] = 1)))    begin     set @result = 8     select @result as 'Result', @keyToLoadBy    end        if (@operationType > 1 and not exists(select [Id] from [InstancesTable] where ([Id] = @instanceId) and ([IsInitialized] = 1)))    begin     set @result = 1     select @result as 'Result', @instanceId as 'InstanceId'    end        if (@result = 0)     exec @result = [System.Activities.DurableInstancing].[AssociateKeys] @surrogateInstanceId, @keysToAssociate, @concatenatedKeyProperties, @encodingOption, @singleKeyId        -- Ensure that this key's data will never be overwritten.    if (@result = 0 and @createKey = 1)    begin     update [KeysTable]     set [EncodingOption] = @encodingOption     where [Id] = @keyToLoadBy    end   end      if (@result != 13)    exec sp_releaseapplock @Resource = 'InstanceStoreLock'       if (@result = 0)   begin    select @result as 'Result',        [Id],        [SurrogateInstanceId],        [PrimitiveDataProperties],        [ComplexDataProperties],        [MetadataProperties],        [DataEncodingOption],        [MetadataEncodingOption],        [Version],        [IsInitialized],        @createdInstance    from [InstancesTable]    where [SurrogateInstanceId] = @surrogateInstanceId        if (@createdInstance = 0)    begin     select @result as 'Result',         [EncodingOption],         [Change]     from [InstanceMetadataChangesTable]     where [SurrogateInstanceId] = @surrogateInstanceId     order by([ChangeTime])          if (@@rowcount = 0)     select @result as 'Result', null, null           select @result as 'Result',         [Id],         [IsAssociated],         [EncodingOption],         [Properties]     from [KeysTable] with (index(NCIX_KeysTable_SurrogateInstanceId))     where ([KeysTable].[SurrogateInstanceId] = @surrogateInstanceId)          if (@@rowcount = 0)      select @result as 'Result', null, null, null, null    end      commit transaction   end   else if (@result = 2 or @result = 14)    commit transaction   else    rollback transaction  end  

 

Why is it creating the storeprocedures if they are already created?

---

http://pablocastilla.wordpress.com/

Answer 3

That does seem weird. I am not sure why it is doing that.

I was thinking we might try to lookup in Reflector where this is occuring to give us a better idea of why. In a brief search I could not find anything mentioning this behavior was expected.

You may need to contact MS Support about this because it seems like unexplained behavior.

Thanks,

---

If this answers your question, please use the "Answer" button to say so | Ben Cline