Default @Transactional in spring and the default lost update

Question

There is one big phenomena in the spring environment or I am terribly wrong. But the default spring @Transactional annotation is not ACID but only ACD lacking the isolation. That means that if you have the method:

@Transactional
public TheEntity updateEntity(TheEntity ent){
  TheEntity storedEntity = loadEntity(ent.getId());
  storedEntity.setData(ent.getData);
  return saveEntity(storedEntity);
}

What would happen if 2 threads enter with different planned updates. They both load the entity from the db, they both apply their own changes, then the first is saved and commit and when the second is saved and commit the first UPDATE IS LOST. Is that really the case? With the debugger it is working like that.

Answer 1

Losing data?

You're not losing data. Think of it like changing a variable in code.

int i = 0;
i = 5;
i = 10;

Did you "lose" the 5? Well, no, you replaced it.

Now, the tricky part that you alluded to with multi-threading is what if these two SQL updates happen at the same time?

From a pure update standpoint (forgetting the read), it's no different. Databases will use a lock to serialize the updates so one will still go before the other. The second one wins, naturally.

But, there is one danger here...

Update based on the current state

What if the update is conditional based on the current state?

public void updateEntity(UUID entityId) {
    Entity blah = getCurrentState(entityId);
    blah.setNumberOfUpdates(blah.getNumberOfUpdates() + 1);
    blah.save();
}

Now you have a problem of data loss because if two concurrent threads perform the read (getCurrentState), they will each add 1, arrive at the same number, and the second update will lose the increment of the previous one.

Solving it

There are two solutions.

1. Serializable isolation level - In most isolation levels, reads (selects) do not hold any exclusive locks and therefore do not block, regardless of whether they are in a transaction or not. Serializable will actually acquire and hold an exclusive lock for every row read, and only release those locks when the transaction commits or rolls back.
2. Perform the update in a single statement. - A single UPDATE statement should make this atomic for us, i.e. UPDATE entity SET number_of_updates = number_of_updates + 1 WHERE entity_id = ?.

Generally speaking, the latter is much more scalable. The more locks you hold and the longer you hold them, the more blocking you get and therefore less throughput.