@OnetoOne Cascade in eclipselink perform multiple updates overrides updated value to existing in DB

Question

Below is the parent table dto which has requestStatus as IN_PROGRESS which gets updated to DONE on updating child table as below:

@OneToOne(cascade = CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="vendor", targetEntity=StatusDTO.class)
    private IStatus serviceabilityStatus;

@Column(name="REQUEST_STATUS", length=15)
    private String requestStatus;

Below is the child table dto which insert record and update requestStatus to DONE in parent table dto

@OneToOne(cascade={CascadeType.PERSIST,CascadeType.MERGE},fetch=FetchType.EAGER,targetEntity=VendorDTO.class)
    @JoinColumn(name="SERVICEABILITY_KEY", unique=true)
    private IVendor vendor;

We checked when running for 100 products, for each product while saving child table, it updates parent table requestStatus to Done and again it updates to IN_PROGRESS from another thread. So, final status is like out of 100, around 50 are marked as Done and remaining as InProgress.

We tried using flush and clear entitymanager but issue still persist.

Answer 1

Apps that concurrently modify the same data in multiple processes are bound to overwrite each other. Database offer pessimistic locking, allowing operations to lock a row until it gets a chance to modify it, preventing anyone from changing it out from under the process, but this is frowned upon and used only as a last resort as it is expensive and difficult to manage, requiring more statements to obtain locks, and processes left waiting until they obtain them.

What instead is usually done is optimistic locking. This allows data to be read in without waiting. On writes the version is checked and incremented, and an exception occurs if it isn't what is expected. This allows detecting when another process has updated the data since you last read it. Your process would need to catch the exception, re-read/refresh its data and try again if necessary. This is optimistic because it is an after the fact check; optimistic in its assumption that it is less expensive to deal with when it occurs then to pessimistically lock everything to prevent.

See https://en.wikibooks.org/wiki/Java_Persistence/Locking#Optimistic_Locking, https://www.baeldung.com/jpa-optimistic-locking and https://www.eclipse.org/eclipselink/documentation/2.6/concepts/cache004.htm for discussions on the topic.

Your application is also cascading the merge from child to its reference parent, increasing the risk of the stale data overwrites; for instance, if a process is only dependent upon and only intends to update a child entry, merging in the parent as well increases the risk that it might be stale. A process that only affects a child data should only merge in the child instance, so choose your cascade options on relationships carefully.

Version locking requires some form of version field in the object that can be incremented or moved forward, but there are other options. EclipseLink has a number of field locking options that can have it check the changed fields, all fields or even selective ones against what is in the database. See https://www.eclipse.org/eclipselink/documentation/2.7/concepts/descriptors002.htm#CIHCFEIB for details