At certain point of time, all java threads which abort the redshift db connections get blocked in the service.
Thread dump:
thread-2" #377 prio=5 os_prio=0 cpu=23073.41ms elapsed=1738215.53s tid=0x00007fd1c413a000 nid=0x5a1f waiting for monitor entry [0x00007fd193dfe000] java.lang.Thread.State: BLOCKED (on object monitor)
at com.amazon.jdbc.common.SStatement.close(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
- waiting to lock <0x00000006086ac800> (a com.amazon.redshift.core.jdbc42.PGJDBC42Statement)
at com.amazon.jdbc.common.SConnection.closeChildStatements(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.jdbc.common.SConnection.closeChildObjects(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.jdbc.common.SConnection.abortInternal(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
- locked <0x0000000607941af8> (a com.amazon.redshift.core.jdbc42.S42NotifiedConnection)
at com.amazon.jdbc.jdbc41.S41Connection.access$000(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.jdbc.jdbc41.S41Connection$1.run(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(java.base@11.0.9.1/ThreadPoolExecutor.java:1128)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(java.base@11.0.9.1/ThreadPoolExecutor.java:628)
at java.lang.Thread.run(java.base@11.0.9.1/Thread.java:829)
These are blocked on the threads which are still running statement on these connections.
thread-366" #23081 daemon prio=5 os_prio=0 cpu=972668.98ms elapsed=1553882.44s tid=0x00007fd1642b3000 nid=0x73ff waiting on condition [0x00007fd1920ac000]
java.lang.Thread.State: TIMED_WAITING (parking)
at jdk.internal.misc.Unsafe.park(java.base@11.0.9.1/Native Method)
- parking to wait for <0x00000006086ae350> (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
at java.util.concurrent.locks.LockSupport.parkNanos(java.base@11.0.9.1/LockSupport.java:234)
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(java.base@11.0.9.1/AbstractQueuedSynchronizer.java:2123)
at java.util.concurrent.ArrayBlockingQueue.poll(java.base@11.0.9.1/ArrayBlockingQueue.java:432)
at com.amazon.jdbc.communications.InboundMessagesPipeline.validateCurrentContainer(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.redshift.client.PGMessagingContext.getReadyForQuery(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.redshift.client.PGMessagingContext.closeOperation(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.redshift.dataengine.PGAbstractQueryExecutor.close(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.jdbc.common.SStatement.replaceQueryExecutor(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.jdbc.common.SStatement.executeNoParams(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
at com.amazon.jdbc.common.SStatement.execute(com.foo.drivers.redshift@1.2.43.1067/Unknown Source)
- locked <0x00000006086ac800> (a com.amazon.redshift.core.jdbc42.PGJDBC42Statement)
Statement executed in these threads : statement.execute(“SHOW SEARCH_PATH”);
Once the java service is restarted, it works fine. But after a few days, this issue comes up again.
Q1 a. Why a close connection thread is blocked even if its child statement is in a queued state?
Q1 b. Is there a way to force close the connection?
Q2 Why are the child statement in the waiting state?