“MySQL” mybatis query result is inconsistent with MySQL execution result?

Time:2021-10-22

1. Broken thoughts

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

Then I wonder why 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?

Only later did I understand the reason for the inconsistency.

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 4 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 dependency

The sample project is a springboot project. In addition to web dependencies, the POM file also has MySQL drivers, 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: create a new user. Before creating a new user, first verify whether the mailbox already exists in the database, then perform some other business, then execute the insert method to insert into the database, then perform some other business, and finally verify the user_ 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 the 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 mailbox
        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, duplicate user_name");
        }
        log.info("do something others...");
    }
}

The code of usermapper interface class is as follows:

@Repository
public interface UserMapper {

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

    /**
     *Query whether the name is duplicate
     * @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 verifying duplicates occur twice, such as user_ ID check 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 insertion (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. Let’s just talk about it and implement it.

2.3. Test

When I access this interface on the browserhttp://127.0.0.1:8080/testAfter, 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. [. [. [/]. [dispatcherservlet]: servlet. Service() for servlet [dispatcherservlet] in context with path [] thread exception [request processing failed; next exception is java.lang.runtimeexception: insert failed, user_name repeated] with root cause

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

An error is thrown during the second verification method, indicating that the same user exists in the database_ Name’s data, and then I took out the SQL and executed it separately in the database. I found that there was no data!

If I don’t believe in evil, I marked the endpoint on the second verification method. When the program executes here, its execution result is:

In other words, such data does exist at this time!

At this time, I queried in the database and couldn’t find this data!

This makes me start to think that it may not be the code or framework, but other problems, such as database transactions.

2.4. Reasons

We know that it is marked on the interface of springboot@TransactionalAnnotation is equivalent to opening a transaction.

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

After my program executes the insert method, I go to the user interface again_ Name query, you can query the inserted data. 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 not be seen by other transactions until it is committed, and the business method is not committed, so the data cannot 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 originally unreasonable in terms of business. When querying duplicate data, I should exclude data with the same ID as the data to be inserted, that is, the 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 before inserting statements

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

3. Summary

This paper records a small problem about querying the database by the 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 verified before insertion.

About the database isolation level, I just mentioned it a little here. I’ll summarize it later when I’m free.