Multiple pooled data sources and 2PC - Spring Boot example

Abstract

This example shows how to set up multiple pooled datasources with Camel Spring Boot and have them working with two-phase commit.

Introduction

One of the databases has a non-unique name column, while the other has a unique one. When trying to insert repeated names in both databases at the same time, the second prepare should fail making the transaction rollback for both databases.

Classes and resources

• MyCamelRouter: where the camel routes are defined

• DataSourcesConfig: where the datasources' beans are created

• MyCamelApplication: the Spring Boot main class

• application.properties: configuration for the datasources and others

• schema-ds1.sql and schema-ds2.sql: initializers for the databases

How to run

1. Run the database containers. Notice the commands have extra arguments to enable initialization and two-phase commit.

   podman run --rm --name db1 -e POSTGRES_PASSWORD=password -p 5432:5432 -v ./src/main/resources/schema-ds1.sql:/docker-entrypoint-initdb.d/init.sql:Z docker.io/library/postgres:latest -c max_prepared_transactions=10

   podman run --rm --name db2 -e POSTGRES_PASSWORD=password -p 5433:5432 -v ./src/main/resources/schema-ds2.sql:/docker-entrypoint-initdb.d/init.sql:Z docker.io/library/postgres:latest -c max_prepared_transactions=10

2. Then run the example using

   mvn spring-boot:run

3. Every few seconds you can see in the logs that a new insert is created, but when it tries to insert a non-unique name then the transaction rollbacks and the names are not inserted in any of the databases.

   [read #8 - Delay] info                  : Exchange[ExchangePattern: InOnly, BodyType: String, Body: There are 4 names in the ds2 database.]
   [timer://runOnce] info                  : Exchange[Id: 0B8F2317CCE5D8D-000000000000000D, RouteGroup: null, RouteId: route2, ExchangePattern: InOnly, Properties: {CamelAggregationStrategy={split1=UseOriginalAggregationStrategy}, CamelCorrelationId=0B8F2317CCE5D8D-0000000000000000, CamelSplitComplete=false, CamelSplitIndex=4, CamelSplitSize=6, CamelStreamCacheUnitOfWork=DefaultUnitOfWork, CamelToEndpoint=log://info?showAll=true}, Headers: {}, BodyType: String, Body: Maria]
   [timer://runOnce] route2                : insert into the first database (non-unique)
   [timer://runOnce] route2                : insert into the second database (unique)
   [timer://runOnce] com.arjuna.ats.jta    : ARJUNA016041: prepare on < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff0a057e34:aedb:66cc8122:39, node_name=1, branch_uid=0:ffff0a057e34:aedb:66cc8122:3f, subordinatenodename=null, eis_name=java:comp/env/jdbc/ds2 > (io.agroal.narayana.BaseXAResource@65fecc5) failed with exception XAException.XA_RBINTEGRITY
   ...
   Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "names_name_key"
   ...
   [timer://runOnce] com.arjuna.ats.arjuna : ARJUNA012073: BasicAction.End() - prepare phase of action-id 0:ffff0a057e34:aedb:66cc8122:39 failed.
   [timer://runOnce] com.arjuna.ats.arjuna : ARJUNA012075: Action Aborting

Cleanup

1. Clear Narayana transaction logs:

   rm -rf transaction-logs

2. Stop the running containers

Running the tests

1. Run the tests using

   mvn clean test

Extra details

The name column in the second database is set up with UNIQUE DEFERRABLE INITIALLY DEFERRED. This configuration delays the constraint check to only be evaluated in the commit phase. Without this argument there would be an exception thrown immediately during the INSERT command, causing the transaction to be rolled-back immediately. This is only set up this way to make the example clearer.

Help and contributions

If you hit any problem using Camel or have some feedback, then please let us know.

We also love contributors, so get involved :-)

The Camel riders!