Optimistic locking and automatic retry



In this post, I’m answering a reader’s question about how to combine an optimistic locking and automatic retry.

Optimistic locking

Here’s the question itself (paraphrased):

Consider optimistic locking on the database level. When someone changes the data while the current operation is running, I need to retrieve that data again and retry the operation. What’s the best way to implement the automatic retry?

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:

Optimistic locking

Optimistic locking

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.

Pessimistic locking

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.

Optimistic locking and automatic retry

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.

Summary

  • Optimistic locking is when you check if the record was updated by someone else before you commit the transaction.
  • Pessimistic locking is when you take an exclusive lock so that no one else can start modifying the record.
  • You cannot combine optimistic locking with the automatic retry. An optimistic locking assumes a manual intervention in order to decide whether to proceed with the update.
  • If you don’t care about the previous updates applied to the record, don’t implement any locking strategy.
Share




  • Amir Shitrit

    What if a user before me updated a certain field of the row, and I tried to update a different column and the two do not collide nor do they have to be consistent with each other. Can I retry then by re-applying the change that I care about on the modified row?
    For example, one user changed a Meeting’s location and I tried to reschedule it.
    The two shouldn’t necessarily affect each other.
    Or, would it make more sense that to split those fields to different entities and version them separately?
    Meaning, must all entities/value objects of an aggregate be consistent with each other?

    • http://enterprisecraftsmanship.com/ Vladimir Khorikov

      You don’t necessarily have to split those fields into separate entities. It really depends on the context. In the situation you described, it seems like it’s fine to do the automatic merge. So yeah, if you are sure that changing some fields cannot potentially bring inconsistency in any way, you can go ahead and allow the retry.

  • Art Gorr

    Another excellent article. If the database has a last modified timestamp for each record (perhaps for audit reasons) – what do you think of using that for the version vs an additional integer column? Ultimately would like to use this value on an E-Tag header in an http GET response, and If-Match header on PUT request. Any advice on “best” version to use in that use case?

    • http://enterprisecraftsmanship.com/ Vladimir Khorikov

      Thank you. Timestamp would work here too. If you already have one, there’s no need in introducing an additional integer column. Regarding E-Tags, I don’t see any difference in using one version column over another. Integers are good for human readability. Other than that, timestamps should work just as good. Also, databases keep track of timestamps automatically on each record update, so they are easier to implement than integers.

      • Art Gorr

        Thank you for the reply. Appreciated. You confirmed my thinking about this.

  • http://alexzaytsev.me hazzik

    > Instead, you could compare all fields in the Product table on each change… ORMs don’t support it

    Actually ORMs DO support that.

    • http://enterprisecraftsmanship.com/ Vladimir Khorikov

      Didn’t know about that. How can I use this feature?

  • Andriy

    Some databases support a feature, when transaction 1 attemps to read data, and transaction 2 writes the same data at the same time, so transaction 1 receives the new, updated version of that data without even reading the disc twice

  • Hristo Yankov

    So, with Optimistic Locking, we’d:
    1. Get the record from the db
    2. Do whatever with it
    3. Retrieve the record again, from the db
    4. Compare the original record with the one from #3
    5. Only if it has not been changed – update in the db

    Steps #3, #4 and #5 must happen in a (C#) lock(…) { }, though, otherwise someone could update the record between your steps #3 and #5.

    • http://enterprisecraftsmanship.com/ Vladimir Khorikov

      That’s not how Optimistic Locking is usually implemented because, as you noted, that would require additional locking in the application code. Instead, you can insert an additional WHERE statement to the SQL query and then check how many rows are updated. For example:

      Without Optimistic Locking:
      UPDATE dbo.[User]
      SET Name = “New name”
      WHERE UserID = 1

      With Optimistic Locking:
      UPDATE dbo.[User]
      SET Name = “New name”, Version = 2
      WHERE UserID = 1 AND Version = 1

      If the version is changed by someone before you, no rows will be updated, ROW_COUNT will return 0. You can check for that row count and if it’s indeed 0, throw an exception.

      • Hristo Yankov

        Thanks for that important clarification, I have completely missed the point!

        I am catching up with your blog, great stuff.