title: Spring data jpa insert into multiple tables to avoid locking tables
body: <p>Can you please help me understand how to insert entities into multiple tables efficiently?</p>
  <p>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.</p>
  <p><a href="https://i.stack.imgur.com/QyDGy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QyDGy.png" alt="enter image description here" /></a></p>
  <p>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.</p>
  <p>This might be a stupid question and has been already answered before, but I hope someone could give me a hint.</p>
  <pre class="lang-java prettyprint-override"><code>    @Entity
      @NoArgsConstructor
      @AllArgsConstructor
      @Table(name = &quot;pricebook&quot;)
      public class Pricebook {
         @Id
         @GeneratedValue(strategy=GenerationType.AUTO) 
         private Long id;
         //....
      }
  
      @Entity
      @NoArgsConstructor
      @AllArgsConstructor
      @Table(name = &quot;sku&quot;)
      public class Sku {
         @Id
         @GeneratedValue(strategy=GenerationType.AUTO)
         private Long id;
         //....
      }
  
      @Entity
      @NoArgsConstructor
      @AllArgsConstructor
      @Table(name = &quot;price&quot;)
      public class Price {
         @Id
         @GeneratedValue(strategy=GenerationType.AUTO)
         private Long id;
  
         @JoinColumn(name = &quot;pricebook_id&quot;, referencedColumnName = &quot;id&quot;, unique = true)
         @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
         private Pricebook pricebook;
  
         @JoinColumn(name = &quot;sku_id&quot;, referencedColumnName = &quot;id&quot;, unique = true)
         @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
         private Sku sku;
  
         //....
      }
  </code></pre>
  <p>Here is PricebookService logic of an upsert.</p>
  <pre><code>  @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(&quot;The pricebook's start date later then its expiration date.&quot;);
          }
  
          final Region region = regionService.findRegionByName(request.getRegion());
  
          final Optional&lt;Pricebook&gt; 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(&quot;pricebook is updated successfully, pricebook={}&quot;, savedOrUpdatedPricebook);
          } else {
              savedOrUpdatedPricebook = pricebookRepository.save(
                      new Pricebook(request.getName(), request.getPricebookType(), region, startDate, expirationDate);
  
              logger.info(&quot;pricebook is created successfully, pricebook={}&quot;, savedOrUpdatedPricebook);
          }
  
          final List&lt;Sku&gt; skus = skuService.createSku(savedOrUpdatedPricebook, request.getSkus());
          logger.debug(&quot;skus are saved successfully, skus={}&quot;, skus);
          return savedOrUpdatedPricebook;
      }
  </code></pre>
  <p>Here is SkuService logic of an upsert. <code>skuToCreateOrUpdate</code> basically is just a method which wraps logic if it's found or new and return a new object.</p>
  <pre><code>    @NonNull
      public List&lt;Sku&gt; createSku(@NonNull Pricebook pricebook, @NonNull List&lt;CreateSkuRequest&gt; skus) {
          return skus.stream().map(sku -&gt; {
              final Optional&lt;Sku&gt; foundSku = skuRepository.findByCode(sku.getCode());
  
              final Sku savedOrUpdatedSku = skuRepository.save(skuToCreateOrUpdate(sku, foundSku.map(Sku::getSkuId).orElse(null)));
  
              final List&lt;Price&gt; prices = priceService.createPrices(pricebook, savedOrUpdatedSku, sku.getPrice());
              logger.debug(&quot;prices are saved successfully, prices={}&quot;, prices);
              return savedOrUpdatedSku;
          }).collect(toList());
      }
  </code></pre>
  <p>Here is PriceService logic of an upsert.</p>
  <pre><code>    @NonNull
      public List&lt;Price&gt; createPrices(@NonNull Pricebook pricebook, @NonNull Sku sku, @NonNull CreatePriceRequest price) {
          final Optional&lt;Price&gt; 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(&quot;price is updated successfully, price={}&quot;, savedOrUpdatedPrice);
          } else {
              savedOrUpdatedPrice = priceRepository.save(
                      new Price(pricebook, sku);
              logger.info(&quot;price is created successfully, price={}&quot;, savedOrUpdatedPrice);
          }
  
          return Collections.singletonList(savedOrUpdatedPrice);
      }
  </code></pre>
  <p>I'm using a JpaRepository all over the places. Like so..</p>
  <pre><code>@Repository
  public interface PricebookRepository extends JpaRepository&lt;Pricebook, Long&gt; {}
  
  @Repository
  public interface SkuRepository extends JpaRepository&lt;Sku, Long&gt; {}
  
  @Repository
  public interface PriceRepository extends JpaRepository&lt;Price, Long&gt; {}
  </code></pre>