当查询Oracle数据库时,其中一个线程锁定时间超过3秒。这会导致访问Oracle数据库时出现许多阻塞线程,从而导致线程数突然增加和应用程序无响应。我正在使用Tomcat 8.5、Tomcat连接池和Java 8。阻塞线程的跟踪如下:
***"http-nio-80-exec-433" #4207 daemon prio=5 os_prio=0 tid=0x00007fd9d8042000 nid=0x503b runnable [0x00007fd839f04000]
java.lang.Thread.State: RUNNABLE
at java.util.Hashtable.get(Hashtable.java:363)
- locked <0x000000070193caa0> (a java.util.Hashtable)
at java.lang.ConditionalSpecialCasing.lookUpTable(ConditionalSpecialCasing.java:151)
at java.lang.ConditionalSpecialCasing.toUpperCaseEx(ConditionalSpecialCasing.java:123)
at java.lang.String.toUpperCase(String.java:2775)
at java.lang.String.toUpperCase(String.java:2833)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1638)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4401)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4482)
- locked <0x000000074cd7d868> (a oracle.jdbc.driver.T4CConnection)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:6272)
at sun.reflect.GeneratedMethodAccessor400.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:210)
at com.sun.proxy.$Proxy637.executeQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor400.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy637.executeQuery(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)***
这是其中一个被阻塞的线程的跟踪信息。
***"http-nio-80-exec-271" #2777 daemon prio=5 os_prio=0 tid=0x00007fd9c8941800 nid=0x19c3 waiting for monitor entry [0x00007fd8356ca000]
java.lang.Thread.State: BLOCKED (on object monitor)
at java.util.Hashtable.get(Hashtable.java:363)
- waiting to lock <0x000000070193caa0> (a java.util.Hashtable)
at java.lang.ConditionalSpecialCasing.lookUpTable(ConditionalSpecialCasing.java:151)
at java.lang.ConditionalSpecialCasing.toUpperCaseEx(ConditionalSpecialCasing.java:123)
at java.lang.String.toUpperCase(String.java:2775)
at java.lang.String.toUpperCase(String.java:2833)
at oracle.jdbc.driver.CharCommonAccessor.init(CharCommonAccessor.java:164)
at oracle.jdbc.driver.VarcharAccessor.<init>(VarcharAccessor.java:88)
at oracle.jdbc.driver.T4CVarcharAccessor.<init>(T4CVarcharAccessor.java:108)
at oracle.jdbc.driver.T4CTTIdcb.fillupAccessors(T4CTTIdcb.java:431)
at oracle.jdbc.driver.T4CTTIdcb.receiveCommon(T4CTTIdcb.java:209)
at oracle.jdbc.driver.T4CTTIdcb.receive(T4CTTIdcb.java:145)
at oracle.jdbc.driver.T4C8Oall.readDCB(T4C8Oall.java:963)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:447)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:235)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:543)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:239)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1246)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1500)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1717)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4401)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4482)
- locked <0x000000074d203f60> (a oracle.jdbc.driver.T4CConnection)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:6272)
at sun.reflect.GeneratedMethodAccessor400.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:210)
at com.sun.proxy.$Proxy637.executeQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor400.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy637.executeQuery(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)***
我不知道为什么toUpperCase()会锁定某些Integer对象(据我所知),而且每天都会出现多次,持续30秒以上。线程转储分析工具在转储中未发现任何死锁。Tomcat记录了查询被阻塞的线程http-nio-80-exec-433花费了5分钟才完成。
这可能与jvm、内存或其他问题有关吗?例如jdbc驱动程序或连接池配置问题?
Hashtable
可能也是一个HashMap
。看起来它经常被锁定,但时间不长。我不太了解HotSpot或其他JVM选项,不知道是否有更好的配置用于极频繁使用的锁。即使您的SQL查询字符串是大写的,它仍然经常被使用。(您正在使用无参数的executeQuery
,对吧?) - Tom Hawtin - tackline