Intro

Applications that we create are used by many end-users at the same time. It is called concurrent access. This may cause problems with data consistency - especially when it comes to saving data to the database. When two concurrent requests are made there is a risk that we lose our data and have inconsistent read operations. This is especially true when one user is updating the data and the other wants to read it at the same time. In today’s article, I would like to show you how to apply one pattern called Optimistic offline lock which can help us in such situations.

The shorter name of this pattern is optimistic locking. So if you find such a name somewhere on the internet, it is the same pattern.

As always all the examples will be in Python.

I would like to show you this with 3 different databases:

• MongoDB
• DynamoDB
• SQLite

What is optimistic offline lock

An optimistic offline lock pattern solves this problem by checking if changes that we want to add to our database are not in conflict with the changes introduced by someone/something else.

Successfully checking before sending data to the database means we can safely add our modifications.

We can do such verification by checking whether, since we fetched our data from the database, no one else has introduced any changes at that time.

The most popular implementation is adding the version property to our database record/document.

When we fetch our record/document the version value is saved locally in the memory together with other record data. Then, if we want to add our modifications, all we need to do is to check our local version value with the version value that is currently in the database. If the values are the same it means that we can apply our changes together with version value incremented.

I can imagine that the above description might make it tough to understand the wider context. Let me show you this using a diagram:

Lost Update problem

One of the concurrent access anomalies is a Lost Update problem.

It is when two users at the same time do the read operation and update operation on the application side in one cycle.

For example, let’s say we want to increase the wallet balance. Let’s assume that the initial wallet balance is 10. We have two concurrent requests to our application. The first one is retrieving the wallet from the DB and increasing the balance by 5. The wallet balance is now 15. The second one is also retrieving the wallet from the DB and increasing the balance by 10. The wallet balance is 20 which is incorrect because it should be 25.

This is how it looks using a diagram.

(I used SQL expressions but the DB engine does not matter. You can see it below in my examples)

Do you see the problem?

The optimistic offline lock pattern is a way to prevent such situations.

Examples

For this article, I have figured out the simple Wallet object, where we can increase and decrease the balance.

The requirements look like that:

The Wallet object is the one that we want to store in our database and protect using optimistic locking.

You can find the code with tests on my GitHub 🚀

https://github.com/szymon6927/szymonmiks.pl/tree/master/blog/examples/src/optimistic_locking

DynamoDB

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

class DynamoDBWalletRepository(IWalletRepository):
    ...

    def update(self, wallet: Wallet) -> None:
        try:
            update_expression = "set balance=:balance_value, version=:version_value"

            expression_attribute_values = {
                ":balance_value": wallet.balance,
                ":version_value": wallet.version + 1,
            }

            self._table.update_item(
                Key={"id": wallet.id, "created_at": wallet.created_at.isoformat()},
                UpdateExpression=update_expression,
                ExpressionAttributeValues=expression_attribute_values,  # type: ignore
                ConditionExpression=Attr("version").eq(wallet.version),
                ReturnValues="UPDATED_NEW",
            )
        except ClientError as error:
            if error.response["Error"]["Code"] == "ConditionalCheckFailedException":
                raise OptimisticLockingError(wallet.id)

            raise RepositoryError.update_operation_failed() from error

MongoDB

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

class MongoDBWalletRepository(IWalletRepository):
    ...

    def update(self, wallet: Wallet) -> None:
        try:
            result = self._collection.update_one(
                {"_id": wallet.id, "version": wallet.version},
                {"$set": {"balance": Decimal128(wallet.balance), "version": wallet.version + 1}},
            )

            if result.modified_count == 0:
                raise OptimisticLockingError(wallet.id)
        except PyMongoError as error:
            raise RepositoryError.update_operation_failed() from error

SQLite

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

class SQLiteWalletRepository(IWalletRepository):
    ...

    def update(self, wallet: Wallet) -> None:
        cursor = self._connection.cursor()

        sql = "UPDATE wallets SET balance = ?, version = ? WHERE id = ? AND version = ?"
        params = (str(wallet.balance), wallet.version + 1, wallet.id, wallet.version)

        try:
            result = cursor.execute(sql, params)

            if result.rowcount == 0:
                raise OptimisticLockingError(wallet.id)
        except sqlite3.Error as error:
            raise RepositoryError.update_operation_failed() from error

And this is what the example test for DynamoDB looks like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

def test_optimistic_locking_works(wallet_dynamodb_table_mock: Table, wallet: Wallet) -> None:
    # given
    repository = DynamoDBWalletRepository(wallet_dynamodb_table_mock)
    repository.create(wallet)

    # when
    wallet = repository.get(wallet.id)

    # and
    someone_modified_the_wallet_in_the_meantime(repository, wallet.id)

    # and
    wallet.increase_balance(Decimal(52), Currency.GBP)

    # then
    with pytest.raises(OptimisticLockingError):
        repository.update(wallet)

I encourage you to check the entire code on my GitHub. In case you have any questions, please let me know 😉

Summary

An optimistic offline lock pattern is not a silver bullet. It will not solve all the problems related to the concurrent data access, but in comparison to other patterns, it is quite easy to implement and maintain. In my opinion, it should be the default solution when it comes to concurrent data access.

I would like to mention other patterns/solutions that you can use if you feel that optimistic locking is not enough for you:

• Pessimistic offline lock
• Atomic writes
• Setting the appropriate level of transaction isolation level (for SQL databases)
• Coarse-Grained Lock

Appendix

Here is a short side note for you. From my experience, many of these problems may be solved through a discussion with the business experts. Problems with concurrent data access are not always a technical issue. It may be a problem with how we understand and implement the business logic. So please do not be scared. Ask the business people questions.