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.