none
Error with executing SQL statements and Transactions through MSMQ Listener RRS feed

  • Question

  • I am encountering error: "The transaction associated with the current connection has completed but has not been disposed.  The transaction must be disposed before the connection can be used to execute SQL statements."

    The scenario is this (note: all SQL statements are done via stored procedures and repository pattern):

    • I have an MSMQ Listener that listens for messages in queue
    • When a message is received (e.g. "DATATABLE1"), it starts an SQL Job that downloads data from a LARGE CSV file based on the message (e.g. "DATATABLE1.csv")
    • It then updates a record in the database pertaining to this particular file, just to flag that it is already downloading (for use by other components of the system).
    • The program then sleeps and loops to check if the program is already done downloading (by querying the Job execution status). If it is not yet done, it sleeps again and just repeats the process until the Job has executed successfully.

    The problem is, the program can query SQL while the Job is running. The moment the Job has executed successfully, the next time the program queries SQL to check the status of the job, it throws the error I mentioned above.

    Here's a rough sample of my code:

    Program Start:

    listener.Start(
        m =>
        {
            using (var downloadService = new DownloadService())
            {
                if (downloadService.Import(m.Message))
                    return true;
                else
                    return false;
            }
        }
    );

    Listener:

    private void OnPeekCompleted(object sender, PeekCompletedEventArgs e)
    {
        try
        {
            _messageQueue.EndPeek(e.AsyncResult);
        }
        catch (Exception ex)
        {
            //do error handling
        }
    
        var messageId = string.Empty;
    
        try
        {
            using (TransactionScope transaction = new TransactionScope())
            {
                Message message;
                if(_strictTransaction)
                    message = _messageQueue.Receive(MessageQueueTransactionType.Automatic);
                else
                    message = _messageQueue.Receive(MessageQueueTransactionType.Single);
    
                if (message != null)
                {
                    messageId = message.Id;
                    var reader = new StreamReader(message.BodyStream);
                    var jsonMessage = reader.ReadToEnd();
                    var concreteMessage = JsonConvert.DeserializeObject<T>(jsonMessage, new JsonSerializerSettings
                    {
                        TypeNameHandling = TypeNameHandling.All
                    });
    
                    if (ProcessMessage(concreteMessage))
                        transaction.Complete();
                    else
                        throw new ApplicationException("Message did not complete.");
                }
            }
            
        }
        catch (Exception ex)
        {
           //do error handling
        }
    
    }
    
    private bool ProcessMessage(T body)
    {
        if (_messageHandler != null)
        {
            var t = _messageHandler(body);
            return _messageHandler(body);
        }
    
        return false;
    }

    DownloadService:

    public bool Download(string fileName)
    {
        _dbRepo.StartJob(fileName, JobType.Download); //Starts the job that downloads content
    
        _dataRepo.UpdateStatus(fileName, true); //flags record for the file to Downloading
    
        WaitJobToComplete:
        Thread.Sleep(3000);
    
        if (_dataRepo.Downloading(fileName)) //this is where ERROR occurs! it only occurs once the Job has successfully completed, but continues as normal while the Job is running
        {
            var jobStatus = _dbRepo.GetJobExecution(fileName, JobType.Download); //queries Job status
            
            if (jobStatus.CurrentExecutionStatus == 4)
            {
                _dataRepo.UpdateStatus(fileName, false); //flags record for the file to Complete
    
                return true;
            }
    
            goto WaitJobToComplete;
        }
        
        return false;
    }

    I have searched and already tried the most common below solutions I found to no avail:

    • increased time out in machine.config
    • increased time out in TransactionScope
    • increased sleep time to wait until Job completes (I thought the error had something to do with it continuously querying every 3 sec)

    Any help would be appreciated as well as any possible solutions.

    Tuesday, June 11, 2019 3:10 PM

All replies

  • Maybe you should remove ‘var t = _messageHandler(body)’.

    Tuesday, June 11, 2019 3:40 PM
  • Hi OCS.New,

    Since your question is more related to MSMQ, you could post a new thread in MSMQ forum.

    The CLR Forum discuss and ask questions about .NET Framework Base Classes (BCL) such as Collections, I/O, Regigistry, Globalization, Reflection. Also discuss all the other Microsoft libraries that are built on or extend the .NET Framework, including Managed Extensibility Framework (MEF), Charting Controls, CardSpace, Windows Identity Foundation (WIF), Point of Sale (POS), Transactions.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, June 12, 2019 2:49 AM
  • I'm going to recommend a few things.

    First off, you should never use Thread.Sleep in a production application. It’s meant for simulating lengthy operations while testing/debugging on an MTA thread. In .NET there's no other reason to use it. Specifically -  Thread.Sleep(n) means block the current thread for at least the number of timeslices that can occur within n milliseconds.

    Also, you did not show your ADO.NET code. Not sure where your connection comes into play, but Complete() should be outside the connection block.

    You can increase the timeout for your transaction like this (use values appropriate for the expected length of your transaction). The code below is for 15 minutes:

    using (TransactionScope scope = 
                 new TransactionScope(TransactionScopeOption.Required, 
                                       new System.TimeSpan(0, 15, 0)))
      {
          // working code here
      }

    You may also have to update your app.config and machine.config to extend past the default max timeout of 10 minutes.

    <configuration>
        <system.transactions>
            <defaultSettings timeout="00:15:00" /> <!-- 15 minutes -->
        </system.transactions>
    </configuration> 
    
    <configuration>
        <system.transactions>
            <machineSettings maxTimeout="00:15:00" /> <!-- 15 minutes -->
        </system.transactions>
    </configuration>


    william xifaras


    Tuesday, June 18, 2019 10:08 PM