[5.x]: Deadlock on Generating catalog pricing job

[jgpadua]

What happened?

Description

I have been receiving the following Deadlock error quite frequently when Craft executes the "Generating catalog pricing" job. My feeling is that since it started, it has become more recent, but I have not been able to find any logic that indicates that it happened for any specific reason.

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction The SQL being executed was: INSERT INTO commerce_catalogpricing (price, purchasableId, storeId, uid, dateCreated, dateUpdated) SELECT basePrice, purchasableId, storeId, UUID(), NOW(), NOW() FROM commerce_purchasables_storesWHEREpurchasableId IN (20168)

Is this a known bug? Is there anything I can do to reduce its occurrence?

Craft CMS version

5.6.17

Craft Commerce version

5.3.9

PHP version

8.3.8

Operating system and version

Linux 6.8.0-1021-aws

Database type and version

Mysql 8.0.32

Image driver and version

No response

Installed plugins and versions

[linear]

PT-2772 [5.x]: Deadlock on Generating catalog pricing job

[nfourtythree]

Hi @jgpadua

Thank you for your message, this is not something we have received reports about.

Catalog prices, by default, are only generated on save of purchasables.

Do you have any custom code that may cause a lot of purchasable saves? Do you see a lot of catalog pricing jobs in your queue?

Thanks!

[jgpadua]

@nfourtythree In our case, we have a product sync that happens every day and we also receive updates whenever a product is updated in a third-party tool. In other words, we can update several products at a given time. Each product is added to a queue and the update is executed. Do you have any recommendations to avoid this?

Another question, I'm not sure how this works in Craft, but in our case the products have a fixed price (they don't have a catalog of prices). Is it still necessary for this job to be executed or would it be unnecessary in our case? If unnecessary, would there be a way to skip this job?

[franck-thorck]

Same issue here, we setup other price for variants, but the new prices don't display and the warning Pricing jobs are currently running still displays.

We are on "craftcms/commerce": "5.3.12"

Thank you,

[nfourtythree]

Hi @jgpadua

Thank you for the further information. Our recommendation here would be, in you custom code where you are syncing your products, just before the saving of the product element, you can prevent the catalog pricing job from being added to the queue.

That can be done with the following code:

Event::once(Queue::class, Queue::EVENT_BEFORE_PUSH, function(PushEvent $event) {
    if ($event->job instanceof CatalogPricing) {
        $event->handled = true;
    }
});

// Save your product

An important point to note is that by not running the jobs during your sync process it would be required to run the catalog pricing generation after you have finished saving everything. You can do that with the following code:

Plugin::getInstance()->getCatalogPricing()->generateCatalogPrices();

As for your second question. There isn't a way to "opt out" of the catalog pricing as the it is used under the hood for all of the powerful querying and sorting that happens for prices in the system.

Thanks!

[nfourtythree]

HI @franck-thorck

Can you see the pricing job in your Craft queue? Are there any errors?

Thanks!

[franck-thorck]

Hi @nfourtythree,

There is no job running in the queue manager, and I don't have error in console.log, phperrors.log or web.log about that.

Thanks!

[jgpadua]

Hi @nfourtythree ,

Thank you for your feedback. The vast majority of my product updates do not update the product price. If the price does not change, can I skip this job? Or does this job look at other things besides the price?

Thanks!

[nfourtythree]

@franck-thorck this sounds a bit strange. Could you perhaps send a DB backup and your composer files to support@craftcms.com, referencing this issue and we can take a look?

Because the queue jobs should be running and updating the prices on save of the products.

[franck-thorck]

@nfourtythree Yes sur, I'll do it.

Thank you!

[nfourtythree]

@jgpadua I know you said you aren't using the catalog pricing rules at the moment but I would say to future proof yourself would be to do as I mentioned above. Prevent the queue jobs from running when you are executing your custom code, then just run the generate prices method once at the end of that process.

This is because catalog pricing rules can have many conditions that, in theory, in the future could cause your products/variants to match and generate new prices.