CODEWITHSUNDEEP
multithreadingoracle-databaselocking
Santu Biswas
Santu Biswas

Reputation: 11

Oracle inactive session holding database lock

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

Upvotes: 0

Views: 1546

Answers (1)

Ori Marko
Ori Marko

Reputation: 58772

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.

Upvotes: 1

Related Questions