Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Recently my database, show me that one insert of CDN is causing DEAD LOCKS, so my db get more slower for a couple of minutes. For check this I use the command:
show engine innodb status;
This is the output log that I see:
LATEST DETECTED DEADLOCK
------------------------
2013-12-09 01:48:43 2ba9e8185700
*** (1) TRANSACTION:
TRANSACTION 480786909, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1103968, OS thread handle 0x2ba93dd32700, query id 39636295 172.31.12.59 ehd7user update
INSERT INTO variable (name, value) VALUES ('cdn_css_cache_files_http', 'a:1:{s:64:\"823ba1006db72809515d2221cd02ec1075d7b49b0c07f49307b3a7930bfdd9e4\";s:73:\"public://cdn/css/http/css_FN5dwXMtrWv4zG39CZT6gsBwwAXxzJbOWqVtVeUPJIo.css\";}')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 264 page no 14 n bits 200 index "PRIMARY" of table "ehd7"."variable" trx id 480786909 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 65 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 20; hex 63646e5f6661726675747572655f737461747573; asc cdn_farfuture_status;;
1: len 6; hex 000000c343ae; asc C ;;
2: len 7; hex 340000019d2467; asc 4 $g;;
3: len 4; hex 693a303b; asc i:0;;;
*** (2) TRANSACTION:
TRANSACTION 480786942, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1103902, OS thread handle 0x2ba9e8185700, query id 39636297 172.31.12.59 ehd7user update
INSERT INTO variable (name, value) VALUES ('cdn_css_cache_files_http', 'a:1:{s:64:\"823ba1006db72809515d2221cd02ec1075d7b49b0c07f49307b3a7930bfdd9e4\";s:73:\"public://cdn/css/http/css_FN5dwXMtrWv4zG39CZT6gsBwwAXxzJbOWqVtVeUPJIo.css\";}')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 264 page no 14 n bits 200 index "PRIMARY" of table "ehd7"."variable" trx id 480786942 lock_mode X locks gap before rec
Record lock, heap no 65 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 20; hex 63646e5f6661726675747572655f737461747573; asc cdn_farfuture_status;;
1: len 6; hex 000000c343ae; asc C ;;
2: len 7; hex 340000019d2467; asc 4 $g;;
3: len 4; hex 693a303b; asc i:0;;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 264 page no 14 n bits 200 index "PRIMARY" of table "ehd7"."variable" trx id 480786942 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 65 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 20; hex 63646e5f6661726675747572655f737461747573; asc cdn_farfuture_status;;
1: len 6; hex 000000c343ae; asc C ;;
2: len 7; hex 340000019d2467; asc 4 $g;;
3: len 4; hex 693a303b; asc i:0;;;
*** WE ROLL BACK TRANSACTION (2)
Comments
Comment #1
MonsJovis CreditAttribution: MonsJovis commentedI can confirm this problem running two MariaDB servers in cluster
Comment #2
tvdijck CreditAttribution: tvdijck commentedWe also had a problem when we moved our regular mysql database to a percona cluster. The CDN module caused a lot of deadlocks.
We had to switch of the module, any solution would be appreciated.
Comment #3
Wim LeersThis is not a CDN module problem; you'd have it with Drupal core too: it's a problem in Drupal 7's handling/tracking of CSS/JS aggregates.
The CDN module merely overrides Drupal core's aggregate handling to make it support CDN integration. Disable it, and you'll experience the same problems in Drupal 7 core.
What you're seeing, is the so-called stampede problem. The CDN module cannot fix it, only Drupal core can. See #886488: [D7] Add stampede protection for css and js aggregation.
For now, the best solution would be to use the AdvAgg module, which doesn't have this stampede problem.
Comment #4
Wim LeersAlso see #1984970-5: Use locks to prevent variable_set() for setting CSS aggregates from locking up the DB on high traffic sites.