We have a very problematic deadlock issue and I'm lucky that I could find out the reason: It's an incompatibility between masquerade.module and the httpbl.module.
It's simple to reproduce on this site:
0. Install httbl.module and masquerade.module
1. Log in as user 1
2. Masquerade as any user
3. Deadlock occurs until wait_timeout limit is reached. The whole website is dead!
I think the following results will help to find the reasons. I hope the problem is reproducable in a simple dev environment.
I've posted this issue into the httpbl.module and not the masquerade.module because it seems that the lockings come from httpbl.
Here's the SHOW ENGINE INNODB STATUS result:
=====================================
141112 15:34:46 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 850315 1_second, 850308 sleeps, 80712 10_second, 45355 background, 45354 flush
srv_master_thread log flush and writes: 852362
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 68824, signal count 171500
Mutex spin waits 1123383, rounds 1001093, OS waits 8197
RW-shared spins 83953, rounds 1730610, OS waits 56093
RW-excl spins 4042, rounds 258235, OS waits 4268
Spin rounds per wait: 0.89 mutex, 20.61 RW-shared, 63.89 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
141111 18:35:32
*** (1) TRANSACTION:
TRANSACTION 40AF1C96, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1613345, OS thread handle 0x7f2b9ef73700, query id 185466644 localhost 127.0.0.1 usr_drupal_xxxxxx Updating
UPDATE httpbl SET status='0', expire='1416936932'
WHERE ( (hostname = 'XX.XXX.XX.XXX') )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1138501 page no 22 n bits 488 index "PRIMARY" of table "drupal_xxxxxx"."httpbl" trx id 40AF1C96 lock_mode X locks rec but not gap waiting
Record lock, heap no 419 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 13; hex 38302e3138372e3130352e3831; asc 80.187.105.81;;
1: len 6; hex 000040af1c95; asc @ ;;
2: len 7; hex 8b000029980110; asc ) ;;
3: len 1; hex 80; asc ;;
4: len 4; hex d474bde4; asc t ;;
*** (2) TRANSACTION:
TRANSACTION 40AF1C94, ACTIVE 0 sec starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1613344, OS thread handle 0x7f2b9ec94700, query id 185466645 localhost 127.0.0.1 usr_drupal_xxxxxx Updating
UPDATE httpbl SET status='0', expire='1416936932'
WHERE ( (hostname = '80.187.105.81') )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1138501 page no 22 n bits 488 index "PRIMARY" of table "drupal_xxxxxx"."httpbl" trx id 40AF1C94 lock mode S locks rec but not gap
Record lock, heap no 419 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 13; hex 38302e3138372e3130352e3831; asc 80.187.105.81;;
1: len 6; hex 000040af1c95; asc @ ;;
2: len 7; hex 8b000029980110; asc ) ;;
3: len 1; hex 80; asc ;;
4: len 4; hex d474bde4; asc t ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1138501 page no 22 n bits 488 index "PRIMARY" of table "drupal_xxxxxx"."httpbl" trx id 40AF1C94 lock_mode X locks rec but not gap waiting
Record lock, heap no 419 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 13; hex 38302e3138372e3130352e3831; asc 80.187.105.81;;
1: len 6; hex 000040af1c95; asc @ ;;
2: len 7; hex 8b000029980110; asc ) ;;
3: len 1; hex 80; asc ;;
4: len 4; hex d474bde4; asc t ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 40BD122C
Purge done for trx's n:o < 40BD117F undo n:o < 0
History list length 844
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1739317, OS thread handle 0x7f2b9ecf6700, query id 202469880 localhost admin
SHOW ENGINE INNODB STATUS
---TRANSACTION 40BD122A, not started
MySQL thread id 1739315, OS thread handle 0x7f2b9ed89700, query id 202469871 localhost admin
--------
Comments
Comment #1
anybodySetting the priority up to critical because it makes the website completely unusable.
Comment #2
bryrock commented@Anybody, I've been unable to reproduce this issue with the simple steps you listed above, and I know of some heavily trafficked sites that do use both httpbl and masquerade. I'm wondering if this may be related to #1343432: Integrity constraint violation.
Can you provide any more specific information?
Comment #3
bryrock commented