0

getting lock messages as.... “Waiting for table metadata lock” occurs in database whenever there is “truncate cache_entity_node” call execute.

How can we disable truncate action on entitycache module ?

We are having issues with “metadata locks” with the DB so performance continue to be an issue here.

2

The locking issue is likely related to your database type and configs.

But you definitely do want to keep the truncate operation. To explain why, here is an order of operations on Entity CRUD in the entitycache.module. For example, on Entity update:

  • entitycache_entity_update() (an implementation of hook_entity_update()) is invoked
  • Skipping the condition check for a moment, cache_clear_all($id, 'cache_entity_' . $type); fires, and it seems your Entity type is node (hence why cache_entity_node cache bin is cleared. You want this, otherwise you will get stale data.
  • cache_clear_all in turn calls _cache_get_object($bin)->clear($cid, $wildcard);, and if for example your cache backend is the default cache implementation, this will call DrupalDatabaseCache::clear, which in turn will likely call db_truncate()
  • db_truncate() calls Database::getConnection($options['target'])->truncate($table, $options);, and that returns the TruncateQuery class
  • This is just so you can try to do these traces yourself, if you haven't already (or someone else reading can do this).
  • Skip to TruncateQuery::__toString(). If there is a transaction in progress, the SQL is 'DELETE FROM {' (and if not it's 'TRUNCATE {' (which is much faster). It sounds like you have a transaction in progress, and so you definitely want a row-by-row deletion with locks during (so it can roll back on failure if needed, whereas TRUNCATE is destructive). If you have many rows, depending on your database configs, this could possibly lock the entire table, not just the rows in question during the operation.

If this is the case, you can try various things to resolve it (caveat: you really want to contract a DBA or someone who knows your infrastructure and database setup very well). But for example, let's say your database type is MySQL: you could try reducing the lock wait timeout from the default. You could also try to change the transaction isolation level (preferable to READ COMMITTED). You'll want to read closely the comments above the $databases array in default.settings.php, specifically init_commands (issued for each connection which allow sessions settings to persist – you can set lock_wait_timeout and transaction_isolation here). There are various references to this, like Use READ COMMITTED for MySQL transactions core issue (for 8.x now, was for 7.x initially).

There is no single answer to this question, because like I said it very much depends on your setup. But I hope this gives you some direction toward solving your locking issue.

Update

Based on the issue tied to the patch your comment, I am adding more info to my answer:

Looking over Waiting for table metadata lock on cache_field table, the earlier patch in #3 gave way to an updated patch in #26. However, if you read all the information, in the end it looks like your best bet is to either 1. Not use entitycache module with DB cache backend (as Fabianx said - then again, I have experienced a similar issue using memcache so, again depending on your DB configs YMMV), or 2. try out the Asynchronous Prefetch Database Query Cache module that @mikeytown2 created to resolve this issue.

5
  • Thanks Scott -- I was about to apply the below patch. Does it make sense ... drupal.org/files/issues/…
    – Athakhan
    Sep 23 '16 at 0:38
  • Updated my answer based on the question in your comment. Sep 23 '16 at 1:00
  • I will go through all the information and once resolve I will share fix here.
    – Athakhan
    Sep 23 '16 at 1:04
  • 1
    Enabling stampede protection and moving locks into memory. # Add in stampede protection $conf['memcache_stampede_protection'] = TRUE; # Move semaphore out of the database and into memory for performance purposes $conf['lock_inc'] = './sites/all/modules/memcache/memcache-lock.inc'; In settings.php, improves DB performance and Memcache Stats shows better cache results... still monitoring
    – Athakhan
    Sep 23 '16 at 20:32
  • @user3494869 glad you got it sorted out for now. DB/app config is an ongoing process. I'd always recommend using memcache or redis over DB caching backend in production. Sep 25 '16 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.