[5.x]: Multiple queue runners can overlap CatalogicPricing job #4141
Description
What happened?
Description
In some cases we use multiple workers that run queue jobs, and CatalogPricing jobs can overlap. In that case (we use Postgres) we get errors like:
[94697] Generating catalog pricing. (attempt: 1, pid: 27) - Error (time: 1.159s): SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected
DETAIL: Process 1074673 waits for ShareLock on transaction 1825032; blocked by process 1074311.
Process 1074311 waits for ExclusiveLock on tuple (109,59) of relation 48920 of database 16385; blocked by process 1074675.
Process 1074675 waits for ShareLock on transaction 1825054; blocked by process 1074673.
HINT: See server log for query details.
CONTEXT: while deleting tuple (111,52) in relation "commerce_catalogpricing"
The SQL being executed was: DELETE FROM "commerce_catalogpricing" WHERE "purchasableId" IN (2118, 2119, 2120)
Proposal
Add a mutex lock to this job and make it retryable.
Craft CMS version
5
Craft Commerce version
5
PHP version
8.3
Operating system and version
No response
Database type and version
PostgreSQL 17
Image driver and version
No response