In some situations, you can encounter performance issues when querying the semaphore tables in your database — these issues are known as deadlocks.

Finding deadlocks

A MySQL deadlock occurs when two processes attempt to update two rows in a database in opposite orders.

For a more detailed explanation on a deadlock and ways to identify them read through the Finding MySQL deadlocks page. 

Resolving Deadlocks

The following section provides a number of different methods that can be used to resolve deadlocks that you have identified. They can be used on an individual basis or used in combination to resolve deadlock issues.

• Overriding the MySQL Transaction Isolation Level setting
• Using The Cache Debug module
• Using Memcached locking

Overriding the MySQL Transaction Isolation Level setting¶

It's possible to avoid deadlocks by changing the transaction isolation level (transaction_isolation) variable from the default value of REPEATABLE-READ to READ-COMMITTED.

You can change the transaction_isolation setting in your connection settings because it is both a session variable and a global variable. You can change it for all queries, or for select queries that are suspect. It's preferable that you modify specific queries, rather than changing transaction_isolation for all queries or changing the overall database setting. This makes it easier to track down custom settings if the website begins behaving strangely.

For further details and steps to change the transaction_isolation setting, review the Overriding MySQL transaction setting page.

Using The Cache Debug module¶

Performance degradation of the semaphore table in MySQL is symptomatic of abusive use of the lock API in Drupal. It is often the result of variable_set calls in places they shouldn't be. The struggle has always been finding them.

The Cache Debug module provides a wrapper for the caching system to help provide debug output. It can be used to output a stack trace to a log file for calls to cache_set or cache_clear_all. The variable system uses the cache API, and removing recurring calls from front-end GET traffic can help resolve locking issues.

Using Memcached Locking¶

For information about memcache on Acquia Cloud, read the Using Memcached page.

In the past, for the Acquia Cloud Classic infrastructure, Acquia has advised using Memcached Locking to assist with preventing deadlocks.

With the change of Acquia Cloud Platform to a Kubernetes based infrastructure, and with potential problems of keeping data integrity with Memcache being ephemeral, it is advised not to implement Memcached Locking. 

Related Resources¶

• Finding MySQL deadlocks
• Overriding MySQL transaction setting
• Avoiding database deadlocks for Acquia Site Factory