Is the query result of “MySQL” mybatis inconsistent with the execution result of MySQL?

Time:2021-1-21

1. Broken thoughts

Recently, we encountered a problem in the business. The business is like this: when inserting a new user, we need to verify whether some information of the user is unique, but in the program, the verification result is never unique. Then I took out the SQL statement printed by mybatis and executed it in the database, and found that there was no data.

Then I wonder, the database is the same, the SQL is the same, and the query results have not changed. Why is the execution result 1 in the program and 0 in the database.

Is it because the results of mybatis and database execution are different?

Is the query result of

It was only later that I understood the reason for the disagreement.

I wrote a code similar to the actual business to simulate the above problems.

2. Recurrence

2.1. Table structure

A user table with only four fields is created in MySQL database.

CREATE TABLE `user`  (
  `user_ ID ` varchar (36) not null comment 'user primary key ID',
  `user_ Name ` varchar (55) null default null comment 'account',
  `Password ` varchar (55) null default null comment 'password',
  `Email ` varchar (55) null default null comment 'mailbox',
  PRIMARY KEY (`user_id`) USING BTREE
);

2.2. Project dependence

The example project is a spring boot project. In addition to web dependencies, the POM file also includes MySQL driver, mybatis and Lombok.

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>2.2.6.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.8</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.0</version>
    </dependency>
</dependencies>

2.2. Business

The business process is as follows: before creating a new user, first verify whether the mailbox exists in the database, then execute some other business, then execute the insert method to insert the database, then execute some other business, and finally verify the user_ Whether name already exists.

@Slf4j
@RestController
public class TestController {

    @Resource
    private UserMapper userMapper;

    /**
     *Is the query result of springboot Dao layer inconsistent with the actual execution result of database?
     */
    @GetMapping("test")
    @Transactional(rollbackFor = RuntimeException.class)
    public void transactionalDemo() {
        //Data to insert
        User user = new User();
        user.setUserId("userId");
        user.setUserName("planeswalker");
        user.setPassword("password");
        user.setEmail("[email protected]");
        //Check email
        if (userMapper.countByEmail(user.getEmail())>0) {
            Throw new runtimeException ("insert failed, user_ ID repeated ');
        }
        //Perform insert user action
        userMapper.insert(user);
        //Verify user_ name
        if (userMapper.countByName(user.getUserName())>0) {
            Throw new runtimeException ("insert failed, user_ Name repeated ');
        }
        log.info("do something others...");
    }
}

The code of the usermapper interface class is as follows:

@Repository
public interface UserMapper {

    /**
     *Check whether the email is duplicate
     * @param email
     * @return
     */
    @Select("select count(*) from user where email=#{email}")
    int countByEmail(String email);

    /**
     *Query whether the name is repeated
     * @param userName
     * @return
     */
    @Select("select count(*) from user where user_name=#{userName}")
    int countByName(String userName);
}

I admit that this method may not be particularly good. For example, why does the method of checking duplication have two times, such as user_ ID verifies the rationality of the duplicate method. However, in order to simulate the problems I encountered in the project, this is very similar. In the project, it is verified again after inserting (because the common verification method will query two tables, and the data of one table is inserted before verification).

Maybe many students already know the problem of this business. Just don’t talk about it first, just carry it out.

2.3. Testing

When I access this interface in my browserhttp://127.0.0.1:8080/testAfter that, the console outputs the following contents:

2020-05-27 14:07:09.183 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.m.UserMapper.countByEmail    : ==>  Preparing: select count(*) from user where email=? 
2020-05-27 14:07:09.208 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.m.UserMapper.countByEmail    : ==> Parameters: [email protected](String)
2020-05-27 14:07:09.218 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.m.UserMapper.countByEmail    : <==      Total: 1
2020-05-27 14:07:09.233 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.mapper.UserMapper.insert     : ==>  Preparing: INSERT INTO user ( user_id,user_name,password,email ) VALUES( ?,?,?,? ) 
2020-05-27 14:07:09.234 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.mapper.UserMapper.insert     : ==> Parameters: userId(String), planeswalker(String), password(String), [email protected](String)
2020-05-27 14:07:09.237 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.mapper.UserMapper.insert     : <==    Updates: 1
2020-05-27 14:07:09.237 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.m.UserMapper.countByName     : ==>  Preparing: select count(*) from user where user_name=? 
2020-05-27 14:07:09.237 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.m.UserMapper.countByName     : ==> Parameters: planeswalker(String)
2020-05-27 14:07:09.238 DEBUG 18375 --- [nio-8080-exec-6] c.b.d.m.i.a.m.UserMapper.countByName     : <==      Total: 1
2020-05-27 14:07:09.250 ERROR 18375 --- [nio-8080-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet]    :  Servlet.service () for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is  java.lang.RuntimeException : insert failed, user_ Name] with root cause

java.lang.RuntimeException : insert failed, user_ Name repeat
    at com.biosan.databasehandler.TestController.transactionalDemo(TestController.java:43) ~[classes/:na]
    at com.biosan.databasehandler.TestController$$FastClassBySpringCGLIB$$dadcb476.invoke(<generated>) ~[classes/:na]
    ......

In the second verification method, an error was thrown, indicating that the same user exists in the database_ Name of the data, and then I took out the SQL to the database alone, found no data!

If I don’t believe it, I hit the endpoint on the second verification method. When the program is executed here, its execution result is:

Is the query result of

That is to say, such data does exist at this time!

And at this time I query in the database, unexpectedly also can’t find this data!

This led me to think that it might not be the code or framework, but other issues, such as database transactions.

2.4. Reasons

We know that the spring boot interface is marked with@TransactionalAnnotation is equivalent to opening a transaction.

The default transaction isolation level of MySQL is read committed, that is, after a transaction is committed, its changes will be seen by other transactions. In the same transaction, if the data is inserted first and then queried, if the query conditions are met, the inserted data can be queried.

When my program is finished executing the insert method, it will use the_ Name query, you can query the inserted data, and at this time I directly query the user in the database_ Name, which is equivalent to opening another transaction for query. Because the submitted isolation level is read, the changes made by a transaction will be seen by other transactions after it is submitted, and the business method is not submitted, so the data can not be queried in the database.

This is why I use the same SQL to query in the program and database, but the query results are different.

2.5. Repair

This problem is unreasonable in terms of business. When I query duplicate data, I should have excluded data with the same ID as the data to be inserted, that is, SQL should be:

select count(*) from user where user_name='planeswalker' and user_id!='userId'

At the same time, verifying duplicate business logic should be done before inserting statements

Of course, that’s what we’ll talk about later.

3. Summary

This paper records a small problem about the database query of springboot + mybatis framework. This is actually a problem of database transaction and isolation level. At the same time, it is also a business problem. It should be checked before inserting.

About the database isolation level, here is just a small mention, and I’ll summarize it later.