[davidjencks]

I think it depends on your driver - there have been reports of problems unless you do this:

(transaction starts)
(datasource may be cached)
Connection c = ds.getConnection();
try {
PreparedStatement s = c.prepareStatement("...");
try {
...
ResultSet rs = s.executeQuery();
try {
(look at resultset)
} finally {rs.close();}
}finally {s.close();}
}finally {c.close();}

(end transaction).

The c.close() is always necessary or you will break your connections.

[ronpfeifle]

Ah -- we discovered a solution to our problem. Most
database communication was happening through a
single database connection associated with a transaction.
However, we were managing unique id's for new entity beans using a database table. So, when creating a new
entity, a session bean had to (potentially) open up
a new database connection to get a unique id. If the
original pool had 10 connections, and 10 clients were being served, then the lookup operation deadlocked the
server until timeout, because, well, there weren't any
more connections in the pool.

We solved this by having a *separate* pool for this
additional session bean chatter. This way, if a
connection was active in the first pool servicing a client, then it would be able to get the additional
connection it needed for the lookup operation.

[ronpfeifle]

We're still experiencing another problem -- a container
managed bean sometimes fails on "finder" calls. That is, a session bean is doing something, calls an entity beans finder method, but it fails intermittently.

I assume that they *should* be using the same connection for the operation. However, it appears that they aren't. (ie, the finder trys to use a different connection).

Just as in the last reply, if the maximum number of
clients are being serviced, then there won't be a connection available for the "finder" call. Could this be a problem with a specific driver (it happened for us with a postgresql driver and an oracle driver)?

[davidjencks]

What are the transaction settings on all the beans? I think if you are using the XA fake-wrapper around a non xa jdbc driver and you have some transactional beans making RequiresNew calls to another... there is a chance all the connections will get tied up in the outer transactions and be unavailable for the inner ones. This should not happen with a true xa driver where (as I understand it) you can multiplex transactions over a connection. What were the settings to get the id values (the problem you solved with a new pool)?

[ronpfeifle]

we *are* using the XA wrappers; I'm sure that this
accounts for alot of that behaviour...

[paulnicklin]

We found a lot of these when using Oracle 8i - they all turn out to be code problems. (jboss 2.2.1)

One thing worth noting is that minerva won't release a connection back to the pool if there is a pending transaction, so you might do...

get conn --> [now 1 in use]
do something
close conn --> [still one in use]

get conn --> [2 in use]
do something
commit --> [minerva releases 1 connection]
close conn [minerva releases other]

Also note that (I think) you must also close the result set (to close the DB cursor) and the prepared statement (because it references the connection I think)

Paul