Concurrent batch request causing DeadLock in database using Spring JPA

Viewed 271

I am using Java 11, Spring boot version 2.4.3 I am trying to batch insert some entities concurrently by using

orderRepository.saveAll(orders)

I am making parallel request of batch of 20 orders. And each order contains a list of Items. When number of items are higher say more than 30 I am getting Deadlock.
My entities looks like this.

Order:

    public class Order {

  @Id
  @Column(name = "Id", updatable = false, nullable = false, columnDefinition = "uniqueidentifier")
  @GeneratedValue(generator = "UUID")
  @GenericGenerator(
          name = "UUID",
          strategy = "org.hibernate.id.UUIDGenerator")
  @Exclude
  private String id;


  @OneToMany(cascade = {CascadeType.ALL}, orphanRemoval = true, mappedBy = "order")
  @Exclude
  @BatchSize(size = 4096)
  @Setter(AccessLevel.NONE)
  private List<Item> items;

Item:

    public class Item {

  @Id
  @Column(name = "Id", updatable = false, nullable = false, columnDefinition = "uniqueidentifier")
  @GeneratedValue(generator = "UUID")
  @GenericGenerator(
    name = "UUID",
    strategy = "org.hibernate.id.UUIDGenerator")
  @Exclude
  private String id;
    
  @ManyToOne
  @JoinColumn(name = "OrderId", foreignKey = @ForeignKey(name = "FK_Item_Order"), nullable = false)
  @ToString.Exclude
  @Exclude
  private Order order;

  @OneToOne(cascade = {CascadeType.ALL}, orphanRemoval = true)
  @JoinColumn(name = "SupplierId", foreignKey = @ForeignKey(name = "FK_Item_Supplier"))
  private Supplier supplier;

Supplier:

public class Supplier {

  @Id
  @Column(name = "Id", updatable = false, nullable = false, columnDefinition = "uniqueidentifier")
  @GeneratedValue(generator = "UUID")
  @GenericGenerator(
    name = "UUID",
    strategy = "org.hibernate.id.UUIDGenerator")
  @Exclude
  private String id;


  @OneToOne(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER, orphanRemoval = true)
  @JoinColumn(name = "AddressId", foreignKey = @ForeignKey(name = "FK_Supplier_Address"))
  private Address address;

  @Column(name = "CreatedAt", insertable = false, updatable = false)
  @Exclude
  private OffsetDateTime createdAt;
}

And the error I am getting :

    Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.CannotAcquireLockException:

 could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: 

could not execute statement] with root causecom.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 529) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Looking at the Database analyser I can see something deadlock is between Order and Supplier and sometimes its between Order and Item .

I am yet to try manual Id generation strategy.

0 Answers
Related