Spring数据jpa插入多表避免锁表 [英] Spring data jpa insert into multiple tables to avoid locking tables
问题描述
你能帮我理解如何高效地将实体插入到多个表中吗?
Can you please help me understand how to insert entities into multiple tables efficiently?
相应地，我有 3 个表和 3 个实体.Pricebook 有一组 SKU，每个 SKU 有 1 个价格.基本上我想要的是事务性地插入多个实体，如果有约束，我必须更新链中的实体.
I have 3 tables and 3 entities accordingly. Pricebook has an array of SKUs, and each SKU has 1 price. Basically what I want is to insert multiple entities transactionally, in case of constraint I must update entities in a chain.
一旦我尝试将超过 1 个 Pricebook 并行插入到数据库中，就会出现问题，因此我实际上遇到了 PostgreSQL 死锁.我发现一种解决方法是将它们一个一个地插入队列中，但我知道这不是一个好主意.
The problem occurred once I try to insert into DB more than 1 Pricebook in parallel, so I'm actually catching a PostgreSQL deadlock. I found a workaround is to insert them one by one putting them in the queue but I understand that it's not a great idea.
这可能是一个愚蠢的问题，之前已经有人回答过，但我希望有人能给我一个提示.
This might be a stupid question and has been already answered before, but I hope someone could give me a hint.
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "pricebook")
public class Pricebook {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
//....
}
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "sku")
public class Sku {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
//....
}
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "price")
public class Price {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
@JoinColumn(name = "pricebook_id", referencedColumnName = "id", unique = true)
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private Pricebook pricebook;
@JoinColumn(name = "sku_id", referencedColumnName = "id", unique = true)
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private Sku sku;
//....
}
这是更新插入的 PricebookService 逻辑.
Here is PricebookService logic of an upsert.
@NonNull
@Transactional
public Pricebook createPricebook(@NonNull CreatePricebookRequest request) {
final Instant startDate = PricebookConverter.toDate(request.getStartDate());
final Instant expirationDate = PricebookConverter.toDate(request.getExpirationDate());
if (startDate.isAfter(expirationDate)) {
throw new InvalidParametersException("The pricebook's start date later then its expiration date.");
}
final Region region = regionService.findRegionByName(request.getRegion());
final Optional<Pricebook> isPricebookFound =
pricebookRepository.findByRegionAndPricebookTypeAndStartDateAndExpirationDate(region,
request.getPricebookName(), startDate, expirationDate);
final Pricebook savedOrUpdatedPricebook;
if (isPricebookFound.isPresent()) {
final Pricebook foundPricebook = isPricebookFound.get();
savedOrUpdatedPricebook = pricebookRepository.save(
new Pricebook(foundPricebook.getPricebookId(), request.getName(), foundPricebook.getPricebookName(), foundPricebook.getRegion(), foundPricebook.getStartDate(),
foundPricebook.getExpirationDate());
logger.info("pricebook is updated successfully, pricebook={}", savedOrUpdatedPricebook);
} else {
savedOrUpdatedPricebook = pricebookRepository.save(
new Pricebook(request.getName(), request.getPricebookType(), region, startDate, expirationDate);
logger.info("pricebook is created successfully, pricebook={}", savedOrUpdatedPricebook);
}
final List<Sku> skus = skuService.createSku(savedOrUpdatedPricebook, request.getSkus());
logger.debug("skus are saved successfully, skus={}", skus);
return savedOrUpdatedPricebook;
}
这是一个 upsert 的 SkuService 逻辑.skuToCreateOrUpdate 基本上只是一个方法，如果它被找到或者是新的，则包装逻辑并返回一个新对象.
Here is SkuService logic of an upsert. skuToCreateOrUpdate basically is just a method which wraps logic if it's found or new and return a new object.
@NonNull
public List<Sku> createSku(@NonNull Pricebook pricebook, @NonNull List<CreateSkuRequest> skus) {
return skus.stream().map(sku -> {
final Optional<Sku> foundSku = skuRepository.findByCode(sku.getCode());
final Sku savedOrUpdatedSku = skuRepository.save(skuToCreateOrUpdate(sku, foundSku.map(Sku::getSkuId).orElse(null)));
final List<Price> prices = priceService.createPrices(pricebook, savedOrUpdatedSku, sku.getPrice());
logger.debug("prices are saved successfully, prices={}", prices);
return savedOrUpdatedSku;
}).collect(toList());
}
这是更新插入的 PriceService 逻辑.
Here is PriceService logic of an upsert.
@NonNull
public List<Price> createPrices(@NonNull Pricebook pricebook, @NonNull Sku sku, @NonNull CreatePriceRequest price) {
final Optional<Price> foundPrice = priceRepository.findByPricebookAndSku(pricebook, sku);
final Price savedOrUpdatedPrice;
if (foundPrice.isPresent()) {
final Price priceToUpdate = foundPrice.get();
savedOrUpdatedPrice = priceRepository.save(
new Price(priceToUpdate.getPriceId(),
pricebook,
sku);
logger.info("price is updated successfully, price={}", savedOrUpdatedPrice);
} else {
savedOrUpdatedPrice = priceRepository.save(
new Price(pricebook, sku);
logger.info("price is created successfully, price={}", savedOrUpdatedPrice);
}
return Collections.singletonList(savedOrUpdatedPrice);
}
我到处都在使用 JpaRepository.像这样..
I'm using a JpaRepository all over the places. Like so..
@Repository
public interface PricebookRepository extends JpaRepository<Pricebook, Long> {}
@Repository
public interface SkuRepository extends JpaRepository<Sku, Long> {}
@Repository
public interface PriceRepository extends JpaRepository<Price, Long> {}
推荐答案
我相信你可能会面临 这个问题，特别是如果两个事务都试图插入相同的 SKU.
I believe you might be facing this issue, which is pretty likely especially if both transactions attempt to insert the same SKUs.
如果是这样的话，我可以想出两种方法来缓解它:
If that's the case, I can think of two ways to mitigate it:
部分解决方案:尝试对
List由中的 SKU 进行排序；skus sku.code和(如果这还不够)使用saveAndFlush()来存储它们，确保插入的顺序.这应该消除循环等待，这意味着现在至少一个事务应该成功(另一个可能会违反唯一约束)
Partial solution: try sorting the SKUs in
List<CreateSkuRequest> skusbysku.codeand (if that's not enough) usingsaveAndFlush()to store them, ensuring the order of insertion. This should eliminate the circular wait, meaning that now, at least one of the transactions should succeed (the other will probably get a unique constraint violation)
完整解决方案:如果您希望两个事务都成功，则必须为 SKU 表获取表级锁.您应该能够使用自定义更新查询来执行此操作:
Full solution: if you prefer both transactions to succeed, you would have to acquire a table-level lock for the SKU table. You should be able to do this using a custom update query:
@Query(value = "LOCK TABLE SKU IN EXCLUSIVE MODE", nativeQuery = true)
@Modifying
void lockTable();
然后，只需调用该方法作为 createSku 中的第一个操作.请注意，这可能只比将事务放入队列中效率稍高，所以如果我是你，我可能仍然会采用这种方法.
then, just call the method as the first operation inside createSku. Note that this might turn out to be only marginally more efficient than putting the transactions in a queue, so if I were you, I'd probably still just go with that approach.
编辑我也不太明白给你两个事务冲突的一致结果的确切场景，这是你试图并行化的批量插入类型吗?如果您真的致力于并行运行事务，也许您可以对输入集进行分组，这样 SKU 就不会重叠.或者，删除重复并预先插入 Sku .正如我所说，我不知道用例是什么，所以不确定这是否有意义.
EDIT I also didn't quite understand the exact scenario which gives you the consistent result of two transactions clashing, is this a batch insert type of thing that you're trying to parallelize? If you're really bent on running the transactions in parallel, perhaps you could group your input set so that the SKUs don't overlap. Or, deduplicate and insert the Skus upfront. As I said, I don't know what the use case is, so not sure if that makes sense.
这篇关于Spring数据jpa插入多表避免锁表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
