Database Administrators Asked by Dark Nebula on December 6, 2021
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:
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?
"The client can always retry" - yes it can. However, the transaction could have performed an arbitrarily large amount of work before the read conflict was detected, which would all have to be re-done on a re-try with no guarantee of success the second time. It may even face starvation if other transactions again get to the locked resource before it does. By blocking instead of failing the transaction goes into a queue for the shared resource, guaranteeing it will eventually get to make progress.
"The client has to wait .. in both cases" Let's consider two scenarios where the other transaction that is locking the desired resource is either very short-lived or very long-lived. In the former the lock is removed quickly and if our transaction is blocked we can quickly progress once more. Had we failed we would have had to re-do all our existing work (which may have been little or lots, who knows) before we could progress. With a long-lived blocker we may get back to requesting the lock only to find it is still held. Then we fail again and repeat. Now we're in a busy-wait loop, burning system resources but making no progress. Or we may get back to requesting the resource to find it is free. Then we can make progress, but are no better off than had we blocked and queued.
"committing changes in order" - what order? When they're submitted? Then my really fast one-row write has to wait behind your 4TB bulk load even though there are no rows in common? That's not good for latency.
There are a class of protocols called multi-valued concurrency control dealing with these scenarios. They excel at some workloads and suffer under others. I enjoyed the Carngie Mellon series on this, lectures 3-5 specifically.
Answered by Michael Green on December 6, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP