JBoss应用Oracle连接超时：C3PO的timeout与idle_test_period配置疑问

Great question—this is a super common pitfall with C3P0 and database failover scenarios like your Oracle node restart issue. Let’s break this down step by step.

Is the statement "idle_test_period should not be greater than c3p0.timeout" correct? #

Yes, this is 100% correct—and your current configuration (timeout=300s, idle_test_period=1800s) is exactly why you’re seeing intermittent timeouts after the Oracle node came back up.

How do these two parameters work together to manage idle connections? #

First, let’s clarify what each parameter actually does (using their raw C3P0 names alongside your Hibernate aliases):

• hibernate.c3p0.timeout → C3P0’s maxIdleTime: This sets the maximum amount of time (in seconds) a connection can sit idle in the pool before it’s permanently removed. In your case, any connection unused for 5 minutes (300s) should be cleared out.
• hibernate.c3p0.idle_test_period → C3P0’s idleConnectionTestPeriod: This sets how often (in seconds) the pool’s maintenance thread runs a test on idle connections to check if they’re still valid. Your current setting means it only checks every 30 minutes (1800s).

The problem with your current setup #

When your Oracle node restarted, all existing connections in the pool became invalid—but your idle_test_period is 6x longer than your timeout. Here’s what happened:

• Connections went idle when the database went down, and started counting down to their 300s timeout.
• But the pool didn’t check these connections for another 1800s. In that window, any connection that hit the 300s timeout was supposed to be removed—but C3P0 relies on its maintenance thread to actually clean these up.
• Until that maintenance thread ran, the pool was still handing out these stale, invalid connections to your application, causing intermittent timeouts.

The correct configuration logic #

For the pool to work reliably during failover, idleConnectionTestPeriod must be less than or equal to maxIdleTime. Here’s why:

• The maintenance thread runs every idleConnectionTestPeriod seconds to check idle connections.
• When it runs, it first checks if a connection’s idle time has exceeded maxIdleTime—if so, it removes the connection immediately.
• For connections still under the timeout, it runs a validity test (like a simple SELECT 1 query). If the test fails (e.g., the database restarted), the connection is removed. If it passes, the connection’s idle timer is reset, and it stays in the pool.

By keeping the test interval shorter than the idle timeout, you ensure:

• Stale connections are detected and removed before they could be handed out to the application.
• Valid idle connections get their timers reset, so they don’t get removed unnecessarily.

Fixing your scenario #

For your setup, I’d recommend:

• Reducing hibernate.c3p0.idle_test_period to a value less than 300s—something like 240s (4 minutes) works well. This ensures the pool checks connections before they hit the idle timeout.
• If you want to be extra cautious, you could also enable testConnectionOnCheckout (though this adds minor overhead), but adjusting the idle test period should resolve the intermittent timeouts on its own.

内容的提问来源于stack exchange，提问作者Saket Saurabh