Plugin Scheduler Logs the error The transaction operation cannot be performed because there are pending requests working on this transaction

Problem

Plugins started or run by the Plugin Scheduler service may log the error "The transaction operation cannot be performed because there are pending requests working on this transaction" to the event log.

Causes

There are several possible causes of this error:

SQL Queries are performed without using the current SQL Transaction
SQLCommands are not disposed
SQLDataReaders are not closed
A Plugin has been invoked from another thread such that it interrupts existing code running in the Plugin Scheduler thread https://jiwa.atlassian.net/browse/DEV-6017

Solution

Make sure all use of the SqlCommand disposes correctly by using a using block, Make sure you always provide the SQLTransaction of the Manager class, and make sure you always close a SqlDataReader by performing a close in a finally block:

Template Code for SQL Queries

var db = manager.Database; // for versions 7.00.177.00 and later
// var db = JiwaFinancials.Jiwa.JiwaApplication.Manager.Instance.Database; // for versions prior to 7.00.177.00
SqlDataReader SQLReader = null;
SqlParameter SQLParam = null;
		
try
{
	using (SqlCommand SQLCmd = new SqlCommand("usp_Jiwa_Debtor_BackOrderFulfillments", db.SQLConnection, db.SQLTransaction))
	{
        SQLCmd.CommandType = System.Data.CommandType.StoredProcedure;
		// Add parameters
		SQLParam = new SqlParameter("@RecID", System.Data.SqlDbType.Char);
		SQLParam.Value = "?????";
		SQLParam.Direction = ParameterDirection.Input;
        SQLCmd.Parameters.Add(SQLParam);
 
		// Execute query
		SQLReader = db.ExecuteReader(SQLCmd);
 
		// Iterate results
		while (SQLReader.Read())
		{
		}
	}
finally
{
	if (SQLReader != null)
		SQLReader.Close();
}

Modify the plugin(s) to observe and set a Critical Section flag - and to yield processing whilst that flag has been set - the following code shows how to do this.

Critical Section Flag

JiwaApplication.BusinessLogic.GenericObjectItem criticalSection = Plugin.Manager.Staff.GenericObjectCollection("Critical Section Flag");  // for versions 7.00.177.00 and later
// JiwaApplication.BusinessLogic.GenericObjectItem criticalSection = JiwaApplication.Manager.Instance.Staff.GenericObjectCollection("Critical Section Flag"); // for versions prior to 7.00.177.00
if (criticalSection == null) 
{
	criticalSection = new JiwaApplication.BusinessLogic.GenericObjectItem();
	criticalSection.RecID = "Critical Section Flag";
	criticalSection.Object = true;
	Plugin.Manager.Staff.GenericObjectCollection.Add(criticalSection); // for versions 7.00.177.00 and later
	//JiwaApplication.Manager.Instance.Staff.GenericObjectCollection.Add(criticalSection); // for versions prior to 7.00.177.00
} 
else 
{
	while (criticalSection.Object == true) 
	{
		// Critical section flag is set, so wait for 1 second - whoever set it should release it soon
		System.Threading.Thread.Sleep(1000);				
	}
	criticalSection.Object = true;
}
 
try
{
    // Existing processing code goes in here - i.e.: The existing code of the Execute method of the ScheduledExecutionPlugin class.
}
finally
{
    criticalSection = Plugin.Manager.Staff.GenericObjectCollection("Critical Section Flag"); // for versions 7.00.177.00 and later
    // criticalSection = JiwaApplication.Manager.Instance.Staff.GenericObjectCollection("Critical Section Flag"); // for versions prior to 7.00.177.00
    If (criticalSection != null)
        criticalSection.Object = false;
}

In the case of plugins invoked on a schedule, this code should be placed right at the start of the Execute method of the ScheduledExecutionPlugin class - and the existing code placed in the try block.

In the case of an asynchronous hook, this should be placed at the start of the handler for that hook (e.g.: the OnFileImport method of a File Watcher), and the existing code placed in the try block.