Doctrine how to resolve race condition?

Question

symfony

There is a service that logs requests. Time, ip, userAgent, and one parameter from the query string, let's call it query_param. Logging happens in the table in a DB. Let's call it redirect. Accordingly, the redirect table has createdAt, ip, ...., query_id fields. The query_id field is a link to the query table, where the values ​​of the query_param parameter from the query are stored. The query table has only two fields id and value (a unique index). The logic of working with this table:
1. We got the value of the query_param parameter from the query
2. We are looking for a record in the query table, where value is equal to the value from paragraph 1
3. If the record is not found, we create it.
4. As a result, we get the record id from the query table
5. Further work is already underway with the redirect table

The logging service registers about a million requests per day, and it is not uncommon for two requests to arrive at the same time with the same query_param value, which is not yet in the query table. And this is where the race condition comes in. Both processes at the same time look for the query_param value in query - both do not find it. Both are trying to insert a new value. Some of the requests work faster, and the second one fails with the UniqueConstraintViolationException error, because the field has a unique index. The problem is that after that the entityManager is closed and further work with the database is impossible. I know that you can recreate the entityManager, but the whole context is lost. Does anyone have any ideas how to solve this problem?

I recently switched to symfony. Before that, I worked with Yii - everything is simple there. I catch this error and understand that someone managed to get in with the insertion ahead of this process, so it’s worth looking for the value in this table again. In general, there is an idea as follows: if I did not find an entry with this value in the query table, put the label into the same redis that I started inserting. But before that, check that another process does not insert this value. But I think it's a crutch. Maybe there is some elegant solution at the Doctrine level?

Answer 1

Essentially, in your case, writing to the database is a critical section . Accordingly, you need to implement any of the ways to synchronize threads to avoid race conditions. There are many options, but specifically in Symfony for this (and for other similar scenarios) there is a Lock component , it provides implementations of ready-made synchronization primitives.
Here you can see a practically finished example of how your code for committing changes should look, taking into account the use of lock.

Answer 2

In addition to the Lock: option, ON CONFLICT (expression) DO NOTHINGand similar solutions in other DBMSs can sometimes be quite appropriate in terms of ease of implementation ...
It literally looks like this:

$sql = <<<SQL
INSERT INTO table (id, value) 
VALUES (555, \'uniqValue\') 
ON CONFLICT (value) DO NOTHING
SQL;

$this->em->getConnection()->executeQuery($sql);

If these are some kind of logs or dictionaries with simple storage logic, then just right ...