SQL CLR - Does lock statement make any difference?

Question

Hello All,

I have written the following piece of code ( sql clr stored procedure ) that writes messages to a local file. The problem occurs when multiple connections call the stored proc at the same time. So I used a lock statement. But this doesn't seem to make any difference? What am I doing wrong here?

lock (SqlContext.Pipe)
{
    StreamWriter sw = File.AppendText("C:\Date.txt");
    int y = 50;

    while (y != 0)
    {
        sw.WriteLine(DateTime.Now + " " + serverName + " -- " + jobId.ToString() );
        System.Threading.Thread.Sleep(new Random().Next());
        y = y - 1;
    }
    sw.Close();

}

Answer 1

lock statement on its own doesn't protect anything. The magic happens only when all threads lock the same object. In you case, each thread locks its own context pipe, the behavior is going to be identical with or without lock.

Besides, from all the damage a CLR procedure can do inside SQL, hijacking a SQL worker to wait in Sleep() is definitely a top offender. I hope you only use it for experimental purposes.

To achieve what you probably want, ie. have only one procedure execute at any time, use an application lock: sp_getapplock. Either wrap the CLR procedure call in T-SQL sp_getapplock/sp_releaseapplock, or execute sp_getapplock on the context connection from your CLR code (and execute sp_releaseapplock on your way out).