My dev site is currently nonfunctional due to this locking issue. I thought I'd capture the error before proceding. I imagine others will see this too.

Error message
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] => 4 ) in DrupalDefaultEntityController->load() (line 196 of /home/rfay/workspace/commerce/includes/entity.inc). Backtrace:
PDOStatement->execute(Array) database.inc:2095
DatabaseStatementBase->execute(Array, Array) database.inc:651
DatabaseConnection->query('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, Array) select.inc:1225
SelectQuery->execute() entity.inc:196
DrupalDefaultEntityController->load(Array, Array) common.inc:7448
entity_load('commerce_order', Array, Array, ) commerce_order.module:616
commerce_order_load_multiple(Array, Array) commerce_order.module:587
commerce_order_load('4') commerce_cart.module:536
commerce_cart_order_load('1') commerce_cart.module:457
commerce_cart_block_view('cart')
call_user_func_array('commerce_cart_block_view', Array) module.inc:795
module_invoke('commerce_cart', 'block_view', 'cart') block.module:827
_block_render_blocks(Array) block.module:657
block_list('sidebar_first') block.module:315
block_get_blocks_by_region('sidebar_first') block.module:268
block_page_build(Array) common.inc:5485
drupal_render_page(Array) common.inc:2537
drupal_deliver_html_page(Array) common.inc:2432
drupal_deliver_page(Array, '') menu.inc:518
menu_execute_active_handler() index.php:21
Files: 
CommentFileSizeAuthor
#56 1320062-provide-easy-way-to-load-unlocked-orders.patch1.88 KBaidanlis
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1320062-provide-easy-way-to-load-unlocked-orders_2.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#52 1320062-provide-easy-way-to-load-unlocked-orders.patch1.8 KBaidanlis
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1320062-provide-easy-way-to-load-unlocked-orders_1.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#49 1320062-provide-easy-way-to-load-unlocked-orders.patch1.76 KBaidanlis
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1320062-provide-easy-way-to-load-unlocked-orders_0.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#47 1320062-provide-easy-way-to-load-unlocked-orders.patch1.93 KBaidanlis
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1320062-provide-easy-way-to-load-unlocked-orders.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#15 field_sql_storage_deadlock-1320062-15.patch2.08 KBbasic
PASSED: [[SimpleTest]]: [MySQL] 37,040 pass(es).
[ View ]
#7 commerce_deadlock.txt7.48 KBrfay

Comments

Restarting mysqld got me past my immediate problem. Not sure what the correct fix to something like this is, or what might have caused it.

Saw this again today when just reloading a page (checkout/4)
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] => 4 ) in DrupalDefaultEntityController->load() (line 196 of /home/rfay/workspace/commerce/includes/entity.inc).

I do suspect that I was logged in as the same user in two different browsers...

Once again, restarting mysqld was the only way I figured out to solve this.

BTW: @DamZ says that if this happens one should do

show engine innodb status

To immediately determine what the problem is.

Category:bug» support

Recategorizing until we know if there's actually a bug in Commerce.

I also have a similar 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] => 3 ) in DrupalDefaultEntityController->load() (line 196 of /var/www/vhosts/cms.huttoncreative.com/httpdocs/includes/entity.inc).

StatusFileSize
new7.48 KB

Here is the result of the latest deadlock, captured with
show engine innodb status

I definitely had the same user active in two browsers (one of which was a debugger).

This situation is completely fatal for that user.

Category:support» bug

I think this is a race condition in Commerce code. The mitigating factor *may* be if it's only caused by something like I'm doing, holding a lock for a long time in the debugger.

Killing the debugger session did in fact resolve this.

I guess the question is, are we doing what we want to do with transactions here, or are we including too much code inside it?

Status:Active» Closed (works as designed)

I'm going to say that this one is just "works as designed". If I'm in there in the debugger, logged in, and in the middle of a transaction, and try to do something else, in another browser... maybe that's OK.

We're making the assumption that my holding a transaction by halting in the debugger is a completely anomalous situation and that there's no real-world way it can happen.

Status:Closed (works as designed)» Active

I'm opening this again as I've come across this issue when viewing the orders page in the admin area. As that is part of Commerce and is something that is probably used by a lot of people this is something that does need looking into.

Category:bug» support

Well, it's part of Commerce but is just a View of data pulled directly from the database. Is there anything in your logs that makes you suspect Commerce?

While talking to rfay on IRC I did some testing and from what I found, as soon as you do a commerce_order_load($order_id);, another request cannot load the same order until either the other request finishes or calls commerce_order_save on the loaded order.

The cause of this is the forUpdate() / db_transaction() in DrupalCommerceEntityController::buildQuery(), if you run the following:

$order = commerce_order_load(74);
drupal_set_message('Loaded at: ' . date('r'));
sleep(10);
commerce_order_save($order);
drupal_set_message('Saved at: ' . date('r'));

in 2 separate browsers/sessions at the "same time", one of the requests will only load the order once the other has saved the order.

I'm not 100% sure it's an issue with Commerce (it makes sense to lock the order row so you don't get a request ending up with stale order data) but is there not a way to load an order in a "I'm not going to change anything so don't lock the row" way?

Hmm, that might make a fine feature request. Damien was the master architect behind our entity locking, so I'll see if he has any thought on a read only load routine. The main problem I see is that with the way we handle cart order refreshing and the fact that we have to save an order to update its order total price field, we may not be able to reliably say for any given order that it won't need to be updated. I suppose in the case fo the cart refresh we could reasonably rule out non-cart orders, though. Hmm...

I guess the "read-only" load would be down to the caller, if you added something like commerce_order_load_readonly that would just load the order like normal and not lock the order row. It would then be up to whoever calls that function to not save the order at a later date.

Would refreshing a cart order and not saving it break anything - eg line item data?

Status:Active» Needs review
StatusFileSize
new2.08 KB
PASSED: [[SimpleTest]]: [MySQL] 37,040 pass(es).
[ View ]

Just to test something...not sure this fixes anything.

Talked with DamZ earlier today about having all inserts and updates do a delete before insert, because we believe this pattern of behavior may prevent deadlocks from occurring. Assuming this passes tests then we can try and pin down the behavior that actually causes the deadlock.

Title:SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded (commerce_order + revision table)SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded
Project:Drupal Commerce» Drupal core
Version:7.x-1.x-dev» 7.x-dev
Component:Order» database system

Is this really a Drupal Core issue?

I don't even think this is a bug, it's perfectly valid for Commerce to lock the order row when an order is loaded but doesn't provide a way for an order to be loaded without locking the order row for times when the caller isn't going to make changes to the order.

subscribing

Using pt-deadlock-logger I found my deadlocks came from the semaphore table. Moving the Lock API in Drupal core to memcache seemed to fix the problem for me. Though this core issue with MySQL should really be investigated further. Potentially a later version of MySQL could also have addressed this issue.

@Josh Waihi,

What version of MySQL are you using?

MySQL 5.1.41-3ubuntu12.10 from Ubuntu Lucid LTS

Gents,

I'm getting a very similar error:

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: DELETE FROM {commerce_order_revision} WHERE (order_id IN (:db_condition_placeholder_0)) ; Array ( [:db_condition_placeholder_0] => 3 ) in DrupalCommerceEntityController->delete() (line 104 of /nfs/c06/h02/mnt/xxx/html/profiles/commerce_kickstart/modules/commerce/includes/commerce.controller.inc).

This happens when I try to delete an order (drupal commerce).

Troubleshooting:
-Confirmed that I only have 1 user logged on 1 browser session (no tabs).
-Increased memory limit to 96M
-Applied the patch mentioned above http://qa.drupal.org/pifr/test/196673

MySQL version:
mysql Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i386) using readline 5.0

Please advise..

Regards,
Ahmed

Thanks for the details on MySQL versions. Does the patch in #15 fix the issue for you? If not - does upgrading to a more recent release of MySQL 5.1 or 5.5 fix the issue? Percona maintains repositories for Ubuntu/RHEL with more recent versions here: http://www.percona.com/doc/percona-server/5.5/installation.html.

On mySQL 5.5; patch #15 didn't fix the issue. It possibly reduced it? It's hard to tell, since this is hard to reliably reproduce in my experience.

The more users roaming through the administrative views of Drupal Commerce module, the more likely it is to happen, though.

Problem goes away completely if I stop Commerce's pessimistic locking.

I'm getting this problem as well any time I try to modify a menu.

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: DELETE FROM {cache_menu} WHERE (cid LIKE :db_condition_placeholder_0 ESCAPE '\\') ; Array ( [:db_condition_placeholder_0] => links:main-menu:% ) in cache_clear_all() (line 176 of /nfs/c08/h04/mnt/126855/domains/mydomain.com/html/includes/cache.inc).

I seem to get the same 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] => 26 ) in DrupalDefaultEntityController->load() (regel 196 van /srv/releases/20120420141419/public_html/includes/entity.inc).

The strange thing is that, as far as i can see, this issue only appears when using memcache. Disabling memcache 'solves' the issue.
MySQL: Ver 14.14 Distrib 5.1.62, for debian-linux-gnu (x86_64) on Ubuntu Server 10.04 LTS

Status:Needs review» Needs work

Doesn't seem like anybody agrees that this is a viable patch. Moving to CNR. I *do* believe this is a real problem and flaw with D7/D8

First received error attempting to save a content type display options (admin/structure/types/manage/'node'/display)

Tried running cron only to see Cron Fail and an Error page display with the error below. I took the advice from #3 and restarted my drupal stack local server and tried running cron a few more times and finally the cron ran successfully and errors stopped appearing... for now.

Also noticed, pages were taking longer to display and upon stopping Drupal Stack local server, it took forever for it to shut down. But upon restarting it seems to be working normal.

PDOException: SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size: UPDATE {field_config_instance} SET field_id=:db_update_placeholder_0, field_name=:db_update_placeholder_1, entity_type=:db_update_placeholder_2, bundle=:db_update_placeholder_3, data=:db_update_placeholder_4, deleted=:db_update_placeholder_5 WHERE (id = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 50 [:db_update_placeholder_1] => field_title_upload [:db_update_placeholder_2] => node [:db_update_placeholder_3] => upload [:db_update_placeholder_4] => a:7:{s:5:"label";s:5:"Title";s:6:"widget";a:5:{s:6:"weight";s:1:"0";s:4:"type";s:14:"text_textfield";s:6:"module";s:4:"text";s:6:"active";i:1;s:8:"settings";a:3:{s:4:"size";s:2:"60";s:12:"maxlength_js";i:1;s:18:"maxlength_js_label";s:76:"Content limited to @limit characters, remaining: <strong>@remaining</strong>";}}s:8:"settings";a:2:{s:15:"text_processing";s:1:"0";s:18:"user_register_form";b:0;}s:7:"display";a:1:{s:7:"default";a:5:{s:5:"label";s:5:"above";s:4:"type";s:12:"text_default";s:6:"weight";s:1:"6";s:8:"settings";a:0:{}s:6:"module";s:4:"text";}}s:8:"required";i:0;s:11:"description";s:0:"";s:13:"default_value";N;} [:db_update_placeholder_5] => 0 [:db_condition_placeholder_0] => 65 ) indrupal_write_record() (line 6975 of /Users/cale/Sites/acquia-drupal/includes/common.inc).

UPDATE:

I noticed I'm still receiving errors in my log messages.. upon saving a new node as Admin..

TYPE: cron
DATE: Saturday, June 16, 2012 - 11:36
USER: Anonymous (not verified)
LOCATION: http://localhost:8082/
REFERRER: http://localhost:8082/content/my-first-video

PDOException: SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size: DELETE FROM {watchdog} WHERE (wid < :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => 6654757 ) in dblog_cron() (line 119 of /Users/cale/Sites/acquia-drupal/modules/dblog/dblog.module).

stickywes - I am getting this issue as well - can you tell me how to stop using commerce's pessimistic logging? Wht are the implications of doing this?

Thanks,

John

I don't really know how Drupal handles DB transactions, so I can't really answer that... I can only speculate off of observed behavior, which I don't really feel comfortable doing. I won't give a detailed explanation about avoiding pessimistic locks in Commerce here because this isn't the Commerce issue queue, but pessimistic locking is specified in commerce_order_entity_info(). If that doesn't put you on the right track, feel free to contact me directly.

Here's a general compare/contrast of locking methods: http://www.dbasupport.com/forums/archive/index.php/t-7282.html

I had this error message whilst performing a number of actions in Drupal 7. For me the solution turned out to be updating the version of PHP.

My Solution

Open the Acquia Dev Desktop Control Panel
Click Settings
Click the 'Config' tab
Under Default PHP versions tick: PHP 5.3.9
Click OK

DGA

Was having the same issue but it wasn't held to just Commerce. Several other modules were throwing the error in my logs as well, my dev and staging sites are on two different servers and it was only happening on one, so I knew it had to be a server configuration issue. I followed these instructions,

http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeou...

and so far so good...

I'm also getting this error a few times a week during heavy loads.

I will pile on here and say that I have a slew of sites that were just peachy until I added Drupal Commerce and now I'm seeing the error popping up from time to time. I don't recall ever worrying about it before...

I worked on a few commerce sites with lock timeout issues and all where resolved by increasing database performance.
I believe the issue may be the reverse of what was mentioned in #15:
Each time an entity is saved all fields (changed or not) are deleted and inserted. Both deletion and insertion of records are vary expensive for the DB as opposed to updating only the changed records only.
This is especially the case with drupal_commerce as it uses a lot of state information, Orders get constantly saved and this creates an enormous and unnecessary expensive amount of database traffic.
I created an issue for this Update, rather than Delete and insert - for significant database performance improvement.
However It is possible a different issue is the cause of this.

I don't know what the problem is but I saw this a lot when cron got set wrong and was running every two hours. Running cron every 5 minutes seemed to eliminate it for me. My site is probably quite low trafficked compared to most e-commerce sites but this might help point to the issue?

@ceng , I think it is a MySql performance issue and Cron can add to the problem, but not sure if Commerce has any cron hooks would be good to know.

@ceng Just checked and no cron hooks for commerce, so cron is not the cause of this issue, although it can make it worth (or push it over the edge).

This could be caused by contributed modules loading orders without unlocking them, see #1514618: Developer documentation: Loading order entities without considering locking can cause problems

Status:Needs work» Needs review
StatusFileSize
new1.93 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1320062-provide-easy-way-to-load-unlocked-orders.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

Here's a rewritten patch from #1514618: Developer documentation: Loading order entities without considering locking can cause problems which adds an easy way to load an order without locking it.

I've rewritten the patch because @davidwhthomas's code was a little bit messy with the unnecessary branch and repeated code.

commerce_order_load(1, array('lock' => FALSE))
commerce_order_load_multiple(array(1, 2), array('lock' => FALSE))

To verify this solves the issue, anyone experiencing problems will need to look at their contributed code and ensure there's no long running threads which are keeping orders open. If fixing the locking solves the issue, then we can close this and document it (documentation issue: #1514618: Developer documentation: Loading order entities without considering locking can cause problems).

Status:Needs review» Needs work

The last submitted patch, 1320062-provide-easy-way-to-load-unlocked-orders.patch, failed testing.

Status:Needs work» Needs review
StatusFileSize
new1.76 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1320062-provide-easy-way-to-load-unlocked-orders_0.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

Corrected patch.

Status:Needs review» Needs work

The last submitted patch, 1320062-provide-easy-way-to-load-unlocked-orders.patch, failed testing.

Status:Needs work» Needs review

Not sure why it's failing, but I think the patch is good. I can also confirm that using the unlocked orders in my long running cron jobs has fixed all the stability issues that I was having.

StatusFileSize
new1.8 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1320062-provide-easy-way-to-load-unlocked-orders_1.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

Re-attaching patch with corrected paths.

Status:Needs review» Needs work

The last submitted patch, 1320062-provide-easy-way-to-load-unlocked-orders.patch, failed testing.

Just trying to connect some dots here. I personally feel that there is a bit of an architectural issue with the semaphore table in general, but I don't have a compiled set of evidence to really support my opinion as of yet. That being said I did find a really interesting patch talking about how the semaphore table is better off as MyIsam:
http://drupal.org/node/1898204

After switching my engine over on the semaphore table it was quite apparent that I was no longer getting the lock wait timeout exceptions. That being said, in my slow query log the seemingly simple inserts into the semaphore table are still taking a really long time...

My biggest issue was with devel and the krumo module. It stores huge chunks of data in the sessions table, and can end up being 200MB+ (or greater than 1GB in my case, bigger than the maximum max_allowed_packet) easily. That means max_packet_errors. I would also increase the innodb logging size to 512MB-1GB.

After clearing the sessions table and reducing the number of MySQL threads, I was able to reduce locking issues. As far as the "increase your max_allowed_packet" advice goes, I think "Drupal" needs to decide on a minimum required value (256MB, 384MB, 512MB) and push the compliance/testing of that back to developers/modules.

Status:Needs work» Needs review
StatusFileSize
new1.88 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1320062-provide-easy-way-to-load-unlocked-orders_2.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

@jason.fisher Drupal makes recommendations on production settings, you can configure your development server however your needs dictate -- if you're using devel and krumo you'll need to tweak your settings accordingly.

@lapith Semaphore's cause all sorts of problems, that's for sure. You can try using memcache instead of your DB layer, that might help.

Re-attaching the patch from the base directory, as the other patch didn't seem to apply from the commerce root.

Status:Needs review» Needs work

The last submitted patch, 1320062-provide-easy-way-to-load-unlocked-orders.patch, failed testing.

Status:Needs work» Needs review

I don't know why it's trying to patch from [/var/lib/drupaltestbot/sites/default/files/checkout] ...

@aidanlis thanks for your work on this very important longstanding issue.

It's getting the patch from the right place, see http://qa.drupal.org/pifr/test/517408, but this is a D7 issue and you are patching commerce... The testbot doesn't download commerce for a D7 issue.

Status:Needs review» Needs work

The last submitted patch, 1320062-provide-easy-way-to-load-unlocked-orders.patch, failed testing.

Project:Drupal core» Drupal Commerce
Version:7.x-dev» 7.x-1.x-dev
Component:database system» Developer experience
Category:bug» feature

Ohh, I did not realise. I'm going to assign this over to the DC queue then as we didn't progress on the core front with Damien's patch.

Project:Drupal Commerce» Drupal core
Version:7.x-1.x-dev» 7.x-dev
Component:Developer experience» database system
Category:feature» bug

Damien moved this in #18 to test the patch in #15. I believe there's a separate issue in the Commerce queue for loading orders as read only. I'd like to leave this one as it was.

Hi everybody,

I'm experiencing the same issue.
I'm a bit lost what is the best the solution?

1) patch the commerce.controller.inc and commerce_order_load like in #56

or

2) use resetCache like described in https://drupal.org/node/1514618#comment-6185298

// Load the order I want to read from.
$order = commerce_order_load('1234');

// Immediately unload to make the order available to everyone again.
entity_get_controller('commerce_order')->resetCache(array($order->order_id));

// Read data values from $order, for example:
$order_wrapper = entity_metadata_wrapper('commerce_order', $order);
$line_items = $order_wrapper->commerce_line_items->value();

In my case I was hitting this problem consistently when running SimpleTest. If a SimpleTest method places orders via the internal browser then checking the results, this acts like two browser sessions hitting the same DB rows and triggering the deadlock.

I changed the code which checks the orders to use the following instead of commerce_order_load_multiple, and the timeouts have gone away:

<?php
 
protected function loadOrdersReadOnly() {
   
$orders     = call_user_func_array('commerce_order_load_multiple',
                                      
func_get_args());
   
entity_get_controller('commerce_order')->resetCache(
     
array_map(function($o) { return $o->order_id; },
               
$orders));
    return
$orders;
  }
?>

You've already got the order_id's as keys in commerce_order_load_multiple(), so you can drop the array_map stuff.

<?php
protected function loadOrdersReadOnly() {
   
$orders = call_user_func_array('commerce_order_load_multiple', func_get_args());
   
entity_get_controller('commerce_order')->resetCache(array_keys($orders));
    return
$orders;
}
?>

I cannot upgrade Drupal or Drupal modules using drush due to this error. Also, many site functions just fail because of this error (or sometimes, seem to hang forever) – for instance, clearing the Drupal cache is impossible.

The most common case I have is:

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: DELETE FROM {cache_bootstrap} WHERE (cid = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => variables ) in cache_clear_all() (line 176of /home/jstephan/public_html/drupal/includes/cache.inc).

I've converted all cache_* tables to MyISAM to see if it solves the issue, but it does not.

I've contacted my web host suggesting it's a MySQL issue, and maybe they can tune it, allocate more memory to it, or something. They replied and directed me back to this page, saying it was a Drupal issue.

Not sure what to do... this is a production site and there are certain critical things we can't do, like update product prices in Ubercart. If anyone has a tip, I'd love to hear it.

An update on my above post: The error has disappeared on the cache clearing action. Not sure what made it disappear. Our host might have tweaked something without telling us. We also performed a few small module updates.

However, trying to adjust product attribute options in Ubercart still causes the browser to hang indefinitely. Perhaps this is a separate issue, so I'll direct it to the Ubercart folks.

Same problem on our site under heady load... :(

Status:Needs work» Closed (duplicate)

I'm going to close this issue in favor of #1608294: Wrong database transaction handling: stalling transactions.
We definitely need to implement a way to load the order read only.
The current behavior is requiring me to disable locking completely on commerceguys sites.

This issue was a Drupal Core issue (that can happen across multiple modules) and that issue is specific to Drupal Commerce. Does this not seem like a fundamental Core problem, anymore?

Didn't notice that. In any case, there is no actual core bug described here.

Please explain what this issue is if it isn't a bug report.