JDBC connection pool compatible with App Engine

Note: I know about this thread but it is quite old and moreover, the solution did not work for me.

I am using App Engine along with Cloud SQL and I would like to share a pool of open connections between all of the application's current users. I have tried several connection pool implementations and they all work perfectly with the local development server, however, when deployed to the cloud, they fail. I suppose that the reason is App Engine's restricted "sandbox" environment. Does anyone know about JDBC connection pool working on App Engine?

Apache Commons DBCP

...
Caused by: java.lang.NoClassDefFoundError: Could not initialize class org.apache.commons.dbcp2.PoolableConnection
at com.google.appengine.runtime.Request.process-a49d46300800d0ca(Request.java)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:254)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2162)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2148)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:1903)
at org.apache.commons.dbcp2.BasicDataSource$PaGetConnection.run(BasicDataSource.java:2267)
at org.apache.commons.dbcp2.BasicDataSource$PaGetConnection.run(BasicDataSource.java:2263)
at java.security.AccessController.doPrivileged(AccessController.java:63)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1404)
...

Tomcat JDBC Connection Pool

...
Caused by: java.lang.SecurityException: Unable to get members for class org.apache.tomcat.jdbc.pool.DataSource

...

Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
... 45 more
Caused by: java.lang.NoClassDefFoundError: javax/management/MalformedObjectNameException
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2517)
... 45 more
Caused by: java.lang.ClassNotFoundException: javax.management.MalformedObjectNameException
... 45 more

HikariCP

...
java.security.AccessControlException: access denied ("java.lang.RuntimePermission" "modifyThreadGroup")
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:375)
at java.security.AccessController.checkPermission(AccessController.java:565)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at java.lang.ThreadGroup.checkAccess(ThreadGroup.java:315)
at java.lang.Thread.init(Thread.java:378)
at java.lang.Thread.<init>(Thread.java:527)
at com.zaxxer.hikari.util.DefaultThreadFactory.newThread(DefaultThreadFactory.java:32)
at java.util.concurrent.ThreadPoolExecutor$Worker.<init>(ThreadPoolExecutor.java:591)
at java.util.concurrent.ThreadPoolExecutor.addWorker(ThreadPoolExecutor.java:922)
at java.util.concurrent.ThreadPoolExecutor.ensurePrestart(ThreadPoolExecutor.java:1591)
at java.util.concurrent.ScheduledThreadPoolExecutor.delayedExecute(ScheduledThreadPoolExecutor.java:305)
at java.util.concurrent.ScheduledThreadPoolExecutor.scheduleAtFixedRate(ScheduledThreadPoolExecutor.java:542)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:161)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:114)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:102)
...

Vibur DBCP

...
Caused by: java.security.AccessControlException: access denied ("java.lang.RuntimePermission" "modifyThreadGroup")
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:375)
at java.security.AccessController.checkPermission(AccessController.java:565)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at java.lang.ThreadGroup.checkAccess(ThreadGroup.java:315)
at java.lang.Thread.init(Thread.java:378)
at java.lang.Thread.<init>(Thread.java:448)
at org.vibur.objectpool.reducer.SamplingPoolReducer.<init>(SamplingPoolReducer.java:78)
at org.vibur.dbcp.pool.PoolOperations$PoolReducer.<init>(PoolOperations.java:88)
at org.vibur.dbcp.pool.PoolOperations$PoolReducer.<init>(PoolOperations.java:86)
at org.vibur.dbcp.pool.PoolOperations.<init>(PoolOperations.java:79)
at org.vibur.dbcp.ViburDBCPDataSource.start(ViburDBCPDataSource.java:197)
....

c3p0

...
Caused by: java.security.AccessControlException: access denied ("java.lang.RuntimePermission" "modifyThreadGroup")
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:375)
at java.security.AccessController.checkPermission(AccessController.java:565)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at java.lang.ThreadGroup.checkAccess(ThreadGroup.java:315)
at java.lang.Thread.init(Thread.java:378)
at java.lang.Thread.<init>(Thread.java:487)
...

回答1:

I had to use Tomcat DBCP 1.4 (older version), because of the GAE frontend not allowing threads to live outside of request scope. Here's an example project: https://github.com/kennberg/appengine-java-connection-pool

Note that the connection pool is necessary once you have too many requests, because there is a limit on number of pending connections per instance. Re-using connections helps stay under the limit.

回答2:

You probably don't need connection pooling at all:

https://cloud.google.com/sql/faq#connections

... if the time to create a new connection is about the same as testing if an existing connection is alive and reusing it, then we recommend that you create a new connection to service each HTTP request, and reuse it for the duration of the request. In particular, the latter case may apply when you connect from Google App Engine to Google Cloud SQL.

回答3:

You may be getting the AccessControlException because your application is set to "Automatic Scaling".

It used to be that a GAE app could be a "backend" or a "frontend" and only backends could use background threads. Now with backends deprecated and replaced with modules, the use of background threads is tied to the scaling type of the app.

https://cloud.google.com/appengine/docs/java/modules/#Java_Background_threads