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) DrupalCoreDatabaseDatabaseExceptionWrapper: 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:DrupalCoreCacheCacheCollector
(: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): DrupalCoreDatabaseConnection->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): DrupalCoreDatabaseConnection->query('INSERT INTO {se...', Array, Array)
#2 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Database/Driver/mysql/Insert.php(32): DrupalCoreDatabaseDrivermysqlConnection->query('INSERT INTO {se...', Array, Array)
#3 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Lock/DatabaseLockBackend.php(77): DrupalCoreDatabaseDrivermysqlInsert->execute()
#4 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/ProxyClass/Lock/DatabaseLockBackend.php(75): DrupalCoreLockDatabaseLockBackend->acquire('locale:ja::anon...', 30)
#5 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Cache/CacheCollector.php(233): DrupalCoreProxyClassLockDatabaseLockBackend->acquire('locale:ja::anon...')
#6 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Cache/CacheCollector.php(327): DrupalCoreCacheCacheCollector->updateCache()
#7 /xxxx/xxxx/xxxx/applns/drupal/st/core/modules/locale/src/LocaleTranslation.php(150): DrupalCoreCacheCacheCollector->destruct()
#8 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/EventSubscriber/KernelDestructionSubscriber.php(51): DrupallocaleLocaleTranslation->destruct()
#9 (internal function): DrupalCoreEventSubscriberKernelDestructionSubscriber->onKernelTerminate(Object(SymfonyComponentHttpKernelEventPostResponseEvent), 'kernel.terminat...', Object(DrupalComponentEventDispatcherContainerAwareEventDispatcher))
#10 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Component/EventDispatcher/ContainerAwareEventDispatcher.php(111): call_user_func(Array, Object(SymfonyComponentHttpKernelEventPostResponseEvent), 'kernel.terminat...', Object(DrupalComponentEventDispatcherContainerAwareEventDispatcher))
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.
