The app runs under IIS. It may use SQLServer database(s); but the only issue
here is the Firebird database we're using. We were not having problems with
lost connections in SQLServer (where the default is NO POOLING, anyway).
Yes, we can simulate it consistently by using multithreading to simulate
simultaneous users in a web environment. It bombs every time.
Here's a typical error:
==================================================
Object reference not set to an instance of an object.
System.NullReferenceException: Object reference not set to an instance of an
object.
at FirebirdSql.Data.Client.Gds.GdsDatabase.get_Send()
at FirebirdSql.Data.Client.Gds.GdsStatement.Fetch()
at FirebirdSql.Data.FirebirdClient.FbCommand.Fetch()
at FirebirdSql.Data.FirebirdClient.FbDataReader.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable
datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord,
Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader
dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32
startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at [et-cetera -- back up through the stack]
==================================================
I could provide a sample, but I'm not sure it's worth the effort, since I'm
quite sure what the problem was. I'd have to spend a lot of time creating a
sample database and sample tables and data, and writing multithreading code to
spawn off a bunch of threads doing large queries. I can't just turn you loose
in the real thing, with proprietary and confidential data.
The deal was we were having lingering connections. Even if you disconnected,
the system maintained a sort of "open connection" in the pool which could be
reused for the next connection request, thus saving a small amount of overhead.
It was suggested that we "clear all pools" when disconnecting.
The trouble we eventually ran into was that when we cleared all pools, anyone
else who was connected would lose his connection. Not nice. In order to
reproduce this in testing, I had to spawn off a bunch of threads to connect, do
some work and disconnect, each thread clearing all pools. If I just tested
with one connection, I never could get an error, for obvious reasons. Well, it
didn't take long at all to run into chaos. The first guy out would clear the
pool(s), and then everybody else blew up.
If we did not clear the pools, the error would not occur, but then we'd still
be maintaining connection pools and the attendant overhead (back to our
original problem). So we just did away with the whole smash: stop using
pools, and stop clearing them. (Either thing would fix it: Either connect
without pooling - in which case you had your own resources and anyone else who
cleared a connection pool wouldn't touch you - or don't clear pools. But if
you connect with pooling and don't clear them, you leave connections out there
waiting to be reused at the next connection request.) Problem solved. It only
takes about ¼ second extra time to connect without pooling, anyway. That beats
blowing up!
Apparently the connections of different simultaneous users using a web app is
quite similar to multithreading.
BTW, I could also solve the problem by putting a lock on the code, but that was
not a desirable solution, since it forced all users of the DB code to file
through in turn, which could cause a bottleneck in a well used web app. I
demonstrated in multithreading tests that this stopped the error while
continuing to share pools and clear them; but it only did it by forcing only
one process at a time to be connected, and not allowing the next one through
the gate until the first one was out. That defeats the whole purpose, and
prevents simultaneous access by multiple users. My testing showed that it took
all the threads longer to get done this way, vs running simultaneously. So the
better solution was to allow simultaneous access without pooling, each one with
independent resources, which was a little more overhead, but a lot less waiting
in line for database access.
{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}
-----Original Message-----
From: Jiri Cincura [mailto:[email protected]]
Sent: Saturday, February 20, 2010 9:27 AM
To: For users and developers of the Firebird .NET providers
Subject: Re: [Firebird-net-provider] connection pooling in a
multithreadingenvironment
On Fri, Feb 5, 2010 at 03:17, Bruce Tiffany <[email protected]> wrote:
> We encountered an error in FB 2.1.0 and 2.5.1 in some web applications:
Is the application run under IIS?
> Similar errors were occurring too frequently from various Firebird
Does it mean you can simulate it consistently?
> Specifically, the line "this.ExecuteCommand(behavior,true);". Because
> this line is enclosed within a try/catch block, the exception is
> handled. However, this alters program flow to the catch block. Inside
What's the IscException exactly?
> But I was able to finally figure it out. I was able to reproduce the
> error in a multithreaded environment. When I locked the code which
Can you show us the test case? Are you aware of, that the provider
*isn't* thread safe?
> called the DLL which uses Firebird, the error went away. Without the
> lock, the error also did not occur when our SQL DLL (issuing Firebird
> queries) was called serially (sequential calls rather than parallel).
Was for these commands separate connection used? Or very careful
synchronization (if you understand the protocol internals).
> This gave me a hunch as to what the problem was. I tried commenting out
> the "FbConnection.ClearAllPools()" statement, and voila! End of error.
Where you commented it out?
> We had started clearing all pools a few years ago because we were seeing
> persistent connections on our servers, because the .NET Data Provider
> pools connections even after they are closed, so that they can be reused
> on the next call to the database. The pooled connection stays "alive"
> on the server until the application is closed; unfortunately, since
> these were web applications, they were never truly closed. So, when any
That's not true. You can specify the connection lifetime. And also -
assuming IIS - the process is ended to recycle worker proces.
--
Jiri {x2} Cincura (CTO x2develop.com)
http://blog.cincura.net/ | http://www.ID3renamer.com
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Firebird-net-provider mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Firebird-net-provider mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
