Generic JDBC - interaction between exhaustion of connection pool and 'number of retries' setting?

[PSAmmirata]

Let’s say I have a Generic JDBC account configured with MaxPoolSize= 10 connections and Checkout Timeout - 10,000 milliseconds. I have a Generic JDBC Execute snap configured with ‘number of retries’ = 5 and ‘retry interval’ = 10 seconds.

When the first JSON document flows into the Generic JDBC Execute snap, it will tries to obtain a connection from the connection pool, but there are already 10 connection in the connection pool and they’re all in use. The Snap waits 10,000 milliseconds for a connection to become free, but no connection comes free during that time.

Does the Snap fail at this time? OR, will the snap wait the 10 second ‘retry interval’ and try to obtain a connection from the connection pool again?

[mbowen]

Hi Paul:

When using the Generic JDBC account, the kind of sql in the Execute snap does matter. If it is a “read” operation (SELECT, DESCRIBE, …) snap will raise a timeout exception (ie, SQLTransientConnectionException) as soon as the checkout timeout has been exceeded. That is, the “retry interval” won’t come in to play here. In your example, snap will wait 10 seconds (connectionTimeout) before failing. It won’t retry.

However, if Execute sql is a “write” operation (ex: UPDATE, INSERT, …) then connection is guarded in a retry block, so snap will wait the “retry interval” too, so will wait for 20 seconds (connectionTimeout:10s + retryInterval:10s) before retrying (up to the configured number of retries). Retries first acquire a connection and then execute the sql.

[mbowen]

I should amend my answer for the “read” operation. There are two spots in the code which acquire a connection. One is in a retry block, the other isn’t. If connection times out outside of the retry block, a timeout exception will be raised and not retried. However, if the connection times out in the retry block, it will be re-attempted per the defined interval and number of retries.

You may ask why the query path gets a connection twice. I’m not sure exactly why, and haven’t traced all of it. However, this code path is for generic database support, so is probably necessary for some backend.

[PSAmmirata]

Thanks @mbowen. So, for a “read” operation that times out on the initial connection attempt it won’t go into the retry block. Correct? If the “read” operation fails for some other reason will it go into the retry block? Or does the “read” operation never use the retry block?

[mbowen]

Hi Paul:

You are correct. If the “read” times out on the initial connection attempt it will raise
an exception and won’t enter the retry block. Otherwise, it will enter the retry block
and any exception raised will be caught and operation retried up to the configured
number of retries.