洗掉通話在多對一關系中永久卡住-有解無憂

嘗試對多對一關系運行洗掉查詢。但是有時當行洗掉的計數超過?50時，它會卡住一段時間。

存盤庫：

@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);
}

域：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;
    }
}

服務：已經用 @Transactional 注釋的方法

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

當我在除錯模式下運行 Eclipse 時，我的斷點卡在洗掉方法中。我 在 x86_64-pc-linux-gnu 上使用 PostgreSQL 9.6.24，由 Debian clang 版本 12.0.1 編譯，64 位 版本，用于池連接 Hikari-CP-3.2.0。

如果我讓我的除錯在很長一段時間（~45 分鐘）后運行，我就會遇到錯誤。

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

提前致謝。

uj5u.com熱心網友回復：

這有兩個可能的原因。

您的洗掉陳述句實際上需要很長時間，或者它被卡在鎖上。

45 分鐘，對于簡單的洗掉來說肯定是很多，而且只有在處理大量資料（如數百萬行）時才能預期。使用解釋計劃來驗證是否使用了預期的索引。

我認為鎖是問題的更可能原因。您需要檢查存在哪些鎖以及它們來自何處。This wiki page about lock monitoring in PostgreSQL似乎是一個很好的起點。

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/478884.html

標籤：爪哇 PostgreSQL 弹簧靴弹簧数据-jpa

上一篇：如何在沒有鎖的情況下同時更新PostgreSQL13表行

下一篇：Postgresql根據條件選擇