.NET, the SqlConnection object, and multi-threading

Question

We have an application which uses an SQL Server 2008 R2 database. Within the application, calls to the database are made using a SqlConnection object.

This SqlConnection object is initialized once, the first time it is accessed, and then re-used throughout the application. The action that we use is the following:

Protected _cn As SqlConnection = Nothing

...

Protected Sub Open()
    If _cn Is Nothing Then
        _cn = New SqlConnection(_sqlConn)
    End If

    If _cn.State = ConnectionState.Closed OrElse _cn.State = ConnectionState.Broken Then
        _cn.Open()
    End If
End Sub

This works perfectly fine during normal execution of the program. However, there are a few portions of the application that are executed in a multi-threaded fashion. When one of these parts is executing, frequent errors occur if other actions are made.

After digging a bit, I realised that this is because there were times where two different threads both attempted to use the same SqlConnection object.

So, after identifying the problem, I now need to find solutions. The obvious solution is to just re-create the SqlConnection object every time a database call requires one - in this case, it would never be shared. Is there any reason not to do this? I assumed originally that we had only one connection object per session of the application for performance reasons, but is this actually the case?

If we do need to keep just one connection object open, what is the suggested solution? Should I put in place some sort of timer which will keep cycling until the connection object is available, and then access it?

Solution 1

The obvious solution is to just re-create the SqlConnection object every time a database call requires one - in this case, it would never be shared. Is there any reason not to do this?

On the contrary, that's absolutely what you should do. That's the behaviour SqlConnection was designed for. You should use a Using statement to automatically close the connection at the end of the block you're using it for, and the connection pool mechanism will automatically handle the real underlying connections to the database.

Solution 2

I see no reason NOT to create a SQL connection every time you need it. In fact, that is probably the best way to do it because it gives the .NET framework the flexibility to manage and reuse connections most efficiently. Wrap each of your SQL connections in a USING so you hang on to them as short a time as possible.

We've created a method that creates a connection and everyone uses that:

using (var conn = GetConnection())
    using (var proc = GetProcedure(conn, "procname"))
        using (var reader = proc.GetReader())
        {
            ... DB stuff
        }

Comments

[Moslem Ben Dhaou]

Just wondering, what if you need changes from two threads to go in the same transaction? Wont you have to "share" the connection?

[Jon Skeet]

@MoslemBenDhaou: I'd argue that if you have two threads wanting to do something in the same transaction, you've got big problems anyway. You'll have to coordinate between them - how are you going to know when to commit otherwise? It would probably be better at that point to put actions onto some producer/consumer queue to do the whole transaction in one thread.

[Moslem Ben Dhaou]

Well I have a FileShare crawler (getting permissions and dropping them somewhere for later Audit). Currently it is starting multiple threads to crawl the same folder (to speed up the process). I would love to have each folder committed in the same transaction rather than having incomplete scanning (current situation, when some threads fail). No transaction concept is implemented currently but I am evaluating the options. The producer/consumer queue would be an option but unfortunately memory is a limit. I guess I am stuck with a memory/time compromise. Any other suggestions?

[Jon Skeet]

@MoslemBenDhaou: It sounds like you're trying to ask a new question in the comments of a fairly different question. I suggest you ask a completely separate question.

[Moslem Ben Dhaou]

True sorry :) was thinking it might be a quick answer. I will move it.

[Kiran Ramaswamy]

Fair enough Jon - pretty much everyone I've asked has had the same response as you. So, off to make the changes. Thanks!

[Mike]

In my case, after implementing Jon's approach, my app went from experiencing timeouts from about a quarter of my threads and taking ~1 minute every cycle, to everything working smoothly and completing in just a second or two. Thanks Jon!