Spring事务与数据库锁

数据库锁保证并发数据一致性，Spring事务管理锁的获取和释放时机。

锁类型对比

悲观锁实现

SELECT FOR UPDATE

@Repository
public class AccountDao {

    // 悲观锁查询
    @Query("SELECT a FROM Account a WHERE a.id = :id FOR UPDATE")
    Account findByIdForUpdate(@Param("id") Long id);

    // 或者使用原生SQL
    @Query(value = "SELECT * FROM account WHERE id = ? FOR UPDATE",
           nativeQuery = true)
    Account lockById(Long id);
}

@Service
public class AccountService {

    @Transactional
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        // 加锁查询，按id顺序加锁防止死锁
        Account from = accountDao.findByIdForUpdate(Math.min(fromId, toId));
        Account to = accountDao.findByIdForUpdate(Math.max(fromId, toId));

        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));
    }
}

JPA悲观锁

@Entity
public class Product {
    @Id
    private Long id;
    private Integer stock;
}

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // PESSIMISTIC_WRITE - 写锁（排他锁）
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT p FROM Product p WHERE p.id = :id")
    Product findByIdWithWriteLock(@Param("id") Long id);

    // PESSIMISTIC_READ - 读锁（共享锁）
    @Lock(LockModeType.PESSIMISTIC_READ)
    @Query("SELECT p FROM Product p WHERE p.id = :id")
    Product findByIdWithReadLock(@Param("id") Long id);
}

@Service
public class ProductService {

    @Transactional
    public void decreaseStock(Long productId, int quantity) {
        // 获取写锁
        Product product = productRepository.findByIdWithWriteLock(productId);
        if (product.getStock() < quantity) {
            throw new RuntimeException("库存不足");
        }
        product.setStock(product.getStock() - quantity);
    }
}

MyBatis悲观锁

<!-- Mapper XML -->
<select id="selectByIdForUpdate" resultType="Account">
    SELECT * FROM account WHERE id = #{id} FOR UPDATE
</select>

<select id="selectByIdForUpdateNowait" resultType="Account">
    SELECT * FROM account WHERE id = #{id} FOR UPDATE NOWAIT
</select>

乐观锁实现

版本号方式

@Entity
public class Product {
    @Id
    private Long id;
    private String name;
    private Integer stock;

    @Version  // JPA版本号注解
    private Integer version;
}

@Service
public class ProductService {

    @Transactional
    public void decreaseStock(Long productId, int quantity) {
        Product product = productRepository.findById(productId)
                .orElseThrow(() -> new RuntimeException("商品不存在"));

        if (product.getStock() < quantity) {
            throw new RuntimeException("库存不足");
        }

        product.setStock(product.getStock() - quantity);
        // 更新时自动检查version，失败抛出OptimisticLockException
        productRepository.save(product);
    }
}

手动版本号

@Entity
public class Order {
    @Id
    private Long id;
    private Integer version;  // 手动维护
    private String status;
}

@Repository
public class OrderRepository {

    @Modifying
    @Query("UPDATE Order o SET o.status = :status, o.version = o.version + 1 " +
           "WHERE o.id = :id AND o.version = :version")
    int updateWithVersion(@Param("id") Long id,
                          @Param("status") String status,
                          @Param("version") Integer version);
}

@Service
public class OrderService {

    @Transactional
    public boolean updateStatus(Long orderId, String newStatus) {
        Order order = orderRepository.findById(orderId).orElse(null);
        if (order == null) return false;

        int updated = orderRepository.updateWithVersion(
            orderId, newStatus, order.getVersion());

        if (updated == 0) {
            // 版本冲突，更新失败
            throw new OptimisticLockException("数据已被修改，请刷新后重试");
        }
        return true;
    }
}

CAS方式

@Repository
public class StockDao {

    // 条件更新，类似CAS
    @Modifying
    @Query("UPDATE Stock s SET s.quantity = s.quantity - :qty " +
           "WHERE s.productId = :productId AND s.quantity >= :qty")
    int decreaseStock(@Param("productId") Long productId,
                      @Param("qty") Integer qty);
}

@Service
public class StockService {

    @Transactional
    public boolean decreaseStock(Long productId, int quantity) {
        int affected = stockDao.decreaseStock(productId, quantity);
        if (affected == 0) {
            // 库存不足或不存在
            return false;
        }
        return true;
    }
}

死锁预防

按顺序加锁

@Service
public class TransferService {

    @Transactional
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        // 按ID排序加锁，避免死锁
        Long first = Math.min(fromId, toId);
        Long second = Math.max(fromId, toId);

        Account acc1 = accountDao.findByIdForUpdate(first);
        Account acc2 = accountDao.findByIdForUpdate(second);

        // 执行转账
        acc1.setBalance(acc1.getBalance().subtract(amount));
        acc2.setBalance(acc2.getBalance().add(amount));
    }
}

设置锁超时

@Transactional(timeout = 10)  // 事务超时
public void process() {
    // MySQL: SELECT ... FOR UPDATE NOWAIT (MySQL 8.0+)
    // Oracle: SELECT ... FOR UPDATE NOWAIT
    // PostgreSQL: SELECT ... FOR UPDATE NOWAIT
}

减少锁持有时间

@Service
public class OrderService {

    // 错误：锁持有时间过长
    @Transactional
    public void createOrder_bad(OrderRequest request) {
        Product product = productDao.findByIdForUpdate(request.getProductId());
        // 模拟耗时操作
        Thread.sleep(5000);  // 锁被长时间持有
        orderDao.insert(new Order(product));
    }

    // 正确：减少锁持有时间
    public void createOrder_good(OrderRequest request) {
        // 先执行不需要锁的操作
        validateRequest(request);

        // 只在必要时刻加锁
        Order order = createOrderWithLock(request);
        // 锁释放后再执行其他操作
        sendNotification(order);
    }

    @Transactional
    protected Order createOrderWithLock(OrderRequest request) {
        Product product = productDao.findByIdForUpdate(request.getProductId());
        Order order = new Order(product);
        orderDao.insert(order);
        return order;
    }
}

锁选择策略

要点总结

• 悲观锁适合写多、冲突频繁场景
• 乐观锁适合读多写少场景，配合@Version
• SELECT FOR UPDATE实现悲观锁
• 版本号机制实现乐观锁，冲突时抛异常
• 按固定顺序加锁预防死锁
• 缩短锁持有时间提高并发性能