Why isn't optimistic concurrency control more commonly supported at the database layer? It is a valid alternative and can completely do away with any blocking locks. For certain usage patterns, this will offer complete safety with better performance.
The primary argument for OCC is: When a SELECT or UPDATE blocks on a lock, there is no guarantee that it will return successfully and failure/success has to be handled at the application layer. Instead of forcing the application to wait for the timeout period before giving the result, we provide the result immediately and let the application figure out how it wants to handle the failure.
There are use cases where this approach will primarily cause issues like starvation (Primarily with large writes, or highly contentious changes). However, in a lot of standard cases (such as building a blog/forum/maybe even a bank), contentions will be rare and seem like they'll be better dealt with by the optimistic pattern.
What am I missing?
I have attempted to rephrase my question with better terminology to clarify the question a little bit more. Below is the question as it was originally asked. Feel free to remove it since it is there in the history as well.
Essentially, I am trying to understand the reasoning for why conflicting changes are blocked in MySQL instead of failing them immediately. At first glance, blocking doesn't seem to provide much benefit over failing immediately:
- The client can always retry if it is important to commit the data
- The client has to wait till any conflicts are resolved in both cases
Overall, it seems to me like a system that involves no blocking locks, purely relying on atomically committing changes in order, while failing those that conflict with other committed transactions would fare better in terms of performance. The client will have to implement retries and this could be non-ideal for large transactions, but that could be a special case in which we switch to blocking mode.
Any thoughts and explanations as to what I'm missing here?