delete call stuck for forever in a Many to One Relationship-CodePudding

Trying to run a delete query on a many-one relationship. But sometime it's stuck for a while when the count of row delete is more then ~50.

Repository:

@Repository
public interface TransitItemRepository extends JpaRepository<TransitItemsMapping, UUID> {

    @Modifying
    @Transactional
    @Query(value="delete from TransitItemsMapping t where t.grouping_form_id=:groupingFormId",nativeQuery = true)
    void deleteByGroupingFormId(@Param("groupingFormId") UUID groupingFormId);
}

Domain:TransitItemsMapping.java

@Data
@Entity
@Table(name = "TransitItemsMapping")
public class TransitItemsMapping implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @GeneratedValue(generator = "uuid")
    @Column(name = "transit_Item_id",unique = true, nullable = false)
    private UUID transitItemId;

    @ToString.Exclude
    @JsonManagedReference
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "grouping_form_id")
    //@OnDelete(action = OnDeleteAction.CASCADE)
    private GroupingForm groupingForm;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(referencedColumnName = "dim_Item_ID",name = "item_id")
    private Item item;

    @Column(name ="item_relationship_id", insertable = false,updatable = false)
    private String itemRelationshipId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "item_relationship_id",referencedColumnName = "dim_item_relationship_id")
    private VendorFactoryItem vendorFactoryItem;

    @Column(name = "edam_id")
    private String edamId;

    @Column(name = "model_number")
    private String modelNumber;

    @Column(name = "description")
    private String description;

    @Column(name = "packaging_details")
    private String packagingDetails;

    @Column(name = "packaging_method")
    private String packagingMethod;

    @Column(name = "is_side_stack")
    private String isSideStack;

    @Column(name = "quantity")
    private Integer quantity;

    @Column(name = "dimensions")
    private String dimensions;

    @Column(name = "product_net_weight")
    private String productNetWeight;

    @Column(name = "plastic_bag_ind")
    private String plasticBagInd;

    @Column(name = "insertion_order")
    private Integer insertionOrder;

    @Column(name = "comments")
    private String comments;

    @Column(name = "item_unique_id")
    private String itemUniqueId;

    @Column(name = "itm_pak_qty")
    private Integer itemPackQuantity;
}

GroupingForm.java

@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Entity
@Table(name = "GroupingForm")
public class GroupingForm implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @GeneratedValue(generator = "uuid")
    @Column(name = "grouping_form_id",unique = true, nullable = false)
    private UUID groupingFormId;

    @Column(name = "grouping_form_name")
    private String groupingFormName;

    @Column(name = "vid")
    private String vid;

    @Column(name = "vendor_name")
    private String vendorName;

    @Column(name = "hovbu")
    private String hovbu;

    @Column(name = "fid")
    private String fid;

    @Column(name = "factory_name")
    private String factoryName;

    @Column(name = "item_count")
    private Integer itemCount;

    @CreationTimestamp
    @Column(name = "creation_date")
    private Timestamp creationDate;

    @Column(name = "created_by")
    private String createdBy;

    @UpdateTimestamp
    @Column(name = "modified_date")
    private Timestamp modifiedDate;

    @Column(name = "modified_by")
    private String modifiedBy;

    @Column(name = "product_engineer")
    private String productEngineer;

    @Column(name = "status")
    private String status;

    @Column(name = "sourcing_type")
    private String sourcingType;

    @Column(name = "total_comments")
    private Integer totalComments;

    @Column(name = "factory_name_chinese")
    private String factoryNameChinese;

    @Column(name = "grouping_form_type")
    private String groupingFormType;//to save as Product/transit/Product_transit

    @Column(name = "ref_id")
    private String refId;

    @JsonBackReference
    @OneToMany(mappedBy = "groupingForm", cascade = CascadeType.ALL)
    private List<ProductItemsMapping> productItems = new ArrayList<>();

    @JsonBackReference
    @OneToMany(mappedBy = "groupingForm", cascade = CascadeType.ALL)
    private List<TransitItemsMapping> transitItems = new ArrayList<>();
    
    @Column(name = "pdf_status")
    private String pdfStatus;

    public GroupingForm(UUID groupingFormId,String groupingFormName, String vid, String vendorName, String hovbu,
                        String fid, String factoryName, String status, String sourcingType, Integer totalComments,
                        Date creationDate, String createdBy, Date modifiedDate, String modifiedBy, String productEngineer,
                        Integer itemCount, String groupingFormType, String refId, String factoryNameChinese) {
        this.groupingFormId = groupingFormId;
        this.groupingFormName = groupingFormName;
        this.vid = vid;
        this.vendorName = vendorName;
        this.hovbu = hovbu;
        this.fid = fid;
        this.factoryName = factoryName;
        this.status = status;
        this.sourcingType = sourcingType;
        this.totalComments = totalComments;
        this.creationDate = creationDate!=null?new Timestamp(creationDate.getTime()):null;
        this.createdBy = createdBy;
        this.modifiedDate = modifiedDate!=null?new Timestamp(modifiedDate.getTime()):null;
        this.modifiedBy = modifiedBy;
        this.productEngineer = productEngineer;
        this.itemCount = itemCount;
        this.groupingFormType = groupingFormType;
        this.refId = refId;
        this.factoryNameChinese = factoryNameChinese;
    }
}

Service: methods which already annotated with @Transactional

private void updateTransitItem(GroupingCardsDto groupingCardsDto, GroupingForm groupingForm) {
        transitItemRepository.deleteByGroupingFormId(groupingCardsDto.getGroupingFormDto().getGroupingFormId());
        groupingFormService.saveTransitItems(groupingCardsDto.getGroupingFormDto(), groupingForm);
    }

when I am running eclipse in debug mode then my breakpoint is stuck in delete method. I am using PostgreSQL 9.6.24 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit version, and for pool connection Hikari-CP-3.2.0.

And If I let my debug running after long time (~45min) I am getting below error.

 marked as broken because of SQLSTATE(08006), ErrorCode(0)\norg.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

Thanks in advance.

CodePudding user response：

There are two possible reasons for this.

Either your delete statement actually takes a really long time, or it is stuck on a lock.

45 min, is certainly a lot for simple delete and could only be expected when you are working on huge amounts of data, like many millions of rows. Use the explain plan to validate that the expected indexes are used.

I consider locks the more likely reason for the problem. You'll need to check what locks are present and where they are coming from. This wiki page about lock monitoring in PostgreSQL seems to be a good starting point.