Lockwait timeout exceeded on drupal-commerce

Question

There's something totally crazy going on here and i can't even diagnose the issue. the error:

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: SELECT revision.order_number AS order_number, revision.revision_id AS revision_id, revision.revision_uid AS revision_uid, revision.mail AS mail, revision.status AS status, revision.log AS log, revision.revision_timestamp AS revision_timestamp, revision.revision_hostname AS revision_hostname, revision.data AS data, base.order_id AS order_id, base.type AS type, base.uid AS uid, base.created AS created, base.changed AS changed, base.hostname AS hostname FROM {commerce_order} base INNER JOIN {commerce_order_revision} revision ON revision.revision_id = base.revision_id WHERE (base.order_id IN (:db_condition_placeholder_0)) FOR UPDATE; Array ( [:db_condition_placeholder_0] => 4880 ) στην DrupalDefaultEntityController->load() 

steps to reproduce: try to add a product to cart

tried to fix:

i have tried setting in /etc/my.cnf transaction_isolation = READ COMMITED(and UNCOMMITED) and verified with SELECT @@transaction_ISOLATION; but yielded the same results.

i have even tried adding in settings.php:

$databases['default']['default']['init_commands']['isolation'] = "SET SESSION transaction_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";

which of course is the same setting as in my.cnf but i thought i should give it a try.

on mysql processes i can see the process in sleep state which stays there until it timeouts 120s later. while the page is loading, the php process is at 0% cpu and the mysql process is ALSO at 0%.

It seems to me that there's no lock as even when i restart mysqld, immediately the same issue appears.

The weirdest thing is that it started happening magically and i have even tried restoring from an older backup(both files and db) but still the same. Moreover if i run manually the above query runs lightning fast without waiting for a lock.

i really don't know what to do as i'm trying to figure this out for 3 days now. Any advice is highly appreciated.

Running drupal 7.89 without any kind of object cached used on php7.4 and mysql v8.0.39

Answer 1

Drupal Commerce tries to avoid loading orders "FOR UPDATE", because transactions are generally unreliable in Drupal. (There are just too many opportunities for modules to UPDATE earlier than the original SELECT expected, breaking the lock.)

That said, I think the most common solution to this was to change the database configuration to use a less restrictive transaction isolation level. Starting from here you should be able to get the gist of it:

https://www.drupal.org/project/drupal/issues/2833539#comment-14193506