Note: mybatis plus is configured with multiple data sources. After adding transactions, the data source switching fails

1, Scene description

For multiple data sources used in the project, Impl has a method: MethodA.

@Service
@AllArgsConstructor
@DS("tableA")
public class XXXXServiceImpl extends ServiceImpl<XXXXMapper, XXXX> implements XXXXService {

    @Override
    public R<?> MethodA(XXXX xxxx) {
      
    }
}

In this method, two tables are operated simultaneously: tableA and tableB (tableA and tableB come from two data sources).

@Service
@AllArgsConstructor
@DS("tableA")
public class XXXXServiceImpl extends ServiceImpl<XXXXMapper, XXXX> implements XXXXService {
 
  // ........
  
    @Override
    public R<?> MethodA(XXXX xxxx) {
      // Method of operating tableA
      operate1(xxxx);
      // Method of operating tableB
      operate2(xxxx);
    }
}

In consideration of data consistency, the landlord added @ Transactional(rollbackFor = Exception.class) above MethodA

@Service
@AllArgsConstructor
@DS("tableA")
public class XXXXServiceImpl extends ServiceImpl<XXXXMapper, XXXX> implements XXXXService {
 
   // ........
 
    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> MethodA(XXXX xxxx) {
      // Method of operating tableA
      operate1(xxxx);
      // Method of operating tableB
      operate2(xxxx);
    }
}

After running the project, it is found that the data source is not switched when operating the data of tableB.

After removing the annotation, however, the data source is switched normally. However, data consistency cannot be guaranteed. This

2, Relevant questions

1. Why does adding @ Transactional(rollbackFor = Exception.class) annotation above this method cause data source switching failure?

Because the database connection will be obtained from the database connection pool when the transaction is started. Although the internal service uses @ DS to switch data sources, it does not change the connection of the whole transaction. All database operations in a transaction are performed after the transaction connection is established, so there will be a problem that the data source is not switched.

2. How to ensure normal data source switching and normal transaction usage?

If you want the internal call switching @ DS to work, you must replace the database connection, that is, change the transaction propagation mechanism to generate new transactions and obtain new database connections.

It can be solved by adding @ Transactional annotation above the external method and @ Transactional (propagation = propagation. Requirements_new) annotation above the internal method.

3, Solution

@Transactional(propagation = Propagation.REQUIRES_NEW)

It means to create a new transaction. If there is a current transaction, suspend the current transaction.

Note: the method added with this annotation should be handled at the end of the business to ensure that the suspended transaction methods have been successfully executed, and then deal with the method of starting a new transaction.

Because when the internal transaction method is abnormal, the external transaction will be rolled back. However, an external transaction exception does not roll back the internal transaction method.

Take our example, add @ Transactional(rollbackFor = Exception.class) annotation above MethodA, and call the method operate2() of tableB internally; Add @ transactional (propagation = propagation. Requirements_new) annotation above.

@Service
@AllArgsConstructor
@DS("tableB")
public class XXXXServiceImpl extends ServiceImpl<XXXXMapper, XXXX> implements XXXXService {
 
 // ........

  @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public Boolean operate2()(XXXX xxxx) {
        return save(xxxx);
    }
}

If operate2() is defined to be called first, operate1() is called last. If operate1() method is abnormal, operate2() will not roll back.

@Service
@AllArgsConstructor
@DS("tableA")
public class XXXXServiceImpl extends ServiceImpl<XXXXMapper, XXXX> implements XXXXService {
 
   // ........
 
    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> MethodA(XXXX xxxx) {
    
      // Method of operating tableB
      operate2(xxxx);
      // Method of operating tableA
      operate1(xxxx);
    }
}

If operate1() is defined as the first call and operate2() is defined as the second call. If there is a method call exception, it will be rolled back.

@Service
@AllArgsConstructor
@DS("tableA")
public class XXXXServiceImpl extends ServiceImpl<XXXXMapper, XXXX> implements XXXXService {
 
   // ........
 
    @Override
    @Transactional(rollbackFor = Exception.class)
    public R<?> MethodA(XXXX xxxx) {
    
      // Method of operating tableA
      operate1(xxxx);
      // Method of operating tableB
      operate2(xxxx);
    }
}

Therefore, it should be noted that method calls configured with @ transactional (propagation = propagation. Requirements_new) annotation should be processed at the end of the business.