Optimistic locking and automatic retry

Here’s the question itself (paraphrased):

To answer this question, let’s step back for a moment and talk about what optimistic locking is.

Optimistic locking is a way to manage concurrency in multi-user scenarios. You generally want to avoid situations when one user overrides changes made by another user without even looking at them. Locking - optimistic locking in particular - is a way to do that.

Note that there’s nothing wrong in the process of overriding data per se, it’s just you need to make sure that it’s a conscious decision and the users do that having full information about changes made previously.

The general idea is this:

Each table you want to implement concurrent access to need a new column: Version. This column is usually an integer or a timestamp. Every time a record in the table changes, its version changes with it.

This way we make sure that the application works with the latest version of that record and avoid the "last transaction wins" situation: when the transaction committed last overrides whatever modifications made before. Now if two users update the record simultaneously, only one of them gets their changes accepted. The other user receives an error message because his record’s version doesn’t match the version stored in the database.

Also note that in order to implement optimistic locking, you don’t necessarily have to introduce a version column. Instead, you could compare all fields in the Product table on each change. I would discourage from using this implementation, though. ORMs don’t support it (they do, see this link) and even if they would, the generated SQL code would be messy and slow.

And of course, let me tell a couple words about the other locking strategy: pessimistic locking. It’s when you maintain an exclusive lock while editing a record. For the end user, it looks like they can’t start editing a product until the person who’s editing it currently releases the lock.

Most relational databases support pessimistic locking out of the box but you can also implement it yourself in the application code.

When it comes to choosing between optimistic and pessimistic locking, I would say go with the former by default. Pessimistic locking is useful when the cost of merging simultaneous changes is high. Which is not the case in the vast majority of domains. Pessimistic locking is too cumbersome to implement and too annoying for the users, so it doesn’t worth it in most cases.

Alright, back to the original question. So, how to combine optimistic locking and automatic retry? In other words, when the application gets an error from the database saying that the versions of a Product don’t match, how to retry the same operation again?

The short answer is: nohow. You don’t want to do that because it defeats the very purpose of having an optimistic lock in the first place.

Remember that the locking mechanism is a way to ensure that all changes are taken into consideration when changing a record in the database. In other words, someone should review the new version of the record and make an informed decision as to whether they still want to submit the update. And that should be the same client who originated the initial request, you can’t make that decision for them.

At this point, you might ask: what if the user doesn’t care if there are any modifications applied to the same record before them? Can we make the automatic retry in this case?

The answer is: also, no. If the user is fine with overriding other users' changes, just don’t implement the locking. There’s no need in optimistic or pessimistic concurrency strategies in this case. The default "last transaction wins" mode will suffice here.

Sometimes, you can automate the merge process. For example, you can proceed with the retry if the database’s snapshot of the record consists of the same data as yours despite the different version values. In most cases, however, you do want the client to intervene. They should re-validate their input taking into account the changes made since the last data retrieval and decide if they still want to update the record.

So when the automatic retries are applicable then? Those are for transient failures only: failures that can be resolved automatically. An example here is a database that goes offline or an API that is unreachable. In this case, you can create a simple helper method which would try to perform the same action for a set number of times.