In the context of a multiuser database desktop application, the concurrency problem has to be considered.
Many articles focus on two models: optimistic and pessimistic locking.
In pessimistic locking you expect a concurrent access to a record and so you lock the resource to prevent others to access it while it's being updated.
In optimistic locking you considert the possibility of a concurrent update as an unlikely event and so you design the application not to lock the resource. Since this may lead to data loss deriving from unmanaged access, you implement a mechanism based on token-fields or timestamps or revision numbers or whatsoever in order to detect the conflict and to raise an error if it happens.
Design a system for detecting concurrency conflicts in a systematic way, (that is on most or on all the tables of a database) might not be a difficult task, but not even a trivial one, and usually is accomplished by the ORM.
Yet this is only half of the story because when a conflict is detected, there should be a resolution.
In many articles seems to emerge the idea that 'since it's remote', then this resolution can be somehow semplified, let's say just show a message the user that a conflicted occured and she needs to reload the data. But what about a message like 'Another user has changed the data, do you want to overwrite them or reload?'? Without providing any information on which data has been changed it would be a bit ridicolous.
In a full-blown implementation differences should be hightlighted. The GUI should host a sort of on-the-fly comparison. I haven't seen many of these implementations so far. It seems quite challanging as an implementation, not to mention the need for testing. Also implementing a simplified version of this comparison that considers only the most meaningful fields seems not an easy task and might be trivial only for the most basic cases. Yet most of the articles on optimistic locking gloss over this point.
On the side of optimistic locking we have a 'strategy' that forces you to chose between two equally unsatifying options. One is "correctness at a higher cost", a cost that is not proportionated to likelyhood of the event. The other option is a 'partial', over simplified implementation.
But then, if the event is really so remote, doesn't it make much more sense to use pessimistic locking? At least for desktop applications? In the most basic form it has a quite simple implementation. The cost in terms of implementation would return to be proportioned to the likelyhood of the event. The cost in terms of inefficiency (no lock is always faster than locking) would the same be low because of the slow-rate of the event.
It is very difficult for me to go on with this reasoning because it seems to go against all the more recent practices. Entity Framework and Entity Framework Corecore do support Optimistic locking out-of-teh-box, while pessimistic locking is not natively supported and requires database-specific sql. Optimistic locking is gaining ground all over. Is there something important that I am not considering?
Is there any good article on the design of a lock system, not only on how orm-optimistic locking or db-pessimistic locking works?