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 commented 4 weeks ago

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 commented 4 weeks ago

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

Caesar2816 commented 4 weeks ago

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 commented 4 weeks ago

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 commented 4 weeks ago

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 commented 4 weeks ago

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 commented 4 weeks ago

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

• © Githubissues.
• Githubissues is a development platform for aggregating issues.

[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