15

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?

  • Are you running periodic cron? Is it successfully completing? – mpdonadio Mar 31 '14 at 16:18
  • Yes cron is successfully running. – Sumit Madan Mar 31 '14 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 '14 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 '14 at 16:23
14

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'");
  • 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 '14 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 '14 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 '14 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. – Marko Blazekovic Jan 23 '15 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 '15 at 21:05
4

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.

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.