Why does MYSQL use blocking locks instead of optimistic attempts that fail immediately?
&quot;The client can always retry&quot; - 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.
&quot;The client has to wait .. in both cases&quot; 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.
&quot;committing changes in order&quot; - 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.