Database read/write separation

This article is a small problem found on the basis of data source read and write separation.

directory

  • background
  • Knowledge reserves
  • Phenomenon of the problem
  • The solution
  • The problem summary

background

Mybatis uses HikariCP to connect to MySQL for read/write separation.

Knowledge reserves

In the last article, we achieved read/write separation of the data source and achieved the desired effect. But there is one small flaw. Now, I need to go a little further.

In our dynamic data source configuration, inherited a class AbstractRoutingDataSource, let’s take a look at this what is the role.

package com.fxb.doraemon.human;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.jdbc.datasource.lookup.DataSourceLookup;
import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;


/** * DataSource implementation: calls various target data sources according to a specified key. After that, the data source is typically specified based on the transaction context of some thread. * /
public abstract class AbstractRoutingDataSourceTest extends AbstractDataSource implements InitializingBean {

    /** * target data source */
    @Nullable
    private Map<Object, Object> targetDataSources;

    /** * Default data source */
    @Nullable
    private Object defaultTargetDataSource;

    / * * *?? * /
    private boolean lenientFallback = true;

    /** * * /
    private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();

    @Nullable
    private Map<Object, DataSource> resolvedDataSources;

    /** * data source after processing?? * /
    @Nullable
    private DataSource resolvedDefaultDataSource;


    /** * retrieves the mapping of the target DataSource based on the specified lookup key. The value of the mapping can be an instance of a DataSource or a String. If it is a String, it is parsed using the setDataSourceLookup method. * specified key can be any type, class - this class implements the gm only lookup process, specific key identification by resolveSpecifiedLookupKey (Object) method and * determineCurrentLookupKey parse (). If we define a Map value as a string, we need to override setDataSourceLookup to return the correct data source. * # 2. If our own defines the key of the Map, we need to rewrite resolveSpecifiedLookupKey method and determineCurrentLookupKey (). * /
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        this.targetDataSources = targetDataSources;
    }

    /** * Specify the default data source (if one exists) * If we define the Map value as a string, we need to override setDataSourceLookup to return the correct data source. * this default Datasource is used if the Datasource cannot be found in the targetDataSources based on the specified key. * /
    public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
        this.defaultTargetDataSource = defaultTargetDataSource;
    }

    /** * If the specified Datsource cannot be found, the specified lenientFallback can be notified to determine whether the default data source should be used * true: the default data source will be used * false: fallback only if the key is null. The default data source is used only when the key is null, otherwise IllegalStateException */ is thrown
    public void setLenientFallback(boolean lenientFallback) {
        this.lenientFallback = lenientFallback;
    }

    /** * resolve setTargetDataSource dataSource name is dataSource. The default value is JndiDataSourceLookup. * /
    public void setDataSourceLookup(@Nullable DataSourceLookup dataSourceLookup) {
        this.dataSourceLookup = (dataSourceLookup ! =null ? dataSourceLookup : new JndiDataSourceLookup());
    }


    @Override
    public void afterPropertiesSet(a) {
        if (this.targetDataSources == null) {
            throw new IllegalArgumentException("Property 'targetDataSources' is required");
        }
        this.resolvedDataSources = new HashMap<>(this.targetDataSources.size());
        this.targetDataSources.forEach((key, value) -> {
            Object lookupKey = resolveSpecifiedLookupKey(key);
            DataSource dataSource = resolveSpecifiedDataSource(value);
            this.resolvedDataSources.put(lookupKey, dataSource);
        });
        if (this.defaultTargetDataSource ! =null) {
            this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource); }}/** * resolves the specified key, corresponding to setTargetDataSources#2. * The default is to return */
    protected Object resolveSpecifiedLookupKey(Object lookupKey) {
        return lookupKey;
    }

    /** * resolves the specified DataSource object to a DataSource instance. * If the dataSourceLookup is a string, you can use setDataSourceLookup to set a custom dataSourceLookup. * /
    protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
        if (dataSource instanceof DataSource) {
            return (DataSource) dataSource;
        } else if (dataSource instanceof String) {
            return this.dataSourceLookup.getDataSource((String) dataSource);
        } else {
            throw new IllegalArgumentException(
                    "Illegal data source value - only [javax.sql.DataSource] and String supported: "+ dataSource); }}@Override
    public Connection getConnection(a) throws SQLException {
        return determineTargetDataSource().getConnection();
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return determineTargetDataSource().getConnection(username, password);
    }

    @Override
    @SuppressWarnings("unchecked")
    public <T> T unwrap(Class<T> iface) throws SQLException {
        if (iface.isInstance(this)) {
            return (T) this;
        }
        return determineTargetDataSource().unwrap(iface);
    }

    @Override
    public boolean isWrapperFor(Class
        iface) throws SQLException {
        return (iface.isInstance(this) || determineTargetDataSource().isWrapperFor(iface));
    }

    /** * Retrieves the current target data source. * call determineCurrentLookupKey get key, in targetDataSources lookup, whether or not to be back, use the default data source. * Throw IllegalStateException if the data source cannot be found. * /
    protected DataSource determineTargetDataSource(a) {
        Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
        Object lookupKey = determineCurrentLookupKey();
        DataSource dataSource = this.resolvedDataSources.get(lookupKey);
        if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
            dataSource = this.resolvedDefaultDataSource;
        }
        if (dataSource == null) {
            throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
        }
        return dataSource;
    }

    /** * specifies the current lookup build. * usually achieve the transaction context of the thread class methods binding. * return key needs and targetDatasources match the type of key in this Map, by resolveSpecifiedLookupKey parsing. * /
    @Nullable
    protected abstract Object determineCurrentLookupKey(a);
}
Copy the code

So how does this class work?

When the procedure to obtain a database connection, it invokes the AbstractRoutingDataSource getConnection (), of course, in fact, is through the DataSource. GetConnection () call.

Phenomenon of the problem

The problem is in the code below

protected DataSource determineTargetDataSource(a) {
    Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
    Object lookupKey = determineCurrentLookupKey();
    DataSource dataSource = this.resolvedDataSources.get(lookupKey);
    if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
        dataSource = this.resolvedDefaultDataSource;
    }
    if (dataSource == null) {
        throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
    }
    return dataSource;
}
Copy the code

Now, I’m going to go through it carefully. When I was in the save operation, called determineCurrentLookupKey to obtain lookupKey isn’t (). As a result, the default data source is used when retrieving the data source (which happens to be the Master data source), while we use the Master data source (coincidence). The following figure

Slave1
master
slave1

In fact, this problem can be seen when you look at the log.

master
master 

HikariPool-1 - Starting...
HikariPool-1 - Start completed.
Copy the code

To query users, use slave2 as data source.

HikariPool-2 - Starting...
HikariPool-2 - Start completed.
Copy the code

All right, so much for the phenomenon. What’s the reason?

It turns out that I annotated the Transactional annotation on the Save method in the Service layer in the business code. Nothing else. The same thing happens when I add annotations to classes. This raises the question of whether to annotate query methods with Transactional annotations or not.

Transactional annotation @Transactional(readOnly=true) is recommended for a query method that involves multiple queries to a database.

@2: Exactly why annotating Transactional makes this happen has not been investigated. This is probably because the transaction is opened before the method is entered, but our read-write separation is determined after the proxy method is entered. So, leads to call determineTargetDataSource method in advance, and we are using AOP weave, have identified the source, not call determineTargetDataSource method again.

@3: New question: why did hikaripool-2-starting print hikaripool-2-starting… Hikaripool-2 -start completed.

The solution

Solution 1

Modify the weaving position:

@Pointcut("@annotation(com.fxb.doraemon.human.annotation.Master) " +
    "|| execution(public * com.fxb.doraemon.human.service.. *.save*(..) )" +
    "|| execution(public * com.fxb.doraemon.human.rest.. *.insert*(..) )" +
    "|| execution(public * com.fxb.doraemon.human.rest.. *.update*(..) )" +
    "|| execution(public * com.fxb.doraemon.human.rest.. *.edit*(..) )" +
    "|| execution(public * com.fxb.doraemon.human.rest.. *.delete*(..) )" +
    "|| execution(public * com.fxb.doraemon.human.rest.. *.del*(..) )" +
    "|| execution(public * com.fxb.doraemon.human.rest.. *.remove*(..) )")
public void writePointcut(a) {}Copy the code

The problem with this solution is that when a query operation involves two queries and you want to start a transaction, you can’t name the methods get*, SELECT *. You also need to specify the data source to use with annotations on the method.

The problem summary

Don’t just look at the phenomenon, look at the essence. Problem solved, look at an internal operation mechanism.

Left in the pit

@1: Should the query method add transaction annotations? Annotation @Transactional(readOnly=true) @2 is recommended if a query method involves multiple queries to the database. Exactly why annotating Transactional causes this problem has not been investigated. @3: New question: why did hikaripool-2-starting print hikaripool-2-starting… Hikaripool-2 -start completed.

The last

If you want to know the latest developments, welcome to pay attention to the public account: Fang Jia Xiaobai. Welcome to exchange and study together.