Scheduler error "New transaction not allowed"

[DannyC]

I'm writing a plugin which has a schedule to run every xx interval.

It uses a SQL script to find candidate sales orders, writes to a history custom field. Saves the sales order & moves to the next sales order.
On the save event, I am getting an error "New transaction is not allowed because there are other threads running in the session.".

Version 7.2.1 SR8

I've tried various little code tweaks to sort it out but I can't can't solve it so I suspect it's a bit more esoteric. My code

Code: Select all

public void Execute(JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin, JiwaFinancials.Jiwa.JiwaApplication.Schedule.Schedule Schedule)
{
   lock (JiwaFinancials.Jiwa.JiwaApplication.Manager.CriticalSectionFlag)
   {
      // place processing code in here
      
      //Find candidate sales orders
      LogToEventLog(String.Format("Executing schedule '{0}'", Schedule.Name), System.Diagnostics.EventLogEntryType.Information, Plugin);   
      
      SqlDataReader SQLReader = null;
      
      try {
         
         JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder salesOrder = Plugin.Manager.BusinessLogicFactory.CreateBusinessLogic<JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder>(null);
         string SQL = "SELECT  TOP (100) PERCENT SO_Main.InvoiceID, SO_Main.InvoiceInitDate, SO_HistoryCustomSettingValues.Contents,";
         SQL += " SO_HistoryCustomSettingValues.SettingValueID, SO_HistoryCustomSettingValues.SettingID ";
         SQL += " FROM SO_Main INNER JOIN SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID AND SO_Main.CurrentHistoryNo = SO_History.HistoryNo";
         SQL += " LEFT OUTER JOIN SO_HistoryCustomSettingValues ON SO_History.InvoiceHistoryID = SO_HistoryCustomSettingValues.HistoryID ";
         SQL += " WHERE (SO_Main.DebtorID = '0FC4070CEBCC491CA15A') AND (SO_HistoryCustomSettingValues.Contents <> 'True' OR ";
         SQL += " SO_HistoryCustomSettingValues.Contents IS NULL) AND (SO_Main.InvoiceInitDate > CONVERT(DATETIME, '2022-02-27 00:00:00', 102))";
            
         using (SqlCommand SQLCmd = new SqlCommand(SQL, Plugin.Manager.Database.SQLConnection, Plugin.Manager.Database.SQLTransaction)) {                           
            SQLReader = SQLCmd.ExecuteReader();
            
            while (SQLReader.Read())
            {
               LogToEventLog(String.Format("Exporting invoice '{0}'", (string)Plugin.Manager.Database.Sanitise(SQLReader, "InvoiceID")), System.Diagnostics.EventLogEntryType.Information, Plugin);   
               salesOrder.Read((string)Plugin.Manager.Database.Sanitise(SQLReader, "InvoiceID"));
               
               JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrderHistory salesHist = salesOrder.SalesOrderHistorys[salesOrder.CurrentHistoryNo];   
               salesHist.CustomSettingValues.get_ItemFromSettingName("ScanOrders").Contents = "True";
               salesOrder.Save();
               LogToEventLog(String.Format("Invoice Saved: '{0}'", salesOrder.InvoiceNo), System.Diagnostics.EventLogEntryType.Information, Plugin);   
               
            }
               
         }
      }
      catch(System.Exception e)
      {
         LogToEventLog(String.Format("Error '{0}'", e.Message), System.Diagnostics.EventLogEntryType.Information, Plugin);   
      }
      finally {
         if (SQLReader != null) {
            SQLReader.Close();
         }
      }            
   }       
}   

[Mike.Sheen]

Hard to say the cause.

I' think I managed to reproduce the issue and work-around it - but I had some unknowns - so I had to make some assumptions.

I think you may have overlooked situations where you might have more that one custom history field - your SQL query assumes there is only one custom field - I think that would have come back to bite you later.

Attached works - I avoid the issue by reading the invoiceid's and putting them into a list, then iterating over that and reading the sales orders, setting the value and saving. I also cleaned up your SQL query to correctly deal with multiple custom fields.

[DannyC]

I think you may have overlooked situations where you might have more that one custom history field

Not overlooked - just lazy in typing the SQL for the question. At the client site the SQL is much tighter.

Your idea of populating a list works nicely. Really appreciate the quick response.

[Mike.Sheen]

Really appreciate the quick response.

If you provide a plugin and a set of steps to follow, we can usually respond really quickly - often I look at forum posts within a few minutes of them being posted - and If I know I can answer quickly I will - but if I see I need to do a bit of work to even repro the issue - like make a plugin myself - then I normally just go back to what I was doing and make a mental note to come back when I have time.

It was fortunate this time I had some time to look at your issue shortly after it was posted - but if you help us in future by giving a whole plugin (including the custom field definition) and some steps to repro we can almost always respond quickly.

It is more effort to give us an isolated plugin - we know this because this is exactly what we have to do when we report issues upstream to our software suppliers. We learnt a long time ago we have to make it as easy as possible for the whoever we want to help us.

An added bonus we found is that when preparing an isolated, reproducible example we often found the solution during that process, negating the need to even report it!

[pricerc]

I was getting the "New transaction is not allowed because there are other threads running in the session." error just by having a background thread trying to write debug logging to a file, not touching SQL at all.

It was more than a little annoying. I'd wanted to move away from a logging library which required an embedded assembly and so rolled my own light-weight logger that ran on a background thread emptying a Queue<T> into a file, where said Queue<T> was being populated by various Log(T event) statements.

It worked great until I tried using it in a plugin.

The point was to have a background thread that would stay out of the way of actual work, and yet its mere presence was enough to upset the SQL client.

[Mike.Sheen]

I was getting the "New transaction is not allowed because there are other threads running in the session." error just by having a background thread trying to write debug logging to a file, not touching SQL at all.

It was more than a little annoying. I'd wanted to move away from a logging library which required an embedded assembly and so rolled my own light-weight logger that ran on a background thread emptying a Queue<T> into a file, where said Queue<T> was being populated by various Log(T event) statements.

It worked great until I tried using it in a plugin.

The point was to have a background thread that would stay out of the way of actual work, and yet its mere presence was enough to upset the SQL client.

This may be related to DEV-9153. That DEV issue was originally around the REST API and API Keys - but it turned out it was caused by the Jiwa collections not being thread-safe.

[pricerc]

I was getting the "New transaction is not allowed because there are other threads running in the session." error just by having a background thread trying to write debug logging to a file, not touching SQL at all.

It was more than a little annoying. I'd wanted to move away from a logging library which required an embedded assembly and so rolled my own light-weight logger that ran on a background thread emptying a Queue<T> into a file, where said Queue<T> was being populated by various Log(T event) statements.

It worked great until I tried using it in a plugin.

The point was to have a background thread that would stay out of the way of actual work, and yet its mere presence was enough to upset the SQL client.

This may be related to DEV-9153. That DEV issue was originally around the REST API and API Keys - but it turned out it was caused by the Jiwa collections not being thread-safe.

Hmmm. I see it's marked as fixed in SR9 - if I can find some time, I'll try and re-enable my background queue and try it. I think I left the code there, just commented out, and wrote directly to the log instead of queueing it.