@DS cannot switch data source in transaction

1. Problems

The project is configured with multiple data sources, and the @DS annotation needs to be used to switch the data source, but an error is reported after using the @DS annotation in the transaction. After checking, it is found that the operation of switching the data source cannot be performed within the transaction.

2. Reason

[Reason reference article]

When the transaction is opened, the database connection will be obtained from the [database connection pool] ;
if the inner service uses @DS to switch the data source, it just does another layer of interception, but it does not change
all the database operations of the connection of the entire transaction within this transaction. , all after the transaction connection is established, so there will be a problem that the data source is not switched.
In order for @DS to work, the database connection must be replaced, that is, to change the propagation of the transaction, generate a new transaction, and obtain a new database connection

Reason summary

There is already a layer of transaction outside the service. This transaction has been connected to the first data source. Using the @DS annotation in the service will invalidate it. When using the @DS annotation in the inner layer, a new transaction needs to be opened for processing. Set [@Transactional] (rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW) to resolve

code example

@DS ( "sub" )
     //You cannot use @DS to switch data sources in a transaction, you need to add the following line to open a new transaction to process 
    @Transactional (rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW)
     public  void  saveInfo (List<bookInfo> List)  {
         //Clear data
        bookMapper.deleteBookInfo();
        //Insert in batches 
        int numPerTimes = 2000 ;
         if (List.size() <= numPerTimes) {
            bookMapper.saveBookInfo(List);
        } else {
             //Insert in batches 
            int maxIndex = List.size();
             int maxTimes = maxIndex / numPerTimes;
            maxTimes += (maxIndex % numPerTimes) > 0 ? 1 : 0;
            int currentTimes = 0;
            while (currentTimes < maxTimes) {
                int fromIndex = numPerTimes * currentTimes;
                int toIndex = fromIndex + numPerTimes;
                toIndex = toIndex > maxIndex ? maxIndex : toIndex;
                //list cutting and inserting
                List<bookInfo> subList = List.subList(fromIndex, toIndex);
                bookMapper.saveBookInfo(subList);
                currentTimes++;
            }
        }
    }

Leave a Comment

Your email address will not be published. Required fields are marked *