Don’t Forget Setting Database Locks on Your Orm Entities

A huge advantage of relational databases is transactions and locking. Don’t waste it by not using it

Image by author

What is even the issue?

As you can see, we have two separate requests that both modify the same database field. But since the second has read the entity before the first process has written its change, they both have the same starting information.

That means that we lose data and there isn’t even an indication that this happened.

So how can this be solved?

Entity Locking

Entity locking refers to implementing any kind of mechanism that allows the detection of concurrency issues.

There are many ways to implement those based on your database's capabilities and the requirements of your application.

I’m writing this from the perspective of the doctrine ORM for PHP since I’m most familiar with it. But it will be very similar in other ORMs like Hibernate for Java. Most of these strategies can also be manually implemented outside of ORMs.

Optimistic Locking

This strategy involves adding a version field to your database record that captures the number of transactions to it. Here is a visualization:

As you can see, this variant does not solve the conflict.
But it will prevent silent data loss. So this method is perfect if:

• you don’t expect high concurrency
• your logic or your user can easily retry an action
• you need to “lock” an entity for longer periods of time, eg. user interaction.
• your database has no support for native locking
• you don’t know if you are going to modify the entity before reading it

Using just basic error handling, you can inform your user that something went wrong and that they should try again.

You can even include the version in your forms so that you can detect changes between user interactions and requests.

To implement it, you have to add the version field and mark it as a version field. In Doctrine it’ll look like this:

#[ORM\Column(type: "integer")]
#[ORM\Version]
public int $version = 0;

The locking itself is then completely handled for you. You only need to make sure to pass the version through forms to benefit from inter-request locking.

Pessimistic Locking

As the name suggests, with this method we expect things will go wrong. At best, our database should support locking natively. But it isn’t strictly required, although I’m not aware of database abstractions that help you emulate it.

Let’s take a look at the expected behavior with pessimistic locking:

The pessimistic lock will actually tell the database that the document should be locked. What then happens is that the database grants you the lock or will block your request until the lock is released.

This means you can be sure that no other process can currently modify your record. An SQL database enforces this, even if other processes don’t use locks.

So this method is great if:

• you expect high concurrency
• write failures are not acceptable
• you know you are going to modify your entity in the request
• you can’t make database schema changes

We can break it down as: If you are modifying your entity, you should lock it while requesting it. In the doctrine, it will look like this:

$em->wrapInTransaction(function () use ($em) {
  $obj = $em->find(Entity::class, 1, LockMode::PESSIMISTIC_WRITE);
  $obj->counter = $obj->counter + 1;
});

As you can see, you also need an explicit database transaction. The doctrine has a nice utility method wrapInTransaction, which does all the requests and even the final flush() for you. If there is some issue in this function, it’ll rollback the transaction.

Bonus: Atomic Writes

This is not a locking method, but it can fulfill the same end goal. When working using object models, it is easy to overlook simple solutions. Some things are easier to do in the database directly.

Most databases use atomic update operations by default, even transaction-averse NoSQL databases like MongoDB and AWS DynamoDB work like that. That means that if a row is in the process of being updated, it can’t be updated by another session at the same time. This model is therefore fairly similar to pessimistic locks.

As you can see, we never actually read the row, just directly update it.

This method is fairly limited, so you should only use it if:

• you expect high concurrency or many updates in general
• you only need to update a few fields of a single record
• you don’t need to read the value beforehand

Final Takeaways

If your project has a chance of being used by more than one person, then you should at least consider implementing optimistic locking. Otherwise, you might rip your hair out trying to replicate unexpected behavior.

Pessimistic locks are useful if the optimistic locks show you that you have concurrency issues. If you write commands or importers, then they can use them preemptively but it’s usually not necessary, as long as you have optimistic locks in place as a failsafe.

And don’t forget to consider technically less complex alternatives.

Did I miss anything or tell you something new?