I've noticed that Deadlocks occur on fairly simple queries when the database is under heavy load and the core database cache is used. I believe that this is due to these queries being ran inside of a transaction (db_transaction()) like node_save() and _registry_update(). Deadlocks do not occur as often when using a cache backend like memcache but they still can happen.
The first step would be to isolate cache and lock operations so they happen outside of the transaction by using a different db connection https://drupal.org/node/18429. After that we could special case tables that can be ran outside of the transaction (history, variables, etc).
Comments
Comment #1
mikeytown2 CreditAttribution: mikeytown2 commentedComment #2
gielfeldt CreditAttribution: gielfeldt commentedIf we move the cache database backend to a new connection, we will just suffer from this: #1679344: Race condition in node_save() when not using DB for cache_field.
It still sounds like a gap-lock issue to me. The "variable" and "semaphore" are quite susceptible to this. The easiest way to solve it, is to switch isolation mode to READ-COMMITTED (be careful if you're using replication, must be mysql >= 5.1.37 and row-based replication).
In settings.php:
I would always recommend using e.g. memcache for semaphores.
Which tables do you experience deadlocks on?
Comment #3
mikeytown2 CreditAttribution: mikeytown2 commentedCache, variables, & semaphore tables. Reports of history table causing deadlocks as well. Using memcache is ideal, but this issue is in core and thus should be fixed.
Keeping cache_field inside the transaction would prevent that issue from happening but fixing it so that cache table can be used with other cache backends would be ideal.
Comment #4
gielfeldt CreditAttribution: gielfeldt commentedI agree.
I do have somewhat a solution for the cache backends. A buffering mechanism that flushes cache operations on database commits: https://drupal.org/sandbox/gielfeldt/1946668 (method 2).
Regarding the variable table, I have a fix for this if changing isolation level is not possible: http://gielfeldt.blogspot.dk/2013/05/drupals-variable-storage-and-lockin...
The semaphore table should be safe to move to another backend (or another connection). It also might be possible to fix by avoiding the db_delete() on the semaphore table in a similar way like I did with the variable table.
Comment #5
Damien Tournoud CreditAttribution: Damien Tournoud commentedAs explained, the cache has to be transactional.
Our merge query implementation and (part of) the lock implementation) only work properly under
READ COMMITTED
transaction isolation. We could decide to enforce this, but in the meantime, just settransaction-isolation=READ-COMMITTED
in your MySQL server configuration, or add the runtime mentioned by @gielfeldt above.I know several high-performance sites (including Drupal.org) that are using this successfully.
Comment #6
Damien Tournoud CreditAttribution: Damien Tournoud commentedComment #7
mikeytown2 CreditAttribution: mikeytown2 commentedFound more info on d.o
https://drupal.org/node/1952972
#1650930: Use READ COMMITTED by default for MySQL transactions
Comment #8
gielfeldt CreditAttribution: gielfeldt commentedBreaking news: Merge queries outside transactions have been solved: #937284: DEADLOCK errors on MergeQuery INSERT due to InnoDB gap locking when condition in SELECT ... FOR UPDATE results in 0 rows
Comment #9
mikeytown2 CreditAttribution: mikeytown2 commented@gielfeldt
That issue helped a lot but deadlocks can still happen under normal operating conditions; thus we still need to figure this out. I've changed our transaction-isolation level to Read Committed and will see If I can get Drupal to deadlock. If it does indeed fix the issue 100% we should strongly recommend the transaction level (by setting it as the default, etc).
Comment #10
mikeytown2 CreditAttribution: mikeytown2 commentedThinking we should enforce this for all new Drupal installs. Do we want to put the code in the install form (install_settings_form) as a hidden field or drop it in at a later stage (install_settings_form_submit)?
Comment #11
Damien Tournoud CreditAttribution: Damien Tournoud commentedThis is actually a duplicate of #1650930: Use READ COMMITTED by default for MySQL transactions. Let's keep the conversation in one place.