Data inconsistancy: Parent table is not updated while Child table is updated

ghz 1years ago ⋅ 2558 views

Question

I have two tables: Customer (parent) and Orders (child). Both are mapped by
customerId (customer_id :unique key, in both parent & child).

In code in some Impl class, I'm saving data after setting fields in entities,

...

String status = someObject.getStatus();
customer.setLastPurchageStatus(status);
order.setStatus(status);
....

and saving it with built-in 'save' as:

try {
   LOGGER.info("Customer status:{}, Order statu:{}", customer.getLastPurchageStatus(), customer.getOrder().getStatus());
   customerRepo.save(customer);
} catch(Exception e) {
  LOGGER.info(e);
}

The above LOGGER.info line logged same status, but when seen in tables after 5 min, Customer table was with old values while Order table was updated with corrected value. This behavior happens only for 5-6% of transactions. I did not see any exceptions in Logs.
Entities are like this:

Customer

@TypeDefs({ @TypeDef(name = "json", typeClass = JsonStringType.class), })
@Entity
@Table(name = "customer")
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties(ignoreUnknown = true)
public class Customer implements java.io.Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -395898276179285945L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "customer_seq")
    private long customerSeq;
    @Column(name = "customer_id")
    private String customerId;
    @Column(name = "last_purchase_status")
    private String lastPurchageStatus;
   

    @JsonManagedReference
    @OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.EAGER, mappedBy = "Customer")
    private Set<Order> order;

Order

@Entity
@Table(name = "order")
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties(ignoreUnknown = true)
public class Order implements java.io.Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -4250363507869746908L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "order_seq")
    private String orderSeq;
    @Column(name = "order_id")
    private String orderId;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_seq", referencedColumnName = "customer_seq")
    @JsonBackReference
    private Customer customer;
    @Column(name = "customer_id")
    private String customerId;
    @Column(name = "status")
    private String status;
   
    .......

    ...other getters/setters
    
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_seq", referencedColumnName = "customer_seq")
    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }
   
    .....

Any help ??

Answer

The effect you might see here is called the "lost update" phenomenon, which you can protect yourself from by using optimistic locking with a @Version Long version field. Read up on the topic on the internet and you will probably see very fast how a concurrent transaction that updates customer rows could cause this.

#spring-boot