How to Implement Custom ConnectionPool for Liferay DXP OSGi Module

Challange

One of our own OSGi modules covered in earlier posts in this series, writes a trace row per request. On a rolling window that table holds somewhere between 3 and 18 million rows.

The module is by design, the most active database writer on the platform, sitting in the main Liferay schema and sharing a 100-connection pool with everything a public-facing portal does. Couple things followed from that:

• Under load the module could take the portal’s connections with it, and the failure signature was the one you would expect

java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not
available, request timed out after 30000ms.

• The cleanup: pruning a rolling window of millions of trace rows is a bulk DELETE against the same instance everything else is using, and the portal felt it every time the job ran.
• Night backup

Caused by: java.sql.SQLException: ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
   at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:526) ~[ojdbc8-19.20.0.0.jar:19.20.0.0.0

Our portal’s schema shares Oracle instance with 50 other applications. What we observed in the DXP logs was that during the backup window, connections through the JDBC driver were refused, with Oracle reporting that the database was available only to users holding the RESTRICTED SESSION privilege. The instance in restricted mode is up, but closed to everyone except SYSDBA. For 15 to 20 minutes, every night, all schemas on that instance were locked out, not just ours. And when access came back, DXP had a cache full of stale entries and a refresh to do, which is its own second-order problem.

The DBA’s summary was blunter than anything I would have written: the Liferay schema influences the availability and the backup of the entire instance. Our trace table was culprit, which set the length of a nightly outage for forty-nine other applications.

Solution: Introduction of new dataSource

Liferay multiple datasource configuration and usage is poorly documented. It is simpler than it looks. Despite the data-source and session-factory attributes exist inservice.xml and the Service Builder DTD, Service Builder only consumes them on the dependency-injector="spring" path. With dependency-injector="ds" the generated persistence hardcodes an OSGi target filter on the bundle symbolic name, and ModelImpl.DATA_SOURCE = "liferayDataSource" seems as not used (I have to investigate it deeper).

The data source resolution happens per OSGi bundle, at bundle start. Each bundle carrying Liferay-Service: true, LiferayServiceExtender calls:

DataSourceUtil.getDataSource(moduleClassLoader)

and that method is, in full, roughly:

for (DataSourceProvider provider :
        ServiceLoader.load(DataSourceProvider.class, classLoader)) {

    return provider.getDataSource();   // first provider on the module CL wins
}

return InfrastructureUtil.getDataSource();   // otherwise the portal pool    

A plain JDK ServiceLoader lookup against the module's own classloader. The extender then registers the resulting DataSource, a TransactionExecutor and a SessionFactory, all keyed by origin.bundle.symbolic.name, and hands the same data source to SchemaCreatorImpl.

So the switch is a META-INF/services file plus a class. No buildService rerun, no regenerated code.

The code

Two pieces. First, a shared resolver in a plain exported-package bundle, so every future utility module reuses it:

public class UtilityPoolDataSourceProvider implements DataSourceProvider {
    public static final String JNDI_NAME = "jndi.name";
    public static final String PROPS_PREFIX = "jdbc.utilityPool.";
    @Override
    public DataSource getDataSource() {
        return _dataSourceDCLSingleton.getSingleton(
            () -> resolveDataSource(
                PropsUtil.getProperties(PROPS_PREFIX, true),
                UtilityPoolDataSourceProvider::_initDataSource,
                InfrastructureUtil::getDataSource));
    }
    protected static DataSource resolveDataSource(
        Properties properties, Function<Properties, DataSource> dataSourceFactory,
        Supplier<DataSource> fallbackDataSourceSupplier) {
        // Absent configuration -> portal pool.
        if (properties.isEmpty()) {
            return fallbackDataSourceSupplier.get();
        }
        // Present but wrong -> fail loudly at bundle start.
        if (Validator.isNull(properties.getProperty(JNDI_NAME))) {
            throw new IllegalStateException(
                PROPS_PREFIX + JNDI_NAME + " is required.");
        }
        return dataSourceFactory.apply(properties);
    }
    private static final DCLSingleton<DataSource> _dataSourceDCLSingleton =
        new DCLSingleton<>();
}

The fallback triggers on absent configuration, never on a caught JNDI failure. A developer laptop with no utility pool keeps working, and a production node with a misconfigured one refuses to start instead of quietly writing to the wrong schema.

Please note, in the consuming module you need a data source subclass and a services file:

// ./internal/jdbc/RequestLimiterDataSourceProvider.java
public class RequestLimiterDataSourceProvider
    extends UtilityPoolDataSourceProvider {
}

You have to create file com.liferay.portal.kernel.dao.jdbc.DataSourceProvider at location /src/main/resources/META-INF/services/ and set its content to

# service-module/src/main/resources/META-INF/services/
#     com.liferay.portal.kernel.dao.jdbc.DataSourceProvider

hr.bp.tools.requestlimiter.internal.jdbc.RequestLimiterDataSourceProvider

// build.gradle
compileOnly project(":modules:tools:datasource-utils")

That empty subclass is load-bearing. bnd computes Import-Package from bytecode references and never reads META-INF/services. Name the shared class directly in the services file and bnd sees no dependency, so the OSGi wire is never generated and the module fails to resolve at runtime. The subclass gives bnd a real bytecode reference. Alternatively you hand-maintain Import-Package: hr.bp.tools.datasource,* in every consumer's bnd.bnd; the one-line class is cheaper.

Get Igor Bešlić’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Then the pool itself, as a Tomcat JNDI resource next to the existing one:

<!-- tomcat/conf/catalina/localhost/ROOT.xml -->
<Resource
    auth="Container"
    dataSource.password="${UTILITY_DATASOURCE_PASSWORD}"
    dataSource.user="${UTILITY_DATASOURCE_USER}"
    driverClassName="oracle.jdbc.OracleDriver"
    factory="com.zaxxer.hikari.HikariJNDIFactory"
    jdbcUrl="${UTILITY_DATASOURCE_URL}"
    type="javax.sql.DataSource"
    maximumPoolSize="20"
    minimumIdle="5"
/>

# portal-ext.properties, next to jdbc.default.jndi.name=jdbc/LiferayPool
jdbc.utilityPool.jndi.name=jdbc/UtilityPool

Two connection pool sizing notes: we started at 20/5 against the portal pool’s configuration of 100. For now only one module uses it.

Why the pool must be JNDI

The shared pool must be a container JNDI resource. Configuring it with url / username / password in portal-ext.properties compiles, deploys, and works — until the day you redeploy a consumer.

When a module’s bundle stops, the extender does this:

if (InfrastructureUtil.getDataSource() != _dataSource) {
    DataSourceFactoryUtil.destroyDataSource(_dataSource);
}

and destroyDataSource closes anything Closeable. A Liferay-managed HikariCP pool is Closeable. Share it across three modules, redeploy one of them, and the other two lose the pool.

The four things that you must count on when switching existing module to the new tablespace

1. Liferay will not create your tables. SchemaCreatorImpl runs tables.sql / indexes.sql only when the release framework sees the module as new. If you are migrating an existing module, its Release_ table row stays in the main schema, so Liferay skips schema creation and the module fails on its first query. The tables must be pre-created by a DBA — take the DDL from the live tables with DBMS_METADATA.GET_DDL. DO not hand-translate Liferay's portable tables.sql.

A brand new module that adopts the utility pool from day one has no prior Release_ row anywhere, gets its tables created in the utility schema automatically, and needs none of this. Migration is the expensive case.

That manual DDL step is also your one good opportunity to do things tables.sql cannot express — interval partitioning on the high-volume table, for instance, so you can prune by DROP PARTITION instead of bulk DELETE.

2. Some things stay behind. The Release_ row, and Counter rows for primary key generation. Counter allocation is batched (counter.increment, default 100), so that is roughly one main-schema write per 100 inserts — and it is why primary keys stay unique across the cutover with no sequence work at all.

3. You lose the shared transaction. The extender registers a separate TransactionExecutor bound to your pool. Your module's writes no longer participate in the portal transaction, so a rolled-back portal transaction will not roll back your trace row. For a diagnostic table that is fine and arguably what you want. For anything else it is a correctness bug waiting to happen — which is why it is item 4 on the checklist above.

4. Cutover is a full-cluster stop. If the module holds any live shared state a rolling restart would put some nodes on the old schema and some on the new one at the same time. Stop the whole cluster, change every node, start them together.

Deploy order matters for the same reason: the consuming module now imports hr.bp.tools.datasource, so if the shared bundle is not deployed first the consumer sits at Resolved with an unsatisfied constraint and every component in it — in our case all the flood-protection filters — goes dark, with nothing louder in the log than one unresolved-constraint line. Check lb -s in Gogo for Active on both before you touch the config.

Verifying it, and getting back

Four checks, in order:

g! lb -s | grep -i "datasource\|yourmodule"      # both Active
g! services | grep DataSource                     # origin.bundle.symbolic.name=<your BSN>

Then generate traffic and confirm row growth in the utility schema and no growth in the old table. Also confirm the "not configured, using the Liferay data source" line appears in a local bundle's startup log and does not appear on a production node.

Rollback is the nicest property of the whole design: remove jdbc.utilityPool.jndi.name from portal-ext.properties and restart the cluster. The provider falls back to the portal pool and the module writes to the original tables, which are still there until you drop them after the soak period. No code redeploy, no database change.

Two caveats on that. Leave the shared bundle installed — removing it, rather than just the property, leaves the consumer unresolved, which is the opposite of what a rollback is for. And the rollback restart is a full-cluster stop too, for the same state-splitting reason as the cutover.

Takeaway

A second datasource in Liferay DXP costs about forty lines of Java and one JNDI resource. The code is the easy part; the real cost is the schema boundary and the transaction boundary you just introduced, and a migration path that needs a DBA and a cluster-wide maintenance window.

This design is a good fit for one category of module: the noisy, non-critical, self-contained one that would otherwise drag a shared resource down with it. For us it paid for itself in the backup window alone. If your module is not in that category, you do not want this; you want to fix the module. BTW this way you may relate to multiple DBMS from one Liferay DXP instance.