the issue is that with FOR UPDATE .. and ultra paranoid isolation levels you can get locks on things that exist, but its not possible to necessarily get a LOCK on things that are just being inserted or altered to suddenly fall in your "range" or worse yet that get appended to your "range". some RDBMS do support range locks (like if I do WHERE foo BETWEEN 1 AND 100), but not all do .. and IIRC you do not really have much control over those (its simply a LOCK escalation strategy RDBMS may use to reduce the number of row level locks). this means in many situations fetching data with a FOR UPDATE even, might not be enough to ensure consistency when you do rights later on.
i have not done a code review of D2 .. actually i have spend very little time looking at any of the code, but from the questions and comments i have seen, there doesnt seem to be enough emphasis. for example i also saw code for the slug extension. which again employs the model of fetching all potential collisions and then doing an insert on the fetched data, without ensuring that between fetching and inserting there are no new collisions. stuff like this should try harder to do things in a single commit if possible, or getting the necessary locks.
locking is non trivial business and so expecting end users to get this right is a bad move imho.
but aside from any of the internal sql, i would also urge to ensure that some of the things i have been asking for (an efficient way to determine the affected tables and if they need to be locked at the start of a flush) a solid way to mark specific models or all model instances as dirty to ensure that users can either choose to reload certain dirty models at once or automatically when they are accessed again (see
DDC-343) is really possible.
at the same time i acknowledge that i am just pointing at theoretical problems, without a promise to do the actual analysis and fixing. its just that this month i have promised people to spend my spare time on other stuff. next month might be more doable, i am traveling a lot .. where i can hopefully spend some time reviewing, then again traveling means that providing feedback and discussing things is more difficult.