Summary

When under high load, Workflow may log the following error in the Windows Event Log:

The INSERT statement conflicted with the FOREIGN KEY constraint "search_activity_instance_fk". The conflict occurred in database "SampleWorkflowDatabase", table "dbo.search_instance", column 'search_id'. The statement has been terminated.

Cause

When the SQL server is under high load, a stored procedure may not properly commit the open transaction upon retry. By default, Workflow automatically retries internal SQL queries that time out. The insert_search_instance stored procedure is run as part of certain maintenance features in Workflow. If a query in this stored procedure times out during the initial run (e.g., the SQL Server is under high load), the transaction is not properly committed during the retry. When SQL Server goes through its internal maintenance tasks, the open transaction is aborted and an expected insert is rolled back.

Workaround

Use the following query to modify the insert_search_instance stored procedure to properly commit or rollback the transaction.

ALTER procedure [dbo].[insert_search_instance]
	@instance_id	uniqueidentifier,
	@parent_instance_id uniqueidentifier,
	@workflow_id	int,
	@workflow_version int
as
	begin
		set nocount on
		set transaction isolation level read committed
		declare
			@error int,
			@error_message nvarchar(256),
			@return	smallint,
			@search_id bigint

		begin transaction

		exec @return = get_internal_search_id @instance_id = @instance_id,
			@search_id = @search_id output
		if @search_id is null
			-- Not in db yet, insert
			begin
				declare @start_date datetime 
				set @start_date = GETUTCDATE()
				insert into search_instance (instance_id, parent_instance_id, version, workflow_id)
				values (@instance_id, @parent_instance_id, @workflow_version, @workflow_id)
				select @search_id = SCOPE_IDENTITY(), @error = @@ERROR
				insert into workflow_reporting_log (report_id, instance_id, workflow_id, version, start_time, status)
				values (@search_id, @instance_id, @workflow_id, @workflow_version, @start_date, 4)
				insert into search_status (search_id, start_time, status_time, status, idle_time)
				values (@search_id, @start_date, @start_date, 4, 0)		
			end
		select @search_id as 'search_id'
		
		commit transaction
		return 0

		fail:
		set @search_id = -1
		rollback transaction
		return -1;
end