19

I read multiple blogs, tried to google, it but didn't find any satisfaction from any solution. I have the following issues : 

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {cache_field} WHERE (cid LIKE :db_condition_placeholder_0 ESCAPE '\\') ; Array ( [:db_condition_placeholder_0] => field\_info:% ) in cache_clear_all() (line 163 of \includes\cache.inc).

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: SELECT 1 AS expression FROM {sessions} sessions WHERE ( (sid = :db_condition_placeholder_0) AND (ssid = :db_condition_placeholder_1) )

I tried to solve this by increasing innodb_pool_buffer_size and wait_timeout in my.ini but didn't worked.

I changed my cache table's engine from Innodb to MyIasm. The issue gone but it is not a perfect solution. The table engine will be changed after an upgrade of drupal. This issue doesn't affect anything on the site but I don't want this to occur.

Any solution guys?

Improve this question
4
  • Are you running periodic cron? Is it successfully completing?
    – mpdonadio ♦
    Mar 31, 2014 at 16:18
  • Yes cron is successfully running.
    – Sumit Madan
    Mar 31, 2014 at 16:19
  • What makes you think the cache table engine will be changed when upgrading? This has not been my experience.
    – keithm
    Mar 31, 2014 at 16:21
  • I mean if I upgrade the drupal the cache table will be changed from MyIasm to Innodb. No???
    – Sumit Madan
    Mar 31, 2014 at 16:23

3 Answers 3

Reset to default
21

Read and follow this post; it assumes InnoDB.

In particular, see the part saying to add the following to the settings.php file.

$databases['default']['default']['init_commands'] = array('isolation' => "SET SESSION tx_isolation='READ-COMMITTED'");
Improve this answer
6
  • In the give link, he is talking only for cache_field deadlock. But on my site, the deadlock and lock wait time out is occuring on all cache tables.
    – Sumit Madan
    Apr 1, 2014 at 7:15
  • Hey Mike, Thanks for the link. I checked your other post and adding this to settings.php $databases['default']['default']['init_commands'] = array('isolation' => "SET SESSION tx_isolation='READ-COMMITTED'"); solved my problem on my local server. I didn't checked it yet on live server. Please confirm that it will not give any issue in future if it is tested by you? I am using commerce kickstart.
    – Sumit Madan
    Apr 1, 2014 at 9:44
  • I use all recommendations live, heard reports that drupal.org uses read committed. Also be sure to be using the latest d7 release as it has a deadlock fix as well.
    – mikeytown2
    Apr 1, 2014 at 15:06
  • 1
    Mikeytown2 From what you are saying I understood that now drupal 7 versions have this solved? wondering about it as I still come up with same problem in latest version.
    – Blissful
    Jan 23, 2015 at 20:29
  • 1
    @MarkoBlazekovic Most of the major issues with deadlocking is half solved in D7. There are still some deadlocking and metadata locking issues with the database cache tables. I'm working on a module to solve them: drupal.org/project/apdqc. APDQC from my testing solves just about every single database locking issue in regards to the cache tables. Be sure to fix all the issues on the status report once it has been installed. Note that the module is still under heavy development as I have yet to release a non dev version of it.
    – mikeytown2
    Jan 23, 2015 at 21:05
5

The best way to get rid of deadlock errors once and for all is the Asynchronous Prefetch Database Query Cache module.

I can't recommend it enough. Brilliantly written and great performance gains.

Improve this answer
1

I tried to apply patch https://www.drupal.org/project/drupal/issues/1650930.

Still I am facing issue on AWS

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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