Problem/Motivation
This is something about \Drupal\serial\SerialSQLStorage::generateValueFromName() function. It is used for generating new number for serial field value.
If we describe the algorithm the we will be have this:
- Insert new record into serial storage table
- Get the last insert ID (sid)
- If sid % 10 == 0 then do cleanup - DELETE FROM {serial_storage} WHERE sid < :sid_we_just_generated
The problem happens when we're trying to create a lot of entities in parallel transactions. If we have two transactions which are going to execute clean up we can get a deadlock. The scenario is:
- Transaction A inserts the row into storage table and gets sid = 100
- Transaction B inserts the row into storage table and gets sid = 110
- Transaction B tries to do cleanup by executing DELETE FROM {serial_storage} WHERE sid < 110. This query is locked becuase row sid=100 (which is going to be deleted) is locked in transaction A. And moreover this DELETE statement create another lock.
- Transaction A tries to do cleanup by executing DELETE FROM {serial_storage} WHERE sid < 100. We already have a lock for rows sid < 110. So this query is also locked. The database considers it as deadlock and this transaction fails, so transaction B can continue.
The problem is - user sees the error. Another big problem, in logs we have completely different error:
SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT savepoint_2 does not exist: ROLLBACK TO SAVEPOINT savepoint_2;
(or savepoint_1, the number does not matter). This really confuses.
We see this irrelevant error because of this code:
catch (\Exception $e) {
$transaction->rollback();
watchdog_exception('serial', $e);
throw $e;
}
In case of deadlock it's not possible to rollback to savepoint. And this piece of code does everyhing possible to hide real error from the developer, becuase rollback fails and watchdog_exception is never reached. I suppose that someone just copypasted it from the core (becuause in entity save we also have such problem). But this is not something which we are going to fix.
I think it's important to improve logic to do not cause deadlocks.
Proposed resolution
I think the cleanup should not happen when we do inserts. Moreover it's completely illogical to execute DELETE statements when we create new entities.
The most obvious solution is moving cleanup to the cron job. In this case we never get deadlocks.
Remaining tasks
Review, another opition is needed.
User interface changes
None.
API changes
None.
Data model changes
None.