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

mikeytown2’s picture

mikeytown2 commented
Related issues: +#1898204: Do not use InnoDB for the semaphore table, use Memory
gielfeldt’s picture

gielfeldt commented

If 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:

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

I would always recommend using e.g. memcache for semaphores.

Which tables do you experience deadlocks on?

mikeytown2’s picture

mikeytown2 commented
Related issues: +#1679344: Race condition in node_save() when not using DB for cache_field

Which tables do you experience deadlocks on?

Cache, 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.

gielfeldt’s picture

gielfeldt commented

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.

I 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.

damien tournoud’s picture

damien tournoud commented
Title: Keep certain queries outside of transactions. » Enforce READ COMMITTED transaction isolation level?

As 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 set transaction-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.

damien tournoud’s picture

damien tournoud commented
Title: Enforce READ COMMITTED transaction isolation level? » Enforce READ COMMITTED transaction isolation level
Version: 7.x-dev » 8.x-dev
mikeytown2’s picture

mikeytown2 commented

Found more info on d.o
https://drupal.org/node/1952972
#1650930: Use READ COMMITTED by default for MySQL transactions

gielfeldt’s picture

gielfeldt commented

Breaking 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

mikeytown2’s picture

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).

mikeytown2’s picture

mikeytown2 commented

Thinking 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)?

damien tournoud’s picture

damien tournoud commented
Status: Active » Closed (duplicate)
Related issues: +#1650930: Use READ COMMITTED by default for MySQL transactions

This is actually a duplicate of #1650930: Use READ COMMITTED by default for MySQL transactions. Let's keep the conversation in one place.