MySQL exclusive lock not appearing to work for new child rows

Question

I have some logic in my application which I think results in the following MySQL calls, however when I get two of these run within a couple of milliseconds, I get two incompatible child rows.

1. Start transaction with Repeatable Read isolation.
2. Fetch and lock Account object SELECT ... FROM Account WHERE id = ? FOR UPDATE
3. Read existing Addressees linked to the Account SELECT ... FROM Address WHERE account_id = ?
4. If existing address is marked as primary, update it. UPDATE Addresses SET primary = false WHERE id = ?
5. Insert new address with primary = true.
6. Complete transaction.

If this process is run twice quickly, I get two Address rows with primary = true and account_id set to the account which I was surprised by because I thought that locking the account in step 2 would prevent multiple transactions from running simultaneously. I don't want to change the behaviour in a way that would limit my throughput too much.

I wondered if I need to switch the isolation level to "Serializable" [sic] but I'm not clear whether a "FOR SHARE" read on step 3 would actually fix the issue.

My data structure is likely obvious from the above but looks like this:

Account:

id PK

other irrelevant account data

Address:

id PK,

account_id - not an actual foreign key, just an id that matches the Accout PK.

primary - boolean, should be a maximum of one primary address per account but this is not enforced in MySQL because it would complicate the DB a little to have a generated column to enable this since MySQL doesn't support partial indexes.

other irrelevant address data

Answer 1

What's happening is that the second transaction doesn't see the UPDATE performed by the first transaction, because of REPEATABLE READ. 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).

This is known as a "lost update." Some rows were updated by transaction 1, but transaction 2 can't see them, and believed it was free to update the rows again. So it does its update, and never sees transaction 1's change.

To solve this, you could use READ COMMITTED isolation level, so transaction 2 does a SELECT on the Address table and sees the most recent committed change.

Alternatively, you could make transaction 2's SELECT on the Address table a locking query. Either SELECT FOR UPDATE or SELECT FOR SHARE would work for this. The way InnoDB implements locking reads, they always view the most recently committed version of a row, as if you had used READ COMMITTED isolation level (even if the transaction was started as REPEATABLE READ).

Answer 2

Your assumption is correct, the second transaction will be blocked at the first statement

SELECT ... FROM Account WHERE id = ? FOR UPDATE

If you see concurrent updates to "Address" table then you are:

• a) not actually starting a transaction before these statements
• b) there are other processes or procedures that update "Address" table without locking an account first
• c) You have a bug in your logic in steps 3) and 4) that somehow allow more than one primary address.

Your accepted answer by Bill is wrong.