May 29, 2014 at 2:25 pm
Hi all,
Not really sure if this is the appropriate forum for this question; if not, please redirect me to a better one =)
We have an application which uses an SQL Server 2008 R2 database. Within the application, calls to the database are made using an 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?
May 29, 2014 at 3:03 pm
In a multi-threaded application, anything that isn't thread safe should be wrapped in a sync lock. This makes it so that only one thread can enter the sync lock section at a time.
As far as whether it's better to have one connection per thread, or sync lock... It depends on how many threads you're running and how much activity you're throwing at the database, along with how much data is being moved and how much memory you have on the machine you're running your application.
The sync lock will require the least amount of work to add in, but it obviously becomes a bottleneck where only one thread can use the connection at a time.
If it were me, I'd try the sync lock first, and if you are having performance issues, then start adding in the extra connections.
May 29, 2014 at 4:26 pm
Generally speaking, in .NET I would recommend always closing a connection as soon as your are finished with it.
This frees the connection for the connection pool and it can be re-used by another part of your code. .NET will actually keep the connection open for a while after you think you have closed it in order to make the next use of a connection quicker.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply