[Reliability] Revisit adding unique action solution for better concurrency handling

[kalessil]

Currently, \ActionScheduler_DBStore::build_insert_sql adds unique actions using a select followed by an insert, which is not an atomic operation. In high-concurrency environments, this gap can result in duplicate unique actions.

We should consider implementing a versioned, nullable unique ID column to prevent duplicates at the atomic level.
Making the column nullable allows (?) us to leave it empty for non-unique actions, what is performance-friendly.

[drewmt]

Hi! I’d be interested in taking this on. Before starting the schema work, would you be open to a short design proposal for a versioned nullable unique key enforced by the database? I’d cover the action identity inputs, backward-compatible upgrade behavior, and a concurrency regression test before implementation.

[kalessil]

Hi @drewmt 👋 Feel free to pick it up - it has no assignee, so I think no one is working on it.

The initial guidance (unverified, take it with a pinch of salt) is to introduce a new column with a unique index.
It should allow nulls for pre-existing entries and for adding new entries with a null value - for BC and the migration phase.

The hash values are populated on the PHP side (what blends into the hash is an R&D topic) and passed in as we add a new action.
One more thing to investigate is how to handle a failed insert: in the worst case, additional SQL to check if the hash is present in the DB, but hopefully we can come up with something smarter.

[drewmt]

Thanks for the guidance! Yes, I’ll pick it up. I’ll first map the identity inputs and insert-failure behavior, then implement the smallest backward-compatible schema change with concurrency coverage.

[barryhughes]

Some potentially useful notes added over here that we might consider as part of the solution.