We are using JBoss 4.2.3GA, EJB 3.0 (SessionBeans, EntityManager and EntityBeans) to an Oracle 10g.
We are having some issues with a concurrency scenario and the db connection pool. In a particular moment a persist or remove or merge or ejb query or native query locks a table or a row, and the other connection requests begings to been blocked, and therefore all connections in the pool are used and blocked until we getting java.sql.SQLException: Closed Connection for new requests.
We are not specifing any transaction isolation level, so we know that we are using Oracle's default READ COMMITTED, but for some reason we don't know, the locks are not been release and new requests are been blocked as well. We don't get any DEADLOCK exception, and we query the gv_$lock table to check the tables and queries, and every time is a different one. This happend no so often, but in a short time frame. If we kill the first lock in the database, the other ones are release. Some other times, we didn't need to kill the lock, it release the connection after several minutes and then the other requests execute normally, causing very ugly effects in our application.
The only thing that could be a clue on this, is maybe that something in our entity model relationship is making Oracle to lock the tables and rows. We have object with one-to-many, many-to-one and many-to-many relationships. But again, with READ COMMITTED should be an issue.
We though implementing the @Version annotation in the Entity model, but once again, it shouldn't be an issue because of the READ COMMITTED isolation level in the DB.
Any thought would be appreciate
Thanks a lot