JPA: Locking a parent object to prevent multiple children being created

Question

I am using JPA with EclipseLink and MySQL to manage my data and I want to prevent my application creating two child objects that are not compatible.

My current logic which does not work looks like this.

1. Start transaction with REPEATABLE_READ isolation.
2. Fetch and lock Account object using a PESSIMISTIC_WRITE lock.
3. Read existing Address objects from returned Account object using a @OneToMany field.
4. Ensure no two addresses are marked as Primary - update an existing one if the new Address is Primary.
5. Add the new Address object to the Account object.
6. Complete the transaction.

However after this, if two requests are made within a few milliseconds of each other, I end up with two addresses marked as Primary which is not what I expected.

I wonder whether I need to change my isolation level to SERIALIZABLE but I'm concerned about the impact this might have on my throughput for unrelated Accounts.

Answer 1

It seems that this is the "lost update" situation which SERIALIZABLE isolation is designed to avoid.

REPEATABLE_READ is designed to ensure that multiple reads in a transaction will return the values from the start of that transaction, so the second transaction doesn't see the UPDATE performed by the first transaction. It only sees the version of rows in the Address table as they were when transaction 2 started (before the update was committed by transaction 1).

I could use READ_COMMITTED isolation level, so transaction 2 does a SELECT on the Address table and sees the most recent committed change, however that would allow queries in a single transaction to read inconsistent data.

I would like JPA to lock on the query for addresses but it seems that's not allowed because the authors have incorrectly (in my opinion) decided that locking the parent should be sufficient.

So instead I will either be using SERIALIZABLE isolation level or QueryHints.LEFT_FETCH set to a.addresses so that the addressses are queried and lockeed during the account fetch.