optimistic locking vs pessimistic locking

Question

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.

Question

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?

Answer 1

If collisions are so rare that optimistic locking is a serious option, even if your collision resolution is a bit clumsy, how would that play a role if it happens once a year or less? However, if you are afraid that this will happen 30 times a day, then optimistic locking is probably not even an option for your use case. As even if you can offer a super simple, super effective conflict resolution, even that resolution will take some time and during that time the data may get changed again by someone else, so you might run from one resolution to the next one without the intermediate state ever hitting the database.

Optimistic locking is also not suitable for all kind of data to begin with, and its main advantage is parallelization. If one user always touches fields A, B, and C and another always touches C, D and E, yet field C is touched rarely at all, then pessimistic locking means user one cannot edit field A while user two holds the lock to edit field D. But why? They could both edit their fields at the same time, this won't conflict. So user two slows down the work of user one for no reason. Yet if there is just one data field or a set of fields that always must be touched together, then there cannot be parallelization.

So think of version control systems: As long as developers edit different files or different parts of the same file, everything will be okay. Only if they touch the same parts of the same file, there will be a conflict and then this conflict must be resolved (choose your change, choose the other change, or merge both changes by hand). For a version control system pessimistic locking would lead to poor performance as either only one developer can work on a project at a time or only one developer can touch a certain file a time.

Parallelization with pessimistic locking can only do well if data can be broken into chunks, chunks can be locked individually and it's unlikely to users want to touch the same chunk at the same time; yet optimistic locking that requires huge data loss chunks are huge and a meaningful merge is not possible is not an option either.

Answer 2

Both locking strategies have their costs.

Optimistic locking is more expensive when considering the functional requirements (endless loop danger, showing data history, consolidation logic)

Pessimistic locking is more expensive from a technical point of view (time-outs, nested locks, distributed locks, lock granularity)

Either option running into an issue is a pain to the user and the developer.

A careful assessment of the functional flow and of the risk (probability * impact) should give some guidance on what the most fitting locking strategy for a particular problem is.

Answer 3

The real solution to locking whilst entering data is to simply design out the need for locking at all.

For instance if an Employee table has fields for password hash, address details and current salary, then there should be three independent screens for updating these details. Each screen should talk to the database updating only the subset of fields it controls If your ORM does not support this then use a better ORM.

So now if you have two users trying to update the same address details, then the problem is at the business level. A simple 'last update wins' strategy is usually sufficient.

There will always be some situations where optimistic or pessimistic locking is required, but these should be rare.