[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[bot]]

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 [email protected], 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.

[sjcallender]

I don't have data to share yet, but this is impacting us heavily on a new site where they are importing hundreds of products per day and will continue to do so for a while.

[nfourtythree]

Hi @sjcallender

As mentioned in a previous comment, if you are programmatically importing a large number of products it is recommended to suppress the catalog pricing job (example code available in comment).

Then you can run the generate prices command once when you know everything has been imported.

Thanks!

[johnnynotsolucky]

@nfourtythree thank you for the suggestion to switch off the job while imports are running.

Unfortunately, this doesn't only happen during importing data.

We're also seeing this when admins are manually updating lots of variants using the Control Panel. Generally once we have a few thousand of these pricing jobs, amongst others, we'll start to see these locking errors.

Other jobs are unrelated to the catalog pricing table, for example, it would be refreshing blitz cache, sending emails, etc.

The specific query that I'm seeing in each failure is this one: https://github.com/craftcms/commerce/blob/44eb3e645dfe42078122d31404fa184fe17ea687/src/services/CatalogPricing.php#L235-L239

We're running a single queue listener for these jobs, so I don't think it's likely that multiple processes from the queue are causing the deadlock.

Is there some other place in Commerce that might be holding a wider lock on that table, outside of these queue jobs?

I've also noticed that saving a variant will add two separate CatalogPricing jobs to the queue, both with the same purchasableIds value, but the first has a siteId set, the second has the siteId as null. Looks like the siteId only matters in this job if catalogPricingRuleIds is set for the job, which it isn't in either.

[nfourtythree]

Hi @johnnynotsolucky

Thank you for your reply and detail.

Improving the performance of updating the pricing catalog is something we are currently investigating/working on and we hope to have some more information on that in the near future.

What you have described, especially in relation to normal CP usage, is not something we have seen reported. You mentioned this issue arises when users simply updating products or variants in the CP. What volume of updates/saves would you be seeing with that?

Thanks

[johnnynotsolucky]

@nfourtythree I took a look now, there were 8 products with around 28 variants each imported recently but no corresponding deadlocks were logged.

However, I can also see they've been showing up intermittently through the day over the past few days but I can't really tell how much updates are going on. Gauging by metrics, it doesn't seem to be too much.

We think it might be happening when there's products/variants being imported, and at the same time, admins are editing variants.

This site is on Craft 5.7.5.

Full stacktrace if it helps:

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
in /home/user/www.site.com/vendor/yiisoft/yii2/db/Command.php:1320

Stack trace:
#0  /home/user/www.site.com/vendor/yiisoft/yii2/db/Command.php(1320): PDOStatement->execute()
#1  /home/user/www.site.com/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute()
#2  /home/user/www.site.com/vendor/craftcms/commerce/src/services/CatalogPricing.php(240): yii\db.Command->execute()
#3  /home/user/www.site.com/vendor/craftcms/commerce/src/queue/jobs/CatalogPricing.php(37): craft\commerce\services\CatalogPricing->generateCatalogPrices()
#4  /home/user/www.site.com/vendor/yiisoft/yii2-queue/src/Queue.php(243): craft\commerce\queue\jobs\CatalogPricing->execute()
#5  /home/user/www.site.com/vendor/yiisoft/yii2-queue/src/cli/Queue.php(162): yii\queue\Queue->handleMessage()
#6  /home/user/www.site.com/vendor/yiisoft/yii2-queue/src/cli/Command.php(146): yii\queue\cli\Queue->execute()
#7  [internal function]: yii\queue\cli\Command->actionExec()
#8  /home/user/www.site.com/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#9  /home/user/www.site.com/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#10 /home/user/www.site.com/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction()
#11 /home/user/www.site.com/vendor/craftcms/cms/src/console/ControllerTrait.php(88): yii\console\Controller->runAction()
#12 /home/user/www.site.com/vendor/yiisoft/yii2/base/Module.php(552): craft\queue\Command->runAction()
#13 /home/user/www.site.com/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction()
#14 /home/user/www.site.com/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction()
#15 /home/user/www.site.com/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction()
#16 /home/user/www.site.com/vendor/craftcms/cms/src/console/Application.php(122): yii\console\Application->handleRequest()
#17 /home/user/www.site.com/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest()
#18 /home/user/www.site.com/craft(13): yii\base\Application->run()
#19 {main}

Next yii\db\Exception: 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_stores`
    WHERE
        `purchasableId` IN (32963)
in /home/user/www.site.com/vendor/yiisoft/yii2/db/Schema.php:676

Stack trace:
#0  /home/user/www.site.com/vendor/yiisoft/yii2/db/Command.php(1325): yii\db.Schema->convertException()
#1  /home/user/www.site.com/vendor/yiisoft/yii2/db/Command.php(1120): yii\db.Command->internalExecute()
#2  /home/user/www.site.com/vendor/craftcms/commerce/src/services/CatalogPricing.php(240): yii\db.Command->execute()
#3  /home/user/www.site.com/vendor/craftcms/commerce/src/queue/jobs/CatalogPricing.php(37): craft\commerce\services\CatalogPricing->generateCatalogPrices()
#4  /home/user/www.site.com/vendor/yiisoft/yii2-queue/src/Queue.php(243): craft\commerce\queue\jobs\CatalogPricing->execute()
#5  /home/user/www.site.com/vendor/yiisoft/yii2-queue/src/cli/Queue.php(162): yii\queue\Queue->handleMessage()
#6  /home/user/www.site.com/vendor/yiisoft/yii2-queue/src/cli/Command.php(146): yii\queue\cli\Queue->execute()
#7  [internal function]: yii\queue\cli\Command->actionExec()
#8  /home/user/www.site.com/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#9  /home/user/www.site.com/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#10 /home/user/www.site.com/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction()
#11 /home/user/www.site.com/vendor/craftcms/cms/src/console/ControllerTrait.php(88): yii\console\Controller->runAction()
#12 /home/user/www.site.com/vendor/yiisoft/yii2/base/Module.php(552): craft\queue\Command->runAction()
#13 /home/user/www.site.com/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction()
#14 /home/user/www.site.com/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction()
#15 /home/user/www.site.com/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction()
#16 /home/user/www.site.com/vendor/craftcms/cms/src/console/Application.php(122): yii\console\Application->handleRequest()
#17 /home/user/www.site.com/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest()
#18 /home/user/www.site.com/craft(13): yii\base\Application->run()
#19 {main}

Additional Information:
Array
(
    [0] => 40001
    [1] => 1213
    [2] => Deadlock found when trying to get lock; try restarting transaction
)