Thread Pool Problem on Adding/Updating Rows

[Bob Day]

Using VS 2003, VB.net SQL MSDE...

The code below fails every time. Essentially, is it rapidly adding 1 row
and then updating that row to the datasource and then repeating 200 times.
It fails on the .UPDATE after about 73 UPDATES with the error message:

"A first chance exception of type 'System.InvalidOperationException'
occurred in system.data.dll.



Additional Information: Timeout expired. The timeout period elapsed prior
to obtaining a connection from the pool. This may have occurred because all
pooled connections were in use and max pool size was reached."



If you UPDATE 50 rows at a time, instead of one, you do not get this error
message. Having researched help, this error implies I am opening a
connection that I am not closing. However, everything below is created in
component designer, which should be handling this automatically.

1) Why is this failing?

2) How do I fix it?


Code Snippet:
' DIM Rows_Updated - rows updated by DataAdapter
Dim Rows_Updated As Integer = -1

Dim count As Integer = -1

' instantiate dataset
Dim DS As New DS_Caller_Base

' clear DataTable rows
ds.PINS.Rows.Clear()

' instantiate a DR to add
Dim DR_To_Add As DS_Caller_Base.PINSRow

For count = 0 To 200

' set DR row = to dataset we are working with
DR_To_Add = DS.PINS.NewPINSRow

With DR_To_Add
' set foreign key value (only needed for new row)
.fld_FK_Customer_Table_ID = 6
.fld_IX_PIN = CStr(count)
End With

' add new empty row to dataset
DS.PINS.AddPINSRow(DR_To_Add)

' cd is an instantiation of Component Designer
Rows_Updated = CD.DA_tblPINS.Update(DS, DS.PINS.TableName)

Next

Thanks!

Bob Day

 

[Kevin Yu [MSFT]]

Hi Bob,

It seems that you're having problem with connection pooling. ADO.net uses
connection pooling by default. When you close a connection, the connection
will be put back to the connection pool. When the DataAdapter updates the
database, it will open a connection automatically, if there's no active
connection available. When the update is completed, the DataAdapter will
set the connection to the initial state.

Since you've got this exception, I think you might have opened the
connection before your code snippet. So when the update is completed, the
connection is still opening. The DataAdapter might get another connection
next time. After a loop, the connection pool is used up. So the exception
is thrown.

You can try to use SqlConnection.State property to monitor the state of the
connection. However, I recommend you to put the Update call outside the
loop. It might hit the performance if you put the Update inside the loop.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: "Bob Day" <[email protected]>
| Subject: Thread Pool Problem on Adding/Updating Rows
| Date: Tue, 4 Nov 2003 14:22:49 -0500
| Lines: 66
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <#[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: dhcp16624016.indy.rr.com 24.166.24.16
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:65389
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Using VS 2003, VB.net SQL MSDE...
|
| The code below fails every time. Essentially, is it rapidly adding 1 row
| and then updating that row to the datasource and then repeating 200 times.
| It fails on the .UPDATE after about 73 UPDATES with the error message:
|
| "A first chance exception of type 'System.InvalidOperationException'
| occurred in system.data.dll.
|
|
|
| Additional Information: Timeout expired. The timeout period elapsed
prior
| to obtaining a connection from the pool. This may have occurred because
all
| pooled connections were in use and max pool size was reached."
|
|
|
| If you UPDATE 50 rows at a time, instead of one, you do not get this error
| message. Having researched help, this error implies I am opening a
| connection that I am not closing. However, everything below is created in
| component designer, which should be handling this automatically.
|
| 1) Why is this failing?
|
| 2) How do I fix it?
|
|
| Code Snippet:
| ' DIM Rows_Updated - rows updated by DataAdapter
| Dim Rows_Updated As Integer = -1
|
| Dim count As Integer = -1
|
| ' instantiate dataset
| Dim DS As New DS_Caller_Base
|
| ' clear DataTable rows
| ds.PINS.Rows.Clear()
|
| ' instantiate a DR to add
| Dim DR_To_Add As DS_Caller_Base.PINSRow
|
| For count = 0 To 200
|
| ' set DR row = to dataset we are working with
| DR_To_Add = DS.PINS.NewPINSRow
|
| With DR_To_Add
| ' set foreign key value (only needed for new row)
| .fld_FK_Customer_Table_ID = 6
| .fld_IX_PIN = CStr(count)
| End With
|
| ' add new empty row to dataset
| DS.PINS.AddPINSRow(DR_To_Add)
|
| ' cd is an instantiation of Component Designer
| Rows_Updated = CD.DA_tblPINS.Update(DS, DS.PINS.TableName)
|
| Next
|
| Thanks!
|
| Bob Day
|
|
|

 

[Bob Day]

Thanks for your help.

I inserted the following line of code at various places in my code snippet,
including directly before and after the update commanded, and x is always
equal to Closed. CD below stands for Component Designer, where the
connection is created (again, everything is from Component Designer).
Dim x As String = CD.Cnn_For_CSolutionDatabaseSQL.State.ToString
So, I think that kills your theory on what the problem is. If it is closed
before and after the UPDATE, why am I getting this error?

I am getting this error randomly, so the code snippet was just an effort to
make it replicatible. I realize in actual good code the UPDATE command
would be better outside the loop.

Bob Day

 

[Kevin Yu [MSFT]]

Hi Bob,

I've done some research on this issue and found that this might be a known
issue. When stress testing an application in debug mode in the VS.Net IDE,
the SQLClient connection pool exhausts quickly and this error occurrs. You
can try the following resolutions:

1. SQL Debugging is turned on; this appears to cause connections to fail to
be
returned to the pool. Turn SQL Debugging off.

2. Raise the pool size. In the connection string, add "Max Pool Size=x"
where x is
an integer value higher than the default of 100.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: "Bob Day" <[email protected]>
| References: <#[email protected]>
<[email protected]>
| Subject: Re: Thread Pool Problem on Adding/Updating Rows
| Date: Wed, 5 Nov 2003 13:26:58 -0500
| Lines: 138
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: dhcp065-029-072-036.indy.rr.com 65.29.72.36
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:65542
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Thanks for your help.
|
| I inserted the following line of code at various places in my code
snippet,
| including directly before and after the update commanded, and x is always
| equal to Closed. CD below stands for Component Designer, where the
| connection is created (again, everything is from Component Designer).
| Dim x As String = CD.Cnn_For_CSolutionDatabaseSQL.State.ToString
| So, I think that kills your theory on what the problem is. If it is
closed
| before and after the UPDATE, why am I getting this error?
|
| I am getting this error randomly, so the code snippet was just an effort
to
| make it replicatible. I realize in actual good code the UPDATE command
| would be better outside the loop.
|
| Bob Day
| ------------------------------
| | > Hi Bob,
| >
| > It seems that you're having problem with connection pooling. ADO.net
uses
| > connection pooling by default. When you close a connection, the
connection
| > will be put back to the connection pool. When the DataAdapter updates
the
| > database, it will open a connection automatically, if there's no active
| > connection available. When the update is completed, the DataAdapter will
| > set the connection to the initial state.
| >
| > Since you've got this exception, I think you might have opened the
| > connection before your code snippet. So when the update is completed,
the
| > connection is still opening. The DataAdapter might get another
connection
| > next time. After a loop, the connection pool is used up. So the
exception
| > is thrown.
| >
| > You can try to use SqlConnection.State property to monitor the state of
| the
| > connection. However, I recommend you to put the Update call outside the
| > loop. It might hit the performance if you put the Update inside the
loop.
| >
| > If anything is unclear, please feel free to reply to the post.
| >
| > Kevin Yu
| > =======
| > "This posting is provided "AS IS" with no warranties, and confers no
| > rights."
| >
| > --------------------
| > | From: "Bob Day" <[email protected]>
| > | Subject: Thread Pool Problem on Adding/Updating Rows
| > | Date: Tue, 4 Nov 2003 14:22:49 -0500
| > | Lines: 66
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <#[email protected]>
| > | Newsgroups: microsoft.public.dotnet.framework.adonet
| > | NNTP-Posting-Host: dhcp16624016.indy.rr.com 24.166.24.16
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl
| microsoft.public.dotnet.framework.adonet:65389
| > | X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
| > |
| > | Using VS 2003, VB.net SQL MSDE...
| > |
| > | The code below fails every time. Essentially, is it rapidly adding 1
| row
| > | and then updating that row to the datasource and then repeating 200
| times.
| > | It fails on the .UPDATE after about 73 UPDATES with the error message:
| > |
| > | "A first chance exception of type 'System.InvalidOperationException'
| > | occurred in system.data.dll.
| > |
| > |
| > |
| > | Additional Information: Timeout expired. The timeout period elapsed
| > prior
| > | to obtaining a connection from the pool. This may have occurred
because
| > all
| > | pooled connections were in use and max pool size was reached."
| > |
| > |
| > |
| > | If you UPDATE 50 rows at a time, instead of one, you do not get this
| error
| > | message. Having researched help, this error implies I am opening a
| > | connection that I am not closing. However, everything below is
created
| in
| > | component designer, which should be handling this automatically.
| > |
| > | 1) Why is this failing?
| > |
| > | 2) How do I fix it?
| > |
| > |
| > | Code Snippet:
| > | ' DIM Rows_Updated - rows updated by DataAdapter
| > | Dim Rows_Updated As Integer = -1
| > |
| > | Dim count As Integer = -1
| > |
| > | ' instantiate dataset
| > | Dim DS As New DS_Caller_Base
| > |
| > | ' clear DataTable rows
| > | ds.PINS.Rows.Clear()
| > |
| > | ' instantiate a DR to add
| > | Dim DR_To_Add As DS_Caller_Base.PINSRow
| > |
| > | For count = 0 To 200
| > |
| > | ' set DR row = to dataset we are working with
| > | DR_To_Add = DS.PINS.NewPINSRow
| > |
| > | With DR_To_Add
| > | ' set foreign key value (only needed for new row)
| > | .fld_FK_Customer_Table_ID = 6
| > | .fld_IX_PIN = CStr(count)
| > | End With
| > |
| > | ' add new empty row to dataset
| > | DS.PINS.AddPINSRow(DR_To_Add)
| > |
| > | ' cd is an instantiation of Component Designer
| > | Rows_Updated = CD.DA_tblPINS.Update(DS, DS.PINS.TableName)
| > |
| > | Next
| > |
| > | Thanks!
| > |
| > | Bob Day
| > |
| > |
| > |
| >
|
|
|