SQLConnection inside thread stuck finalization thread

Question

We have a class that reads the database in parallel to the main thread.

public class CardHandligSpeedometer : IDisposable
{
    private Thread m_Thread;

    public CardHandligSpeedometer()
    {
        createThread();        
    }        

    public void StartThread()
    {
        m_IsStopped = false;

        if (m_Thread == null)
        {
            createThread();
        }

        m_Thread.Start();
    }

    public void StopThread()
    {
        m_IsStopped = true;

        if (m_Thread != null)
        {
            m_Thread.Join(500);

            if (m_Thread != null && m_Thread.IsAlive)
            {
                m_Thread.Abort();
            }

            m_Thread = null;
        }
    }

    public void Dispose()
    {
        StopThread();
    }

    void getHandledCard()
    {
       while (!m_IsStopped)
       {                    
            try
            {
                using (SqlConnection connection = new SqlConnection(LetFlowLib.FactoryData.StrCnt))
                {
                    connection.Open();

                    using (SqlCommand cmd = new SqlCommand("GetHandledCardCountFor2Hours", connection))
                    {
             //code
                    }

                    Thread.Sleep(m_Interval);
                }
            }
            catch (Exception ex)
            {
                // code
            }                
        }            
    }

    private void createThread()
    {
        m_Thread = new Thread(getHandledCard);
        m_Thread.SetApartmentState(ApartmentState.MTA);
        m_Thread.IsBackground = true;
    }
}

A thread is created and started in the main thread

m_CardHandlingSpeedometer = gcnew ltr_GUI::CardHandligSpeedometer(_CTX.GetAppointID(), 30000);
m_CardHandlingSpeedometer->StartThread();

After reading from the database for the first time and exiting 'using', the finalizing thread is blocked.

If GC::Collect(2); and GC::WaitForPendingFinalizers(); are called in the main thread, the program freezes on the method WaitForPendingFinalizers.

But if change SATA to MTA in the creation method createThread(), then everything works fine.

What is the reason for this?

Answer 1

the program freezes on the method WaitForPendingFinalizers.

Because you have a deadlock: the main threading is waiting on GC, and GC can't start because it's waiting for the other thread, which is stuck waiting on a call which needs to be marshalled back to the main thread, which is waiting on GC.......

But if change SATA to MTA in the creation method createThread(), then everything works fine.

I think you meant STA. Clearly there is a call somewhere (probably the dispose) which needs to be marshalled back (as you have an STA set up already), and declaring thsi thread as MTA is basically saying "I don't care, just execute it on this thread".

---

This code is just the complete wrong way to go about concurrent and asynchronous database calls.

The use of Thread.Abort is also an extremely bad idea, and doesn't work at all in .NET Core and version 5+.

Instead, use async and await with a CancellationToken

private async Task GetHandledCard(TimeSpan waitTime, CancellationToken cancellationToken)
{
    while (!cancellationToken.IsCancellationRequested)
    {
        try
        {
            using SqlConnection connection = new SqlConnection(LetFlowLib.FactoryData.StrCnt);
            connection.OpenAsync(cancellationToken);
            using SqlCommand cmd = new SqlCommand("GetHandledCardCountFor2Hours", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            // use cmd.ExecuteReaderAsync or cmd.ExecuteScalarAsync etc with cancellationToken
            await Task.Delay(waitTime, cancellationToken);
        }
        catch (OperationCanceledException)
        {
            return;
        }
        catch (Exception ex)
        {
            //code
        }                
    }
}

And then call it

using var cts = new CancellationTokenSource(TimeSpan.FromMinutes(10));
var task = Task.Run(() => GetHandledCard(TimeSpan.FromSeconds(30), cts.Token), cts.Token);
// do more stuff
await task;