0

In our application Thread dump I can see the below runnable thread while in Oracle db DBA can find that session is inactive which is holding lock. In which scenario the oracle session gets inactive while locking table? This is causing blocking in db.

ajp-nio-8009-exec-37" prio=5 tid=0x175 nid=0xaf RUNNABLE (JNI Native Code) - stats: cpu=122796 blk=-1 wait=-1 java.lang.Thread.State: RUNNABLE at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) at java.net.SocketInputStream.read(SocketInputStream.java:171) at java.net.SocketInputStream.read(SocketInputStream.java:141) at oracle.net.ns.Packet.receive(Packet.java:311) at oracle.net.ns.DataPacket.receive(DataPacket.java:105) at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:305) at oracle.net.ns.NetInputStream.read(NetInputStream.java:249) at oracle.net.ns.NetInputStream.read(NetInputStream.java:171) at oracle.net.ns.NetInputStream.read(NetInputStream.java:89) at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:123) at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:79) at oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1(T4CMAREngineStream.java:429) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:397) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875) - locked oracle.jdbc.driver.T4CConnection@2ff33c0b

James Z
  • 12,209
  • 10
  • 24
  • 44
Santu Biswas
  • 11
  • 2

1 Answers1

1

If you use oracle you must enforce a max lifetime to its connections so that it won't return to connection pool and create a leak as you observed

by default Oracle does not enforce a max lifetime for connections

If you use HikariCP set maxLifetime properties:

maxLifetime This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit.

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • We are using hybris server and default connection pool. But I could not understand the above answers. Could you please explain how from runnable thread sql session gets inactive – Santu Biswas Nov 06 '18 at 09:31
  • @SantuBiswas You can submit query that takes long time and don't wait for it to end with timeout exception – Ori Marko Nov 06 '18 at 09:33
  • here the problem is we are not getting timeout or time out could not be set in Oracle db end as well. The users exit from browsers when they face such issues. Still I have doubt why the transaction is not committed and the session locks table without doing anything. – Santu Biswas Nov 06 '18 at 10:04
  • @SantuBiswas then investigate the leak in your code as unclosed resource – Ori Marko Nov 06 '18 at 10:05
  • if we use appache coomon pools what property we need to use in case of maxlifetime. – Santu Biswas Nov 06 '18 at 10:54
  • @SantuBiswas Try `maxConnLifetimeMillis` , see https://commons.apache.org/proper/commons-dbcp/configuration.html – Ori Marko Nov 06 '18 at 10:55