Approaches to handling concurrent access at the application level

This question is really language- and framework-neutral, but since we’re here, and I’m using ZF3 + Doctrine:

I have a situation where we want to avoid the lost update problem – e.g., Bob loads /entities/edit/123 at the same time as Alice, and Bob changes foo to bar, and Alice changes baz to bat, and Bob hits save, and then Alice hits save and blows away Bob’s update.

Wait – come to think of it, I believe (having stared at a lot of SQL logs) that under the hood Doctrine is clever enough to only do UPDATE entity SET foo = ‘bar’ and leave the rest untouched. True? So in the above case this wouldn’t be a problem. So let’s suppose they edited the same attribute and you have a conflict. (Or let’s suppose you’re lazy like me and really don’t want the task of keeping track of who modified what.)

What I have been doing is a technique I learned a long time ago: your entity has a last_modified attribute, you load its value into the form as a hidden field, and at update time you fetch it again and compare. If it’s the same, carry on; if not, abort the UPDATE with a message saying sorry, it seems another user/process has modified this record in the time since you loaded it, please reload and try again. And I think I’ve used apps that do this very thing.

(I’ve read that people sometimes also have a version column that they increment with each update and use that instead of a timestamp, but the concept is the same.)

Now, I have a user complaining that when this happens, it is too great an inconvenience, and that instead what I should do is track who is currently editing what, and if an edit is in progress on entity #123, don’t let anyone else load /entities/edit/123, but give them a message instead saying somebody else is working on this, please wait (or go talk to her/him and say get on with it). So I set about reading about how to implement this, and it’s rather onerous, and raises some potential problems of its own.

So, the question: in general terms, how do you usually handle this, and what do you suggest?

Maybe before saving changes you can compare if the time the column was last updated has changed since you loaded the page and if so let the user know that changes have been made by someone else and let them choose to overwrite them or not?

Wait – come to think of it, I believe (having stared at a lot of SQL logs) that under the hood Doctrine is clever enough to only do UPDATE entity SET foo = ‘bar’ and leave the rest untouched. True?

False. There is no guarantee that the ORM will do that, and in general it always does a full record update in order to avoid re-generating the UPDATE statement.

In general, you want to look into two concepts:

  • optimistic locking
  • pessimistic locking

Both are documented in generic DB-related tools, and also in the Doctrine documentation.

Yes, I thought of that too. Give them the choice whether to blow away someone else’s effort. Mmm, not sure I like that.

OK, when I look at the query log, I see only UPDATE thing SET foo = 'bar' when they only modified foo. Why is that?

And I have done some reading about pessimistic/optimistic and it appears – unless I misunderstood or was misinformed – my current approach is optimistic. I optimistically assume it will not be a problem in most cases (in practice, it’s rarely a problem), and there’s a strategy to fall back on when there is a conflict.

[UPDATE] i’m reading https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/transactions-and-concurrency.html#locking-support and it is excellent reading. Thank you.

Persistence is handled by the “persister” API at https://github.com/doctrine/orm/tree/434820973cadf2da2d66e7184be370084cc32ca8/lib/Doctrine/ORM/Persisters/Entity

Depending on implementation of persister (this is all internal ORM API), the generated SQL statements may vary greatly.

As of the locking, “optimistically assuming” is OK, but I’m talking about locking, which is much stricter :wink:

Persistence is handled by the “persister” API at https://github.com/doctrine/orm/tree/434820973cadf2da2d66e7184be370084cc32ca8/lib/Doctrine/ORM/Persisters/Entity

Ah, nothing like a little light reading for my lunch break. :grinning:

I have some more studying and experimenting to do. The idea of displaying the conflicts to the user and letting them work it out — pick and choose what changes to apply – will take some work but does make sense, especially given that Alice and Bob have equal privileges to edit the entity. One is always free to hack up the other’s work.

It is almost like manually resolving a conflict in git. You would just have to build an interface intuitive enough for the user to manually merge the changes.