Distributed transaction with SpringBoot + PostgreSql + mybatis + NarayanaJTA

I tried to execute a distributed transaction with SpringBoot + PostgreSql + mybatis + NarayanaJTA

Supports multiple databases, which is very annoying

Previously I tried to support multiple databases using DynamicAbstractRoutingDataSource, As I confirmed later, rollback did not work well, so I tried another method to support multiple data sources.

If you want something that works first, please refer to the source link at the bottom of the page.

Why not use Atomikos Transaction Manager

Introductory books on Spring Boot introduced distributed transactions using Atomikos, and there were many samples using Atomicos for information on the net. However, since the handling of XID, which is the unique key of each data source, was suspicious when the connection destination DB was variable (remember), this time we will support multiple databases using Narayana JTA.

environment

pom.xml


<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jta-narayana</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
  </dependency>
  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.0</version>
  </dependency>
  <dependency>
    <groupId>com.integralblue</groupId>
    <artifactId>log4jdbc-spring-boot-starter</artifactId>
    <version>1.0.1</version>
  </dependency>
  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
  </dependency>
</dependencies>

Realization goal

--Data can be read by connecting to multiple data sources at the same time. --Distributed transactions can be realized by TwoPahseCommit --Corresponding to the case where the number of data source connections is variable (for example, if you have a database for each branch code in the branch list received in the request)

Let's connect to PostgreSql using Narayana for the time being

The cooperation between Spring Boot and Narayana itself can be realized by registering the Narayana DataSource Bean provided by Spring as a DataSource Bean.

For example, you can connect for distributed transactions using Narayana just by registering with Bean like this.

DatasourceConfigOne.java


package com.example.datasource;

import com.example.common.DataSourceUtil;
import com.example.config.MyDataBaseProperties;
import com.example.constance.DataBaseConst;
import javax.sql.DataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

@Configuration
@EnableConfigurationProperties(MyDataBaseProperties.class)
@MapperScan(sqlSessionFactoryRef = DataBaseConst.DataSourceOneConst.NAME,
    basePackages = DataBaseConst.DataSourceOneConst.MAPPER)
public class DatasourceConfigOne {

  @Autowired
  MyDataBaseProperties properties;

  @Autowired
  MybatisProperties mybatisProperty;

  /**
   * Get dataSource one.
   * 
   * @return
   */
  @Primary
  @Bean
  public DataSource getDataSourceOne() {
    return DataSourceUtil.getDataSource(
        this.properties.getProperty(DataBaseConst.DataSourceOneConst.DATA_SOURCE).getDetail());
  }
}

MyDataBaseProperties gets the data source information by specifying the property name from the information list of the data source described in the yaml file. In DataSourceUtil # getDataSource, NarayanaDataSourceBean is created based on the acquired property.

Also, since Mybatis is used as ORM this time, a method to generate SqlSessionFactoryBean for Mybatis is also added to this class.

DatasourceConfigOne.java


/**
 * Get SqlSessionFactory one.
 * 
 * @return
 */
@Primary
@Bean(name = DataBaseConst.DataSourceOneConst.NAME)
public SqlSessionFactoryBean getSqlSessionFactoryOne() {
  SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
  factory.setConfiguration(this.mybatisProperty.getConfiguration());
  factory.setDataSource(getDataSourceOne());
  return factory;
}

All you have to do is read Mybatis Config and create a session with the data source information. With this, this data source setting is utilized when the DAO corresponding to the MapperScan basePackages declared in the class is DI.

@MapperScan(sqlSessionFactoryRef = DataBaseConst.DataSourceOneConst.NAME,
    basePackages = DataBaseConst.DataSourceOneConst.MAPPER)

If the database to connect to is decided, distributed transactions can be performed without problems even with declarative transactions by increasing this class by the number of databases connected based on this.

Enable all Connection Manager features in Narayana

Distributed transactions are certainly possible using the NarayanaDataSourceBean provided by Spring. However, as you can see from the excerpt source below, only a few features are available.

java:org.springframework.boot.jta.narayana.NarayanaDataSourceBean


@Override
public Connection getConnection() throws SQLException {
  Properties properties = new Properties();
  properties.put(TransactionalDriver.XADataSource, this.xaDataSource);
  return ConnectionManager.create(null, properties);
}

java:com.arjuna.ats.internal.jdbc.ConnectionManager


/*
 * Connections are pooled for the duration of a transaction.
 */
public static synchronized Connection create (String dbUrl, Properties info) throws SQLException
{
    String user = info.getProperty(TransactionalDriver.userName, "");
    String passwd = info.getProperty(TransactionalDriver.password, "");
    String dynamic = info.getProperty(TransactionalDriver.dynamicClass, "");
    String poolConnections = info.getProperty(TransactionalDriver.poolConnections, "true");
    Object xaDataSource = info.get(TransactionalDriver.XADataSource);
    int maxConnections = Integer.valueOf(info.getProperty(TransactionalDriver.maxConnections, "10"));

~~~ Omitted ~~~
}

Since only XADataSource is passed as a property when creating a connection, neither the connection pool usage setting nor the maximum number of connections can be set. Other properties are used only to confirm that the data sources are the same, so there is not much effect, but if you use 10 or more data sources, this should not be the case. Moreover, dynamicClass, poolConnections, and maxConnections are setting items that do not exist in PGXADataSource of PostgreSql.

Therefore, create a class that extends NarayanaDataSourceBean and a class that extends PGXADataSource. Regarding the extension of PGXADataSource, it may not be necessary if you implement it by passing the property value from the outside, but still create it.

MyXaDataSource.java


package com.example.common;

import java.util.Objects;
import org.postgresql.xa.PGXADataSource;

/**
 * Extends PGXADataSource for TransactionalDriver.
 * 
 * @author suimyakunosoko
 *
 */
public class MyXaDataSource extends PGXADataSource {

  /** enable pool connection. */
  private boolean poolConnections = true;

  /** max pool connection counts. */
  private int maxConnections = 10;

  public String getDynamicClass() {
    return this.getClass().getName();
  }

  public boolean getPoolConnections() {
    return this.poolConnections;
  }

  public void setPoolConnections(boolean poolConnections) {
    this.poolConnections = poolConnections;
  }

  public int getMaxConnections() {
    return this.maxConnections;
  }

  public void setMaxConnections(int maxConnections) {
    this.maxConnections = maxConnections;
  }

  @Override
  public boolean equals(Object obj) {
    if (!(obj instanceof MyXaDataSource)) {
      return false;
    }
    MyXaDataSource casted = (MyXaDataSource) obj;
    return Objects.equals(casted.getURL(), this.getURL())
        && Objects.equals(casted.getUser(), this.getUser())
        && Objects.equals(casted.getPassword(), this.getPassword());
  }

}

Add the properties poolConnections and maxConnections that were missing in MyXaDataSource, which is an extension of PGXADataSource. Match the initial value to Connection Manager. Since getDynamicClass is used only for data source check, pass the class name appropriately.

The equals override is also done by using the XADataSource's equal method when the Connection Manager checks for the existence of the data source. If you leave the default, it will be a comparison between hash values, so we will override it. Since the URL of PGXADataSource includes the user name and password, it is not necessary, but I will add it here.

MyNarayanaDataSourceBean.java


package com.example.common;

import com.arjuna.ats.internal.jdbc.ConnectionManager;
import com.arjuna.ats.jdbc.TransactionalDriver;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.springframework.boot.jta.narayana.NarayanaDataSourceBean;

/**
 * Extends NarayanaDataSourceBean for ConnectionManager and enable TransactionalDriverProperties.
 * 
 * @author suimyakunosoko
 *
 */
public class MyNarayanaDataSourceBean extends NarayanaDataSourceBean {

  private final Properties properties;

  /**
   * Wrap NarayanaDataSourceBean for ConnectionManager.
   * 
   * @param myXaDataSource MyXaDataSource
   */
  public MyNarayanaDataSourceBean(MyXaDataSource myXaDataSource) {
    super(myXaDataSource);
    this.properties = new Properties();
    this.properties.put(TransactionalDriver.userName, myXaDataSource.getUser());
    this.properties.put(TransactionalDriver.password, myXaDataSource.getPassword());
    this.properties.put(TransactionalDriver.dynamicClass, myXaDataSource.getDynamicClass());
    this.properties.put(TransactionalDriver.poolConnections,
        String.valueOf(myXaDataSource.getPoolConnections()));
    this.properties.put(TransactionalDriver.XADataSource, myXaDataSource);
    this.properties.put(TransactionalDriver.maxConnections,
        String.valueOf(myXaDataSource.getMaxConnections()));
  }

  @Override
  public Connection getConnection() throws SQLException {
    return ConnectionManager.create(null, this.properties);
  }

  @Override
  public Connection getConnection(String username, String password) throws SQLException {
    return ConnectionManager.create(null, this.properties);
  }

}

Modify to set items that were not set in MyNarayanaDataSourceBean, which is an extension of NarayanaDataSourceBean. MyXaDataSource is specified as an argument, but if the PostgreSql server, MySql server, and Oracle server all support it, I feel that an interface that covers the necessary setting items will be prepared. This time, I don't care as long as I can connect to PostgreSQL.

Place jbossts-properties.xml in the resource so that TwoPahseCommit is enabled

It works as it is and rolls back properly, but Narayana's initial setting is OnePhaseCommit and some strange WARN log flows.

WARN 4812 --- [           main] com.arjuna.ats.common                    : ARJUNA048002: Could not find configuration file, URL was: null

This is the message if you try to read the jbossts-properties.xml file and it is not found. In addition to enabling TwoPahseCommit, I will store this file under the resource.

jbossts-properties.xml


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <!-- (default is YES) -->
    <!-- (twoPhaseCommit is NO) -->
    <entry key="CoordinatorEnvironmentBean.commitOnePhase">NO</entry>
    
    <!-- (Must be unique across all Arjuna instances.) -->
    <!-- (default is 1) -->
    <entry key="CoreEnvironmentBean.nodeIdentifier">1</entry>

</properties>

This allows WARN log suppression and Two Phase Commit.

So far, the static distributed transaction is complete, but what if it is dynamic?

With the implementation so far, distributed transactions for static data sources (such as the main DB of the own system and the DB of the linked system) can be realized. But suppose that a crazy design was done here. Moreover, it cannot go against the design. The content of the design is ** "Yes! Since there are many branches, let's build a server for each branch and have a database!" ** Actually it is impossible ... I wanted to think that it was impossible, but I encountered an event similar to this, so I have no choice but to deal with it.

DI gives up as long as the connection destination is decided in the logic

Since the data source to be used is not decided at the timing when each controller and each service are executed, the DI of Spring is given up. Therefore, it is necessary to realize the following functions absorbed by the framework.

  1. Create a SqlSession when the data source to be used is decided
  2. Generate a Dao instance with connection information injected
  3. Session collection after transaction completion

Implemented 1 and 2 by referring to the official website of Mybatis. 3 is realized by using the function of AOP.

Dynamically create a SqlSession to create a Dao instance

Basically, if you read the official Mybatis document and implement it obediently, there are few points to be careful about. It should work properly if you manage the created Session information for each request (every Thread), do not create the same session many times, and at least pay attention to these two.

This time, create SqlSessionUtil for creating SqlSession and SqlSessionManager for managing SqlSession. Both are used as DI.

SqlSessionUtil.java


package com.example.common;

import com.example.config.MyDataBaseProperties;
import com.example.constance.DataBaseConst;
import java.util.Objects;
import javax.sql.DataSource;
import javax.sql.XADataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class SqlSessionUtil {

  @Autowired
  MyDataBaseProperties properties;

  @Autowired
  MybatisProperties mybatisProperty;

  @Autowired
  SqlSessionManager manager;

  /**
   * create SqlSessionFactory by DataBase name.
   * 
   * @param name DataBase name
   * @return
   */
  public SqlSessionFactory getSqlSessionFactory(String name) {
    return getSqlSessionFactory(name, null);
  }

  /**
   * <p>
   * create SqlSessionFactory by DataBase name.
   * </p>
   * <p>
   * when no DataBase name on yml, create SqlSessionFactory by defBase.
   * </p>
   * 
   * @param name DataBase name
   * @param defBase use when yml dose not contain DataBaseName
   * @return
   */
  public SqlSessionFactory getSqlSessionFactory(String name, String defBase) {
    XADataSource dataSourceprop = this.properties.getProperty(name, defBase).getDetail();
    DataSource dataSource = DataSourceUtil.getDataSource(dataSourceprop);

    TransactionFactory transactionFactory = new JdbcTransactionFactory();
    Environment environment = new Environment("development", transactionFactory, dataSource);
    Configuration configuration = DataSourceUtil.fillNullByDefault(new Configuration(environment),
        this.mybatisProperty.getConfiguration());
    configuration.addMappers(DataBaseConst.DataSourceDefault.MAPPER);
    return new SqlSessionFactoryBuilder().build(configuration);
  }

  /**
   * Get SqlSession by name.
   * 
   * @param name DataBase name
   * @return
   */
  public SqlSession getSqlSession(String name) {
    return getSqlSession(name, null);
  }

  /**
   * <p>
   * create SqlSession by DataBase name.
   * </p>
   * <p>
   * when no DataBase name on yml, create SqlSession by defBase.
   * </p>
   * 
   * @param name DataBase name
   * @param defBase use when yml dose not contain DataBaseName
   * @return
   */
  public SqlSession getSqlSession(String name, String defBase) {
    SqlSession session = this.manager.get(name);
    if (!Objects.isNull(session)) {
      return session;
    }
    session = getSqlSessionFactory(name, defBase).openSession();
    this.manager.put(name, session);

    return session;
  }

}

In addition to the function to create a data source from a configuration file in SqlSession, a function to create connection information from a template when there is no connection information is also added. The SqlSessionManager that looks like a Singletone has a ThreadLocal variable internally, so it is not affected by other threads. There may be a method that passes an instance of SqlSessionManager in consideration of batch update etc.

SqlSessionManager.java


package com.example.common;

import java.util.HashMap;
import java.util.Map;
import java.util.Objects;
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Component;

@Component
public class SqlSessionManager {
  ThreadLocal<Map<String, SqlSession>> sessionMap = new ThreadLocal<>();

  /**
   * put SqlSession.
   * 
   * @param key key
   * @param session SqlSession
   */
  public void put(String key, SqlSession session) {
    init();
    if (Objects.isNull(this.sessionMap.get())) {
      this.sessionMap.set(new HashMap<String, SqlSession>());
    }
    this.sessionMap.get().put(key, session);
  }

  /**
   * get SqlSession by key.
   * 
   * @param key key
   * @return
   */
  public SqlSession get(String key) {
    init();
    return this.sessionMap.get().get(key);

  }

  /**
   * close all session.
   */
  public void close() {
    init();
    this.sessionMap.get().forEach((key, session) -> session.close());
    this.sessionMap.set(null);
  }

  private void init() {
    if (Objects.isNull(this.sessionMap.get())) {
      this.sessionMap.set(new HashMap<String, SqlSession>());
    }
  }

}

SqlSessionManagerd keeps the created SqlSession Map. Instance creation timing! = Thread start timing, so NULL check is performed at the beginning of each method as an NPE countermeasure. It might have been good to create an instance of LocalThread at the time of request acceptance in AOP.

Close the dynamically created SqlSession after the Transaction is completed

It is good to create a SqlSession, but if you close the SqlSession created after the transaction starts before the transaction ends, it will not work properly. Therefore, I will close the Session after the declarative Transaction is completed in AOP.

SessionCloseAspect.java


package com.example.aop;

import com.example.common.SqlSessionManager;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

@Aspect
@Component
@Order(Ordered.LOWEST_PRECEDENCE - 1)
@Transactional
public class SessionCloseAspect {

  @Autowired
  SqlSessionManager manager;

  @After("@within(org.springframework.transaction.annotation.Transactional)")
  public void closeSqlSession() {
    this.manager.close();
  }

}

Order is specified from the one with the largest number. Personally, the priority is high = I felt that it was executed first, so it feels a little strange, but if it is executed in this order, that would be the case.

It is OK if no exception occurs even if the declarative transaction is exited after the operation with the dynamically created SqlSession.

I confirmed normal operation for the time being (problem to be worried about)

No modifier information found for db. Connection will be closed immediately

I'm worried that logs will always come out. It seems that the option to disconnect while synchronizing with other connections when terminating the connection is disabled.

I also want to merge jbossts-properties.xml into the yaml file as much as possible, but it seems to be useless because the WARN log appears.

reference

Narayana Spring Boot example
Narayana official sample. Since there is little information in English, I made a trial and error based on this.

jbossts-properties.xml
When I got Narayana from Maven, there was no XML file for setting, so I borrowed it from here

Source

If you want to see the source because the explanation is difficult to understand, or if you want something that can be used quickly because the entrustment is good, please click here. https://github.com/suimyakunosoko/narayana-spring-boot-mybatis-postgresql

Recommended Posts

Distributed transaction with SpringBoot + PostgreSql + mybatis + NarayanaJTA
Until data acquisition with Spring Boot + MyBatis + PostgreSQL
Supports multi-port with SpringBoot
I wrote a CRUD test with SpringBoot + MyBatis + DBUnit (Part 1)