I'm trying to get to the bottom of a known Drupal Commerce bug to do with row locking.
When a user logs in on my development site, Drupal Commerce starts multiple identical queries that look like this, even when the cart is empty:
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 (''203'')) FOR UPDATE
They're all started less than a second apart, and they all hang and sit waiting for a transaction lock to be granted. The thread I linked to suggests the problem could be the same user is logged in on different browsers, but there's only one entry in my sessions table, so I'm looking for another cause.
What else could is going on here? The big obvious clue I can see is that I can't seem to run that query without an error - "base.order_id IN (''203'')) FOR UPDATE" throws a syntax error, and as far as I can see that's not normal usage for IN - it's normally two integers seperated by a comma, like a limit query, like ('1','12'). The order itself is an empty cart: opening the blob file just gives me this:
<!DOCTYPE HTML><html lang='en' dir='ltr'><meta charset="utf-8" /><meta name="robots" content="noindex,nofollow" /><meta http-equiv="X-UA-Compatible" content="IE=Edge"><style>html{display: non
But I don't know enough about SQL to know if this is something you can do in a join somehow - what's really going on here? And where else should I be looking to debug it?
UPDATE 11/02: I haven't gotten to the root cause of this, but I've done some more investigating.
Channel Islander is right, there's nothing screwy with that syntax. I must have just changed it somehow between copy and pasting and trying to get the query to run.
I truncated the order and order_revisions table in the dev site, then tried to replicate the bug by adding and removing stuff from my admin user's cart (trying to create a lot of revisions). I couldn't replicate the bug like this, and performance got a lot better from them onwards: page loads when logging in and out sped up by multiple seconds, and I can't even catch this SELECT... FOR UPDATE query in phpMyAdmin's status tab.
This seems to have resolved the issue for now, but truncating both tables is obviously not feasible for a production site. Maybe setting orders at "cart" stage to expire after a certain time would be, though?
