Hi guys, we keep having deadlocks with the semaphore table in our mysql error logs lately.

We are running on centos 5.8 with mysql 5.5.34 on the same box.

Here's a little sample by just hitting the home page of our site:

*** WE ROLL BACK TRANSACTION (2)
InnoDB: transactions deadlock detected, dumping detailed information.
131028 11:25:11
*** (1) TRANSACTION:
TRANSACTION 15805C1, ACTIVE 3 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 89671, OS thread handle 0x49675940, query id 9018471 localhost user update
INSERT INTO semaphore (name, value, expire) VALUES ('variable_init', '334039550526e81d4467dc8.60998691', '1382973909.9296')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19017 page no 3 n bits 136 index "PRIMARY" of table "our_table_name"."semaphore" trx id 15805C1 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Any hints ?

Thanks.

Comments

So we find out that using the innodb mode on the semaphore table was the cause of the problem. When we switch it back to MyISAM mode, we never got deadlock for that specific table again.

I can confirm this problem for a large site too. I'm not sure where the reason for that lies (i think maybe in a contrib module). Finally we also switched to MyISAM.
I documented this in my German blog entry, if someone is interested: http://julian.pustkuchen.com/drupal-7-high-traffic-websites-guide

http://www.DROWL.de || Professionelle Drupal Lösungen aus Ostwestfalen-Lippe (OWL)
http://www.webks.de || webks: websolutions kept simple - Webbasierte Lösungen die einfach überzeugen!
http://www.drupal-theming.com || Individuelle Responsive Themes

You can resolve this by upgrading to the latest version of Drupal 7. See https://drupal.org/node/937284 for the fix.