When managing data, you need to take into account several matters. One of them is concurrency. It happens when multiple people try to simultaneously access some form of data (entities, objects, database records, etc) to either see it or update it.

Imagine there is a customers data table and I update a piece of customer information at the same time as some other user. That is called a collision and it’s affecting the consistency of a record in a system. Whose changes should be saved? Mine? The other user’s? Neither? A combination? Coming up next is a brief explanation of a problem I had in my team, a practical example of how it was detected and it’s fixing.

The issue

A week ago, we received a Bugsnag error report about an integrity violation when saving values in a many-to-many database table. The problem was a unique key constraint and a duplicate insertion.

Debugging how we were saving the relations, I found we were using the syncWithoutDetaching Eloquent method. It solved our need for saving relations without duplicating existing ones. But calling this method many times produced another issue behind the scenes. 

A couple of years ago, a Laravel issue was reported setting precedent of this bug. A user reported sending multiple requests concurrently would result in the duplicate insertion of relations.

Reproducing it

In pursuit of reproducing this concurrency issue, I went to Postman and copied the request code in cURL format. Since this request is a POST, I had to choose the raw JSON data format, otherwise, when exporting the code to cURL I would get no data sent over.

locking strategies

After setting the data as JSON, I clicked the Code button and copied the cURL request which looked like this:

curl -X POST \
  http://localhost:90/api/resource-groups/a53aedad-d857-444c-b369-74b394b39268/resources \
  -H 'Content-Type: application/json' \
  -H 'Postman-Token: 5e88d411-a46c-4a41-8aff-ffba8c5f6766' \
  -H 'cache-control: no-cache' \
  -d '{
	"resource_id": "ff1ab722-1547-41d4-8bdc-b56dcd5b5945"
}'

In order to make concurrent cURL requests, you need to use the ampersand symbol. And so I copied the request over about five times to reproduce the database exception.

Strategies

So which strategy should we choose for addressing concurrency issues?

Pessimistic locking is a way of protecting the integrity of your records until you are done updating them. It locks the records for your own use so you can process your data safely. Once you are done updating your records, you unlock them for others to use.

Optimistic locking is used to detect and resolve collisions. You would choose this strategy when having data collisions is unlikely. 

If you consider you will never have a collision you can take the overly optimistic locking approach, which neither tries to avoid nor detect collisions. Some possible scenarios are: 

  1. Single user systems.
  2. Systems where the system of record is guaranteed to be accessed by only one user or system process at a time.
  3. Read-only tables. 

Let’s be pessimistic

Our controller actions were already wrapped around transactions but the error would still happen at the end when the transaction was committed. What we needed to solve the issue was pessimistic locking.

So I set up to use the lockForUpdate method when saving our many to many relationships:

public function addResources(Collection $resources): void
{
    $this->resources()->lockForUpdate()->syncWithoutDetaching($resources->map->getKey());
}

Conclusion

It is important to consider the right strategy that suits your access characteristics. This will depend on the complexity of your system and the volume of transactions your data tables have.

Nevertheless, I would like to emphasize the fact that feature and unit tests are not enough. We, Software Engineers, need to make thorough testing to really check the stability and robustness of our systems. This is what stress testing is about. To ensure the reliability of our software, we ought to have error handling in place as well as catching performance and availability issues before reaching to a production environment.

Be safe, use pessimistic locking ?