create table catalog (
id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
name varchar(50) NOT NULL DEFAULT '' COMMENT '商品名稱',
browse_count int(11) NOT NULL DEFAULT 0 COMMENT '瀏覽數(shù)',
version int(11) NOT NULL DEFAULT 0 COMMENT '樂(lè)觀鎖，版本號(hào)',
PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE table browse (
id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
cata_id int(11) NOT NULL COMMENT '商品ID',
user varchar(50) NOT NULL DEFAULT '' COMMENT '',
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <parent>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-parent</artifactId>
 <version>2.1.1.RELEASE</version>
 <relativePath/> <!-- lookup parent from repository -->
 </parent>
 <groupId>com.hqs</groupId>
 <artifactId>dblock</artifactId>
 <version>1.0-SNAPSHOT</version>
 <name>dblock</name>
 <description>Demo project for Spring Boot</description>

 <properties>
 <java.version>1.8</java.version>
 </properties>

 <dependencies>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
 </dependency>

 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-devtools</artifactId>
 <scope>runtime</scope>
 </dependency>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-test</artifactId>
 <scope>test</scope>
 </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>

 <!-- aop -->
 <dependency>
 <groupId>org.aspectj</groupId>
 <artifactId>aspectjweaver</artifactId>
 <version>1.8.4</version>
 </dependency>

 </dependencies>

 <build>
 <plugins>
 <plugin>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-maven-plugin</artifactId>
 </plugin>
 </plugins>
 </build>

</project>

public interface CatalogRepository extends CrudRepository<Catalog, Long> {

 @Query(value = "select * from Catalog a where a.id = :id for update", nativeQuery = true)
 Optional<Catalog> findCatalogsForUpdate(@Param("id") Long id);

 @Lock(value = LockModeType.PESSIMISTIC_WRITE) //代表行級(jí)鎖
 @Query("select a from Catalog a where a.id = :id")
 Optional<Catalog> findCatalogWithPessimisticLock(@Param("id") Long id);

 @Modifying(clearAutomatically = true) //修改時(shí)需要帶上
 @Query(value = "update Catalog set browse_count = :browseCount, version = version + 1 where id = :id " +
 "and version = :version", nativeQuery = true)
 int updateCatalogWithVersion(@Param("id") Long id, @Param("browseCount") Long browseCount, @Param("version") Long version);

}

package com.hqs.dblock;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.web.client.TestRestTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DblockApplication.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class DblockApplicationTests {

 @Autowired
 private TestRestTemplate testRestTemplate;

 @Test
 public void browseCatalogTest() {
 String url = "http://localhost:8888/catalog";
 for(int i = 0; i < 100; i++) {
 final int num = i;
 new Thread(() -> {
 MultiValueMap<String, String> params = new LinkedMultiValueMap<>();
 params.add("catalogId", "1");
 params.add("user", "user" + num);
 String result = testRestTemplate.postForObject(url, params, String.class);
 System.out.println("-------------" + result);
 }
 ).start();
 }
 }

 @Test
 public void browseCatalogTestRetry() {
 String url = "http://localhost:8888/catalogRetry";
 for(int i = 0; i < 100; i++) {
 final int num = i;
 new Thread(() -> {
 MultiValueMap<String, String> params = new LinkedMultiValueMap<>();
 params.add("catalogId", "1");
 params.add("user", "user" + num);
 String result = testRestTemplate.postForObject(url, params, String.class);
 System.out.println("-------------" + result);
 }
 ).start();
 }
 }
}

package com.hqs.dblock.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface RetryOnFailure {
}

package com.hqs.dblock.aspect;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.hibernate.StaleObjectStateException;
import org.springframework.orm.ObjectOptimisticLockingFailureException;
import org.springframework.stereotype.Component;

@Slf4j
@Aspect
@Component
public class RetryAspect {
 public static final int MAX_RETRY_TIMES = 5;//max retry times

 @Pointcut("@annotation(com.hqs.dblock.annotation.RetryOnFailure)") //self-defined pointcount for RetryOnFailure
 public void retryOnFailure(){}

 @Around("retryOnFailure()") //around can be execute before and after the point
 public Object doConcurrentOperation(ProceedingJoinPoint pjp) throws Throwable {
 int attempts = 0;

 do {
 attempts++;
 try {
 pjp.proceed();
 } catch (Exception e) {
 if(e instanceof ObjectOptimisticLockingFailureException ||
  e instanceof StaleObjectStateException) {
  log.info("retrying....times:{}", attempts);
  if(attempts > MAX_RETRY_TIMES) {
  log.info("retry excceed the max times..");
  throw e;
  }
 }

 }
 } while (attempts < MAX_RETRY_TIMES);
 return null;
 }
}