[5.x]: Deadlock on `craft_commerce_products` from OR-clause UPDATE in `Variant::afterSave`

[denisyilmaz]

What happened?

Description

Saving a product with many variants (~44 in our case) reliably deadlocks on craft_commerce_products. The failing statement is always the OR-filtered write in Variant::afterSave() (vendor/craftcms/commerce/src/elements/Variant.php ~line 1143):

Db::update(Table::PRODUCTS, $defaultData, [
    'or',
    ['id' => $ownerId],
    ['defaultVariantId' => $this->id],
]);

Two concurrent variant-save transactions appear to take row locks on the same product in opposite orders. The inline comment already flags this block as legacy denormalization (@TODO ... can be removed at the next breaking change), so it may already be on the way out.

One direction that might help, if the column itself is staying for now, would be splitting the OR into two ordered, single-target updates so lock acquisition is deterministic. Something along the lines of:

Db::update(Table::PRODUCTS, $defaultData, ['id' => $ownerId]);

Db::update(
    Table::PRODUCTS,
    $defaultData,
    ['and', ['defaultVariantId' => $this->id], ['not', ['id' => $ownerId]]]
);

There may well be reasons the current shape is preferable that we're missing. Happy to defer to whatever approach you'd prefer, and to help with a PR if useful.

Note on our setup: our project has a custom EVENT_AFTER_SAVE_ELEMENT listener. Heavy work (static file regeneration) is dispatched to the queue rather than run inline, but per-variant the listener still does some logging and pushes a couple of queue jobs. On a 44-variant product save that's a non-trivial amount of extra work inside the outer save transaction, which likely widens the window for two overlapping requests to collide on the OR-clause lock. So our setup probably surfaces this earlier than a vanilla 44-variant product would.

Steps to reproduce

1. Create a product with 40+ variants on Postgres.
2. Save the product draft twice in quick succession, or perform any action that results in two overlapping element-save requests for the same product (e.g. drag-reorder variants while a previous save is still in flight).
3. Repeat a few times.

Expected behavior

Concurrent variant saves on the same product complete without deadlocking.

Actual behavior

A Postgres deadlock is raised and the save fails:

SQLSTATE[40P01]: Deadlock detected
DETAIL:  Process 2061002 waits for ShareLock on transaction 33614784; blocked by process 2061067.
Process 2061067 waits for ShareLock on transaction 33614190; blocked by process 2061002.
CONTEXT:  while updating tuple (41,27) in relation "craft_commerce_products"

UPDATE "craft_commerce_products"
SET "defaultVariantId"=4482, "defaultSku"='...', "defaultPrice"='31.5', ...
WHERE ("id"=4408) OR ("defaultVariantId"=4482)

Craft CMS version

^5.9.20

Craft Commerce version

^5.6.2

PHP version

8.4

Operating system and version

No response

Database type and version

Postgres 16

Image driver and version

No response

Installed plugins and versions

[linear]

COM-560

[lukeholder]

Thanks for reporting this. It is fixed for the next release.

To get the fix early, change your craftcms/commerce requirement in composer.json to:

"require": {
  "craftcms/commerce": "5.x-dev as 5.6.2",
  "...": "..."
}

Then run composer update.

We will update this ticket once the release is out.

[brandonkelly]

Commerce 5.6.3 is out with that fix. Thanks again!