SQL CLR make sure finally block is executed

Question

I have a SQL server CLR stored proc that is used to retrieve a large set of rows, then do a process and update a count in another table.

Here's the flow:

select -> process -> update count -> mark the selected rows as processed

The nature of the process is that it should not count the same set of data twice. And the SP is called with a GUID as an argument.

So I'm keeping a list of GUIDs (in a static list in the SP) that are currently in process and halt the execution for subsequent calls to the SP with the same argument until one currently in process finishes.

I have the code to remove the GUID when a process finishes in a finally block but it's not working everytime. There are instances (like when the user cancels the execution of the SP)where the SP exits without calling the finally block and without removing the GUID from the list so subsequent calls keeps waiting indefinitely.

Can you guys give me a solution to make sure that my finally block will be called no matter what or any other solution to make sure only one ID is in process at any given time.

Here's a sample of the code with the processing bits removed

[Microsoft.SqlServer.Server.SqlProcedure]
public static void TransformSurvey(Guid PublicationId)
{
    AutoResetEvent autoEvent = null;
    bool existing = false;

    //check if the process is already running for the given Id
    //concurrency handler holds a dictionary of publicationIds and AutoresetEvents
    lock (ConcurrencyHandler.PublicationIds)
    {
        existing = ConcurrencyHandler.PublicationIds.TryGetValue(PublicationId, out autoEvent);
        if (!existing)
        {
            //there's no process in progress. so OK to start
            autoEvent = new AutoResetEvent(false);
            ConcurrencyHandler.PublicationIds.Add(PublicationId, autoEvent);
        }
    }
    if (existing)
    {
        //wait on the shared object
        autoEvent.WaitOne();
        lock (ConcurrencyHandler.PublicationIds)
        {
            ConcurrencyHandler.PublicationIds.Add(PublicationId, autoEvent); //add this again as the exiting thread has removed this from the list
        }
    }
    try
    {
        // ... do the processing here..........

    }
    catch (Exception ex)
    {
        //exception handling
    }
    finally
    {
        //remove the pubid          
        lock (ConcurrencyHandler.PublicationIds)
        {
            ConcurrencyHandler.PublicationIds.Remove(PublicationId);
            autoEvent.Set();
        }
    }

}

I'd have to see some code for illustration to better answer the question, but would it be possible for you to create a second stored procedure (that executes the first one) and put the finally block there? — Josh Austin, Jan 31 '13 at 03:03
Added a code sample. Yes but can I be certain that the outer SP will execute properly if a rude abort happened in the inner SP ? — Amila, Jan 31 '13 at 04:17
The code sample is helpful, but I'm very interested in how the Cancel code works in to this where the finally doesn't get executed. I always understood that once you are in a try block the finally is always executed. Maybe there is a better way to handle the cancel? — James, Jan 31 '13 at 16:20
I also thought the finally will run not matter what. But later found that Finally is only executed if all goes well or if there's a graceful abort of the thread. Code exits without going to finally if there's a so called rude abort. — Amila, Feb 01 '13 at 03:48
Does that mean that you are using a "ThreadAbortException" to "Cancel"? I don't know the constraints of your overall environment but I would consider having the user cancel action set a flag in a table and when possible during your processing check the flag. That is a common practice and why most cancel buttons take a few seconds before they actually cancel the process anyway. — James, Feb 01 '13 at 23:07
No the issue I have is the finally block is not run if a user starts 2 instances of the SP the SP from SSMS at the same time and click the stop button in one SSMS window. Then the running instance continues to wait as the stopping thread does not notify that it s exiting. — Amila, Feb 03 '13 at 03:18

score 1 · Answer 1 · answered Jan 31 '13 at 04:01

Wrapping the code at a higher level is a good solution, another option could be the using statement with IDisposable.

public class SQLCLRProcedure : IDisposable
{
     public bool Execute(Guid guid)
     {
           // Do work
     }
     public void Dispose()
     {
           // Remove GUID
           // Close Connection
     }
}

using (SQLCLRProcedure procedure = new SQLCLRProcedure())
{
  procedure.Execute(guid);
}

This isn't verified in a compiler but it's commonly referred to as the IDisposable Pattern. http://msdn.microsoft.com/en-us/library/system.idisposable.aspx

Thanks, but according to this msdn article http://msdn.microsoft.com/en-us/library/yh598w02.aspx A using block is converted to a try/finally by the compiler. But the finally blocks aren't called when there's a rude abort (like user cancelling the sp). So I will end up with the same situation with the finally is not being executed — Amila, Jan 31 '13 at 10:19

SQL CLR make sure finally block is executed

1 Answers1