1

The database i'm integrated is configured as if a connection is idle(not being used for a while), then connection is dropped. Since im using spring batch in persistent configuration, there is always an active database connection on running threads.

One of my spring batch job is dependent to data from external web service which takes long time to execute. Thats why i already lose the database connection when i get the result.

I tried to use taskscheduler to register a heartbeat query(select 1 from dual) before the web request occurs, which executes the queryevery 5 minutes to keep the connection alive but even if the query executes periodically, i guess it executes the query on a seperate connecyion since it runs on another thread.

Does anyone have an alternative suggestion to keep the connection alive while on locked thread?

I use JPA's EntityManager for the haertbeat query

PRowLeR
  • 182
  • 2
  • 12

1 Answers1

1

If you use Spring then you also use HikariCP. The recent JDBC standard defines method isValid() so you do not have to call SQL to check whether Connection is alive.

More over there is one more mechanism you can use. It is called TCP keepalive. If you insert stanza ENABLE=BROKEN into your JDBC url Oracle JDBC drivers will enable TCP Keepalive feature on a TCP connection

jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=myhost))(CONNECT_DATA=(SERVICE_NAME=orcl)))

Then it will be Linux kernel who will be sending keepalive probes over TCP connection even if your thread is blocked.

Beware: The delay for 1st probe and frequency is determined by Linux kernel parameters.

# cat /proc/sys/net/ipv4/tcp_keepalive_time
7200

# cat /proc/sys/net/ipv4/tcp_keepalive_intvl
75

# cat /proc/sys/net/ipv4/tcp_keepalive_probes
9

By default the 1st keep alive probe (TCP window carying 0 bytes) is sent after 2 hours. While Cisco/Juniper usually cut off TCP connection after one hour. So usually you need root privileges to lower values of tcp_keepalive_time to 15 minutes or so.

ibre5041
  • 4,903
  • 1
  • 20
  • 35