SCM

[#1011184] System.Exception: Timeout while getting a connection from the pool

View Trackers | Bugs | Download .csv | Monitor

Date:
2012-04-05 12:46
Priority:
3
State:
Open
Submitted by:
Jochen Wezel (jwezel)
Assigned to:
Nobody (None)
Npgsql Version:
2.0.11
Category:
Group:
Resolution:
Rejected
Summary:
System.Exception: Timeout while getting a connection from the pool

Detailed description
Background: I'm moving my application from mssql to npgsql v2.0.11.92. After a few minutes or hours of running my application, I get a System.Exception: Timeout while getting a connection from the pool.

I'm using lib version 2.0.11.92 in x86 environment on x64 platform. All connections are closed and disposed in finally block at the latest, but after a while, I receive the error. So, it should be a problem of the Npgsql library.

In my application, there are lots of queries happening in the background every few seconds, but most of the times, they're just running sequentially, not in paralell.

My used connection string is: SERVER=mydbserver;MINPOOLSIZE=3;MaxPoolSize=15;ConnectionLifeTime=7;DATABASE=Test DB;UID=user;PWD=pass

Followup

Message
Date: 2012-04-12 08:49
Sender: Jochen Wezel

The Origin was a statement like this:
Dim MyCmd As New NpgsqlCommand
MyCmd.CommandText = "SELECT * FROM
GeneralProperties WHERE PropertyName = :PropertyName LIMIT
1"
MyCmd.CommandType = CommandType.Text
MyCmd.Connection = CreateDbConnection()
MyCmd.Parameters.Add(":PropertyName",
NpgsqlTypes.NpgsqlDbType.Varchar).Value = key
Dim Result As
DataLayerBase.GeneralPropertyFullDataRow = Nothing
Dim MyReader As IDataReader =
CompuMaster.Data.DataQuery.ExecuteReader(MyCmd,
CompuMaster.Data.DataQuery.Automations.AutoOpenAndCloseAndDi
sposeConnection)
If MyReader.Read Then
Result = New
DataLayerBase.GeneralPropertyFullDataRow(MyReader)
End If
Return Result

The problem could also be solved if the reader - when it is
disposing - should close the used connection if it is
forced to close the connection automatically after reaching
end (value is assigned transparently here in method
CompuMaster.Data.DataQuery.ExecuteReader(MyCmd,
CompuMaster.Data.DataQuery.Automations.AutoOpenAndCloseAndDi
sposeConnection) ).

I don't know if this fits the standard behaviour of .NET or
the other .NET database providers, but might be a working
and senseful solution...
Date: 2012-04-10 10:21
Sender: Jochen Wezel

We found the issue: the datareader is opened with
Connection-Auto-Closing. Unfortunately, there was an issue
that at one point the reader hasn't been read till the end
and it exited after the 1st row. In this case, autoclose
won't happen because it happens only if the reader reads
till its end...

I assume (but haven't proofed yet): at MS SQL, we haven't
faced this issue, maybe because there is an default timeout
in the connection (after 30 seconds?) and/or command, so it
automatically closes in every case and doesn't wait
infinitive till the connection pool limit explodes...

Date: 2012-04-05 20:05
Sender: Francisco Figueiredo jr.


Also, would you mind to give a try with latest beta .93? We made some changes in the connection pool to improve its performance.

I hope it helps.
Date: 2012-04-05 20:04
Sender: Francisco Figueiredo jr.


Are you closing your data readers?

From your connection string, you are setting the max pool to 15 connections. Is it possible that your application is having more than 15 concurrent clients with connections open?

Attached Files:

Changes:

Field Old Value Date By
status_idClosed2012-04-12 08:49jwezel
status_idOpen2012-04-10 10:21jwezel
ResolutionNone2012-04-10 10:21jwezel
close_date2012-04-10 10:212012-04-10 10:21jwezel
Powered By FusionForge