[5.x]: Product save causes long-running queries leading to minutes-long hangs

What happened?

Description

On our staging/production environment, saving a Commerce product from the control panel can take many minutes, the CP appears to hang, and the front of the site is hard to reach while the save is in progress. In the queue we see jobs such as “Generating catalog pricing” and “Updating search indexes”, but profiling suggests the real bottleneck is long-running MySQL work, not those jobs completing slowly in isolation.

On local (DDEV) with the same project, product saves complete in a reasonable time.

We traced two concrete failure modes:

1. SQLSTATE[HY000]: General error: 2006 Server has gone away during elements/apply-draft. The stack shows the failure when acquiring a DB mutex / revision work (GET_LOCK via MysqlMutex) after Product::afterPropagate() → Revisions::createRevision(), consistent with the DB connection being dropped mid-request (e.g. server wait_timeout vs a very long apply/save).

2. SHOW FULL PROCESSLIST repeatedly shows the same SELECT on elements (columns such as id, canonicalId, fieldLayoutId, uid, …) stuck in Statistics for hundreds to thousands of seconds. Several such connections can pile up at once.

We have used KILL on stuck connections. Local DB is MySQL 8; server is MariaDB 10.6, so optimizer behavior may differ from dev even with identical code.

We have some fields coming from plugins in the products, so I tried to remove them, but still no luck. We don't have a lot of products and variants (~75 total).

I've seen others issues like this one but we still have the problem. Everything is up to date.

Steps to reproduce

1. Open the control panel on a remote environment.
2. Edit a Commerce product.
3. Click Save.
4. CP and site are now stuck.

Expected behavior

Product save should complete in a short, predictable time, without blocking the site for minutes, and without MySQL sessions stuck during normal saves.

Actual behavior

• Save take minutes; CP and site feel blocked for other users.
• DB may show multiple long-lived SELECT … elements … queries in Statistics, or 2006 Server has gone away during apply-draft / revision-related steps.

Craft CMS version

5.9.17

Craft Commerce version

5.6.1

PHP version

8.2.30

Operating system and version

Linux 4.18.0-553.89.1.lve.el8.x86_64

Database type and version

MariaDB 10.6.24

Image driver and version

GD 8.2.30

Installed plugins and versions

• CKEditor 5.3.1
• Neo 5.5.7
• SEO 5.0.0
• Moneris for Craft Commerce 1.0.0 (custom payment gateway by us)

Start by reproducing the remote Commerce product save and inspect the elements/apply-draft request. Trace Product::afterPropagate(), Revisions::createRevision(), and MysqlMutex around GET_LOCK, while comparing the stuck elements SELECT in SHOW FULL PROCESSLIST across MySQL and MariaDB. Done means identifying the cause and verifying that saves complete predictably without long-lived queries or Server has gone away errors.