Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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
Comments
Comment #1
rfayRestarting mysqld got me past my immediate problem. Not sure what the correct fix to something like this is, or what might have caused it.
Comment #2
rfaySaw 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).
Comment #3
rfayI 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.
Comment #4
rfayBTW: @DamZ says that if this happens one should do
To immediately determine what the problem is.
Comment #5
rszrama CreditAttribution: rszrama commentedRecategorizing until we know if there's actually a bug in Commerce.
Comment #6
chazsmi CreditAttribution: chazsmi commentedI 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).
Comment #7
rfayHere 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.
Comment #8
rfayI 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?
Comment #9
rfayI'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.
Comment #10
mjpa CreditAttribution: mjpa commentedI'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.
Comment #11
rszrama CreditAttribution: rszrama commentedWell, 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?
Comment #12
mjpa CreditAttribution: mjpa commentedWhile 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:
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?
Comment #13
rszrama CreditAttribution: rszrama commentedHmm, 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...
Comment #14
mjpa CreditAttribution: mjpa commentedI 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?
Comment #15
basic CreditAttribution: basic commentedJust 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.
Comment #18
Damien Tournoud CreditAttribution: Damien Tournoud commentedComment #20
Damien Tournoud CreditAttribution: Damien Tournoud commented#15: field_sql_storage_deadlock-1320062-15.patch queued for re-testing.
Comment #22
basic CreditAttribution: basic commented#15: field_sql_storage_deadlock-1320062-15.patch queued for re-testing.
Comment #23
mjpa CreditAttribution: mjpa commentedIs 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.
Comment #24
guy_schneerson CreditAttribution: guy_schneerson commentedsubscribing
Comment #25
Josh Waihi CreditAttribution: Josh Waihi commentedUsing 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.
Comment #26
basic CreditAttribution: basic commented@Josh Waihi,
What version of MySQL are you using?
Comment #27
Josh Waihi CreditAttribution: Josh Waihi commentedMySQL 5.1.41-3ubuntu12.10 from Ubuntu Lucid LTS
Comment #28
ShErbO CreditAttribution: ShErbO commentedGents,
I'm getting a very similar error:
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
Comment #29
basic CreditAttribution: basic commentedThanks 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.
Comment #30
rfayRelated: #1363826: Deadlock issues when saving orders concurrently
Comment #31
wwedding CreditAttribution: wwedding commentedOn 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.
Comment #32
Jasonrj CreditAttribution: Jasonrj commentedI'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).
Comment #33
xatoo CreditAttribution: xatoo commentedI seem to get the same error:
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
Comment #34
rfayDoesn'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
Comment #35
calefilm CreditAttribution: calefilm commentedFirst 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.
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).
Comment #36
johnennew CreditAttribution: johnennew commentedstickywes - 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
Comment #37
wwedding CreditAttribution: wwedding commentedI 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
Comment #38
dga5000 CreditAttribution: dga5000 commentedI 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
Comment #39
ryanoreilly CreditAttribution: ryanoreilly commentedWas 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...
Comment #40
aidanlis CreditAttribution: aidanlis commentedI'm also getting this error a few times a week during heavy loads.
Comment #41
Todd Young CreditAttribution: Todd Young commentedI 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...
Comment #42
guy_schneerson CreditAttribution: guy_schneerson commentedI 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.
Comment #43
johnennew CreditAttribution: johnennew commentedI 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?
Comment #44
guy_schneerson CreditAttribution: guy_schneerson commented@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.
Comment #45
guy_schneerson CreditAttribution: guy_schneerson commented@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).
Comment #46
aidanlis CreditAttribution: aidanlis commentedThis could be caused by contributed modules loading orders without unlocking them, see #1514618: Developer documentation: Loading order entities without considering locking can cause problems
Comment #47
aidanlis CreditAttribution: aidanlis commentedHere'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.
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).
Comment #49
aidanlis CreditAttribution: aidanlis commentedCorrected patch.
Comment #51
aidanlis CreditAttribution: aidanlis commentedNot 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.
Comment #52
aidanlis CreditAttribution: aidanlis commentedRe-attaching patch with corrected paths.
Comment #54
lapith CreditAttribution: lapith commentedJust 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...
Comment #55
jason.fisher CreditAttribution: jason.fisher commentedMy 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.
Comment #56
aidanlis CreditAttribution: aidanlis commented@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.
Comment #58
aidanlis CreditAttribution: aidanlis commentedI don't know why it's trying to patch from [/var/lib/drupaltestbot/sites/default/files/checkout] ...
Comment #59
rfay@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.
Comment #61
aidanlis CreditAttribution: aidanlis commentedOhh, 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.
Comment #62
rszrama CreditAttribution: rszrama commentedDamien 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.
Comment #63
thim CreditAttribution: thim commentedHi 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();
Comment #64
warbo CreditAttribution: warbo commentedIn 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:
Comment #65
aidanlis CreditAttribution: aidanlis commentedYou've already got the order_id's as keys in commerce_order_load_multiple(), so you can drop the array_map stuff.
Comment #66
paul2 CreditAttribution: paul2 commentedI 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:
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.
Comment #67
paul2 CreditAttribution: paul2 commentedAn 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.
Comment #68
AnybodySame problem on our site under heady load... :(
Comment #69
bojanz CreditAttribution: bojanz commentedI'm going to close this issue in favor of #1608294: Provide a way to load an order without locking.
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.
Comment #70
wwedding CreditAttribution: wwedding commentedThis 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?
Comment #71
bojanz CreditAttribution: bojanz commentedDidn't notice that. In any case, there is no actual core bug described here.
Comment #72
wwedding CreditAttribution: wwedding commentedPlease explain what this issue is if it isn't a bug report.
Comment #73
yuseferi CreditAttribution: yuseferi commentedI have the same problem,what is the solution for this bug???
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] => 10849 ) in DrupalDefaultEntityController->load() (line 196 of /var/www/vhosts/******/httpdocs/includes/entity.inc).
Comment #74
suntower CreditAttribution: suntower commentedAs of today, having same issue. Kickstart 2. Core 7.32. One confirmed user hitting database. Occurs when one attempts to Edit an order or Add an order. Help!
Comment #75
howdytom CreditAttribution: howdytom as a volunteer commentedSame error message here while opening admin/commerce/orders. Running Commerce Kickstart 7.x-2.23
Comment #76
scotwith1tLooks like #2240427: Improve commerce entities locking has replaced #1608294: Provide a way to load an order without locking as the way forward but doesn't have much in the way of recent progress. Hopefully this will be corrected soon!
Comment #77
DamienMcKenna