java - Dead Connections Returned to JDBC Connection Pool - Glassfish 3.1.2.2 -

java - Dead Connections Returned to JDBC Connection Pool - Glassfish 3.1.2.2 -

i'm having issue jdbc connection pool on glassfish handing out dead database connections. i'm running glassfish 3.1.2.2 using jconn3 (com.sybase.jdbc3) connect sybase 12.5. our organization has nightly reboot process during time restart sybase server. issue manifests when effort utilize database connection during reboot occurs. here order of operations produce issue:

sybase downwards restart. connection requested pool. db operation fails expected. connection returned pool in closed state. sybase up. connection requested pool. db operation fails due "connection closed" exception. connection returned pool

i've implemented database recovery singleton attempts recover scenario. time database exception occurs create jmx phone call pause queue's , execute flushconnectionpool operation on jdbc connection pool. if database connection still not process sets timer retry in 10 minutes. while process works, it's not without flaws.

i realize there's setting on pool can require validation on database connection prior handing out i've shied away performance reasons. process performs approximately 5 1000000 database transactions day.

my question is, know of way avoid returning dead connection pool in first place?

you've pretty summed options. had problem, midnight db going down. us, turned on connection validation, don't have transaction volume.

glassfish offers custom validation option, class can specified validation.

by default, classes provided glassfish (you'll see them offered options in console) sql statement this:

select 1;

the syntax varies bit between databases, sql server uses '1', whereas postgres, uses 1. intent same.

the net cost db nail every time seek connection, it's really, inexpensive hit. still, it's hit.

but implement own version. check, say, every 10th request, or less frequent. roll random number 1 n (n = 10, 20, 100...), if '1', select (and fail, if fails), otherwise homecoming "true". @ same time, configure if observe error, purge entire pool. tweak have chance of happening when db goes downwards @ night (dunno how busy scheme @ night) vs peak processing.

you "lower odds" during peak processing. "if time between 6am , 6pm odds = 1000 else odds = 100; if (random(odds) == 1) { select... }"

a random alternative removes need maintain thread safe counter.

in end, doesn't matter, want timely note db downwards can inquire gf abort pool.

i can see thrashing bit @ origin db comes up, perchance refreshing pool more once, should harmless.

different ways play that, that's avenue consider.

java java-ee glassfish connection-pooling