C# Question about thread safety

Question

I'm using a threadpool to do some heavy processing and also bits of sql. Currently I open sql connections when I need them, run a query and then close them. This works fine. The application has been running with no issues. As more work is being done by this application it's using more threads. More threads mean more opening/closing of SQL connections. In SQL 2005 this actually hammers the server. My test server is doing around 175 transactions / sec. Approx 150 of these are being run in the master database and are "ValidateSQLLogin".

I'm going to change the app so that each thread has it's own connection and then this connection is passed around the thread.

So my question is:

If a SQL connection object is created locally in a thread and is then passed by ref to a static function of another class would this be unsafe?

void ThreadA()
{
    SqlConnection a = new SqlConnection(....);
    MyStaticClass.DoStuff(ref a);
}

void ThreadB()
{
    SqlConnection b = new SqlConnection(....);
    MyStaticClass.DoStuff(ref b);
}

static void MyStaticClass.DoStuff(ref SqlConnection sql)
{
    // Do stuff with sql
}

My initial thought is that it would be unsafe as 10 threads could all call the same static function at the same time, each passing their own connection object.

Previously the static functions opened their own connections and closed them when they were done.

If it is unsafe whats the best method to work round this. I need to try and minimize open/close of Sql connections.

Thanks

Gareth

Answer 1

A parameter to a static function is not the same as a static field. Each execution of the static function will use different copy of the connection. You don't even need to have the parameter as reference (you need reference parameters only if you want to change them).

Answer 2

Each thread has it's own stack, and parameters and local variables are stored on the stack, so there is no problem having several threads calling the same static method. Each method call has it's own parameters and local variables.

There is no reason to pass the SqlConnection by reference to the method, though.

Answer 3

As said by others, calling a static method from multiple threads is not in itself a danger. However, if the static method modifies/accesses static fields* instead of just working with the parameters you pass it, you will need to make it thread-safe.

• exception: some value types use atomic operations for access/writing and are implicitly thread-safe for those operations. However, race conditions may still occur in the logic that retrieves and updates these.

Answer 4

In .Net all the static members are thread-safe.

Answer 5

If you have that load of transactions I would recommend to keep one static connection, that remains open while you have this transactions, and when, the load greatly decreases or is null, it automatically closes, and with new requests it opens again.

Answer 6

Try the following:

You could use the lock statement to prevent other threads from entering the code block where you are using this connection, the other threads are automatically waiting till the previous thread is done and then enter this segment. Just make sure that the object you are locking is private to your static class.