background

Some time ago tracking MyBatis source code, analysis of MyBatis paging query results, found that the incoming IPage parameter results have included the query data, thought that the key of paging query statement is the first input parameter must be IPage, and do not need to return the value of it.

MyBatis pagination plugin (MyBatis pagination plugin)

  1. The return value and first input type of the paging query DAO method must beIPage 。
  2. IPageIn the paging parametersizeConfiguration. Only 500 entries per page, which meanssize > 500The back page parameter is invalidated. How to solve?
  3. What happens to other transactions when the database row lock is locked?

This article will answer the above three questions.

MyBatis paging query usage

The first step is to set up the paging query plug-in.

@Configuration public class MyBatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); }}Copy the code

Second, write the paging query DAO method:

IPage<XX> queryByPage(IPage<XX> iPage, @Param("state") int state);
Copy the code

After the method is executed, the query data is stored in the iPage parameter, and the method return value can be directly obtained. It is important to note that this method must have a return value.

I initially thought that if the query results were stored in parameters, the method definition would be free of return values. Yesterday’s code was written like this:

void queryByPage(IPage<XX> iPage, @Param("state") int state);
Copy the code

As a result, the execution reported an SQL exception:

org.mybatis.spring.MyBatisSystemException: 
nested exception is org.apache.ibatis.exceptions.TooManyResultsException: 
Expected one result (or null) to be returned by selectOne(), 
but found: 500
Copy the code

Wonder for a long time, how does this paging query become a single query? Compare old project code, restore paging query method, normal.

Conclusion: The MyBatisPlus paging method must return IPage, not void.

MyBatisPlus page bar limit

Previous page paging query, each page data is very small, did not find this problem.

This implementation is a batch task, a processing of data to as large as possible. IPage pagination parameter size is set to 1000 and 500 logs are output.

Use the client to connect to the database query, one time can fetch 1000, and MyBatisPlus paging query, this 500 is controlled by who? Can you change it?

The PaginationInterceptor limits the number of entries on a page to 500, if needed:

@Bean
public PaginationInterceptor paginationInterceptor(){
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    paginationInterceptor.setLimit(1000);
    return paginationInterceptor;
}
Copy the code

Database row locks implement distributed locks

Services require that a task be designed as a multi-machine, parallel task, and ensure data consistency. The Quartz framework’s distributed tasks can only be executed by one node, which is not the case with Spring Task.

So the problem becomes the design of distributed locking, which is based on the database row locking mechanism in Quartz cluster solution.

Before I tested distributed locks based on row locks, I thought that if one transaction performed a select for UPDATE operation on the same record, another transaction would raise an exception and fail to acquire the lock.

The test found that after a record was locked, the interaction flow would look something like this:

  1. A transaction execution records A’sselect for updateAfter the transaction is not committed or the connection is disconnected.
  2. B transaction concurrently executes record Aselect for update“, it blocks and enters the wait state.
  3. Transaction A commits or disconnects and transaction B gets the lock. I tested transaction A to commit after 100 seconds, at which point transaction B can also get the lock again, so it will not be abnormal, but will block waiting.

Conclusion: Row locks for database records are exclusive, and other transactions block waiting.

Afterword.

That’s a summary of the last working day of the year.

Get ready for the Lunar New Year!