Do multiple SQL/CLR calls of the same procedure run at the same time (like threads)?

Question

I have a singleton (because i thought it would be usefull to have these available all the time) to represent some settings, which will clear the old and load the new settings from the database at the beginning of the clr procedure call.

But if CLR calls are handled like threads I will probably get the problem at some point that the settings are cleared in exact that moment I want to access them.

So will this be an issues? Can I work around this with a simple object lock?

EDIT:

Code Sample Settings:

public class Settings
{
    public static Settings Default
    {
        get { return _default ?? (_default = new Settings()); }
    }
    private static Settings _default;

    private Dictionary<string, string> _settingsDict;

    private Settings()
    {
        _settingsDict = new Dictionary<string, string>();
    }

    public void ReloadSettings()
    {
        _settingsDict.Clear();

        using (var connection = new SqlConnection("context connetion=true"))
        using (var command = connection.CreateCommand())
        {
            command.CommandText = ...
            connection.Open();

            // Read Settings with DataReader into _settingsDict
        }
    }

    public string Get(string key) {
        get { return _settingsDict["key"] }
    }
}

Procedure:

[SqlProcedure]
public static void InsertData(SqlString csv)
{
    Settings.Default.ReloadSettings();

    var setting = Settings.Default.Get("SETTING");

    using (var connection = new SqlConnection("context connetion=true"))
    using (var command = connection.CreateCommand())
    {
        ...
    }
}

If would be a good idea to make it very clear in the question that you are specifically talking about SQL/CLR. This is currently hidden away in the tags only. — Marc Gravell, May 15 '14 at 08:13
I've updated the question with a code sample and a better title — Staeff, May 15 '14 at 08:40

score 0 · Answer 1 · answered May 24 '14 at 14:39

The easiest way to do this is to not mutate the existing dictionary, but to create a new one and atomically write it to the global variable.

In order to have a non-readonly static variable, you need unsafe permissions in SQL Server. Be aware of that. You can avoid this requirement by using a wrapper class

class MutableCell<T> { public volatile T value; }
static readonly MutableCell<...> myVar = new ...();

I added volatile which is required here because multiple threads are racing to read and write to that variable.

In general, doing threading and mutable state inside of SQL Server is not the best of ideas. It is best avoided. You risk introducing very hard and catastrophic bugs. Your scenario looks legitimate, though.

st4hoo · Answer 2 · 2014-05-15T08:59:31.770

-1

Just do the reload transactionally and use ConcurrentDictionary.

Starting point: http://msdn.microsoft.com/en-us/library/dd287191(v=vs.110).aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

edited May 15 '14 at 08:59

answered May 15 '14 at 08:51

st4hoo

2,196
17
25

Just using a thread-safe dict does not make all operations thread-safe. He can still see an empty dict from a different thread. – usr May 24 '14 at 14:37

score -1 · Answer 3 · answered May 24 '14 at 14:04

Threading in SQL-CLR is rather different than normal windows code in that it is handled by something called "SQLOS". Thread's are assigned to a scheduler for a given task and they cannot be reallocated to a different thread so you can make some assumptions there. That said

The concurrent Dictionary unfortunately uses locking so you have to be really careful, as it requires the "unsafe" permission set.

Adam Mechanic has a really good presentation on this stuff: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B404

When you run a proc it pretty much is spun out as its own thread. So two people calling it will clash.

Do multiple SQL/CLR calls of the same procedure run at the same time (like threads)?

3 Answers3