Not able to abort redshift connection - having a statement in waiting state

Question

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?

Answer

Hello, based on the details which are shared, the error messages seem to be originating from Java and not on the Redshift server.  To investigate on Redshift end , its recommended to monitor Redshift cluster for any downtime or resource contentions during the issue time-frame and whether that is co-relating with the application threads to be blocked - >https://docs.aws.amazon.com/redshift/latest/mgmt/metrics.html. Another thing to take a look at is whether the Redshift's driver version  is updated to the latest version ->https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html.    The questions posted are more Java application specific and I am afraid I will not be able to answer those as code support falls outside of our scope.

Not able to abort redshift connection - having a statement in waiting state

Relevant content