Merge conflict Gerrit cannot merge this change. It needs a rebase onto main.

Review main Ready for review Quiet

89418[BUGFIX] UniqueConstraintViolationException in Registry::set()

Adam Koppe CR -2 V 0 Easy +15 −13 updated 10/20/26, 14:17

Commit message

Stop throwing on concurrent Registry::set() calls to the same registry
entry by removing the SELECT COUNT(*). At the time we UPDATE/INSERT the
count may no longer be up-to-date. Attempt to UPDATE optimistically
falling back to INSERT if the entry does not yet exist.

Reduce the minimum amount of database queries done in Registry::set()
from two to one, improving performance for registry entries often
set but never removed like 'tx_scheduler' 'lastRun'.

Test this change

Run a command in your clone of the TYPO3 Core. Then try what the change does and vote on Verified in Gerrit. How to review a patch

Check out patch set 7
git fetch https://review.typo3.org/Packages/TYPO3.CMS refs/changes/18/89418/7 && git checkout FETCH_HEAD
Cherry-pick onto main
git fetch https://review.typo3.org/Packages/TYPO3.CMS refs/changes/18/89418/7 && git cherry-pick FETCH_HEAD

Review

  • Code review CR -2 Blocked
    • Stefan Bürk -2
  • Verified V 0 Needs a vote
    • core-ci +1 CI

Issues 1

Files 1

Review log 2430

Show 45 earlier messages
  1. Adam Koppe uploaded patch set 1 05/16/25, 08:03

  2. Adam Koppe commented on patch set 1 05/16/25, 08:07

    The `INSERT ... ON CONFLICT DO UPDATE` / `INSERT ... ON DUPLICATE KEY UPDATE` sql statements match more closely what we're doing in Registry::set(). Using them would reduce the amount of SQL statements from 2 to 1 and reduce complexity of the PHP code. Should these be introduced in a separate change set for "main"?
  3. core-ci voted on patch set 1 V +1 05/16/25, 08:09 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/79029

  4. Adam Koppe commented on patch set 1 05/16/25, 08:10

    Registry.php:97

    When cherry picking for v11 / v10 `$_` must be be added here.
  5. Garvin Hicking commented on patch set 1 05/16/25, 22:27

    Reply

    Did you by chance research whether the TYPO3 DBAL has abstracted this to differentiate the pgsql/mariadb+mysql/sqlite differences in syntax easily? Because if we would need to add that, it might increase conplexity even more? I wonder instead if we could di a select+(update|insert) transaction here instead, but haven't looked into it properly.

    Registry.php:97 Reply

    We only do commits for community/supported LTS versions, so no more commits are made to anything below 12.4. Would you adjust the commit to that?
  1. Adam Koppe commented on patch set 1 05/19/25, 06:53

    Reply

    I don't know of any abstractions of those statements in doctrine/dbal and couldn't find any occurrences of "do update", "on conflict", "on duplicate" nor "duplicate key" in the 5.0.x branch at a859e1c4e ("Merge pull request #6953 from morozov/column-introspection"). I'm not sure if it would be even useful to create such an abstraction in the first place because `ON CONFLICT DO UPDATE` and `ON DUPLICATE KEY UPDATE` are different statements with different capabilities.

    Registry.php:97 Reply

    I will update that.
  2. Adam Koppe updated the commit message in patch set 2 V +1 05/19/25, 06:54

  3. core-ci voted on patch set 2 V +1 05/19/25, 07:02 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/79128

  4. Adam Koppe commented on patch set 2 05/19/25, 07:50

    Reply

    I also couldn't find anything with those four search terms in TYPO3's main branch at cbdd06b6b0a ("[BUGFIX] Allow zero and blank string as valid type values").
  5. Stefan Bürk commented on patch set 2 CR -1 05/19/25, 18:49

    Not sure about this change. First, simply ignoring the error on insert would not handle the situation in any way (calling code simply assums it has ben set/inserted with the given value). Second, using duplicate key sql statements mentioned in another comment even if there would be an API for that in doctrine/dbal or TYPO3 may be an option - not sure currently. Needs some thoughts on calling code or if that should handle the case that is has been "set" meanwhile .... something which should be considered. On the otherside .. key existing namespace will be updated in any case even if another proces just updated it - there is noc ACID/CAS handling in that case. Tricky. Putting it on my list - anyway, 12.4 will be not a traget for that.
  6. Adam Koppe commented on patch set 2 05/19/25, 21:23

    Reply

    > First, simply ignoring the error on insert would not handle the situation in any way What exactly is the patch lacking? The exception isn't thrown anymore and the outcome visible via _Registry::get()_ is one that the un-patched implementation also generates in some situations. > calling code simply assums it has ben set/inserted with the given value What exactly assumes/requires the calling code? If one reads that as "After calling _Registry::set()_ the given value is the current value", this isn't how the un-patched implementation of _Registry::set()_ behaves. Let me illustrate that with the following example, where there are two concurrent _Registry::set()_ invocations called P1 and P2: 1. P1 `SELECT COUNT(*)`s no rows. 2. P1 `INSERT`s an entry with a value of timestamp 1 3. P2 `SELECT COUNT(*)`s one row. 4. P2 `UPDATE`s the entry with a value of timestamp 2 P1's _Registry::set()_ call successfully returned, but the timestamp 1 value is in its `$this->entries` process-local cache, which isn't the current value anymore. Only P2's `$this->entries` process-local cache contains the current value that is timestamp 2. With this patch applied, the following example no longer throws: 1. P1 `SELECT COUNT(*)`s no rows. 2. P2 `SELECT COUNT(*)`s no rows. 3. P2 `INSERT`s an entry with a value of timestamp 2 4. P1 fails to `INSERT` an entry a with value of timestamp 1 P1's _Registry::set()_ call successfully returned, but the timestamp 1 value is in its `$this->entries` process-local cache, which isn't the current value. Only P2's `$this->entries` process-local cache contains the current value that is timestamp 2. The difference between these two is that in the first example, it is guaranteed the value of timestamp 1 was observable at some point in time, whereas in the second example the value of timestamp 1 was never observable. I think this guarantee that the patched version lacks isn't useful in the context of TYPO3's Registry API, especially when considering the "sys_registry" table as an internal implementation detail behind the interface. Even if TYPO3 guaranteed "After calling _Registry::set()_ the given value is the current value" in a reasonable way within the current interface that doesn't suggest it, consumers wouldn't be able to use that because there'd still be a time frame left between the return of _Registry::set()_ and the use of the value. For TYPO3 consumers requiring the very current value of the Registry entry, TYPO3 already provides exclusive as well as shared locks via the Locking API. Using `INSERT ... ON CONFLICT DO UPDATE` / `INSERT ... ON DUPLICATE KEY UPDATE` would just simplify the internal _Registry::set()_ implementation, as with a single atomic SQL statement we would no longer need to worry about ordering in the method body. It wouldn't change anything regarding the paragraph directly above, but it would enable TYPO3 to still guarantee that all values passed into _Registry::set()_ were observable at some point. > 12.4 will be not a traget for that. I will remove 12.4 from the "Releases" git trailer.
  7. Adam Koppe updated the commit message in patch set 3 CR -1 V +1 05/19/25, 21:24

    Remove 12.4 from Releases
  8. core-ci voted on patch set 3 V +1 05/19/25, 21:30 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/79179

  9. Stefan Bürk commented on patch set 3 CR -2 05/20/25, 06:46

    Reply

    The point is, that the registry is a generic tool and not only used for the last run timestamp of the scheduler task. Beside that, registry is a public API and we do not know what people are using it for (not only the core). That means, it is not guaranteed that parallel process wanting to write the same value/thing to to the key (we simply don't know that). In case of the scheduler it may be "neglitable" to ignore an insert error, but that is not a guranteed behaviour. With this change this is hidden within the `Registry::set()` method versus the current state emitting a exception which may be handled by calling code. So far for the insert part. When the key already exists and two processes want to update they will do it. Still, only as a quick think without taking time, we should either * doing an update in the catched unique key exception instead of doing nothing (last will win like on update) * wrapping the select -> insert|update into a transaction which may introduce dead locks to the system or at least give the change for it. This also requires to deal with lock modes and different handling accross supported database systems. * doing a pre-work trying get an API for `INSERT ... ON CONFLICT DO UPDATE` / `INSERT ... ON DUPLICATE KEY UPDATE` into doctrine/dbal directly (contributing there) [TBH not investigated if that make sense and how high the change is that this gets accepted]. Then rise it in TYPO3 and use it (main only) * doing a dedicated pre-work implementing an API for an API for `INSERT ... ON CONFLICT DO UPDATE` / `INSERT ... ON DUPLICATE KEY UPDATE` into the TYPO3 doctrine/dbal extended API (QueryBuilder) and using it here (main only) From that possibilities I would say that adding now an update to the catched exception of the insert statement would be the only way as a bugfix for now, but again - only a quick mentioning and not a final decision. I need to take time to think about it. However, one point is clear: catching the exception and do nothing is not the way to go return the false expression the set command succeeded !
  10. Adam Koppe commented on patch set 3 05/20/25, 23:02

    Reply

    I will update the patch to use the "update inside catch" solution. It also solves our original UniqueConstraintViolationException, while only introducing overhead to the few invocations in which UniqueConstraintViolationException is thrown. --- That the final value must be the one specified in the latest database statement can only be guaranteed by the "update inside catch" solution; not by the "empty catch" solution. The following example illustrates that in which the concurrent Registry::set() calls P1 and P2 set the same entry to V1 and V2 respectively: 1. P1 `SELECT COUNT(*)`s no rows. 2. P2 `SELECT COUNT(*)`s no rows. 3. P2 `INSERT`s the entry with V2 4. P1 fails to `INSERT` the entry with V1 5. (only with "update inside catch" solution) P1 `UPDATE`s the entry to V1 The correct final value is V1 as the `INSERT` of P2 is ordered before the `UPDATE` of P1. Only the "update inside catch" solutions produces the correct value. The "empty catch" solution leaves the entry at the incorrect value V2. The fact that the `UPDATE` statement is used for ordering if the `INSERT` fails becomes relevant if there are more than two concurrent Registry::set() calls: In the following example P1, P2 and P3 set the same entry to V1, V2 and V3 respectively: 1. P1 `SELECT COUNT(*)`s no row 2. P2 `SELECT COUNT(*)`s no row 3. P1 `INSERT`s the entry with V1 4. P3 `SELECT COUNT(*)`s one row 5. P2 fails to `INSERT` V2 6. P3 `UPDATE`s the entry to V3 7. (only with "update inside catch" solution) P2 `UPDATE`s the entry to V2 The final value is V2 as the `INSERT` of P1 as well as the `UPDATE` of P3 are ordered before the `UPDATE` of P2. The "empty catch" solution leaves the entry at the incorrect value V3. If we defined the final value as the one specified in the most recently executed database statement that is either the `INSERT` or the initial `UPDATE` (e.q. the `UPDATE` inside the catch doesn't count), the value should be V3 in the example above because the `INSERT` of P1 as well as the `INSERT` of P2 are ordered before the `UPDATE` of P3.
  11. Adam Koppe uploaded patch set 4 V +1 05/20/25, 23:02

    Switch to update inside catch solution Copied Votes: * Code-Review-2 (copy condition: "changekind:NO_CHANGE OR **is:MIN**")
  12. core-ci voted on patch set 4 V +1 05/20/25, 23:08 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/79257

  13. Benni Mack updated the commit message in patch set 5 V +1 04/22/26, 06:34

    Copied Votes: * Code-Review-2 (copy condition: "changekind:NO_CHANGE OR **is:MIN**")
  14. core-ci voted on patch set 5 V -1 04/22/26, 07:23 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/100737

  15. Adam Koppe rebased onto patch set 6 V -1 04/22/26, 09:43

    Copied Votes: * Code-Review-2 (copy condition: "changekind:NO_CHANGE OR **is:MIN**")
  16. Adam Koppe commented on patch set 6 05/04/26, 12:56

    Reply

    I resolved this thread for now because it is not clear to me from the single usage introduced in this CR what the abstraction should be capable of.

    Reply

    Thread resolved since I added the UPDATE to the catch block and adjusted the change description.
  17. Garvin Hicking commented on patch set 6 10/02/26, 12:43

    How about this: ``` $affected = $connection->update('sys_registry', ['entry_value' => $v], $where, $types); if ($affected === 0) { try { $connection->transactional(fn() => $connection->insert(...)); // savepoint-safe } catch (UniqueConstraintViolationException) { $connection->update('sys_registry', ['entry_value' => $v], $where, $types); } } ``` In fact DBAL 4.4 currently has no "upsert" logic that I asked for.
  18. Adam Koppe commented on patch set 6 10/02/26, 14:43

    Reply

    Good suggestion for an optmized common case. What is _Connection::transactional()_ needed for? We could upsert using _Connection::executeStatement()_ on databases supporting it.
  19. Garvin Hicking commented on patch set 6 10/09/26, 07:14

    Reply

    The transaction would be a postgreSQL guard: On PostgreSQL, any failed statement inside a transaction puts the whole transaction into an aborted state. Until the transaction is rolled back, every later statement is rejected: ``` BEGIN; -- opened by some caller, not by Registry INSERT INTO sys_registry ...; -- unique violation UPDATE sys_registry SET ...; -- ERROR 25P02: current transaction is aborted, -- commands ignored until end of transaction block ``` So if anything calls Registry::set() while a transaction is open, the catch block wouldnt recover. Its UPDATE fails, and the caller's whole transaction is lost. Using `transactional` would (with my understanding) allow to only affect that single statement and leave the other `Registry::set()` call "fail" gracefully. About upsert: I think we should not introduce the complexity to maintain "upsert" for our own DBAL implementation, I think this effort should go directly intro the Doctrine DBAL.
  20. Adam Koppe uploaded patch set 7 10/19/26, 14:15

    Copied Votes: * Code-Review-2 (copy condition: "changekind:NO_CHANGE OR **is:MIN**")
  21. Adam Koppe commented on patch set 7 10/19/26, 14:22

    Reply Unresolved

    Thank you.

    Registry.php:103

    Loop because there could be a concurrent Registry::remove() ordered after the failed INSERT that fails the UPDATE.
  22. core-ci voted on patch set 7 V +1 10/19/26, 14:23 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/111656

  23. Garvin Hicking commented on patch set 7 10/19/26, 18:46

    Registry.php:103 Reply

    This sounds like a setup for a deadlock? "I have a bad feeling about this"(tm)
  24. Adam Koppe commented on patch set 7 10/20/26, 13:19

    Registry.php:103 Reply Unresolved

    Getting into the second interation of the loop requires concurrent Registry::set() and Registry::remove() calls. Getting into the third interation requires two new calls because the calls that got us here completed. As long as the number of concurrent Registry::set() and Registry::remove() calls is bound, the number of loop iterations is. I don't think any UPDATE after a failed INSERT is of use for callees. When two concurrent Registry::set() calls attempt to write values A and B respectively any of A or B is a valid final value for the registry entry. The callees know that their A or B may have been observed by a concurrent Registry::get() but don't know if their value is the final value. The number of UPDATE statements in the Registry::set() implementation does not change what the callee knows because by the time their Registry::set() finally returns another Registry::set() may have already updated the registry entry. So I'm open for no UPDATE as written in V1 of the patch or one unchecked UPDATE like in your snippet in the other thread.
  25. Garvin Hicking commented on patch set 7 10/20/26, 14:17

    I'll have to think on that and will try to talk to Stefan about it