For performance reasons, we have implemented a hybrid EJB/direct-JDBC implementation. Essentially, for queries that produce large result sets, I have implemented direct JDBC calls within our many session facade beans. These JDBC calls are interspersed with some basic entity bean lookups that have produced a significant performance increase.
Example code in a session bean:
------
SiteStatusLocal siteStatusEjb = siteHome.findByName(...)
ConnectionWrapper wrapper = ConnectionWrapper.getInstance();
Connection conn = wrapper.getConnection();
PreparedStatement stmt = wrapper.getPreparedStatement(conn, ....);
stmt.setInt(1, siteStatusEjb.getId());
ResultSet result = stmt.executeQuery()
-----
As the code suggests, I have a connection wrapper that retrieves a JDBC connection from the JBoss pool. The wrapper is then used to retrieve a pre-compiled statement. An entity bean is used to retrieve a status ID for the statement parameter.
My problem is that application deadlocks occasionally occur when accessing the siteStatusEjb.getId() method (or similar EJB calls) under light stress (10-15 concurrent threads).
I am using Commit Option A/Pessimistic Locking.
Can anyone direct me on how to address this issue? Note that direct JDBC is used only for SELECT statements.
Thanks in advance...
