数据库乐观锁深度解析：MySQL、PostgreSQL 实战 + Spring Boot 集成指南

// ❌ 错误：用旧的 version 重试
while (rows == 0) {
    rows = update(entity.getVersion());  // version 永远是旧值，死循环
}

// ✅ 正确：失败后重新查询
int maxRetry = 3;
for (int i = 0; i < maxRetry; i++) {
    Product latest = productRepo.findById(id);   // 重新查询最新数据
    int rows = productMapper.updateWithVersion(latest.getVersion(), ...);
    if (rows > 0) break;
    if (i == maxRetry - 1) throw new BusinessException("操作失败，请稍后重试");
}

package com.example.demo.entity;

import jakarta.persistence.*;
import lombok.Data;
import java.time.LocalDateTime;

@Data
@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private Integer stock;

    /**
     * 乐观锁版本号字段
     * JPA 会自动在 UPDATE 的 WHERE 中加入版本比对，并在成功后自动 +1
     * 支持类型：int, Integer, long, Long, Timestamp
     */
    @Version
    @Column(nullable = false)
    private Integer version;

    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

    @PrePersist
    @PreUpdate
    public void onUpdate() {
        this.updatedAt = LocalDateTime.now();
    }
}

package com.example.demo.repository;

import com.example.demo.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {
}

package com.example.demo.service;

import com.example.demo.entity.Product;
import com.example.demo.repository.ProductRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.orm.ObjectOptimisticLockingFailureException;
import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Slf4j
@Service
@RequiredArgsConstructor
public class ProductService {

    private final ProductRepository productRepository;

    /**
     * 扣减库存（JPA @Version 乐观锁）
     * @Retryable：检测到乐观锁冲突后，最多重试 3 次，指数退避
     */
    @Transactional
    @Retryable(
        retryFor = ObjectOptimisticLockingFailureException.class,
        maxAttempts = 3,
        backoff = @Backoff(delay = 100, multiplier = 2)  // 100ms, 200ms, 400ms
    )
    public void decreaseStock(Long productId, int quantity) {
        // 1. 读取实体（含 version 字段）
        Product product = productRepository.findById(productId)
            .orElseThrow(() -> new RuntimeException("商品不存在: " + productId));

        // 2. 校验库存
        if (product.getStock() < quantity) {
            throw new RuntimeException("库存不足，当前库存: " + product.getStock());
        }

        // 3. 修改库存（version 由 JPA 自动管理，无需手动修改）
        product.setStock(product.getStock() - quantity);

        // 4. 保存时 Hibernate 生成：
        //    UPDATE products SET stock=?, version=? WHERE id=? AND version=?
        //    若 version 不匹配，抛出 ObjectOptimisticLockingFailureException
        productRepository.save(product);

        log.info("库存扣减成功：productId={}, quantity={}, newStock={}, version={}",
            productId, quantity, product.getStock(), product.getVersion());
    }
}

@SpringBootApplication
@EnableRetry   // 启用 Spring Retry
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

package com.example.demo.exception;

import org.springframework.http.HttpStatus;
import org.springframework.orm.ObjectOptimisticLockingFailureException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestControllerAdvice;

import java.util.Map;

@RestControllerAdvice
public class GlobalExceptionHandler {

    /**
     * 乐观锁冲突异常处理：重试次数耗尽后的兜底响应
     */
    @ExceptionHandler(ObjectOptimisticLockingFailureException.class)
    @ResponseStatus(HttpStatus.CONFLICT)
    public Map<String, Object> handleOptimisticLock(ObjectOptimisticLockingFailureException e) {
        return Map.of(
            "code", 409,
            "message", "操作繁忙，请稍后重试",
            "detail", "数据版本冲突：" + e.getIdentifier()
        );
    }
}

package com.example.demo.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 注意插件顺序（官方推荐）：多租户 → 分页 → 乐观锁 → 防全表更新删除
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

package com.example.demo.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.time.LocalDateTime;

@Data
@TableName("products")
public class Product {

    @TableId(type = IdType.AUTO)
    private Long id;

    private String name;

    private Integer stock;

    /**
     * MyBatis-Plus 乐观锁注解
     * 支持类型：int, Integer, long, Long, Date, Timestamp, LocalDateTime
     * 注意：仅支持 updateById(entity) 和 update(entity, wrapper) 方法触发乐观锁
     */
    @Version
    private Integer version;

    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updatedAt;
}

// Mapper
@Mapper
public interface ProductMapper extends BaseMapper<Product> {
}

package com.example.demo.service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.entity.Product;
import com.example.demo.mapper.ProductMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Slf4j
@Service
public class ProductService extends ServiceImpl<ProductMapper, Product> {

    /**
     * 扣减库存（MyBatis-Plus 乐观锁）
     * OptimisticLockerInnerInterceptor 会自动在 SQL 中加入 version 比对
     */
    @Transactional
    public boolean decreaseStock(Long productId, int quantity) {
        // 1. 必须先查询（获取 version）
        Product product = getById(productId);
        if (product == null || product.getStock() < quantity) {
            return false;
        }

        // 2. 修改数据
        product.setStock(product.getStock() - quantity);

        // 3. 调用 updateById：
        //    MP 自动生成：UPDATE products SET stock=?,version=? WHERE id=? AND version=?
        boolean success = updateById(product);

        if (!success) {
            log.warn("乐观锁冲突：productId={}, 当前version={}", productId, product.getVersion());
        }
        return success;
    }
}

// 自定义注解
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface OptimisticRetry {
    int maxAttempts() default 3;
}

// AOP 切面
@Aspect
@Component
@Slf4j
public class OptimisticRetryAspect {

    @Around("@annotation(optimisticRetry)")
    public Object retry(ProceedingJoinPoint pjp, OptimisticRetry optimisticRetry) throws Throwable {
        int maxAttempts = optimisticRetry.maxAttempts();
        Exception lastException = null;

        for (int attempt = 1; attempt <= maxAttempts; attempt++) {
            try {
                return pjp.proceed();
            } catch (ObjectOptimisticLockingFailureException e) {
                lastException = e;
                log.warn("乐观锁冲突，第 {}/{} 次重试", attempt, maxAttempts);
                if (attempt < maxAttempts) {
                    Thread.sleep(50L * attempt);   // 简单退避
                }
            }
        }
        throw new BusinessException("操作失败，请稍后重试", lastException);
    }
}

// 使用：只需加注解
@OptimisticRetry(maxAttempts = 3)
@Transactional
public void placeOrder(Long productId, int quantity) {
    // 业务代码不需要感知乐观锁细节
    productService.decreaseStock(productId, quantity);
}

// ✅ 推荐：指数退避重试（避免惊群效应）
@Retryable(
    retryFor = ObjectOptimisticLockingFailureException.class,
    maxAttempts = 3,
    backoff = @Backoff(delay = 100, multiplier = 2, random = true) // 加随机因子
)

// ❌ 错误：固定间隔且间隔为 0
@Retryable(maxAttempts = 10, backoff = @Backoff(delay = 0))  // 10 次无间隔重试，瞬间压垮 DB