Optimistic locking in Peewee ORM


In this post I'll share a simple code snippet you can use to perform optimistic locking when updating model instances. I've intentionally avoided providing an implementation for this in peewee, because I don't believe it will be easy to find a one-size-fits-all approach to versioning and conflict resolution. I've updated the documentation to include the sample implementation provided here, however.

What is optimistic locking?

Before defining optimistic locking, I'll first describe it's opposite, pessimistic locking. Suppose you need to update a record in your database, but you cannot do so atomically -- you'll have to read the record, then save it in a separate step. How do you ensure that, in a concurrent environment, another thread doesn't sneak in and modify the row between the read and update steps?

The answer depends on the database you are using, but in Postgres you would issue a SELECT ... FOR UPDATE query. The FOR UPDATE locks the row for the duration of your transaction, effectively preventing any other thread from making changes while you hold the lock. SQLite does not support FOR UPDATE because writes lock the entire database, making row-level (or even table-level) locks impossible. Instead, you would begin a transaction in IMMEDIATE or EXCLUSIVE mode before performing your read. This would ensure that no other thread could write to the database during your transaction.

This type of locking is problematic for, what I hope are, obvious reasons. It limits concurrency, and for SQLite the situation is much worse because no write whatsoever can occur while you hold the lock. This is why optimistic locking can be such a useful tool.

Optimistic locking

Unlike pessimistic locking, optimistic locking does not acquire any special locks when the row is being read or updated. Instead, optimistic locking takes advantage of the database's ability to perform atomic operations. An atomic operation is one that happens all at once, so there is no possibility of a conflict if multiple threads are hammering away at the database.

One simple way to implement optimistic locking is to add a version field to your table. When a new row is inserted, it starts out at version 1. Subsequent updates will atomically increment the version, and by comparing the version we read with the version currently stored in the database, we can determine whether or not the row has been modified by another thread.


Here's the code for the example implementation included in the documentation:

from peewee import *

class ConflictDetectedException(Exception): pass

class BaseVersionedModel(Model):
    version = IntegerField(default=1, index=True)

    def save_optimistic(self):
        if not self.id:
            # This is a new record, so the default logic is to perform an
            # INSERT. Ideally your model would also have a unique
            # constraint that made it impossible for two INSERTs to happen
            # at the same time.
            return self.save()

        # Update any data that has changed and bump the version counter.
        field_data = dict(self._data)
        current_version = field_data.pop('version', 1)
        field_data = self._prune_fields(field_data, self.dirty_fields)
        if not field_data:
            raise ValueError('No changes have been made.')

        ModelClass = type(self)
        field_data['version'] = ModelClass.version + 1  # Atomic increment.

        query = ModelClass.update(**field_data).where(
            (ModelClass.version == current_version) &
            (ModelClass.id == self.id))
        if query.execute() == 0:
            # No rows were updated, indicating another process has saved
            # a new version. How you handle this situation is up to you,
            # but for simplicity I'm just raising an exception.
            raise ConflictDetectedException()
            # Increment local version to match what is now in the db.
            self.version += 1
            return True

Here’s a contrived example to illustrate how this code works. Let’s assume we have the following model definition. Note that there’s a unique constraint on the username – this is important as it provides a way to prevent double-inserts, which the BaseVersionedModel cannot handle (since inserted rows have no version to compare against).

class User(BaseVersionedModel):
    username = CharField(unique=True)
    favorite_animal = CharField()

We'll load these up in the interactive shell and do some select + update operations to show the code in action.

Example usage

To begin, we'll create a new User instance and save it. After the save, you can look and see that the version is 1.

>>> u = User(username='charlie', favorite_animal='cat')
>>> u.save_optimistic()
>>> u.version

If we immediately try and call save_optimistic() again, we'll receive an error indicating that no changes were made. This logic is completely optional, I thought I'd include it just to forestall any questions about how to implement it:

>>> u.save_optimistic()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "x.py", line 18, in save_optimistic
    raise ValueError('No changes have been made.')
ValueError: No changes have been made.

Now if we make a change to the user's favorite animal and save, we'll see that it works and the version is now increased to 2:

>>> u.favorite_animal = 'kitten'
>>> u.save_optimistic()
>>> u.version

To simulate a second thread coming in and saving a change, we'll just fetch a separate instance of the model, make a change, and save, bumping the version to 3 in the process:

# Simulate a separate thread coming in and updating the model.
>>> u2 = User.get(User.username == 'charlie')
>>> u2.favorite_animal = 'macaw'
>>> u2.save_optimistic()
>>> u2.version

Now if we go back to the original instance and try to save a change, we'll get a ConflictDetectedException because the version we are saving (2) does not match up with the latest version in the database (3):

# Now, attempt to change and re-save the original instance:
>>> u.favorite_animal = 'little parrot'
>>> u.save_optimistic()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "x.py", line 30, in save_optimistic
    raise ConflictDetectedException()
ConflictDetectedException: current version is out of sync

And that's all there is to it!

Thanks for reading

Thanks for reading this post, I hope that this technique and the example code will be helpful for you! Don't hesitate to add a comment if anything is unclear or if you have any questions.

Comments (0)

Commenting has been closed, but please feel free to contact me