RE: i2b2 - v1.7.07b - Connection Pool --> problems --> queues

Peter Beninato

unread,

Jul 1, 2016, 3:53:13 PM7/1/16

to i2b2-ins...@googlegroups.com, Matthew Bergman, Cole McCandlish, Erik Benton, Bruce McManus, Timothy Kilgore

Hello,

There are some errors complaining about locks. Here are some glimpses from the JBOSS log (below).

Has anyone seen errors like this?

Not sure who would be locking [select * from octrii2b2data.qt_query_instance where query_instance_id =?];

If this is even what is being locked.

It seems related to the queues, and the Medium Queue:

12:27:52,027 INFO [edu.harvard.i2b2.crc.ejb.ExecRunnable] (Thread-86) Working on in 14119 in MEDIUM_QUEUE

Which is rather funny, since, the information in qt_query_instance, only seems to reference the SMALL_QUEUE.

I’m not sure there is a medium_queue in v1.7.07. I haven’t seen it.

Let me ask, how are the queues created? Is this a JBOSS/Axis2 admin function?

Thanks for your attention.

-Peter

12:11:08,195 INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] (Thread-115) Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]

12:11:08,225 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] (Thread-115) SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]

12:11:08,226 WARN [org.springframework.jdbc.support.SQLErrorCodesFactory] (Thread-115) Error while extracting database product name - falling back to empty error codes: org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is java.sql.SQLException: Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6d5b2704

at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:296) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:320) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:216) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:140) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:103) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678) [spring-2.0.jar:2.5.6.SEC03]

at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:721) [spring-2.0.jar:2.5.6.SEC03]

at edu.harvard.i2b2.crc.dao.setfinder.QueryInstanceSpringDao.getQueryInstanceByInstanceId(QueryInstanceSpringDao.java:128) [CRC-core.jar:]

at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.setQueryInstanceStatus(QueryExecutorDao.java:372) [CRC-core.jar:]

at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.executeSQL(QueryExecutorDao.java:339) [CRC-core.jar:]

at edu.harvard.i2b2.crc.ejb.ExecRunnable.processQueryRequest(ExecRunnable.java:469) [CRC-core.jar:]

at edu.harvard.i2b2.crc.ejb.ExecRunnable.run(ExecRunnable.java:184) [CRC-core.jar:]

at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]

Caused by: java.sql.SQLException: Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6d5b2704

at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.tryLock(BaseWrapperManagedConnection.java:384)

at org.jboss.jca.adapters.jdbc.WrappedConnection.lock(WrappedConnection.java:146)

at org.jboss.jca.adapters.jdbc.WrappedConnection.getMetaData(WrappedConnection.java:813)

at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:285) [spring-2.0.jar:2.5.6.SEC03]

... 16 more

12:11:08,239 ERROR [edu.harvard.i2b2.crc.ejb.ExecRunnable] (Thread-115) Got an excpetion in ExecRunnable (RUN): PreparedStatementCallback; uncategorized SQLException for SQL [select * from octrii2b2data.qt_query_instance where query_instance_id =?]; SQL state [null]; error code [0]; Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6d5b2704; nested exception is java.sql.SQLException: Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6d5b2704

12:11:08,244 ERROR [stderr] (Thread-115) org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select * from octrii2b2data.qt_query_instance where query_instance_id =?]; SQL state [null]; error code [0]; Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6d5b2704; nested exception is java.sql.SQLException: Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6d5b2704

12:27:51,955 ERROR [edu.harvard.i2b2.crc.role.AuthrizationHelper] (Thread-126) Could not find the Roles from the cache for [octri/cohorts/joeuser]

From: i2b2-ins...@googlegroups.com [mailto:i2b2-ins...@googlegroups.com] On Behalf Of Peter Beninato
Sent: Friday, July 01, 2016 9:54 AM
To: i2b2 Install Help
Cc: Matthew Bergman; Cole McCandlish; Erik Benton; Bruce McManus; Timothy Kilgore
Subject: i2b2 - v1.7.07b - Connection Pool --> problems

Hello,

We have been live for about a month on v1.7.07b.

In the past few days, some of our users are experiencing issues where the message is reported on the front-end as:

ERROR: PreparedStatementCallBack; SQL [select * from octri2b2data.qt_query_staus_type where status_type_id = ?; Closed Connection, nested exception is java.sql.SQLRecoverableException; Closed Connection

I am also seeing the error manifested as:

1) No previous queries are populated

2) Go to run a query, and the pop-up where one chooses Count, patient set, encounter set, does not have any checkboxes.

In the prior v1.7.x versions this seemed to be related a fix that was proposed by Ron Buckley with NationWideChildrens involving making some methods variables static in ServiceClient.java.

Has that code change been reviewed as a way to make the connection pool more stable?

Is the connection Pool managed somehow either via JBOSS admin or axis2?

Are there any other possible fixes to this scenario?

From an end-user perspective it makes the system appear unstable/unusable.

Thanks for any assistance.

_________________________________

Peter Beninato - OCTRI DW Developer

Oregon Health & Science University

503-494-9985

beni...@ohsu.edu

_________________________________

The information contained in this EMAIL message is confidential and protected by law. The information is intended only for the person or business identified in the document. If you are not the intended recipient, a sharing or copying the information will result in a violation of the law. If you have received this EMAIL by mistake, please notify the sender of this EMAIL and copy the Office of Information Privacy & Security at oi...@ohsu.edu.

Please remember to cite the NCRR/NCATS-funded CTSA grant (UL1RR024140) in any publications resulting from OCTRI funding, support services or consultations, and to submit your publication to PubMed Central.

--
You received this message because you are subscribed to the Google Groups "i2b2 Install Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to i2b2-install-h...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Dale Johnson

unread,

Aug 26, 2016, 8:43:22 AM8/26/16

to i2b2 Install Help

Is there a solution to this problem? We are suddenly seeing the same error at UAB only over the last couple of days.

Thank you.

Peter Beninato

unread,

Aug 26, 2016, 12:55:49 PM8/26/16

to i2b2-ins...@googlegroups.com

Hello,

I’m not sure of the specifics, but we needed some workaround. Once we hit the “limit” on the connection pool essentially the application died.

I’m not sure if it is in the ds*.xml files, but we implemented a reconnect/keep-alive scenario that essentially gets us out of the dead application mode.

The lock, I believe is related to the queue “hand-over”, actually it seems that when the query moves from one queue to the next, in oracle, you will see two sessions running the same queryID. It seems that the whole query is rerun, and doesn’t really just “move” from on queue to the next. So, I believer there is contention on the underlying qt_* table between the two sessions, leading to error about the lock.

We can provide more of specifics on the reconnect/keep-alive fix, if interested, maybe there is some effort to resolve in future versions.

-Peter

rb...@med.unc.edu

unread,

Sep 12, 2016, 4:33:11 PM9/12/16

to i2b2 Install Help, beni...@ohsu.edu

Hi Peter,

Could you share your fix? We are seeing some similar behavior and have essentially tried to circumvent the entire QUEUE process via the config files but still see odd behavior (including the duplication of queries on Oracle as well as in QT_QUERY_* tables).

- Robert

Peter Beninato

unread,

Sep 13, 2016, 8:03:58 PM9/13/16

to rb...@med.unc.edu, i2b2 Install Help

Hello Robert,

Below is the info from a tracking issue. Basically it seems that for each datasource in the *ds.xml files you want to add these validation tags:

<validate-on-match>false</validate-on-match>

<background-validation>true</background-validation>

<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

</validation>

Hope it works for you too.

-Peter

Matthew Bergman added a comment - 05/Jul/16 4:34 PM

From what I am reading the "Closed Connection” part of the error message indicates that the the connection pool itself is operating correctly, however, all of the connections in the pool are closed. This can happen if the network connection between JBoss and Oracle is disrupted, the connections are killed by a firewall, or Oracle closes the connections for some reason.

By default the connection pool will not get rid of those closed connections in the pool but instead keep returning the closed connections to JBoss to try and use. At which point we see the error. My guess is that this state was recovered by you restarting JBoss?

The most common strategy to deal with this is to add a ‘validator’ to the connection pool in the crc-ds.xml config file. Each time the application requests a connection from the pool JBoss will run a validation on the connection before returning it. If the connection is closed then it will be removed from the pool and a new connection established.

There are two validators we can use:
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
And
<valid-connection-checker-class-name>
org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
</valid-connection-checker-class-name>
The documentation makes it sound like the OracleValidConnectionChecker is lighter weight since it does a PING vs a full query like the first method.

Matthew Bergman added a comment - 05/Jul/16 4:41 PM - edited

References:

Matthew Bergman added a comment - 27/Jul/16 2:27 PM

I have added the following to the -ds.xml config files:

<validation>
<validate-on-match>false</validate-on-match>
<background-validation>true</background-validation>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
</validation>

I was not able to make the valid-connection-checker-class-name or valid-connection-checker attributes work, both threw invalid syntax errors.

Reference:
http://www.ironjacamar.org/doc/userguide/1.2/en-US/html_single/index.html

Reply all

Reply to author

Forward