Spring Boot+SQL/JPA实战悲观锁和乐观锁开发者社区

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Spring Boot+SQL/JPA实战悲观锁和乐观锁

原创

字段	类型	备注
id	INT	自增主键id
title	VARCHAR	文章标题
comment_count	INT	文章的评论数量

字段	类型	备注
id	INT	自增主键id
article_id	INT	评论的文章id
content	VARCHAR	评论内容

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <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>

@Data
@Entity
public class Article {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private Long commentCount;
}

@Data
@Entity
public class Comment {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long articleId;
    private String content;
}

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

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

@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";
}

@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("没有对应的文章");
        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);
}

@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", "测试内容" + finalI);
                String result = testRestTemplate.postForObject(url, params, String.class);
            }).start();
}

select * from article

select count(*) comment_count from comment

select * from article

select count(*) comment_count from comment

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);
}

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

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);
}

字段	类型	备注
version	INT DEFAULT 0	版本号

@Data
@Entity
public class Article {
    private Long version;
}

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);
}

// 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("服务器繁忙,更新数据失败");
    // articleRepository.save(article);
}

select * from article

select count(*) comment_count from comment

@Data
@Entity
public class Article {
    @Version
    private Long version;
}

// CommentService
public void postComment(Long articleId, String content) {
    Optional<Article> articleOptional = articleRepository.findById(articleId);

Spring Boot+SQL/JPA实战悲观锁和乐观锁

Spring Boot+SQL/JPA实战悲观锁和乐观锁

业务还原

数据库设计

代码实现

并发问题分析

悲观锁解决并发问题

利用SQL解决并发问题

利用JPA自带行锁解决并发问题

乐观锁解决并发问题

利用SQL实现版本号解决并发问题

利用JPA实现版本现解决并发问题