2

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?

Improve this question

1 Answer 1

Reset to default
1

"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.

Improve this answer
3
  • I do agree with you that there are cases where my approach (I believe it's called optimistic concurrency control) will not be favorable. Essentially, places where there are lot of conflicting operations going on. However, there are also a lot of use cases where conflicts are rare and locking rows/tables degrades performance with very little benefit. I am mostly curious why this approach is not supported out of the box by databases.
    – Dark Nebula
    Jul 23, 2020 at 15:19
  • As to your question about "committing changes in order", I am probably thinking of "commit order". Essentially, if we think of each change as a transaction that does some READs and WRITEs, what matters is the time at which commit is called. At that time, the database will check that all of our READs and WRITEs are consistent, and if so apply the change. If not, it'll fail the transaction and we'll start anew. This will be ideal for cases where conflict is rare but lot of transactions "lock" the same rows.
    – Dark Nebula
    Jul 23, 2020 at 15:21
  • Refer to this answer that gets close to what I'm trying to convey: stackoverflow.com/a/38494179/13759092. The only difference is that I'm hoping to follow a similar pattern at the database level with no blocking at all.
    – Dark Nebula
    Jul 23, 2020 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.