How does hikariCP actively disconnect

[Caesar2816]

How does hikariCP set the SQL timeout period to ensure that the global SQL automatically disconnects the connection after 30 seconds of execution, so that the connection is not full because the connection is not released while holding the connection

The following configurations cannot be actively disconnected after testing, but will continue to perform:
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.validation-timeout=30000

[IRus]

@Caesar2816 you need to setup socket timeout property on jdbc driver properties, i.e:

spring.datasource.url=jdbc:mysql://localhost:3306/dbname?socketTimeout=30000
spring.datasource.url=jdbc:postgresql://localhost:5432/dbname?socketTimeout=30000
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=dbname;socketTimeout=30

Postgres

https://jdbc.postgresql.org/documentation/use/

socketTimeout (int) Default 0
The timeout value used for socket read operations. If reading from the server takes longer than this value, the connection is closed. This can be used as both a brute force global query timeout and a method of detecting network problems. The timeout is specified in seconds max(2147484) and a value of zero means that it is disabled.

MariaDB

https://mariadb.com/docs/server/connect/programming-languages/java/connect/

Defines the network socket timeout (SO_TIMEOUT) in milliseconds. When set to 0, there is no socket timeout.

JDBC

Or, you can set it directly on the prepared statement:

statement.setQueryTimeout(30); // Timeout in seconds

[Caesar2816]

Sorry for forgetting to mark the ORACLE database I used
clarify sprintboot 2.1.3.RELEASE HikariCP 2.7.9 ojdbc819.3.0.0 mybatis-spring-boot-starter 2.1.3

The following methods have now been tested
The first:
mybatis.configuration.default-statement-timeout=30
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.validation-timeout=30000
spring.jdbc.template.query-timeout=30
Adding the above three configurations does not work

The second kind: custom datasouce to set the timeout time is not good
Third: rewrite sqlsession implementation class manual set timeout time is not good
Fourth: The interface does not add @transaction()
Fifth: Custom mybatis blocker does not work
Sixth: Add @options() annotation above the interface of the dao layer to add timeout duration is not good
Seventh: xml file tag after the timeout parameter does not work

[IRus]

mybatis.configuration.default-statement-timeout=30
spring.jdbc.template.query-timeout=30

So what do you use, mybatis or jdbc template? Both?

ORACLE

As far as I know, there is no equivalent of socketTimeout in Oracle.
You can still try setting query timeout on prepared statement directly, or make sure mybatis did it for you.

Anyway, HikariCP will not manage cancelation for you, you need to investigate what Oracle can do there. Even if you close the connection from the client, the server will keep it for some time, so you need to adjust SQLNET.EXPIRE_TIME in session, to close the connection (and hopefully cancel running query) sooner.

[IRus]

https://stackoverflow.com/questions/1388749/oracle-connection-query-timeout

[Caesar2816]

I am using mybatis because considering that the underlying is calling JDBC, so adding JDBC configuration to do the test result is not effective
If the SQL execution is not released because the HikariCP connection pool is used, the connection will remain, and the OOM service needs to disconnect from the database at the service level to protect it
In many cases, neither mybatis level nor HikariCP level can actively disconnect
At the database level, you can set a separate timeout period to protect the database

[IRus]

I am using mybatis because considering that the underlying is calling JDBC, so adding JDBC configuration to do the test result is not effective

It's not JDBC configuration, it's JdbcTemplate which is kind of "ORM".

Current statement:

If the SQL execution is not released because the HikariCP connection pool is used, the connection will remain, and the OOM service needs to disconnect from the database at the service level to protect it

Original statement:

How does hikariCP set the SQL timeout period to ensure that the global SQL automatically disconnects the connection after 30 seconds of execution, so that the connection is not full because the connection is not released while holding the connection

So seems you don't need to stop active queries, instead you just want to not have any idle connections to database which is

minimumIdle=0
idleTimeout=30000

[Caesar2816]

I am using mybatis because considering that the underlying is calling JDBC, so adding JDBC configuration to do the test result is not effective

It's not JDBC configuration, it's JdbcTemplate which is kind of "ORM".

Current statement:

If the SQL execution is not released because the HikariCP connection pool is used, the connection will remain, and the OOM service needs to disconnect from the database at the service level to protect it

Original statement:

How does hikariCP set the SQL timeout period to ensure that the global SQL automatically disconnects the connection after 30 seconds of execution, so that the connection is not full because the connection is not released while holding the connection

So seems you don't need to stop active queries, instead you just want to not have any idle connections to database which is

minimumIdle=0
idleTimeout=30000

This treatment did not meet my expectations
My idea is to set the SQL execution time uniformly for the global SQL of the service 30s and separately for some special interfaces, such as interface A 60s and interface B 80s
minimumIdle=0 can handle idle connections, but not SQL execution times
If there are multiple SQL implementations that take too long to fuse, there will still be problems with no available connections or OOM

[IRus]

So for SQL execution time you need to set queryTimeout property on prepared statement, which out of control of hikari, consult your jdbc driver documentation, database documentation and ORM documentation on this topic.

[Caesar2816]

So for SQL execution time you need to set queryTimeout property on prepared statement, which out of control of hikari, consult your jdbc driver documentation, database documentation and ORM documentation on this topic.

Yes, HikariCP does have no control over the timeout duration
I have found the relevant literature of mybatis and JDBC and have done tests, but it is also out of control
Would you like to ask, according to your experience, have any good ideas

[brettwooldridge]

For Oracle you need to configure the oracle.jdbc.ReadTimeout property. See here.