[ZODB-Dev] Maximum pool size considered harmful

[Martin Kretschmar]

> > but the implementation should be a little bit
> > more thread aware.
> 
> Could you be more specific about what you mean,
> preferably phrased as a modification to what Jim
> described?

We have a WinNT/W2K application, which makes use of
a lot of remote database accesses to a Microsoft SQL
Server. Under load tests and with the help of WinDbg
it could repeatedly be seen, that occasionally access
violations and other nasty things were happing somewhere
inside the ODBC code. The "reason" was a MFC CDatebase
object, which was shared among threads. There had been
a warning of a resource leak, so it was kept and reused.
A critical section makes sure, that only one thread is
working on the CDatebase object at any time. The strange
effects were completely gone, when each thread got back
his own previosly used cached CDatebase object.
  
So for a scheme which e.g. wants to make use of a
stack of usused connections, it would be nice to
have the option of giving back each thread his
previously used connections or creating a new one.
This also could be benefical on SMP machines. In
a sense, we need least one "known broken" flag
which enforces this type of behavior depending on
the type of the accessed database. A second flag
would indicate this as a request but not demand.

> Could you be more specific about what you mean,
> preferably phrased as a modification to what Jim
> described?

- Keep a weak-key dictionary (weak set?) of connections
  so we can do things like compute connection and cache
  statistics.

- Keep a list of unused connections together with the
  thread id of the last owning thread, the type of the
  database and the last time of activity, for reuse.

- When someone calls open(), a thread should always get
  back his previously used connection if it exists. Else
  depending on the type of the database or if the list
  is empty, a new connection is created. Else the
  connection is taken from the end of the list.

- When someone closes a connection, the connection will
  be added to the end of the list. If the list is over
  a maximum size, connections are discarded on a LRU
  scheme from the list.

Martin