Spring Boot+SQL/JPA combat pessimistic lock and optimistic lock

[Reproduced please indicate the source] :Juejin. Cn/post / 684490…

The business reduction

First environment is: Spring Boot 2.1.0 + data-jPA + mysql + Lombok

Database design

For a blog system with comments, there are usually two tables: 1. Article table 2. Comments on the table. In addition to saving some article information, the article table also has a field to save the number of comments. We designed a minimal table structure to restore the business scenario.

The article article table

field	type	note
id	INT	The primary key ID is automatically increased
title	VARCHAR	The article title
comment_count	INT	The number of comments on the article

The comment comments table

field	type	note
id	INT	The primary key ID is automatically increased
article_id	INT	The article ID of the comment
content	VARCHAR	Comment on the content

When a user comments, 1. Get the article according to the article ID 2. Insert a comment record 3. The number of comments on this article is increased and saved

Code implementation

Start by introducing corresponding dependencies in Maven

<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> The < version > 2.1.0. RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>
Copy the code

Then write the entity class corresponding to the database

@Data
@Entity
public class Article {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    private Long commentCount;
}
Copy the code

@Data
@Entity
public class Comment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Long articleId;

    private String content;
}
Copy the code

We then create a Repository corresponding to these two entity classes. Since spring-JPA-Data’s CrudRepository already implements the most common CRUD operations, our Repository simply inherits the CrudRepository interface and does nothing else.

public interface ArticleRepository extends CrudRepository<Article, Long> {
}
Copy the code

public interface CommentRepository extends CrudRepository<Comment, Long> {
}
Copy the code

Then we will briefly implement the Controller interface and Service implementation class.

@Slf4j
@RestController
public class CommentController {

    @Autowired
    private CommentService commentService;

    @PostMapping("comment")
    public String comment(Long articleId, String content) {
        try {
            commentService.postComment(articleId, content);
        } catch (Exception e) {
            log.error("{}", e);
            return "error: " + e.getMessage();
        }
        return "success"; }}Copy the code

@Slf4j
@Service
public class CommentService {
    @Autowired
    private ArticleRepository articleRepository;

    @Autowired
    private CommentRepository commentRepository;

    public void postComment(Long articleId, String content) {
        Optional<Article> articleOptional = articleRepository.findById(articleId);
        if(! articleOptional.isPresent()) { throw new RuntimeException("There is no corresponding article."); } Article article = articleOptional.get(); Comment comment = new Comment(); comment.setArticleId(articleId); comment.setContent(content); commentRepository.save(comment); article.setCommentCount(article.getCommentCount() + 1); articleRepository.save(article); }}Copy the code

Concurrent problem analysis

From the code implementation just now, we can see the process of this simple Comment function. When the user initiates a Comment request, find out the entity class Article of the corresponding Article from the database, and then generate the corresponding Comment entity class Comment according to the Article information, insert it into the database, and then increase the number of comments of the Article. Then update the modified article to the database, the whole process is as follows flow chart.

There is a problem in this process. When there are multiple users making concurrent comments at the same time, they enter step 1 to get the Article, then insert the corresponding Comment, and finally update the number of comments in Step 3 to save them to the database. Article.mentcount +1 is the same as article.mentCount +1 saved in Step 3, so the number of articles that should have been +3 has been increased by 1.

Let’s try it out with the test case code

@RunWith(SpringRunner.class)
@SpringBootTest(classes = LockAndTransactionApplication.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class CommentControllerTests {
    @Autowired
    private TestRestTemplate testRestTemplate;

    @Test
    public void concurrentComment() {
        String url = "http://localhost:9090/comment";
        for (int i = 0; i < 100; i++) {
            int finalI = i;
            new Thread(() -> {
                MultiValueMap<String, String> params = new LinkedMultiValueMap<>();
                params.add("articleId"."1");
                params.add("content"."Test content" + finalI);
                String result = testRestTemplate.postForObject(url, params, String.class); }).start(); }}}Copy the code

Here we open 100 threads and send comment requests corresponding to article ID 1.

Before sending the request, the database data is

select * from article
Copy the code

select count(*) comment_count from comment
Copy the code

After sending the request, the database data is

select * from article
Copy the code

select count(*) comment_count from comment
Copy the code

It is obvious that the comment_count in the article table is not 100; it is not necessarily 14 as in my diagram, but it is certainly not greater than 100, and the number of comment_counts in the comment table is certainly 100.

This illustrates the concurrency problem mentioned at the beginning of this article, which is quite common and should be carefully avoided on any system with a process like the one above.

Below will show an example is used to show how to use pessimistic and optimistic lock lock to prevent concurrent data problems, at the same time give the SQL plan and JPA bring, SQL program can general “any system”, or even no language, while the JPA solution is very fast, if you happen to use JPA, then you can simply use the optimistic or pessimistic locks lock. Finally, some differences between optimistic and pessimistic locking will be compared based on the business

Pessimistic locks solve concurrency problems

Pessimistic locking, as its name implies, is the pessimistic belief that the data it operates will be handled by other threads, so it must own the data, can be understood as “exclusive lock”. In Java, synchronized and ReentrantLock are pessimistic locks, as are table locks, row locks, read and write locks in databases.

Use SQL to solve concurrency problems

A row lock is a process in which a row of data is locked. Other threads must wait to read or write data from the same table, but other threads can still manipulate data from the same table. Select * from table_name where table_name = ‘for UPDATE’; select * from table_name where table_name = ‘for update’;

Now to modify the original code, add a manual write SQL query method to ArticleRepository.

public interface ArticleRepository extends CrudRepository<Article, Long> {
    @Query(value = "select * from article a where a.id = :id for update", nativeQuery = true)
    Optional<Article> findArticleForUpdate(Long id);
}
Copy the code

We then changed the query method used in CommentService from findById to our own method

public class CommentService { ... public void postComment(Long articleId, String content) { // Optional<Article> articleOptional = articleRepository.findById(articleId); Optional<Article> articleOptional = articleRepository.findArticleForUpdate(articleId); . }}Copy the code

This ensures that only one thread will be able to access the Article until we commit it to the transaction.

Now, using the test case, the article.comment_count value must be 100.

Use JPA’s own row locks to solve concurrency problems

JPA provides a more elegant way to add for UPDATE to SQL, with the @lock annotation, whose arguments can be passed to the desired Lock level.

Now add JPA’s locking method to ArticleRepository, where the lockmodeType.pessimism _write parameter is a row lock.

public interface ArticleRepository extends CrudRepository<Article, Long> {
    ...

    @Lock(value = LockModeType.PESSIMISTIC_WRITE)
    @Query("select a from Article a where a.id = :id")
    Optional<Article> findArticleWithPessimisticLock(Long id);
}
Copy the code

The same as long as the query methods in the CommentService findArticleWithPessimisticLock instead (), test cases to test again, sure there would be no concurrency issues. If we look at the console print, we can see that JPA has added for update to the SQL query.

Optimistic locking solves concurrency problems

Optimistic locking, as the name implies, is extremely optimistic, thinking that the resource won’t be handled by other threads, so it will not be locked, just before inserting the database to determine whether the data has been modified. So pessimistic lock is to restrict other threads, and optimistic lock is to restrict oneself, although its name has lock, but not actually lock, just at the end of the operation to determine the specific operation.

Optimistic locking is usually the version number mechanism or CAS algorithm

Use SQL implementation version numbers to solve concurrency problems

The version number mechanism is to add a field to the database as the version number, for example, we add a field version. Then get it at this pointArticleYou get a version number, like you get version 1, and then you get thisArticleOne operation, after the operation to insert into the database. Oh, what’s the databaseArticleVersion 2 is different from the version I have, which means I haveArticleIt’s not up to date, so you can’t put it in the database. In this way, the problem of data collisions during concurrency is avoided.

So we now add a field version to the Article table

The article article table

field	type	note
version	INT DEFAULT 0	The version number

The version field is then added to the corresponding entity class

@Data
@Entity
public class Article {
    ...

    private Long version;
}
Copy the code

Then add the update method to ArticleRepository. Note that this is the update method, as opposed to adding the query method in pessimistic locking.

public interface ArticleRepository extends CrudRepository<Article, Long> {
    @Modifying
    @Query(value = "update article set comment_count = :commentCount, version = version + 1 where id = :id and version = :version", nativeQuery = true)
    int updateArticleWithVersion(Long id, Long commentCount, Long version);
}
Copy the code

You can see that the update WHERE has a condition to determine version and set version = version + 1. This ensures that data will only be updated if the version number of the database is the same as the version number of the entity class being updated.

Then modify the code slightly in CommentService.

// CommentService public void postComment(Long articleId, String content) { Optional<Article> articleOptional = articleRepository.findById(articleId); . int count = articleRepository.updateArticleWithVersion(article.getId(), article.getCommentCount() + 1, article.getVersion());if (count == 0) {
        throw new RuntimeException("Server busy, update data failed");
    }
    // articleRepository.save(article);
}
Copy the code

First, the Article query method just needs the normal findById() method without locking anything.

Then update the Article with the new updateArticleWithVersion() method. You can see that this method returns a value representing the number of database rows that were updated, or 0 indicating that there are no rows eligible to be updated.

After that, it’s up to us to decide what to do with it. In this case, it’s a direct rollback, and Spring will roll back the previous data operation for us, canceling all operations this time to ensure data consistency.

Now let’s test it with the test case

select * from article
Copy the code

select count(*) comment_count from comment
Copy the code

Now you see that the number of comment_count and Comment in Article is no longer 100, but they must be the same. If the Article table has a conflict, it will not be updated to the database. In this case, we throw an exception to roll back the transaction, so that comments will not be inserted when the Article is not updated, which solves the problem of data inconsistency.

This kind of direct rollback processing method has poor user experience. Generally speaking, if the number of Article updates is judged to be 0, the user will try to query the information from the database again and modify it again, and try to update the data again. If not, the user will query again until it can be updated. Of course, it will not be a wireless loop such operations, will set a line, such as the loop 3 times query modification update is not good, this time will throw an exception.

Use the JPA implementation version now to solve the concurrency problem

JPA has a way to achieve pessimistic lock, optimistic lock naturally also have, now use JPA own method to achieve optimistic lock.

The version field of the Article entity class is annotated with @version.

The following types are supported for version properties: int, Integer, short, Short, long, Long, java.sql.Timestamp.

The type of the version number supports the three basic data types int, short, long and their wrapper class Timestamp. We are using the long type now.

@Data
@Entity
public class Article {
    ...

    @Version
    private Long version;
}
Copy the code

Then just change the comment flow in CommentService back to the business code we started with “triggering concurrency issues”. This shows that JPA’s optimistic lock implementation is non-intrusive.

// CommentService public void postComment(Long articleId, String content) { Optional<Article> articleOptional = articleRepository.findById(articleId); . article.setCommentCount(article.getCommentCount() + 1); articleRepository.save(article); }Copy the code

As before, use test cases to see if you can prevent concurrency problems.

select * from article
Copy the code

select count(*) comment_count from comment
Copy the code

The number of comment_count and Comment in the same Article is not 100, but they are certainly the same. Look at the IDEA of the console will find system throws ObjectOptimisticLockingFailureException exception.

This is similar to our own implementation of optimistic locking. If the data is not updated successfully, we throw an exception and roll back to ensure data consistency. If you want to implement retry process can capture ObjectOptimisticLockingFailureException this exception, often using AOP + custom annotations to implement a global general retry mechanism, this is according to the specific business situation to develop, Want to know you can search the program.

Pessimistic locks compared to optimistic locks

Pessimistic locking works well in scenarios where you write too much and read too little. Because at the time of using this thread will monopolize the resources, in the case of this article is a id, if there is a lot of comments, operation is suitable to use pessimistic locks, otherwise the user just browsing articles and if no comments, use pessimistic locks will often locked, increased the lock unlock resource consumption.

Optimistic locking works well in scenarios where you write less and read more. Because optimistic locks are rolled back or retried when conflicts occur, if the volume of write requests is high, conflicts often occur, and frequent rollback and retries can be very costly to system resources.

So pessimistic locking and optimistic locking are not absolutely good or bad, must be combined with the specific business situation to decide which way to use. In addition, alibaba development manual is also mentioned:

If the probability of each access conflict is less than 20%, optimistic lock is recommended; otherwise, pessimistic lock is recommended. The number of optimistic lock retries must be at least three.

Alibaba suggests that the value of 20% conflict probability be used as the dividing line to decide the use of optimistic lock and pessimistic lock. Although this value is not absolute, it is also a good reference summed up by various leaders of Alibaba.

[Reproduced please indicate the source] :Juejin. Cn/post / 684490…