SQLSTATE[40001]: Serialization failure: 1213 Deadlock

If you know of related issues, it usually helps to link to them in the related issues field. Documentation of the 40001 error.

#2601762: Mismatched entity and/or field definitions after upgrade from RC1 to RC2 is a separate issue with a solution.

I downgraded this to normal because support requests cannot be major.

I was not offering a solution, just a comment with some information. As I wrote, it would help people if you reference the similar issues you found in the related issues field, rather than sending them off searching.

I do not understand exactly what you mean in this passage:

The site setup is based on 3 instances of website setup or file system using one database. Whenever updates are made to the site cache is cleared from all 3 instances / nodes for changes to work properly esp. twig template changes.

Please explain more. As you add information, enhance the issue summary so that contributors can evaluate the issue quickly.

And what I mean by "I don't understand" is that the configuration does not seem ordinary.

Hi atul_shin,

Did you manage to solve the problem ?

I am having the same issue and my website infrastructure is quite similar.

You have different Drupal sites with separate databases sharing a files directory?

Or on closer reading, is it sites using the same database that is synced in a DB cluster? Comment #6 keeps confusing me because I think we are talking about one actual synchronized database but maybe not.

worth checking this too #2077827: Lock system is broken in many regards (was: needs a precision of at least 14 for timestamp comparison)

Jumping in here to provide an update, as I don't like leaving threads in limbo.

I put the server solution together - but I am not a Drupal developer; my exposure is pretty limited and I'm not responsible for the Drupal site for the customer per-se; but I do need to help when things go wrong to prove/disprove infrastructure issues.

I have not seen the Deadlock issue since Apr 14, 2017. A few things have changed since then; the website now has considerably less load and On Apr 16, the developers made a change to the site that enables render cache and page cache. They've also disabled rebuild.php if that makes a difference I'm not sure at this point.

In response to joelpittet; referencing the lock system/php precision - our php environment shows precision set to 14 so I do not believe this is relevant.

To clarify the DB questions from cilefen:

It's one website; the httpdocs (entire web directory) are synced across all 3 webnodes (node 1, node2 and node3) as described by Atul in post #6. Each webnode is also running MariaDB-galera; in a galera cluster. So when node1 is serving the website, it reads the data from it's local file system and it's local MariaDB instance. Configuration of the nodes are similar to https://severalnines.com/blog/scaling-drupal-multiple-servers-galera-clu...

If I see the deadlock error again, I will update the ticket. At this point, I'm not sure if it's resolved due to the changes, or just not showing up due to the significantly less load.

I guess you also applied updates?

There was an interesting change that I would expect to improve cache conflicts a lot, see my comment in #1679344-106: Race condition in node_save() when not using DB for cache_field.

My suggestion would be to close this, either as a confirmation that I'm right with my comment above or we close it as a duplicae of that issue and comment that while the mentioned change, that it did not fully solve the problem.

Thanks for updating the ticket and reporting back.

I came here after running into the following error on deploy, could also be related to #2077827: Lock system is broken in many regards (was: needs a precision of at least 14 for timestamp comparison) - First time i've seen the issue so i've not debugged any further yet but i'll post back if I have any more info.

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]:          [error]

Serialization failure: 1213 Deadlock found when trying to get lock;

try restarting transaction: INSERT INTO {cache_config} (cid, expire,

created, tags, checksum, data, serialized) VALUES

(:db_insert_placeholder_0, :db_insert_placeholder_1,

:db_insert_placeholder_2, :db_insert_placeholder_3,

:db_insert_placeholder_4, :db_insert_placeholder_5,

:db_insert_placeholder_6) ON DUPLICATE KEY UPDATE cid = VALUES(cid),

expire = VALUES(expire), created = VALUES(created), tags =

VALUES(tags), checksum = VALUES(checksum), data = VALUES(data),

serialized = VALUES(serialized); Array

(

    [:db_insert_placeholder_0] => last_write_timestamp_cache_config

    [:db_insert_placeholder_1] => -1

    [:db_insert_placeholder_2] => 1494288480.186

    [:db_insert_placeholder_3] => 

    [:db_insert_placeholder_4] => 0

    [:db_insert_placeholder_5] => d:1494288480.187;

    [:db_insert_placeholder_6] => 1

)

 in Drupal\Core\Cache\ChainedFastBackend->markAsOutdated() (line 306

of /data/app/core/lib/Drupal/Core/Cache/ChainedFastBackend.php).

I am still seeing

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in                                         [error]
/usr/share/nginx/html/docroot/core/lib/Drupal/Core/Database/Statement.php:59

routinely with my installation of 8.3.1. I see it primarily with drush cr.

I am willing to help troubleshoot this, but before I start dumping "how to reproduce" and env info in here that may not be helpful, let me ask if anyone has any specific questions that I might be able to answer.

Any one knowing if this is something that is getting any work? It seems like a pretty serious problem, and one that rather limit me right now.

Have anybody found workarounds or even solution for this problem with the setup? I want to run several instances with a galera setup. And this issue first started today, I am wondering if it is something that comes and goes?

I can add that when running though varnish (fastly) the site is taken down entirely.

Best,
Jonas Krat

It seems this one is related https://www.drupal.org/node/2871617

Ran into this today when loading a node edit form in 8.5.x-dev.

Are these duplicate issues?

They sure seem very similar. I marked #2871617: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction as a duplicate of this one (please reopen if anyone thinks they should be kept separate).

It might be worth noting I got this without a setup as complex as mentioned in the original issue. Not sure if all the people commenting here are running clusters like the OP, or are also seeing this in simpler setups.

I also found #2336627: Deadlock on cache_config (DatabaseBackend::setMultiple()) which might be related.

Hey,

It is also happening on my set up (NGINX / PHP7 / MariaDB / Drupal 8.4.2)....

Here is the latest error similar to previous ones...

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6), (:db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12, :db_insert_placeholder_13), (:db_insert_placeholder_14, :db_insert_placeholder_15, :db_insert_placeholder_16, :db_insert_placeholder_17, :db_insert_placeholder_18, :db_insert_placeholder_19, :db_insert_placeholder_20), (:db_insert_placeholder_21, :db_insert_placeholder_22, :db_insert_placeholder_23, :db_insert_placeholder_24, :db_insert_placeholder_25, :db_insert_placeholder_26, :db_insert_placeholder_27), (:db_insert_placeholder_28, :db_insert_placeholder_29, :db_insert_placeholder_30, :db_insert_placeholder_31, :db_insert_placeholder_32, :db_insert_placeholder_33, :db_insert_placeholder_34), (:db_insert_placeholder_35, :db_insert_placeholder_36, :db_insert_placeholder_37, :db_insert_placeholder_38, :db_insert_placeholder_39, :db_insert_placeholder_40, :db_insert_placeholder_41), (:db_insert_placeholder_42, :db_insert_placeholder_43, :db_insert_placeholder_44, :db_insert_placeholder_45, :db_insert_placeholder_46, :db_insert_placeholder_47, :db_insert_placeholder_48), (:db_insert_placeholder_49, :db_insert_placeholder_50, :db_insert_placeholder_51, :db_insert_placeholder_52, :db_insert_placeholder_53, :db_insert_placeholder_54, :db_insert_placeholder_55), (:db_insert_placeholder_56, :db_insert_placeholder_57, :db_insert_placeholder_58, :db_insert_placeholder_59, :db_insert_placeholder_60, :db_insert_placeholder_61, :db_insert_placeholder_62), (:db_insert_placeholder_63, :db_insert_placeholder_64, :db_insert_placeholder_65, :db_insert_placeholder_66, :db_insert_placeholder_67, :db_insert_placeholder_68, :db_insert_placeholder_69), (:db_insert_placeholder_70, :db_insert_placeholder_71, :db_insert_placeholder_72, :db_insert_placeholder_73, :db_insert_placeholder_74, :db_insert_placeholder_75, :db_insert_placeholder_76), (:db_insert_placeholder_77, :db_insert_placeholder_78, :db_insert_placeholder_79, :db_insert_placeholder_80, :db_insert_placeholder_81, :db_insert_placeholder_82, :db_insert_placeholder_83), (:db_insert_placeholder_84, :db_insert_placeholder_85, :db_insert_placeholder_86, :db_insert_placeholder_87, :db_insert_placeholder_88, :db_insert_placeholder_89, :db_insert_placeholder_90), (:db_insert_placeholder_91, :db_insert_placeholder_92, :db_insert_placeholder_93, :db_insert_placeholder_94, :db_insert_placeholder_95, :db_insert_placeholder_96, :db_insert_placeholder_97), (:db_insert_placeholder_98, :db_insert_placeholder_99, :db_insert_placeholder_100, :db_insert_placeholder_101, :db_insert_placeholder_102, :db_insert_placeholder_103, :db_insert_placeholder_104), (:db_insert_placeholder_105, :db_insert_placeholder_106, :db_insert_placeholder_107, :db_insert_placeholder_108, :db_insert_placeholder_109, :db_insert_placeholder_110, :db_insert_placeholder_111), (:db_insert_placeholder_112, :db_insert_placeholder_113, :db_insert_placeholder_114, :db_insert_placeholder_115, :db_insert_placeholder_116, :db_insert_placeholder_117, :db_insert_placeholder_118), (:db_insert_placeholder_119, :db_insert_placeholder_120, :db_insert_placeholder_121, :db_insert_placeholder_122, :db_insert_placeholder_123, :db_insert_placeholder_124, :db_insert_placeholder_125), (:db_insert_placeholder_126, :db_insert_placeholder_127, :db_insert_placeholder_128, :db_insert_placeholder_129, :db_insert_placeholder_130, :db_insert_placeholder_131, :db_insert_placeholder_132), (:db_insert_placeholder_133, :db_insert_placeholder_134, :db_insert_placeholder_135, :db_insert_placeholder_136, :db_insert_placeholder_137, :db_insert_placeholder_138, :db_insert_placeholder_139), (:db_insert_placeholder_140, :db_insert_placeholder_141, :db_insert_placeholder_142, :db_insert_placeholder_143, :db_insert_placeholder_144, :db_insert_placeholder_145, :db_insert_placeholder_146), (:db_insert_placeholder_147, :db_insert_placeholder_148, :db_insert_placeholder_149, :db_insert_placeholder_150, :db_insert_placeholder_151, :db_insert_placeholder_152, :db_insert_placeholder_153), (:db_insert_placeholder_154, :db_insert_placeholder_155, :db_insert_placeholder_156, :db_insert_placeholder_157, :db_insert_placeholder_158, :db_insert_placeholder_159, :db_insert_placeholder_160), (:db_insert_placeholder_161, :db_insert_placeholder_162, :db_insert_placeholder_163, :db_insert_placeholder_164, :db_insert_placeholder_165, :db_insert_placeholder_166, :db_insert_placeholder_167), (:db_insert_placeholder_168, :db_insert_placeholder_169, :db_insert_placeholder_170, :db_insert_placeholder_171, :db_insert_placeholder_172, :db_insert_placeholder_173, :db_insert_placeholder_174), (:db_insert_placeholder_175, :db_insert_placeholder_176, :db_insert_placeholder_177, :db_insert_placeholder_178, :db_insert_placeholder_179, :db_insert_placeholder_180, :db_insert_placeholder_181) ON DUPLICATE KEY UPDATE cid = VALUES(cid), expire = VALUES(expire), created = VALUES(created), tags = VALUES(tags), checksum = VALUES(checksum), data = VALUES(data), serialized = VALUES(serialized); Array ( [:db_insert_placeholder_0] => language.zh-hant:core.base_field_override.node.retailer.nid [:db_insert_placeholder_1] => -1 [:db_insert_placeholder_2] => 1513850688.839 [:db_insert_placeholder_3] => [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => b:0; [:db_insert_placeholder_6] => 1 [:db_insert_placeholder_7] => language.zh-hant:core.base_field_override.node.retailer.uuid [:db_insert_placeholder_8] => -1 [:db_insert_placeholder_9] => 1513850688.839 [:db_insert_placeholder_10] => [:db_insert_placeholder_11] => 0 [:db_insert_placeholder_12] => b:0; [:db_insert_placeholder_13] => 1 [:db_insert_placeholder_14] => language.zh-hant:core.base_field_override.node.retailer.vid [:db_insert_placeholder_15] => -1 [:db_insert_placeholder_16] => 1513850688.839 [:db_insert_placeholder_17] => [:db_insert_placeholder_18] => 0 [:db_insert_placeholder_19] => b:0; [:db_insert_placeholder_20] => 1 [:db_insert_placeholder_21] => language.zh-hant:core.base_field_override.node.retailer.langcode [:db_insert_placeholder_22] => -1 [:db_insert_placeholder_23] => 1513850688.839 [:db_insert_placeholder_24] => [:db_insert_placeholder_25] => 0 [:db_insert_placeholder_26] => b:0; [:db_insert_placeholder_27] => 1 [:db_insert_placeholder_28] => language.zh-hant:core.base_field_override.node.retailer.type [:db_insert_placeholder_29] => -1 [:db_insert_placeholder_30] => 1513850688.839 [:db_insert_placeholder_31] => [:db_insert_placeholder_32] => 0 [:db_insert_placeholder_33] => b:0; [:db_insert_placeholder_34] => 1 [:db_insert_placeholder_35] => language.zh-hant:core.base_field_override.node.retailer.revision_timestamp [:db_insert_placeholder_36] => -1 [:db_insert_placeholder_37] => 1513850688.839 [:db_insert_placeholder_38] => [:db_insert_placeholder_39] => 0 [:db_insert_placeholder_40] => b:0; [:db_insert_placeholder_41] => 1 [:db_insert_placeholder_42] => language.zh-hant:core.base_field_override.node.retailer.revision_uid [:db_insert_placeholder_43] => -1 [:db_insert_placeholder_44] => 1513850688.839 [:db_insert_placeholder_45] => [:db_insert_placeholder_46] => 0 [:db_insert_placeholder_47] => b:0; [:db_insert_placeholder_48] => 1 [:db_insert_placeholder_49] => language.zh-hant:core.base_field_override.node.retailer.revision_log [:db_insert_placeholder_50] => -1 [:db_insert_placeholder_51] => 1513850688.839 [:db_insert_placeholder_52] => [:db_insert_placeholder_53] => 0 [:db_insert_placeholder_54] => b:0; [:db_insert_placeholder_55] => 1 [:db_insert_placeholder_56] => language.zh-hant:core.base_field_override.node.retailer.status [:db_insert_placeholder_57] => -1 [:db_insert_placeholder_58] => 1513850688.839 [:db_insert_placeholder_59] => [:db_insert_placeholder_60] => 0 [:db_insert_placeholder_61] => b:0; [:db_insert_placeholder_62] => 1 [:db_insert_placeholder_63] => language.zh-hant:core.base_field_override.node.retailer.title [:db_insert_placeholder_64] => -1 [:db_insert_placeholder_65] => 1513850688.839 [:db_insert_placeholder_66] => [:db_insert_placeholder_67] => 0 [:db_insert_placeholder_68] => b:0; [:db_insert_placeholder_69] => 1 [:db_insert_placeholder_70] => language.zh-hant:core.base_field_override.node.retailer.uid [:db_insert_placeholder_71] => -1 [:db_insert_placeholder_72] => 1513850688.839 [:db_insert_placeholder_73] => [:db_insert_placeholder_74] => 0 [:db_insert_placeholder_75] => b:0; [:db_insert_placeholder_76] => 1 [:db_insert_placeholder_77] => language.zh-hant:core.base_field_override.node.retailer.created [:db_insert_placeholder_78] => -1 [:db_insert_placeholder_79] => 1513850688.839 [:db_insert_placeholder_80] => [:db_insert_placeholder_81] => 0 [:db_insert_placeholder_82] => b:0; [:db_insert_placeholder_83] => 1 [:db_insert_placeholder_84] => language.zh-hant:core.base_field_override.node.retailer.changed [:db_insert_placeholder_85] => -1 [:db_insert_placeholder_86] => 1513850688.839 [:db_insert_placeholder_87] => [:db_insert_placeholder_88] => 0 [:db_insert_placeholder_89] => b:0; [:db_insert_placeholder_90] => 1 [:db_insert_placeholder_91] => language.zh-hant:core.base_field_override.node.retailer.promote [:db_insert_placeholder_92] => -1 [:db_insert_placeholder_93] => 1513850688.839 [:db_insert_placeholder_94] => [:db_insert_placeholder_95] => 0 [:db_insert_placeholder_96] => b:0; [:db_insert_placeholder_97] => 1 [:db_insert_placeholder_98] => language.zh-hant:core.base_field_override.node.retailer.sticky [:db_insert_placeholder_99] => -1 [:db_insert_placeholder_100] => 1513850688.839 [:db_insert_placeholder_101] => [:db_insert_placeholder_102] => 0 [:db_insert_placeholder_103] => b:0; [:db_insert_placeholder_104] => 1 [:db_insert_placeholder_105] => language.zh-hant:core.base_field_override.node.retailer.default_langcode [:db_insert_placeholder_106] => -1 [:db_insert_placeholder_107] => 1513850688.84 [:db_insert_placeholder_108] => [:db_insert_placeholder_109] => 0 [:db_insert_placeholder_110] => b:0; [:db_insert_placeholder_111] => 1 [:db_insert_placeholder_112] => language.zh-hant:core.base_field_override.node.retailer.revision_translation_affected [:db_insert_placeholder_113] => -1 [:db_insert_placeholder_114] => 1513850688.84 [:db_insert_placeholder_115] => [:db_insert_placeholder_116] => 0 [:db_insert_placeholder_117] => b:0; [:db_insert_placeholder_118] => 1 [:db_insert_placeholder_119] => language.zh-hant:core.base_field_override.node.retailer.content_translation_source [:db_insert_placeholder_120] => -1 [:db_insert_placeholder_121] => 1513850688.84 [:db_insert_placeholder_122] => [:db_insert_placeholder_123] => 0 [:db_insert_placeholder_124] => b:0; [:db_insert_placeholder_125] => 1 [:db_insert_placeholder_126] => language.zh-hant:core.base_field_override.node.retailer.content_translation_outdated [:db_insert_placeholder_127] => -1 [:db_insert_placeholder_128] => 1513850688.84 [:db_insert_placeholder_129] => [:db_insert_placeholder_130] => 0 [:db_insert_placeholder_131] => b:0; [:db_insert_placeholder_132] => 1 [:db_insert_placeholder_133] => language.zh-hant:core.base_field_override.node.retailer.ds_switch [:db_insert_placeholder_134] => -1 [:db_insert_placeholder_135] => 1513850688.84 [:db_insert_placeholder_136] => [:db_insert_placeholder_137] => 0 [:db_insert_placeholder_138] => b:0; [:db_insert_placeholder_139] => 1 [:db_insert_placeholder_140] => language.zh-hant:core.base_field_override.node.retailer.metatag [:db_insert_placeholder_141] => -1 [:db_insert_placeholder_142] => 1513850688.84 [:db_insert_placeholder_143] => [:db_insert_placeholder_144] => 0 [:db_insert_placeholder_145] => b:0; [:db_insert_placeholder_146] => 1 [:db_insert_placeholder_147] => language.zh-hant:core.base_field_override.node.retailer.path [:db_insert_placeholder_148] => -1 [:db_insert_placeholder_149] => 1513850688.84 [:db_insert_placeholder_150] => [:db_insert_placeholder_151] => 0 [:db_insert_placeholder_152] => b:0; [:db_insert_placeholder_153] => 1 [:db_insert_placeholder_154] => language.zh-hant:core.base_field_override.node.retailer.rh_action [:db_insert_placeholder_155] => -1 [:db_insert_placeholder_156] => 1513850688.84 [:db_insert_placeholder_157] => [:db_insert_placeholder_158] => 0 [:db_insert_placeholder_159] => b:0; [:db_insert_placeholder_160] => 1 [:db_insert_placeholder_161] => language.zh-hant:core.base_field_override.node.retailer.rh_redirect [:db_insert_placeholder_162] => -1 [:db_insert_placeholder_163] => 1513850688.84 [:db_insert_placeholder_164] => [:db_insert_placeholder_165] => 0 [:db_insert_placeholder_166] => b:0; [:db_insert_placeholder_167] => 1 [:db_insert_placeholder_168] => language.zh-hant:core.base_field_override.node.retailer.rh_redirect_response [:db_insert_placeholder_169] => -1 [:db_insert_placeholder_170] => 1513850688.84 [:db_insert_placeholder_171] => [:db_insert_placeholder_172] => 0 [:db_insert_placeholder_173] => b:0; [:db_insert_placeholder_174] => 1 [:db_insert_placeholder_175] => language.zh-hant:core.base_field_override.node.retailer.menu_link [:db_insert_placeholder_176] => -1 [:db_insert_placeholder_177] => 1513850688.84 [:db_insert_placeholder_178] => [:db_insert_placeholder_179] => 0 [:db_insert_placeholder_180] => b:0; [:db_insert_placeholder_181] => 1 ) in Drupal\Core\Cache\ChainedFastBackend->setMultiple() (line 191 of /home/FOOO/public_html/core/lib/Drupal/Core/Cache/ChainedFastBackend.php).

It sometimes happens while doing drush cr or when clearing caches via UI .... And it even happened while site doing cron jobs...

The main problem I found is that we have 3 drupal 8 sites on the same server... If any of get affected in turn it kills other sites and the only way to get them back is to perform restart of PHP-FPM ...

I do think this is rather serious issue as I cannot identify actual problem I only see symptoms like this popping up at random times without any real correlation... Also I use Cloudflare which in turn puts 502 error for all pages...

Cheers,
Povilas

@puogintas Is there anything special about the database server system configuration (clustering, etc)?

Obviously this kind of thing is difficult to reproduce but it would be nice.

I tried to reproduce it a few days ago but wasn't able to.

This should probably be a bug report now, and major (since it stops Drupal from working in different scenarios)?

It's major by definition.

I am using JSONAPI to create nodes and getting this error when two or more nodes are being PATACHED/UPDATED with an ID for a entity_reference field to another bundle of node type. Error occurs when Drupal tries to insert into the reference field table.

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {node__field_collection_ref} (entity_id, revision_id, bundle, delta, langcode, field_collection_ref_target_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5); Array ( [:db_insert_placeholder_0] => 1597 [:db_insert_placeholder_1] => 1597 [:db_insert_placeholder_2] => creation [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => en [:db_insert_placeholder_5] => 1600 )

Same config and error as #32 : it happens randomly after updating a node.

Just found it in my db log:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3,

I myself was not affected :-) only a Guest Session catching a feed : /taxonomy/term/5455/feed

Using D8.4.5 and PHP7.1

We saw the same as #32 when doing node/media save on D8.4.5.

Drush cr has also triggered the same error randomly on about all D8 versions.
But of course I'm not able to systematically reproduce it either.

In our case, my colleague (tahirmus) has found a solution to this problem. We had this problem while bulk importing. It's related to the gap lock of MySql in InnoDB engine.

Here you can find more information how to solve your problem regarding gap lock.

• https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
• https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

I hope, it will helps you to solve your problem.

Cheers, Ramy

In our case, switching the isolation level to READ COMMITTED solved the problem.

another core issue related to this problem:
https://www.drupal.org/project/drupal/issues/1650930

Same problem for me since yesterday. How is it that the problem appears overnight ?

Is that bad ? it's my production site

Should we restore the server ?

 [error]  SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6), (:db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12, :db_insert_placeholder_13), (:db_insert_placeholder_14, :db_insert_placeholder_15, :db_insert_placeholder_16, :db_insert_placeholder_17, :db_insert_placeholder_18, :db_insert_placeholder_19, :db_insert_placeholder_20), (:db_insert_placeholder_21, :db_insert_placeholder_22, :db_insert_placeholder_23, :db_insert_placeholder_24, :db_insert_placeholder_25, :db_insert_placeholder_26, :db_insert_placeholder_27), (:db_insert_placeholder_28, :db_insert_placeholder_29, :db_insert_placeholder_30, :db_insert_placeholder_31, :db_insert_placeholder_32, :db_insert_placeholder_33, :db_insert_placeholder_34), (:db_insert_placeholder_35, :db_insert_placeholder_36, :db_insert_placeholder_37, :db_insert_placeholder_38, :db_insert_placeholder_39, :db_insert_placeholder_40, :db_insert_placeholder_41), (:db_insert_placeholder_42, :db_insert_placeholder_43, :db_insert_placeholder_44, :db_insert_placeholder_45, :db_insert_placeholder_46, :db_insert_placeholder_47, :db_insert_placeholder_48), (:db_insert_placeholder_49, :db_insert_placeholder_50, :db_insert_placeholder_51, :db_insert_placeholder_52, :db_insert_placeholder_53, :db_insert_placeholder_54, :db_insert_placeholder_55), (:db_insert_placeholder_56, :db_insert_placeholder_57, :db_insert_placeholder_58, :db_insert_placeholder_59, :db_insert_placeholder_60, :db_insert_placeholder_61, :db_insert_placeholder_62), (:db_insert_placeholder_63, :db_insert_placeholder_64, :db_insert_placeholder_65, :db_insert_placeholder_66, :db_insert_placeholder_67, :db_insert_placeholder_68, :db_insert_placeholder_69), (:db_insert_placeholder_70, :db_insert_placeholder_71, :db_insert_placeholder_72, :db_insert_placeholder_73, :db_insert_placeholder_74, :db_insert_placeholder_75, :db_insert_placeholder_76), (:db_insert_placeholder_77, :db_insert_placeholder_78, :db_insert_placeholder_79, :db_insert_placeholder_80, :db_insert_placeholder_81, :db_insert_placeholder_82, :db_insert_placeholder_83), (:db_insert_placeholder_84, :db_insert_placeholder_85, :db_insert_placeholder_86, :db_insert_placeholder_87, :db_insert_placeholder_88, :db_insert_placeholder_89, :db_insert_placeholder_90), (:db_insert_placeholder_91, :db_insert_placeholder_92, :db_insert_placeholder_93, :db_insert_placeholder_94, :db_insert_placeholder_95, :db_insert_placeholder_96, :db_insert_placeholder_97), (:db_insert_placeholder_98, :db_insert_placeholder_99, :db_insert_placeholder_100, :db_insert_placeholder_101, :db_insert_placeholder_102, :db_insert_placeholder_103, :db_insert_placeholder_104), (:db_insert_placeholder_105, :db_insert_placeholder_106, :db_insert_placeholder_107, :db_insert_placeholder_108, :db_insert_placeholder_109, :db_insert_placeholder_110, :db_insert_placeholder_111), (:db_insert_placeholder_112, :db_insert_placeholder_113, :db_insert_placeholder_114, :db_insert_placeholder_115, :db_insert_placeholder_116, :db_insert_placeholder_117, :db_insert_placeholder_118), (:db_insert_placeholder_119, :db_insert_placeholder_120, :db_insert_placeholder_121, :db_insert_placeholder_122, :db_insert_placeholder_123, :db_insert_placeholder_124, :db_insert_placeholder_125), (:db_insert_placeholder_126, :db_insert_placeholder_127, :db_insert_placeholder_128, :db_insert_placeholder_129, :db_insert_placeholder_130, :db_insert_placeholder_131, :db_insert_placeholder_132), (:db_insert_placeholder_133, :db_insert_placeholder_134, :db_insert_placeholder_135, :db_insert_placeholder_136, :db_insert_placeholder_137, :db_insert_placeholder_138, :db_insert_placeholder_139) ON DUPLICATE KEY UPDATE cid = VALUES(cid), expire = VALUES(expire), created = VALUES(created), tags = VALUES(tags), checksum = VALUES(checksum), data = VALUES(data), serialized = VALUES(serialized); Array
(
    [:db_insert_placeholder_0] => core.base_field_override.node.demande.nid
    [:db_insert_placeholder_1] => -1
    [:db_insert_placeholder_2] => 1519795165.859
    [:db_insert_placeholder_3] => 
    [:db_insert_placeholder_4] => 0
    [:db_insert_placeholder_5] => b:0;
    [:db_insert_placeholder_6] => 1
    [:db_insert_placeholder_7] => core.base_field_override.node.demande.uuid
    [:db_insert_placeholder_8] => -1
    [:db_insert_placeholder_9] => 1519795165.859
    [:db_insert_placeholder_10] => 
    [:db_insert_placeholder_11] => 0
    [:db_insert_placeholder_12] => b:0;
    [:db_insert_placeholder_13] => 1
    [:db_insert_placeholder_14] => core.base_field_override.node.demande.vid
    [:db_insert_placeholder_15] => -1
    [:db_insert_placeholder_16] => 1519795165.859
    [:db_insert_placeholder_17] => 
    [:db_insert_placeholder_18] => 0
    [:db_insert_placeholder_19] => b:0;
    [:db_insert_placeholder_20] => 1
    [:db_insert_placeholder_21] => core.base_field_override.node.demande.langcode
    [:db_insert_placeholder_22] => -1
    [:db_insert_placeholder_23] => 1519795165.859
    [:db_insert_placeholder_24] => 
    [:db_insert_placeholder_25] => 0
    [:db_insert_placeholder_26] => b:0;
    [:db_insert_placeholder_27] => 1
    [:db_insert_placeholder_28] => core.base_field_override.node.demande.type
    [:db_insert_placeholder_29] => -1
    [:db_insert_placeholder_30] => 1519795165.859
    [:db_insert_placeholder_31] => 
    [:db_insert_placeholder_32] => 0
    [:db_insert_placeholder_33] => b:0;
    [:db_insert_placeholder_34] => 1
    [:db_insert_placeholder_35] => core.base_field_override.node.demande.revision_timestamp
    [:db_insert_placeholder_36] => -1
    [:db_insert_placeholder_37] => 1519795165.859
    [:db_insert_placeholder_38] => 
    [:db_insert_placeholder_39] => 0
    [:db_insert_placeholder_40] => b:0;
    [:db_insert_placeholder_41] => 1
    [:db_insert_placeholder_42] => core.base_field_override.node.demande.revision_uid
    [:db_insert_placeholder_43] => -1
    [:db_insert_placeholder_44] => 1519795165.859
    [:db_insert_placeholder_45] => 
    [:db_insert_placeholder_46] => 0
    [:db_insert_placeholder_47] => b:0;
    [:db_insert_placeholder_48] => 1
    [:db_insert_placeholder_49] => core.base_field_override.node.demande.revision_log
    [:db_insert_placeholder_50] => -1
    [:db_insert_placeholder_51] => 1519795165.859
    [:db_insert_placeholder_52] => 
    [:db_insert_placeholder_53] => 0
    [:db_insert_placeholder_54] => b:0;
    [:db_insert_placeholder_55] => 1
    [:db_insert_placeholder_56] => core.base_field_override.node.demande.status
    [:db_insert_placeholder_57] => -1
    [:db_insert_placeholder_58] => 1519795165.859
    [:db_insert_placeholder_59] => 
    [:db_insert_placeholder_60] => 0
    [:db_insert_placeholder_61] => b:0;
    [:db_insert_placeholder_62] => 1
    [:db_insert_placeholder_63] => core.base_field_override.node.demande.title
    [:db_insert_placeholder_64] => -1
    [:db_insert_placeholder_65] => 1519795165.859
    [:db_insert_placeholder_66] => 
    [:db_insert_placeholder_67] => 0
    [:db_insert_placeholder_68] => b:0;
    [:db_insert_placeholder_69] => 1
    [:db_insert_placeholder_70] => core.base_field_override.node.demande.uid
    [:db_insert_placeholder_71] => -1
    [:db_insert_placeholder_72] => 1519795165.859
    [:db_insert_placeholder_73] => 
    [:db_insert_placeholder_74] => 0
    [:db_insert_placeholder_75] => b:0;
    [:db_insert_placeholder_76] => 1
    [:db_insert_placeholder_77] => core.base_field_override.node.demande.created
    [:db_insert_placeholder_78] => -1
    [:db_insert_placeholder_79] => 1519795165.859
    [:db_insert_placeholder_80] => 
    [:db_insert_placeholder_81] => 0
    [:db_insert_placeholder_82] => b:0;
    [:db_insert_placeholder_83] => 1
    [:db_insert_placeholder_84] => core.base_field_override.node.demande.changed
    [:db_insert_placeholder_85] => -1
    [:db_insert_placeholder_86] => 1519795165.859
    [:db_insert_placeholder_87] => 
    [:db_insert_placeholder_88] => 0
    [:db_insert_placeholder_89] => b:0;
    [:db_insert_placeholder_90] => 1
    [:db_insert_placeholder_91] => core.base_field_override.node.demande.promote
    [:db_insert_placeholder_92] => -1
    [:db_insert_placeholder_93] => 1519795165.859
    [:db_insert_placeholder_94] => 
    [:db_insert_placeholder_95] => 0
    [:db_insert_placeholder_96] => a:16:{s:4:"uuid";s:36:"38e5e18e-3593-4ca1-8364-4f8348600829";s:8:"langcode";s:2:"fr";s:6:"status";b:1;s:12:"dependencies";a:1:{s:6:"config";a:1:{i:0;s:17:"node.type.demande";}}s:2:"id";s:20:"node.demande.promote";s:10:"field_name";s:7:"promote";s:11:"entity_type";s:4:"node";s:6:"bundle";s:7:"demande";s:5:"label";s:22:"Promoted to front page";s:11:"description";s:0:"";s:8:"required";b:0;s:12:"translatable";b:1;s:13:"default_value";a:1:{i:0;a:1:{s:5:"value";i:0;}}s:22:"default_value_callback";s:0:"";s:8:"settings";a:2:{s:8:"on_label";s:2:"On";s:9:"off_label";s:3:"Off";}s:10:"field_type";s:7:"boolean";}
    [:db_insert_placeholder_97] => 1
    [:db_insert_placeholder_98] => core.base_field_override.node.demande.sticky
    [:db_insert_placeholder_99] => -1
    [:db_insert_placeholder_100] => 1519795165.859
    [:db_insert_placeholder_101] => 
    [:db_insert_placeholder_102] => 0
    [:db_insert_placeholder_103] => b:0;
    [:db_insert_placeholder_104] => 1
    [:db_insert_placeholder_105] => core.base_field_override.node.demande.default_langcode
    [:db_insert_placeholder_106] => -1
    [:db_insert_placeholder_107] => 1519795165.859
    [:db_insert_placeholder_108] => 
    [:db_insert_placeholder_109] => 0
    [:db_insert_placeholder_110] => b:0;
    [:db_insert_placeholder_111] => 1
    [:db_insert_placeholder_112] => core.base_field_override.node.demande.revision_translation_affected
    [:db_insert_placeholder_113] => -1
    [:db_insert_placeholder_114] => 1519795165.859
    [:db_insert_placeholder_115] => 
    [:db_insert_placeholder_116] => 0
    [:db_insert_placeholder_117] => b:0;
    [:db_insert_placeholder_118] => 1
    [:db_insert_placeholder_119] => core.base_field_override.node.demande.metatag
    [:db_insert_placeholder_120] => -1
    [:db_insert_placeholder_121] => 1519795165.859
    [:db_insert_placeholder_122] => 
    [:db_insert_placeholder_123] => 0
    [:db_insert_placeholder_124] => b:0;
    [:db_insert_placeholder_125] => 1
    [:db_insert_placeholder_126] => core.base_field_override.node.demande.path
    [:db_insert_placeholder_127] => -1
    [:db_insert_placeholder_128] => 1519795165.859
    [:db_insert_placeholder_129] => 
    [:db_insert_placeholder_130] => 0
    [:db_insert_placeholder_131] => b:0;
    [:db_insert_placeholder_132] => 1
    [:db_insert_placeholder_133] => core.base_field_override.node.demande.menu_link
    [:db_insert_placeholder_134] => -1
    [:db_insert_placeholder_135] => 1519795165.859
    [:db_insert_placeholder_136] => 
    [:db_insert_placeholder_137] => 0
    [:db_insert_placeholder_138] => b:0;
    [:db_insert_placeholder_139] => 1
)

@zenimagine

Try #10 from #1650930: Use READ COMMITTED for MySQL transactions

Thank you. I am not very good at changing code.

My database errors are scary

1) What is this problem ?

2) I have 3 drupal installations on my server, is it because of that ?

3) Is this problem serious or did I continue to use Drupal normally ?

4) Why does this happen now and not before ?

5) Does this mean that my database is damaged ?

@zenimagine

This issue seems to append randomly.
It is annoying, but not serious I think : cache_config table.

Try #10 from #1650930: Use READ COMMITTED for MySQL transactions

means adding this snippet into your settings.php file:

$databases['default']['default']['init_commands'] = array(
  'isolation' => "SET SESSION tx_isolation='READ-COMMITTED'"
);

Best.

Thank you very much for these explanations.

Reassure me this does not risk damaging the database ?

Because the problem is on my site in production.

Bad news : despite READ-COMMITTED, the error came back :

Drupal\Core\Database\DatabaseExceptionWrapper : SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum,[...]

This issue always happens during node updates.
Very annoying indeed...

Sometimes, website editors cannot work anymore because too many errors happen.
I think this issue should be Critical.

Had this problem too after updating some modules on D 8.4.5. Clearing cache tables in database via MyPhpAdmin seems to solve the problem. Needed to clear chache two times

We are also running into these deadlocks regularly, typically during our git rollout using jenkins/ansible, rather randomly while processing drush updb/entup/cr/cron/cim.

Drupal 8 (currently 8.5.1, but this is happening since the beginning of D8)
Drush 8.1.16
Nginx
MySQL 5.7.21-log
PHP 7.0.27-0+deb9u1

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6), (:db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12, :db_insert_placeholder_13), (:db_insert_placeholder_14, :db_insert_placeholder_15, :db_insert_placeholder_16, :db_insert_placeholder_17, :db_insert_placeholder_18, :db_insert_placeholder_19, :db_insert_placeholder_20), (:db_insert_placeholder_ …

This is causing regular outages with a client's web site. I agree with @brulain that this should be critical.

My site in production is getting worse because of this problem. I do not understand why the problem occurred in one fell swoop

If you can install memcached on your server, install it and use the contrib module https://www.drupal.org/project/memcache.
All my deadlocks issues disappeared.

It also sometimes happens when flushing all caches.
@ j.b memchached is (most of time) not available on mutualised hosting.

We have same White Page problem, on the front page.
We install memcached on the VPS server (Ubuntu 16.04), and memcache module.

But, it doesn't solve the problem !

Reoccurs 2x this morning...

D8.5.1
PHP 7.1.15

:-(

Thanks. Didier

Hi,

I was also getting the same error in cache_config as :

Uncaught PHP Exception Drupal\\Core\\Database\\DatabaseExceptionWrapper: "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6), (:db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12, :db_insert_placeholder_13), (:db_insert_placeholder_14, :db_insert_placeholder_15, :db_insert_placeholder_16, :db_insert_placeholder_17, :db_insert_placeholder_18

Placed the following in settings.php :

$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";

And then monitored the performance with apache bench as : ab -n 100000 -c 20000

I didnot get the error, about 5 times running the scenario.

Please let me know if this solution works and can be used ahead.

Drupal 8.5.2

I was also getting the same error in cache_config as :

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6), (:db_insert_placeholder_7...

Tbought I'd share my workaround for this issue (Drupal 8 on IIS, MySql) is to deactivate Search API, clear all caches, reactivate Search API and finally reindex everything. A few extra steps, but it prevents deadlock error everytime (thus far and several months into the project).

Please review #2966607: Invalidating 'node_list' and other broad cache tags early in a transaction severely increases lock wait time and probability of deadlock. I think that might be a better solution than what has been proposed here. But to be certain, it needs reviews!

I don't know solution, but I am also experiencing "Serialization failure: 1213 Deadlock found when trying to get lock;" on semaphore and cache_ tables. This might happen 1-2 times in a 3 months with very low load of servers, sometimes during cron jobs or deployments.

I tried to replicate deadlock on dev laptop (drupalvm with virtualbox) by creating higher load, but could't. Then I tried to replicate on SIT/DEV environment and it was very easy to get Deadlock.

Steps to replicate:
* Do fresh Drupal installation
* Execute drush cr constantly. This bash command can do it "while :; do drush cr; done"
* Open page with some views results, like /admin/reports/dblog or /admin/people
* 1 out of 3 requests was getting Deadlock
* This way doesn't work on all environments/installations.

I also tried to replicate on another Dev/Virtual server where DB and apache is on same machine and Deadlock didn't happen.

SIT/DEV environment have Maria DB, webserver and DB server are on separate machines.

Would be nice to solve this without "transaction-isolation = READ-COMMITTED" or memcached.

Like several here, I've been confronted with this problem since 8.3.x, even in low-traffic access scenarios, most notably during a "drush cr", on MySQL 5.7, be it with DB cache (then getting it on cache_config inserts too) or with Memcache (then only getting it on the semaphore table during drush core-cron).

Just got it today on a dev workstation with PHP 7.2 / MariaDB 10 again, definitely in a single-user scenario, during a Drush CR.

Already had this error.

You can try a :
drush cc render

before reattempt the :
drush cr

I'm facing this issue as well. I have Golang script that loads nodes concurrently and see this error pretty consistently. I wrote this simple bash script to replicate this issue:

#!/bin/bash

for i in `seq 1 50`;
do
        drush php-eval '$node = Drupal\node\Entity\Node::create(["type" => "page", "langcode" => "en", "uid" => "1"]); $node->title = "Test"; $node->save(); echo "Node Created\n";' &
done

wait

Drop this script into the base of your drupal installation and give it a run. You will see the serialization errors pretty consistently. WARNING this script creates page nodes. So don't run it on site in production.

What appears to be a fix for me is adding the settings below to my mysql my.cnf. This file usually is located at /etc/my.cnf on a mysql installation:

innodb_locks_unsafe_for_binlog = 1
transaction-isolation=REPEATABLE-READ

Note these setting have to be under '[mysqld]' statement in the config file. Don't just randomly put them in the configuration file.

I ran this test on my local machine with docker. I'm going to move to testing it on my companies dev severs next and will update my findings after that.

Interesting workaround, however, this isolation level is costly. Quoting Percona "This however comes at high cost – InnoDB needs to maintain transaction history up to the point of transaction start, which can be very expensive. " ( https://www.percona.com/blog/2015/01/14/mysql-performance-implications-o... )

Also, from the MySQL docs at https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_in... "Enabling innodb_locks_unsafe_for_binlog may cause phantom problems [...]" and "innodb_locks_unsafe_for_binlog is deprecated and will be removed in a future MySQL release.".

All in all, it seems this may be useful as a temporary hack, but the real solution involves updating the transaction isolation level for the session when doing operations which may lock and resetting it afterwards, but that's much more work.

MySQL5.7.22 PHP7.2.4 Drupal 8.6.0 commerce2.9 This message appears intermittently (testing with Bartik). Tried to force it by using various combinations of "Flush all caches", running update.php, and visiting various pages and changing content. I have not found a sequence that reliability generates the error. Of course the Drupal 8 signature hash... Warning: rename(sites/default/files/php/twig/ still exists in 8.6.

I encounter this error while saving a page with lots off paragraph.
I solve it with raising the php_admin_value max_input_vars (there 1300 inputs inside the page).

I don't know if it's the source of the problem cause after changing it and restarting apache I was unable to reproduce it even if I lower this value.

Hope could help some

No effect even i applied DB isolation fix. It seems we don't have solution yet.
I found this issue always when i flush cache only.
Still looking for solution......

same problem.

[mysqld]
transaction-isolation = READ-COMMITTED

fixed it.

Just in case it helps with fixing this: I was getting the error while trying to do a mass import of Webform results on Drupal 8 using the experimental import feature. I was getting to around 1440 records imported and this error kept popping up consistently.

I also use memcache on this site. I removed the memcache configuration lines from my settings.php file and was able to import the full set of Webform results without error.

The lines I commented out for my import to work were:

$conf['cache_backends'][] = 'sites/all/modules/memcache/memcache.inc';
$conf['cache_default_class'] = 'MemCacheDrupal';
$conf['cache_class_cache_form'] = 'DrupalDatabaseCache';
$conf['page_cache_without_database'] = TRUE;
$conf['page_cache_invoke_hooks'] = FALSE;

I think that's unrelated. Also your issue might be that you didn't enable the memcache lock in your configuration.

#56 works for us.

I don't think this issue should be marked as fixed.

@prafull, #56 works for us.

Placed the following in settings.php :

$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";

Yes, After Applying #56 Issue not replicated again.

Apply the changes only in default.settings.php.

Apply (add) the same in your settings.php or settings.local.php manually:

$databases['default']['default'] = [
  'init_commands' => [
    'isolation' => "SET SESSION tx_isolation='READ-COMMITTED'",
    'lock_wait_timeout' => 'SET SESSION innodb_lock_wait_timeout = 20',
    'wait_timeout' => 'SET SESSION wait_timeout = 600',
  ],
];

This is a private case!

Apply (add) the following into your settings.php or settings.local.php manually:

$databases['default']['default'] = [
  'init_commands' => [
    'isolation' => "SET SESSION tx_isolation='READ-COMMITTED'",
    'lock_wait_timeout' => 'SET SESSION innodb_lock_wait_timeout = 20',
    'wait_timeout' => 'SET SESSION wait_timeout = 600',
  ],
];

Hm. I don't know if I would exactly call that "Fixed". The error might be avoided with some different database settings, but fact is, that it happens with default database settings. Which it (IMHO) shouldn't. Drupal should either work with default database settings without any error (e.g. by retrying automatically) or those settings should be included in the default configuration.

Please also note that using Galera Cluster in multi-master setup, only tx_isolation REPEATABLE_READ is available AFAIK.

Should this be re-statused to Closed (duplicate) per Win Leers's comment in #59? There was no patch committed so it doesn't seem to me this issue shouldn't be marked as Fixed. Marking it as a duplicate would quickly point new searchers to the issue with the preferred fix.

This issue needs to be reopened and was closed incorrectly. There was no fix here that was committed or added to documentation.

It says only maintainers can reopen or I would have done it.

I am reopening the issue until it is confirmed to be duplicate.

@vuil as I see these empty comments with a changed employer across different issues, I would like to ask you if you could stop doing this. The issue is big enough already!

@hchonov Yes, it is true. I understand. Thank you!

Just get this messages again ... (meanwhile D8.7.10)

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0,

Seems not to be a problem of every site of the same installation.

I use MariaDB 10.3

I have a service that tries to create nodes in Drupal through JSON:API. The concurrency is set to 2, and half of the requests fail with this error.

I run Drupal 8.7.10 and MariaDB 10.2.25.

What error exactly? If it's on the cachetags table then #2966607: Invalidating 'node_list' and other broad cache tags early in a transaction severely increases lock wait time and probability of deadlock might improve that, so try that with 8.8.0-rc1, where it is fixed. If it's a cache table then consider an alternative cache backend like redis or memcache (but note that both need a corresponding patch that requires the above issue/8.8 to also avoid race conditions).

This is the error:

NOTICE: PHP message: Uncaught PHP Exception Drupal\Core\Entity\EntityStorageException: "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {node_field_data} (nid, vid, type, langcode, status, uid, title, created, changed, promote, sticky, default_langcode, revision_translation_affected) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12)

Ok, that's not a cache table, but the referenced issue might still help, I'd recommend to try with 8.8.

That and maybe also check #1650930: Use READ COMMITTED by default for MySQL transactions

I think it fixed my issue, thanks.

I had the same issue today (D 8.6.16):

Drupal\Core\Entity\EntityStorageException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_bootstrap} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6) ON DUPLICATE KEY UPDATE cid = VALUES(cid), expire = VALUES(expire), created = VALUES(created), tags = VALUES(tags), checksum = VALUES(checksum), data = VALUES(data), serialized = VALUES(serialized); Array ( [:db_insert_placeholder_0] => last_write_timestamp_cache_bootstrap [:db_insert_placeholder_1] => -1 [:db_insert_placeholder_2] => 1575390493.257 [:db_insert_placeholder_3] => [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => d:1575390493.257999897003173828125; [:db_insert_placeholder_6] => 1 ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save()

I applied these changes at settings.php:

$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";

Seems to fix it for now, but It would be nice to have a solution that doesn't involve fiddling with the configuration file.

Does anyone have any idea why the issue happened? The solution in the last comment works so far but I don't know why it happened before.

I got the deadlock error while creating multiple nodes via rest api (about 6 nodes at a time, in a foreach loop in js) and an arbitrary amount of them returned that error ( about 20 to 40%) on a localhost.

@103: upgrade to 8.8. doesnt really help:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_config} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, : ........................ :db_insert_placeholder_160) ON DUPLICATE KEY UPDATE cid = VALUES(cid), expire = VALUES(expire), created = VALUES(created), tags = VALUES(tags), checksum = VALUES(checksum), data = VALUES(data), serialized = VALUES(serialized); Array ( [:db_insert_placeholder_0] => language.de:core.base_field_override.node.simplenews_issue.nid [:db_insert_placeholder_1] => -1 .............................. [:db_insert_placeholder_160] => 1 ) in Drupal\Core\Cache\ChainedFastBackend->setMultiple() (Line 191 in /core/lib/Drupal/Core/Cache/ChainedFastBackend.php).

Location was: /taxonomy/term/2951/feed at 19:48
a secont one today /name-of-page at 16:47

with no referrer for user anonymous

a third one two days ago at /taxonomy/term/2089/feed (so it happens not many times)

If I click on location i get no error message on syslog

I use mariadb 10.3

Is editing settings.php still the only solution? I did look at #104 but still getting lock issue.

Yes still same issue. Although applying patch https://www.drupal.org/project/drupal/issues/1650930

This isssue, due to clear cache. In clear cache we will truncate the data from tables. On the same time if we load the site, it will insert the data in cache tables, then it will be deadlocked.

#106 or #110 did not work for me.

happens 1-4 times every time a page load happens.

D 8.8.4

Team, Any luck on this issue? This is super critical. If this occurs then my whole live website goes down. I tried all given suggestions. Still issue. Any work around?

Hi all, also for me is super critical.. my corporate site goes down.
Thanks! (Drupal 8.8.6)

Could you try to apply the changes of #87?
Please provide a feedback.
Thank you.

I have tried #87 giving fpollowing error
Notice: Undefined offset: 0 in Drupal\Core\Database\Database::parseConnectionInfo() (line 212 of /opt/lampp/htdocs/.../core/lib/Drupal/Core/Database/Database.php).
Drupal\Core\Database\Database::parseConnectionInfo(Array) (Line: 253)
Drupal\Core\Database\Database::addConnectionInfo('default', 'default', Array) (Line: 290)
Drupal\Core\Database\Database::setMultipleConnectionInfo(Array) (Line: 129)
Drupal\Core\Site\Settings::initialize('/opt/lampp/htdocs/....', 'sites/default', Object) (Line: 1084)
Drupal\Core\DrupalKernel->initializeSettings(Object) (Line: 683)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)
Notice: Undefined index: driver in Drupal\Core\Database\Database::openConnection() (line 364 of /opt/lampp/htdocs/.../core/lib/Drupal/Core/Database/Database.php).
Drupal\Core\Database\Database::openConnection('default', 'default') (Line: 166)
Drupal\Core\Database\Database::getConnection('default')
call_user_func_array('Drupal\Core\Database\Database::getConnection', Array) (Line: 79)
Drupal\Component\DependencyInjection\PhpArrayContainer->createService(Array, 'database') (Line: 173)
Drupal\Component\DependencyInjection\Container->get('database', 1) (Line: 260)
Drupal\Component\DependencyInjection\PhpArrayContainer->resolveServicesAndParameters(Array) (Line: 62)
Drupal\Component\DependencyInjection\PhpArrayContainer->createService(Array, 'cache.container') (Line: 173)
Drupal\Component\DependencyInjection\Container->get('cache.container') (Line: 543)
Drupal\Core\DrupalKernel->getCachedContainerDefinition() (Line: 904)
Drupal\Core\DrupalKernel->initializeContainer() (Line: 476)
Drupal\Core\DrupalKernel->boot() (Line: 692)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)
The website encountered an unexpected error. Please try again later.
Drupal\Core\Database\DriverNotSpecifiedException: Driver not specified for this database connection: default in Drupal\Core\Database\Database::openConnection() (line 365 of /opt/lampp/htdocs/.../core/lib/Drupal/Core/Database/Database.php).
Drupal\Core\Database\Database::openConnection('default', 'default') (Line: 166)
Drupal\Core\Database\Database::getConnection('default')
call_user_func_array('Drupal\Core\Database\Database::getConnection', Array) (Line: 79)
Drupal\Component\DependencyInjection\PhpArrayContainer->createService(Array, 'database') (Line: 173)
Drupal\Component\DependencyInjection\Container->get('database', 1) (Line: 260)
Drupal\Component\DependencyInjection\PhpArrayContainer->resolveServicesAndParameters(Array) (Line: 62)
Drupal\Component\DependencyInjection\PhpArrayContainer->createService(Array, 'cache.container') (Line: 173)
Drupal\Component\DependencyInjection\Container->get('cache.container') (Line: 543)
Drupal\Core\DrupalKernel->getCachedContainerDefinition() (Line: 904)
Drupal\Core\DrupalKernel->initializeContainer() (Line: 476)
Drupal\Core\DrupalKernel->boot() (Line: 692)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)

Here are my two cents after battling with this issue.

Make a bash script, and run it on a cron job to check the logs for this error, and clear cache when it does.

It's not ideal, but until there is something better, this is working.

last_lock=$(grep -r 'try restarting transaction' $LOG_PATH  | sort -t ' ' -k 4.9,4.12n -k 4.5,4.7M -k 4.2,4.3n -k 4.14,4.15n -k 4.17,4.18n -k 4.20,4.21n | cut -d ']' -f 1 | sed -e 's/^\[//' | xargs -I {} date "+%s" -d {} | uniq | sort -n | tail -1)
current_date=$(date "+%s")
difference=$(expr $current_date - $last_lock)
if [ "$difference" -lt "60" ]; then 
  echo "Clearing cache!" &&
  $DRUSH cr &&
  echo "Done!"
else 
  echo "No recent deadlocks found"
  echo "$difference since last lock"
fi

I'm sure this could be improved as well.

Our cron is currently set to run every minute for other reasons, and this check is pretty light weight assuming your logs aren't huge. We are using log rotation on our servers.

Another viable alternative is to turn off the config cache bin.

in settings.php
$settings['cache']['bins']['config'] = 'cache.backend.null';

#56 Works for me.
Thanks a lot

#56 also works for me. tested on Drupal 7 with memcached and opcache enabled.

@commonColby _ I tried #119

it says

Symfony\Component\DependencyInjection\Exception\ServiceNotFoundException: You have requested a non-existent service "cache.backend.null". Did you mean one of these: "cache.backend.apcu", "cache.backend.php", "cache.backend.memory"? in Drupal\Component\DependencyInjection\Container->get() (line 153 of core/lib/Drupal/Component/DependencyInjection/Container.php).

@isalmanhaider, the service that is not found is NullBackendFactory and it is usually declared in development.services.yml

services:
  cache.backend.null:
    class: Drupal\Core\Cache\NullBackendFactory

Usually this service is loaded in settings.local.php (see example.settings.local.php)

/**
 * Enable local development services.
 */
$settings['container_yamls'][] = DRUPAL_ROOT . '/sites/development.services.yml';

More info here #2348219: You have requested a non-existent service "cache.backend.null"

56 work

Deadlock on Semaphore table results intermittent service denial of the site

Hi All,
Request your urgent support for the issue I'm facing on Drupal. Please note, the Production site was up and running for the initial 6 months without any issue. But then suddenly this issue started to popup.

Experience deadlock on Semaphore table frequently, which result in service denial of the site.

Site configuration:

Drupal version : 8.8.6
Apache version: Apache/2.4.46 PHP 7.2.33
Drush version : 10.3.1

MySQL Database (AWS RDS 8.0.20, with Multi-AZ and backup enabled)
2 CMS nodes configured to use same drupal file system with EFS

The site is under normal traffic & it is observed that without traffic also this issues exists. It is observed that during cache reload using Drush (drush cr) the issue is consistent. Even after restarting the cms nodes, without any traffic the issue occurs. During the issue time the php-fpm active threads are in blocked state, so it cannot serve any request from Apache server.

PHP-FPM Thread count

[root@ip-xx-xx-xx-xx bin]# systemctl status php-fpm
● php-fpm.service - The PHP FastCGI Process Manager
Loaded: loaded (/usr/lib/systemd/system/php-fpm.service; disabled; vendor preset: disabled)
Active: active (running) since Tue 2021-04-20 16:59:08 JST; 29min ago
Main PID: 3135 (php-fpm)
Status: "Processes active: 50, idle: 0, Requests: 96, slow: 0, Traffic: 0req/sec"
CGroup: /system.slice/php-fpm.service
├─3135 php-fpm: master process (/etc/php-fpm.conf)
├─3139 php-fpm: pool www
├─3140 php-fpm: pool www
├─3141 php-fpm: pool www
├─3142 php-fpm: pool www
├─3143 php-fpm: pool www
Error from php-fpm

"at /xxx/xxx/xxx/applns/drupal/st/core/lib/Drupal/Core/Database/Connection.php line 695
[20-Apr-2021 16:17:52 Asia/Tokyo] Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get
lock; try restarting transaction: INSERT INTO {semaphore} (name, value, expire) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array
(
[:db_insert_placeholder_0] => locale:ja::anonymous:Drupal\Core\Cache\CacheCollector
[:db_insert_placeholder_1] => 992790133607e8016bd90d6.01860943
[:db_insert_placeholder_2] => 1618903102.1116
)
in /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Cache/CacheCollector.php on line 233 #0 /xxx/xxx/xxx/applns/drupal/st/core/lib/Drupal/Core/Database/Connection.php(659): Drupal\Core\Database\Connection->handleQueryException(Object(PDOException), 'INSERT INTO {se...', Array, Array)

#1 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php(357): Drupal\Core\Database\Connection->query('INSERT INTO {se...', Array, Array)
#2 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Database/Driver/mysql/Insert.php(32): Drupal\Core\Database\Driver\mysql\Connection->query('INSERT INTO {se...', Array, Array)
#3 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Lock/DatabaseLockBackend.php(77): Drupal\Core\Database\Driver\mysql\Insert->execute()
#4 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/ProxyClass/Lock/DatabaseLockBackend.php(75): Drupal\Core\Lock\DatabaseLockBackend->acquire('locale:ja::anon...', 30)
#5 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Cache/CacheCollector.php(233): Drupal\Core\ProxyClass\Lock\DatabaseLockBackend->acquire('locale:ja::anon...')
#6 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Cache/CacheCollector.php(327): Drupal\Core\Cache\CacheCollector->updateCache()
#7 /xxxx/xxxx/xxxx/applns/drupal/st/core/modules/locale/src/LocaleTranslation.php(150): Drupal\Core\Cache\CacheCollector->destruct()
#8 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/EventSubscriber/KernelDestructionSubscriber.php(51): Drupal\locale\LocaleTranslation->destruct()
#9 [internal function]: Drupal\Core\EventSubscriber\KernelDestructionSubscriber->onKernelTerminate(Object(Symfony\Component\HttpKernel\Event\PostResponseEvent), 'kernel.terminat...', Object(Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher))
#10 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Component/EventDispatcher/ContainerAwareEventDispatcher.php(111): call_user_func(Array, Object(Symfony\Component\HttpKernel\Event\PostResponseEvent), 'kernel.terminat...', Object(Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher))

We applied transaction_isolation to READ_COMMITTED for MySQL connection, but the issue occurs again.

Currently we changed the semaphore table to MEMORY Engine and found that the issues frequency reduced. But during the load testing the issue reproduced. We are facing the issue in our Production site, so any help will be highly appreciated.

It seems as if this issue is actually at least 2 separate issues with the same error message.

Issue 1:

Deadlock on cache tables, specifically cache_config, sometimes fixed by doing a cache clear

Issue 2:

Deadlock on entity tables when saving entities.

I've been encountering issue 2 over the last few days trying to import large amounts of data via a restful api endpoint. Each time I run the process I get a random number of failures, all caused by the database deadlock issue but on different tables. Often it's paragraph tables (the process is creating nodes which have paragraph entities attached) but sometimes node tables too.

To complicate my case further, some of the operations I'm doing use the migration system to migrate data from CSV files. Those are failing more than the direct entity creation route.

Adding the isolation settings to my settings.php seems to improve things, but the problem doesn't go away completely, I get about half the number of errors.

I just recently migrated my Drupal site from 7 to 8 and I just started getting the deadlock issue like everyone else.

Does this still work?

$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";

#EDIT:

I tried it and it didn't work.

I did it like this:

$settings['config_sync_directory'] = '../config/sync';
$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";
$databases['default']['default'] = array (
'database' => 'DB',
'username' => 'USERNAME',
'password' => 'FakePassowrd',
'prefix' => '',
'host' => 'localhost',
'port' => '3306',
'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
'driver' => 'mysql',
);

Is that correct?

Thanks!

Does any doubt remain that this is a duplicate of #1650930: Use READ COMMITTED by default for MySQL transactions?

Hi Cilefen,

So all I need to do is add this?

'isolation_level' => 'READ COMMITTED',

Thanks!

Hmm i added 'isolation_level' => 'READ COMMITTED', and it worked!

I used Drush to cache rebuild and I got the deadlock again :(

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {cache_entity} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6) ON DUPLICATE KEY UPDATE cid = VALUES(cid), expire = VALUES(expire), created = VALUES(created), tags = VALUES(tags), checksum = VALUES(checksum), data = VALUES(data), serialized = VALUES(serialized);

What happens if I TRUNCATE cache_entity and then run `drush cr`? Will I mess up my website?

Having skimmed through this thread, it looks like there's been a lot of discussion of the symptoms, but not much investigation into the root causes of the problem. Having done some digging, I think what we have is a fairly major design flaw in the way the cache system interacts with transactions which has the potential to trigger this deadlock problem every time persistent SQL caches are modified inside of transactions at the same time in parallel requests. In particular, this is a huge problem for entity saves happening in parallel requests, and it doesn't matter if the entities being saved are completely unrelated.

The process of saving an entity look like this:

1. The entity save() method is called, which in turn results in SqlContentEntityStorage->save() being called.
2. SqlContentEntityStorage->save() starts a transaction, and invokes EntityStorageBase->save() in a try/catch block, ensuring the transaction is rolled back if something goes wrong.
3. EntityStorageBase->save() calls doPreSave(), doSave(), and doPostSave(). All work touching the database in any of these function calls happens inside the context of the transaction.
4. During doPostSave(), EntityBase->invalidateTagsOnSave() is called to invalidate the tags of the entity being saved.

It's in step 4 where we get problems: if the cache invalidation hits multiple cache bins then ChainedFastBackEnd->markAsOutdated() will ensure the last_write_timestamp cid will get set on the consistent backend for each cache bin, which for example results in queries that look like this:

INSERT INTO "cache_config" ("cid", ...) VALUES (:db_insert_placeholder_0, ...) ON DUPLICATE KEY UPDATE "cid" = VALUES("cid") ...
INSERT INTO "cache_bootstrap" ("cid", ...) VALUES (:db_insert_placeholder_0, ...) ON DUPLICATE KEY UPDATE "cid" = VALUES("cid") ...

The problem is that in the competing request the same cache invalidations might happen, but there's no guarantee that they will happen in the same order. This isn't (or shouldn't be) a problem if no explicit transaction is in progress, as each INSERT then occurs in its own transaction, however during an entity save we're inside a transaction, and we've got two competing Mysql threads trying to achieve record locks on the same rows in both tables but in a different order, giving us a classic deadlock.

This is quite a big problem: the triggering scenario described above is one way this problem can occur (based on a real event that happened in my dev environment earlier today, but my production logs are littered with similar examples), but given how much scope there is for stuff to occur inside entity pre and post saves, or for the cache system to perform changes in other transaction contexts, there's potentially a lot of other scenarios that could trigger this problem.

There's a couple of ideas to investigate for solving this:

1. Decouple the doPostSave phase (and probs also the doPreSave phase) of the entity save from the transaction, which could be achieved by starting the transaction inside SqlContentEntityStorage->doSave() instead of save(). This has been suggested in 3088228. I'm not sure if this could have unintended consequences though, for example if there's a presumption that it's always possible to roll back a save during doPostSave.

2. Decouple the cache system's interactions with the database from any current transaction. This could be achieved by establishing a second dedicated database connection to the default database for use exclusively by DatabaseBackend, which ensures all its changes happen independently of any transaction in progress on the main connection. In principal I don't see any reason why any change to the cache tables should happen in the context of any other non-cache related transaction (but do correct me if I'm wrong). There might also be other fun unintended consequences to this idea which need to be thought through, such as suddenly doubling the number of connections being made to the database across a large php-fpm pool.

Not working with #130 for our sites.

@lambic in https://www.drupal.org/project/drupal/issues/2833539#comment-14135230
I am getting both issued you describe when importing large amounts of data.

[21-Apr-2022 18:52:15 Europe/Amsterdam] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock for "drupal_groups_field_data"

[21-Apr-2022 18:52:53 Europe/Amsterdam] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock for "drupal_cache_bootstrap"

Trying the isolation settings on my development server rightnow.

Since my previous comment I imported quite a lot of data on my dev server. The isolation settings did not quite work. I still got a few deadlock's on the cache tables, but none yet on entity tables.

The isolation level solution doesn't always work. From time to time we have this problem again and again during the cron run. I think this is a bigger issue!

My mass import of data apparently ran at least twice into the serialization error for content. Groups in my case. As admin user I can delete all groups, except those 2. Editing goes fine, but deleting does not work.
/group/5016/delete gives a 'Access denied', for both Administrators and the Group Creator. Odd.

I can confirm that this issue is occurring on two different Drupal 9.4 sites that we recently launched. The issue is so severe on one that we have temporarily reverted back to the Drupal 7 installation since this specific issue not only results in an inability to run a normal backup on the database, it also results in database thrashing as Drupal repeatedly tries to update the cache_config table without success. It ultimately brought one site offline (which is why that site is back running Drupal 7 until we get this resolved).

I am bumping this back up to critical as it is currently preventing the operation of a site on Drupal 9. The issue is currently emerging on the second site.

Both sites are using Google's CloudSQL instance.

Same Problem here.
We have a large multisite installation (same core but every domain has it's own db schema).
When more than one node in an instance is saved at the same time (we tested it with five people clicking on node save) one or two of the transactions fail with a deadlock.

We are currently already running with Read-Optimistic and are using memcache for caching and cemaphore handling.
We applied the patches above that tinker with db timeouts but this didn't solve the problem.
Test have been run on a Galera Cluster in Master-Slave mode. But we desire to run it at Multiple Master mode. Therefore the Galera docs say that only Repeatable-Read is allowed: https://galeracluster.com/library/documentation/isolation-levels.html

Here are the log messages on such a failure.

Drupal\Core\Entity\EntityStorageException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO "menu_link_content_data" ("id", "revision_id", "bundle", "langcode", "enabled", "title", "description", "menu_name", "link__uri", "link__title", "link__options", "external", "rediscover", "weight", "expanded", "parent", "changed", "default_langcode", "revision_translation_affected") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12, :db_insert_placeholder_13, :db_insert_placeholder_14, :db_insert_placeholder_15, :db_insert_placeholder_16, :db_insert_placeholder_17, :db_insert_placeholder_18); Array ( [:db_insert_placeholder_0] => 1086 [:db_insert_placeholder_1] => 1089 [:db_insert_placeholder_2] => menu_link_content [:db_insert_placeholder_3] => de [:db_insert_placeholder_4] => 1 [:db_insert_placeholder_5] => Ecki-Test [:db_insert_placeholder_6] => [:db_insert_placeholder_7] => main [:db_insert_placeholder_8] => internal:/node/39e083c2-a333-4b22-b9eb-f0169cec6ae2 [:db_insert_placeholder_9] => [:db_insert_placeholder_10] => a:0:{} [:db_insert_placeholder_11] => 0 [:db_insert_placeholder_12] => 1 [:db_insert_placeholder_13] => 0 [:db_insert_placeholder_14] => 0 [:db_insert_placeholder_15] => [:db_insert_placeholder_16] => 1661343256 [:db_insert_placeholder_17] => 1 [:db_insert_placeholder_18] => 1 ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (Zeile 811 in /var/www/html/hessenweb/web/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

Drupal\Core\Entity\EntityStorageException: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT savepoint_1 does not exist: ROLLBACK TO SAVEPOINT savepoint_1; Array ( ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (Zeile 811 in /var/www/html/hessenweb/web/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

The second one first occured when changing TL to Repeatable-Read but did occure later on again when switching back to Read-Commited.

I think the right way to get rid of this issue is to follow up on @pstewart what he says in https://www.drupal.org/project/drupal/issues/2833539#comment-14466120

I experienced another one. On a Windows server. Drupal 9.4.8, MySQL 5.7.35, PHP 7.4.30 with OPCache enabled:

[21-Oct-2022 10:00:55 Europe/Amsterdam] Uncaught PHP Exception Drupal\Core\Entity\EntityStorageException: "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO "drupal_cache_bootstrap" ("cid", "expire", "created", "tags", "checksum", "data", "serialized") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6) ON DUPLICATE KEY UPDATE "cid" = VALUES("cid"), "expire" = VALUES("expire"), "created" = VALUES("created"), "tags" = VALUES("tags"), "checksum" = VALUES("checksum"), "data" = VALUES("data"), "serialized" = VALUES("serialized"); Array
(
[:db_insert_placeholder_0] => last_write_timestamp_cache_bootstrap
[:db_insert_placeholder_1] => -1
[:db_insert_placeholder_2] => 1666339255.259
[:db_insert_placeholder_3] =>
[:db_insert_placeholder_4] => 0
[:db_insert_placeholder_5] => d:1666339255.26;
[:db_insert_placeholder_6] => 1
)
" at core\lib\Drupal\Core\Entity\Sql\SqlContentEntityStorage.php line 811

Edit: This happened when 5 users were creating a subgroup and a subsubgroup within a few minutes. They were having a course about creating (sub)subgroups. One of those 5 users ran into the deadlock error. Net effect: a subgroup was created, but without the required reference to the parent (tree, left, right, depth values). The user that ran into the deadlock error tried it once again and succeeded this time. She had created one 'loose' subgroup and one correct subgroup by then.
Luckily our site is very low-traffic most of the times, but that this can happen with only 5 concurrent users frightens me a bit. Is somebody attempting to fix the issue?

Edit 2: in the subgroup module the references to the parent (tree, left, right, depth) are added after the group->save(), outside of any transaction. So a fatal error can cause 'loose' subgroups, even if the fatal error only occurs when adding something into the cache_bootstrap table.

Same issue here. This is the kind of error that kills open source projects. Running a script to monitor the log and consequently clear cache is the only option to bring the system back up and no one will sign up for that in the future.

Could you describe a little bit more about the circumstances when this happened?

A little bit of insight here:

https://dev.mysql.com/blog-archive/innodb-data-locking-part-1-introduction/

This issue happens every few days on Drupal 9.3.x site. The mentioned deadlock error shows up in the log and minutes later The State does not exist errors start showing up and all users get a whitescreen. Once cache tables are successfully trunced the site is back. We are using the script monitoring the log to clear cache method described above.

Are you getting deadlock errors only on cache tables? Or also on entity tables? See the difference in #126.

Error below:

Uncaught PHP Exception Drupal\\Core\\Database\\DatabaseExceptionWrapper: "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO "cache_entity" ("cid", "expire", "created", "tags", "checksum", "data", "serialized") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6) ON DUPLICATE KEY UPDATE "cid" = VALUES("cid"), "expire" = VALUES("expire"), "created" = VALUES("created"), "tags" = VALUES("tags"), "checksum" = VALUES("checksum"), "data" = VALUES("data"), "serialized" = VALUES("serialized"); Array\n(\n [:db_insert_placeholder_0] => values:node:4654\n [:db_insert_placeholder_1] => -1\n [:db_insert_placeholder_2] => 1669891822.898\n [:db_insert_placeholder_3] => entity_field_info node_values\n [:db_insert_placeholder_4] => 0\n [:db_insert_placeholder_5] => O:23:"Drupal\\node\\Entity\\Node":29:{s:10:"in_preview";N;s:9:"

Edit:
Every time that I have ever seen the error it's on a cache_ table. That does not mean it never happens on others but I've not seen it.

Once again the worst part about all of this is that once the cache gets corrupted the public site white screens for everyone until you get a clean cache clear which can be difficult as well. I go straight to truncing the cache_ tables to be sure..

Maybe bypass cache if it's corrupted?

I'll add my case here as as well.

We get deadlock errors on cache_entity on a Drupal 9.4.4 instance, using PHP 8.0.26 and mysql 8.0.28. In the past we used PHP 7 and we never had these deadlocks with the same codebase. Transaction level of mysql is "READ-COMMITTED".

We get the deadlock in a cron job which is running in the slow hours, the cron job updates a bunch of nodes and our own php code is not using any of the caches (whatever cache is used is implicit by Drupal).

The first error in "Recent logs" is like this:

User warning: Rollback attempted when there is no active transaction. This can cause data integrity issues. in Drupal\mysql\Driver\Database\mysql\Connection->rollBack() (regel 458 van /data/code/application/www/core/modules/mysql/src/Driver/Database/mysql/Connection.php)
#0 /data/code/application/www/core/includes/bootstrap.inc(347): _drupal_error_handler_real(512, 'Rollback attemp...', '/data/code/jobb...', 458)
#1 [internal function]: _drupal_error_handler(512, 'Rollback attemp...', '/data/code/jobb...', 458)
#2 /data/code/application/www/core/modules/mysql/src/Driver/Database/mysql/Connection.php(458): trigger_error('Rollback attemp...', 512)
#3 /data/code/application/www/core/lib/Drupal/Core/Database/Transaction.php(96): Drupal\mysql\Driver\Database\mysql\Connection->rollBack('drupal_transact...')
#4 /data/code/application/www/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php(809): Drupal\Core\Database\Transaction->rollBack()
#5 /data/code/application/www/core/lib/Drupal/Core/Entity/EntityBase.php(339): Drupal\Core\Entity\Sql\SqlContentEntityStorage->save(Object(Drupal\node\Entity\Node))
#6 /data/code/application/www/modules/contrib/workflow/src/Entity/WorkflowTransition.php(636): Drupal\Core\Entity\EntityBase->save()
#7 /data/code/application/www/modules/contrib/workflow/src/Entity/WorkflowTransition.php(608): Drupal\workflow\Entity\WorkflowTransition->_updateEntity()
#8 /data/code/application/www/modules/contrib/workflow/workflow.module(289): Drupal\workflow\Entity\WorkflowTransition->executeAndUpdateEntity(true)

It originates in the workflow module, but that's because that triggers the saving of a node.

The second error is this:
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM "cache_entity" WHERE "cid" IN (:db_condition_placeholder_0); Array ( [:db_condition_placeholder_0] => values:node:915230 ) in Drupal\Core\Cache\DatabaseBackend->deleteMultiple() (regel 293 van /data/code/application/www/core/lib/Drupal/Core/Cache/DatabaseBackend.php).

The current short-term work-around is to catch the error and retry in our own code, but it's a PITA.

For the cache tables related problems, worth the try the redis module, using the lock backend redis implementation

https://git.drupalcode.org/project/redis/-/blob/8.x-1.6/example.services...

Hi, hope everyone had great holidays. Just a quick update on what I found out since my last comment. It seems that things are a bit more difficult with Galera cluster than I originally thought. It's way too simplified to just say that Galera doesn't support READ-COMMITTED. In a master-slave setup, READ-COMMITTED is in fact supported and in a multi-master-setup, things are also not as clear as it might seem. This amazing article here explains it perfectly:

https://clusterengine.me/understanding-isolation-levels-in-galera-cluster/

In a multi-master setup we have to differentiate between the intra-node transaction level and the cluster-wide transaction level. Within a node you can in fact specify transaction_isolation=READ-COMMITTED and it will work. I guess if this might cause any major problems, the Galera developers would probably block the database from starting at all in this configuration. In a multi-master-setup the transaction_isolation will have the usual effect *within the same node*.

On a cluster-wide scale, this transaction-level is not possible. But it won't cause any data corruption or major breakage. It will just silently fallback to REPEATABLE-READ or, strictly speaking to the special transaction-level "SNAPSHOT ISOLATION" which is closely related to REPEATABLE-READ but solves a problem that only occurs cluster-wide.

So in summary, you CAN actually specify "transaction_isolation=READ-COMMITTED" on a multi-master Galera cluster without making the situation WORSE or risking any problems that haven't been there in the first place. The MariaDB documentation is quite misleading/unclear which might keep some people from attempting this. It certainly kept me away for a while.

Additionaly, a while ago, there used to be some bugs that caused that database for us to crash in this configuration. Back then, I thought, well that's why this configuration is not recommended - but they were just bugs. I never figured out when/how they were solved, but I haven't seen them for at least more than a year. Make absolutely sure you are using the most recent stable MariaDB Version.

We are running dozens of Drupal websites with this configuration without any problems now and the deadlocks have disappeared. So, if you are suffering from this issue on a Galera multi-master cluster I'd like to encourage you to try setting transaction_isolation=READ-COMMITTED and see if it solves your problems. Make sure to have reliable backups, of course - but that's a no-brainer, I guess.

This is a duplicate of #1650930: Use READ COMMITTED by default for MySQL transactions which is close to getting committed.

@cmlara pointed me to #134 which notes issues when READ_COMMITTED is in use with caching in particular. There's an existing issue open to resolve that - #2347867: Race conditions with lock/cache, session storage - add a non-transactional database connection. I'll reference @pstewart's comment from that issue.

#148 solved the problem for me..
Manually truncate all cache tables then drush cr...

In PMA I went to index.php?route=/server/status/processes
Killed the sleeping process and was good.
Helped for now, just encountered it first time, not in production yet.