TheMSsForum.com >> Ado >> Running out of transactions?

This is a multi-part message in MIME format.

------=_NextPart_000_0128_01C3874D.EF6C4E70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I have a series of COM+ objects. One is called by my ASP.Net =
application and is passed the name of an action and the parameters for =
that action. That object then queries the database with the action name =
to see if it should be in a transaction, and if so, it hands off to a =
version of itself that has its TransactionOption set to Required (so I =
only run in a transaction when necessary). The called method of the =
code in both versions looks like this:

Public Function Gateway(ByVal Security As String, ByVal Action As =
String) As Object
Try
Gateway =3D CommonGateway(Security, Action)
If ContextUtil.IsInTransaction Then ContextUtil.SetComplete()
Catch
If ContextUtil.IsInTransaction Then ContextUtil.SetAbort()
Throw 'Returns error to application
End Try
End Function

So the transaction should either be committed or aborted no matter what.

The action could involve one or more calls to the database, and each =
call goes through a single COM+ object. The code for this object is =
below.

If I look at Enterprise Manager and watch the Process Info (under =
Management\Current Activity), when I call a non-transactional action =
multiple times, I notice that eventually nearly 20 processes are open to =
the database, all in "sleeping" state with 0 open transactions, command =
is "AWAITING COMMAND", application is ".Net SqlClient Data Provider", =
and subsequent calls do not raise this number. (What I find odd here is =
that one call opens two processes, and so when I make the next call, it =
doesn't immediately reuse the existing processes. Maybe that's just =
.Net's special way of preparing for load?)

If I make multiple calls to a transactional action, the number of =
connections keeps going up, with 1 open transaction each. At this =
point, I start getting errors. The error tends to vary -- sometimes =
it's a "Transaction was implicitly or explicitly committed or aborted", =
others a "timeout period elapsed prior to obtaining a connection from =
the pool", others a simple "general network error" -- but it all seems =
to be related to having so many processes open. Stopping and restarting =
SQL Server makes the error go away (until the number of processes hits =
whatever the threshhold is again).

Also of note, the COM+ objects don't shut down after the three minutes =
they're configured for.

This would all seem to indicate (to my untrained eye) that references =
are not being released somewhere, but I can't figure out how or why. At =
the very least, shouldn't the GC clean up what I'm failing to catch?

"BaseDAL" COM+ object code:
Public Enum ReturnType

NoReturn =3D 0

Scalar =3D 1

DataSet =3D 2

XML =3D 3

End Enum

=20

<System.Runtime.InteropServices.ProgId("BaseDAL.MyDB"), _

Transaction(TransactionOption.Supported), _

ObjectPoolingAttribute(Enabled:=3DTrue), _

JustInTimeActivation(True), EventTrackingEnabled(True)> _

Public Class MyDB

Inherits ServicedComponent

Private blnDebug As Boolean

=20

Private Function OpenMyDBConnection() As =
SqlClient.SqlConnection

=20

Dim oConfig As New WMS.Config 'An object to read =
the registry

OpenWarehouseDBConnection =3D New =
SqlClient.SqlConnection(oConfig.ConfigValue("MyDB", "ConnectionString"))

OpenMyDBConnection.Open()

oConfig =3D Nothing

=20

End Function

=20

Private Function BuildSPCommand(ByVal SPName As String, =
ByVal Parameters As Hashtable) As SqlClient.SqlCommand

=20

Dim oCmd As New SqlClient.SqlCommand(SPName, =
OpenMyDBConnection())

=20

Dim SqlParameter As SqlClient.SqlParameter

=20

oCmd.CommandType =3D CommandType.StoredProcedure

SqlClient.SqlCommandBuilder.DeriveParameters(oCmd)

If Not Parameters Is Nothing Then

For Each SqlParameter In oCmd.Parameters

If =
Parameters.ContainsKey(SqlParameter.ParameterName) Then

If =
IsDBNull(Parameters.Item(SqlParameter.ParameterName)) Then

SqlParameter.Value =3D =
DBNull.Value

Else

SqlParameter.Value =3D =
Parameters.Item(SqlParameter.ParameterName).ToString

End If

End If

Next

End If

BuildSPCommand =3D oCmd

oCmd =3D Nothing

=20

End Function

=20

Public Function RunSP(ByVal SP As String, ByVal ReturnType =
As BaseDAL.ReturnType, Optional ByVal Params As Hashtable =3D Nothing) =
As Object

Dim oCmd As SqlClient.SqlCommand =3D =
BuildSPCommand(SP, Params)

RunSP =3D RunAndReturn(oCmd, ReturnType)

oCmd.Dispose() : oCmd =3D Nothing

End Function

=20

Public Function RunSQL(ByVal SQL As String, ByVal =
ReturnType As BaseDAL.ReturnType) As Object

Dim oCmd As New SqlClient.SqlCommand(SQL, =
OpenWarehouseDBConnection())

RunSQL =3D RunAndReturn(oCmd, ReturnType)

oCmd.Dispose() : oCmd =3D Nothing

=20

End Function

=20

Private Function RunAndReturn(ByRef oCmd As =
SqlClient.SqlCommand, ByVal rtType As BaseDAL.ReturnType) As Object

=20

Dim oConfig As New WMS.Config, strTimeout As String

strTimeout =3D oConfig.ConfigValue("MyDB", =
"CommandTimeout")

oConfig =3D Nothing

If IsNumeric(strTimeout) Then oCmd.CommandTimeout =
=3D CInt(strTimeout)

Select Case rtType

Case ReturnType.NoReturn

RunAndReturn =3D =
oCmd.ExecuteNonQuery()

Case ReturnType.Scalar

RunAndReturn =3D oCmd.ExecuteScalar()

Case ReturnType.DataSet

Dim oDA As New =
SqlClient.SqlDataAdapter(oCmd)

Dim oDS As New Data.DataSet

oDA.Fill(oDS)

oDA.Dispose() : oDA =3D Nothing

RunAndReturn =3D oDS

Case ReturnType.XML

Dim oXMLReader As Xml.XmlReader =3D =
oCmd.ExecuteXmlReader()

Dim sbdXML As New Text.StringBuilder

If oXMLReader.Read Then

Do

=
sbdXML.Append(oXMLReader.ReadOuterXml)

Loop Until oXMLReader.EOF

End If

oXMLReader.Close() : oXMLReader =3D =
Nothing

RunAndReturn =3D sbdXML.ToString()

sbdXML =3D Nothing

End Select

End Function

=20

End Class

=20

------=_NextPart_000_0128_01C3874D.EF6C4E70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV>I have a series of COM+ objects.  =
One is=20
called by my ASP.Net application and is passed the name of an action and =
the=20
parameters for that action.  That object then queries the database =
with the=20
action name to see if it should be in a transaction, and if so, it hands =
off to=20
a version of itself that has its TransactionOption set to Required (so I =
only=20
run in a transaction when necessary).  The called method of the =
code in=20
both versions looks like this:</DIV>
<DIV> </DIV>
<DIV>Public=20
Function Gateway(ByVal Security =
<FONT=20
color=3D#0000ff>As String, ByVal Action=20
As String) As=20
Object <FONT=20
color=3D#0000ff>Try     Gateway =3D =
CommonGateway(Security,=20
Action)     If=20
ContextUtil.IsInTransaction Then=20
ContextUtil.SetComplete() <FONT=20
size=3D1>Catch     <FONT=20
color=3D#0000ff>If ContextUtil.IsInTransaction <FONT=20
color=3D#0000ff>Then ContextUtil.SetAbort()     =
<FONT=20
color=3D#0000ff>Throw 'Returns error to application End Try End Function</DIV>
<DIV> </DIV>
<DIV>So the transaction should either be =
committed or=20
aborted no matter what.</DIV>
<DIV> </DIV>
<DIV>The action could involve one or more =
calls to the=20
database, and each call goes through a single COM+ object.  The =
code for=20
this object is below.</DIV>
<DIV> </DIV>
<DIV>If I look at Enterprise Manager and =
watch the=20
Process Info (under Management\Current Activity), when I call a=20
non-transactional action multiple times, I notice that =
eventually nearly=20
20 processes are open to the database, all in "sleeping" state with =
0 open=20
transactions, command is "AWAITING COMMAND", application is ".Net =
SqlClient Data=20
Provider", and subsequent calls do not raise this number.  (What I =
find odd=20
here is that one call opens two processes, and so when I make the next =
call, it=20
doesn't immediately reuse the existing processes.  Maybe that's =
just .Net's=20
special way of preparing for load?)</DIV>
<DIV> </DIV>
<DIV>If I make multiple calls to a =
transactional action,=20
the number of connections keeps going up, with 1 open transaction =
each.  At=20
this point, I start getting errors.  The error tends to vary -- =
sometimes=20
it's a "Transaction was implicitly or explicitly committed or aborted", =
others a=20
"timeout period elapsed prior to obtaining a connection from the pool", =
others a=20
simple "general network error" -- but it all seems to be related to =
having so=20
many processes open.  Stopping and restarting SQL Server makes the =
error go=20
away (until the number of processes hits whatever the threshhold is=20
again).</DIV>
<DIV> </DIV>
<DIV>Also of note, the COM+ objects don't =
shut down=20
after the three minutes they're configured for.</DIV>
<DIV> </DIV>
<DIV>This would all seem to indicate (to my =
untrained=20
eye) that references are not being released somewhere, but I can't =
figure out=20
how or why.  At the very least, shouldn't the GC clean up what I'm =
failing=20
to catch?</DIV>
<DIV> </DIV>
<DIV>"BaseDAL" COM+ object =
code:</DIV>
<DIV>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-spacerun: yes">       =
<SPAN=20
style=3D"COLOR: blue">Public Enum=20
ReturnType<?xml:namespace prefix =3D o ns =3D=20
"urn:schemas-microsoft-com:office:office" =
/><o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
NoReturn =3D=20
0<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-spacerun: =
yes">           &n=
bsp; =20
Scalar =3D 1<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
DataSet =3D=20
2<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
XML =3D=20
3<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"COLOR: blue">End <SPAN=20
style=3D"COLOR: blue">Enum<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Lucida =
Console'"><FONT=20
size=3D1> <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">      =20
<System.Runtime.InteropServices.ProgId(<SPAN=20
style=3D"COLOR: olive">"BaseDAL.MyDB"), =
_<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">      =20
Transaction(TransactionOption.Supported), =
_<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">      =20
ObjectPoolingAttribute(Enabled:=3DTrue),=20
_<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">      =20
JustInTimeActivation(True),=20
EventTrackingEnabled(True)>=20
_<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"COLOR: blue">Public Class=20
MyDB<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"COLOR: blue">Inherits=20
ServicedComponent<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"COLOR: blue">Private blnDebug As=20
Boolean<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Lucida =
Console'"><FONT=20
size=3D1> <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"COLOR: blue">Private Function=20
OpenMyDBConnection() As=20
SqlClient.SqlConnection<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><FONT=20
size=3D1> <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: =
2">           &nbs=
p; =20
      =20
Dim oConfig <SPAN=20
style=3D"COLOR: blue">As New =
WMS.Config=20
'An object to read the=20
registry<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: =
2">           &nbs=
p; =20
      =20
OpenWarehouseDBConnection =3D New=20
SqlClient.SqlConnection(oConfig.ConfigValue(<SPAN=20
style=3D"COLOR: olive">"MyDB", <SPAN=20
style=3D"COLOR: =
olive">"ConnectionString"))<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: =
2">           &nbs=
p; =20
      =20
OpenMyDBConnection.Open()<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
oConfig =3D=20
Nothing<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Lucida =
Console'"><FONT=20
size=3D1> <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"COLOR: blue">End <SPAN=20
style=3D"COLOR: blue">Function<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Lucida =
Console'"><FONT=20
size=3D1> <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"mso-tab-count: 1">       =
<SPAN=20
style=3D"COLOR: blue">Private Function=20
BuildSPCommand(ByVal SPName <SPAN=20
style=3D"COLOR: blue">As String, <SPAN=20
style=3D"COLOR: blue">ByVal Parameters As=20
Hashtable) As=20
SqlClient.SqlCommand<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT-FAMILY: 'Lucida Console'"><FONT=20
size=3D1> <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 8pt; FONT