@DS+@Transactional annotation switch data source failure problem and solution

background

The MySQL database is used in the project and the library distribution strategy is adopted according to the functional module. Therefore, operations of multiple MySQL databases may be involved in a business logic class.

In our project, @DS ("xxx") is used to realize data source switching.

When annotations are added to a class, it means that all methods in this class use this data source;
When annotations are added to a method, it means that the data source used on this method is prioritized over all other configurations;

Problem analysis

Code

rely

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>
<dependency>
    <groupId></groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.1</version>
</dependency>
<dependency>
    <groupId></groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.3.1</version>
</dependency>

yml configuration

spring:
  datasource:
    dynamic:
      primary: master #Set the default data source or data source group, the default value is master      strict: false #Strictly match the data source, default false. If true does not match the specified data source, exception is thrown, false uses the default data source      datasource:
        master:
          url: jdbc:mysql://localhost:3306/demo_01?useSSL=false&amp;autoReconnect=true&amp;characterEncoding=utf8
          username: root
          password: xxx
          driver-class-name:  # 3.2.0 starts to support SPI, this configuration can be omitted        slave:
          url: jdbc:mysql://172.23.168.70:3306/dynamic?useSSL=false&amp;autoReconnect=true&amp;characterEncoding=utf8
          username: root
          password: xxx
          driver-class-name:

Object entity

/**
 * @author itender
 * @date 2023/4/28 11:01
 * @desc
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TableName("t_dynamic_template")
public class DynamicTemplateEntity {

    @TableId(type = )
    private Integer id;

    /**
      * language
      */
    private String language;

    /**
      * Language encoding
      */
    @TableField("language_code")
    private String languageCode;

    /**
      * Creation time
      */
    @TableField("created_time")
    private Date createdTime;

    /**
      * Created by
      */
    @TableField("created_by")
    private Integer createdBy;

    /**
      * Creator name
    */
    @TableField("created_by_name")
    private String createdByName;
}

/**
 * @author itender
 * @date 2023/4/28 10:57
 * @desc
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@TableName("t_user")
public class UserEntity {

    /**
      * Primary key id
      */
    @TableId(type = )
    private Integer id;

    /**
      * User name
      */
    private String username;
}

Controller code

/**
 * @author itender
 * @date 2023/4/28 10:34
 * @desc
 */
@RestController
@RequestMapping("template")
public class DynamicTemplateController {

    private final DynamicTemplateService dynamicTemplateService;

    @Autowired
    public DynamicTemplateController(DynamicTemplateService dynamicTemplateService) {
         = dynamicTemplateService;
    }

    @GetMapping
    public List<DynamicTemplateEntity> list() {
        return ();
    }

    @PostMapping
    public Integer add(@RequestBody DynamicTemplateEntity template) {
        return (template);
    }
}

service

/**
 * @author itender
 * @date 2023/4/28 10:36
 * @desc
 */
public interface DynamicTemplateService {

    /**
      * Query template collection
      *
      * @return
      */
    List&lt;DynamicTemplateEntity&gt; list();

    /**
      * Add a template
      *
      * @param template
      * @return
      */
    Integer add(DynamicTemplateEntity template);
}

mapper

/**
 * @author itender
 * @date 2023/4/28 11:09
 * @desc
 */
@DS("slave")
@Mapper
@Repository
public interface DynamicTemplateMapper extends BaseMapper<DynamicTemplateEntity> {
}

/**
 * @author itender
 * @date 2023/4/28 11:08
 * @desc
 */
@Mapper
@Repository
@DS("master")
public interface UserMapper extends BaseMapper<UserEntity> {
}

Business code

/**
 * @author itender
 * @date 2023/4/28 11:15
 * @desc
 */
@Service
public class DynamicTemplateServiceImpl implements DynamicTemplateService {

    private final DynamicTemplateMapper dynamicTemplateMapper;

    private final UserMapper userMapper;

    private final UserService userService;

    @Autowired
    public DynamicTemplateServiceImpl(DynamicTemplateMapper dynamicTemplateMapper, UserMapper userMapper, UserService userService) {
         = dynamicTemplateMapper;
         = userMapper;
         = userService;
    }

    @Override
    public List<DynamicTemplateEntity> list() {
        List<DynamicTemplateEntity> templateList = (new QueryWrapper<>());
        if ((templateList)) {
            return ();
        }
        List<UserEntity> userList = (new QueryWrapper<>());
        if ((userList)) {
            return templateList;
        }
        Map<Integer, String> userMap = ().collect((UserEntity::getId, UserEntity::getUsername, (key1, key2) -> key1));
        (template -> ((())));
        return templateList;
    }

    @Transactional(rollbackFor = )
    @Override
    public Integer add(DynamicTemplateEntity template) {
        List<UserEntity> userList = (new QueryWrapper<>());
        if ((userList)) {
            ("");
        }
        Map<Integer, String> userMap = ().collect((UserEntity::getId, UserEntity::getUsername, (key1, key2) -> key1));
        ((()));
        (new Date());
        (template);
        return ();
    }
}

test

When the method does not have @Transactional annotation, the data source can be switched normally

[
    {
        "id": 1,
        "language": "Chinese",
        "languageCode": "chinese",
        "createdTime": "2023-04-27T18:56:25.000+00:00",
        "createdBy": 1,
        "createdByName": "itender"
    }
]

The data source can be switched normally.

Switching data source fails when the method has @Transactional annotation

### Error updating database.  Cause: : Table 'demo_01.t_dynamic_template' doesn't exist
### The error may exist in com/itender/threadpool/mapper/ (best guess)
### The error may involve -Inline
### The error occurred while setting parameters
### SQL: INSERT INTO t_dynamic_template  ( language, language_code, created_time, created_by, created_by_name )  VALUES  ( ?, ?, ?, ?, ? )
### Cause: : Table 'demo_01.t_dynamic_template' doesn't exist
; bad SQL grammar []; nested exception is : Table 'demo_01.t_dynamic_template' doesn't exist] with root cause
: Table 'demo_01.t_dynamic_template' doesn't exist

analyze

Spring's @Transactional declarative transaction management is implemented through dynamic proxy.
The @DS annotation is not valid in the mapper interface, service interface, and service methods, and the default main data source is obtained. It is guessed that the @DS annotation cannot be intercepted due to the aop aspect mechanism of spring, and the data source cannot be switched. The correct way is to add it to the service implementation class or specific methods in the implementation class.
Calling the @DS annotation method within the transaction method, the @DS annotation does not take effect either. The reason is that spring can only intercept the @Transactional annotation of the outermost method. At this time, the data source of the transaction is loaded. Even if the @DS annotation method is called within the transaction method, the data source of the outer transaction is obtained, resulting in @DS invalidation.
In the same implementation class, a conventional method with non-DS annotation calls @DS annotation, and the @DS is also invalid. The reason is the same as 2. It is caused by the spring's aop mechanism. If there is indeed such business needs, the DS annotation method can be defined in different classes and called through bean injection, and this problem will not occur.

Solution

Put the query user logic into another separate business logic class

/**
 * @author itender
 * @date 2023/4/28 14:25
 * @desc
 */
public interface UserService {

    /**
      * Query user collection
      *
      * @return
      */
    List&lt;UserEntity&gt; list();
}

/**
 * @author itender
 * @date 2023/4/28 14:27
 * @desc
 */
@Service
public class UserServiceImpl implements UserService {

    private final UserMapper userMapper;

    @Autowired
    public UserServiceImpl(UserMapper userMapper) {
         = userMapper;
    }

    @DS("master")
    @Transactional(rollbackFor = , propagation = Propagation.REQUIRES_NEW)
    @Override
    public List<UserEntity> list() {
        return (new QueryWrapper<>());
    }
}

Modify template business class

@DS("slave")
@Transactional(rollbackFor = , propagation = Propagation.REQUIRES_NEW)
@Override
public Integer add(DynamicTemplateEntity template) {
    // List<UserEntity> userList = (new QueryWrapper<>());
    List<UserEntity> userList = ();
    if ((userList)) {
        ("");
    }
    Map<Integer, String> userMap = ().collect((UserEntity::getId, UserEntity::getUsername, (key1, key2) -> key1));
    ((()));
    (new Date());
    (template);
    return ();
}

The test successfully inserted a piece of data.

Summarize

Spring's @Transactional declarative transaction management is implemented through dynamic proxy.

The @DS annotation is not valid in the mapper interface, service interface, and service methods, and the default main data source is obtained. It is guessed that the @DS annotation cannot be intercepted due to the aop aspect mechanism of spring, and the data source cannot be switched. The correct way is to add it to the service implementation class or specific methods in the implementation class.

Calling the @DS annotation method within the transaction method, the @DS annotation does not take effect either. The reason is that spring can only intercept the @Transactional annotation of the outermost method. At this time, the data source of the transaction is loaded. Even if the @DS annotation method is called within the transaction method, the data source of the outer transaction is obtained, resulting in @DS invalidation.

In the same implementation class, a conventional method with non-DS annotation calls @DS annotation, and the @DS is also invalid. The reason is the same as 2. It is caused by the spring's aop mechanism. If there is indeed such business needs, the DS annotation method can be defined in different classes and called through bean injection, and this problem will not occur.

The above is personal experience. I hope you can give you a reference and I hope you can support me more.