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

MonsJovis’s picture

I can confirm this problem running two MariaDB servers in cluster

tvdijck’s picture

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

Wim Leers’s picture

Title: I have innodb deadlock with this module » CSS/JS aggregation stampedes cause InnoDB deadlocks
Priority: Critical » Normal
Status: Active » Closed (works as designed)
Related issues: +#886488: [D7] Add stampede protection for css and js aggregation

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