We did it that way, too. But of course that is not the issue at all, so it made no difference. Of course we always dispose of connections when closing them. That is not the problem. Test results were identical whether we used "using" or not.
The problem is sharing a connection pool, and then clearing all pools. Clearing all pools knocks the slats out from every other connection, and suddenly they all lack "an instance of an object"! It makes perfect sense. Stop clearing pools, and the problem stops; but then we have all these lingering connections (yes, I know you can set the length of time they linger ... that's also beside the point). So we decided to back up and punt. We do not clear all pools, and we do not use pools (we connect with pooling turned off). Performance has not suffered at all, unless you call no longer bombing "suffering" (I'll take such suffering any day!). The use of pooling, and in particular the clearing of pools, is potentially dangerous, especially in a multithreading environment, and/or when lotsa folks are sharing the database, working simultaneously. Again, you don't have to think long about this to see why it makes perfect sense that it would be so. I guess the bottom line is: Be very, very careful about clearing pools. -----Original Message----- From: Gareth [mailto:[email protected]] Sent: Saturday, March 20, 2010 3:29 AM To: For users and developers of the Firebird .NET providers Subject: Re: [Firebird-net-provider] connection pooling in amultithreadingenvironment Use a connection within a using clause. using (FbConnection connection = OpenConnection()) { using (FbCommand command = new FbCommand(commandName, connection)) { command.CommandType = commandType; if (internalParameters != null) { command.Parameters.AddRange(internalParameters); } return command.ExecuteNonQuery(); } } This forces the connection to be disposed when finished. If you are using pooling then the connection will either be returned to the pool or disposed properly. You should also use an IDataReader in a using clause. On Sat, Mar 20, 2010 at 12:15 AM, Jiri Cincura <[email protected]> wrote: > On Fri, Mar 19, 2010 at 21:39, Bruce Tiffany <[email protected]> wrote: >> Yes, we can simulate it consistently by using multithreading to simulate >> simultaneous users in a web environment. It bombs every time. > > Are you using one connection on more threads (simultaneously)? > >> 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. > > It depends. If you know you're not going to do work with database, > then clearing pool doesn't hurt. > >> 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. > > Can you provide a test case? And put it into tracker. > >> Apparently the connections of different simultaneous users using a web app >> is quite similar to multithreading. > > Kind of. But the pooling is thread safe, only the connection etc. itself > isn't. > > -- > 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 > -- Gareth ------------------------------------------------------------------------------ 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 Help PBS provide all Americans with opportunities to explore new ideas and new worlds. Support your PBS station today. http://www.pbs.org/support ____________________________________________________________________________________ This email may contain material that is confidential or proprietary to PBS and is intended solely for use by the intended recipient. Any review, reliance or distribution of such material by others, or forwarding of such material without express permission, is strictly prohibited. If you are not the intended recipient, please notify the sender and destroy all copies. ____________________________________________________________________________________ ------------------------------------------------------------------------------ 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
