By using AWS re:Post, you agree to the Terms of Use

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

0

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?

asked 2 months ago36 views
1 Answers
0

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.

SUPPORT ENGINEER
answered 2 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions