“This is the 10th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

1. The twitter

Recently, I encountered a problem in a business. The business was like this: When inserting a new user, it was necessary to verify whether some information about the user was unique, but the verification result in the program was never unique. Then I took out the execution SQL statement printed by MyBatis and executed it in the database, but found no data.

Then I wonder, the database is the same ah, the SQL is the same ah, query results are not changed ah, why the execution of the result in the program is 1, but in the database is 0.

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

Only later did I understand the reason for the discrepancy.

I wrote code similar to the real business to simulate the above problem.

2. Repeat problems

2.1. Table structure

MySQL database creates a user table with only 4 fields.

CREATE TABLE 'user' (' user_id 'varchar(36) NOT NULL COMMENT' iD ', 'user_name' varchar(55) NULL DEFAULT NULL COMMENT 'iD ',' password 'varchar(55) NULL DEFAULT NULL COMMENT' id ', 'email' varchar(55) NULL DEFAULT NULL COMMENT 'email ', PRIMARY KEY (' user_id') USING BTREE);Copy the code

2.2. Project dependencies

The example project is a SpringBoot project with a POM file that includes mysql drivers, MyBatis, and Lombok in addition to web dependencies.

<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> < version > 2.2.6. RELEASE < / version > < / dependency > < the dependency > < groupId > mysql < / groupId > < artifactId > mysql connector - Java < / artifactId > < version > 5.1.47 < / version > < / dependency > < the dependency > < the 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> The < version > 1.3.0 < / version > < / dependency > < / dependencies >Copy the code

Business of 2.2.

The business process looks like this: Create a new user, verify that the mailbox exists in the database before creating a new user, then perform some other services, then insert into the database, then perform some other services, and finally verify that the user_name exists.

@Slf4j @RestController public class TestController { @Resource private UserMapper userMapper; /** * Springboot Dao layer query result is inconsistent with the actual database execution result. */ @transactional ("test") @transactional (rollbackFor = runtimeException.class) public void transactionalDemo() {// The data to insert User user = new User(); user.setUserId("userId"); user.setUserName("planeswalker"); user.setPassword("password"); user.setEmail("[email protected]"); If (usermapper.countbyemail (user.getemail ())>0) {throw new RuntimeException(" insert failed, user_id repeat "); } // Perform the insert user operation usermapper.insert (user); // check user_name if (usermapper.countbyName (user.getUsername ())>0) {throw new RuntimeException(" insert failed, user_name repeat "); } log.info("do something others..." ); }}Copy the code

The code for the userMapper interface class is as follows:

@repository public interface UserMapper {@param email @return */ @select (" Select count(*) from UserMapper user where email=#{email}") int countByEmail(String email); @param userName * @return */ @select (" Select count(*) from user where user_name=#{userName}") int countByName(String userName); }Copy the code

I admit that this method may not be particularly good, such as why there are two checks for duplicate methods, such as the reasonableness of the user_id check for duplicate methods. But to simulate the problem I encountered in my project, this is very similar to the one where the insert was validated again (because the common validation method queries two tables and one table was inserted before the validation).

Probably many of you already know what the problem is with this business, so let’s just do it.

2.3. Test

When I am on the browser to access this interface after http://127.0.0.1:8080/test, the console output of the following contents:

The 2020-05-27 14:07:09. 18375-183 the DEBUG [nio - 8080 - exec - 6] C.B.D.M.I.A.M.U serMapper. CountByEmail: = = > Preparing: select count(*) from user where email=? The 2020-05-27 14:07:09. 18375-208 the DEBUG [nio - 8080 - exec - 6] C.B.D.M.I.A.M.U serMapper. CountByEmail: = = > the Parameters: 123 @gmail.com (String) the 2020-05-27 14:07:09. 18375-218 the DEBUG [nio - 8080 - exec - 6] C.B.D.M.I.A.M.U serMapper. CountByEmail: < = = Total: 1 2020-05-27 14:07:09. 18375-233 the DEBUG [nio - 8080 - exec - 6] C.B.D.M.I.A.M apper. 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), 123 @gmail.com (String) the 2020-05-27 14:07:09. 18375-237 the DEBUG [nio - 8080 - exec - 6] C.B.D.M.I.A.M apper. UserMapper. Insert: < = = Updates: 1 2020-05-27 14:07:09. 18375-237 the DEBUG [nio - 8080 - exec - 6] C.B.D.M.I.A.M.U serMapper. 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) the 2020-05-27 14:07:09. 18375-238 the DEBUG [nio - 8080 - exec - 6] C.B.D.M.I.A.M.U serMapper. 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 failure, user_name repeat] with root cause Java lang. RuntimeException: Failed to insert, User_name repeat. Ats com biosan. Databasehandler. TestController. TransactionalDemo (43) TestController. Java: ~ / classes / : na ats com.biosan.databasehandler.TestController$$FastClassBySpringCGLIB$$dadcb476.invoke(<generated>) ~[classes/:na] ......Copy the code

SQL > select * from database where user_name = “user_name”;

Unconvinced, I added an endpoint to the second verification method, and when the program reached this point, its result was:

That means there is such data!

And right now I inquire in the database again, also can’t find this data unexpectedly!

This got me thinking that it might not be the code or the framework, but something else, such as database transactions.

2.4. Why

We know that annotating a SpringBoot interface with the @Transactional annotation opens a transaction.

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

SQL > insert into user_name; SQL > insert into user_name; SQL > insert into user_name; The changes it makes are seen by other transactions, and the business method is not committed, so the data cannot be queried in the database.

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

2.5. Repair

I should have excluded data with the same ID as the data to be inserted. That is, the SQL should have been:

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

Also, validate duplicate business logic before inserting statements…

That, of course, is the next story.

3. Summary

This article documents a minor issue with the SpringBoot+MyBatis framework query database. This is actually a database transaction and isolation level issue, but it is also a business issue that should be checked before insertion.

Database isolation levels are only mentioned here and will be summarized at a later time.

Finally, this article is included in the Personal Speaker Knowledge Base: Back-end technology as I understand it, welcome to visit.