I've getting long pauses when trying to do permissions checking. The queries are typically
select count(1) from SAKAI_REALM_RL_FN,SAKAI_REALM force index (AK_SAKAI_REALM_ID) where SAKAI_REALM_RL_FN.REALM_KEY =
We can get 30 of these hung, all waiting for index `PRIMARY` of table `sakai`.`SAKAI_REALM`
Often there's at least one update or insert active, but I'm not so sure it's all the time.
I've seen several different deadlocks on the main INSERT into SAKAI_REALM_RL_GR or UPDATE of SAKAI_REALM. But many times we get something like a 30 sec hang that doesn't show up in the deadlock section of "show innodb status."
This is mysql 5.1.44. We haven't done anything unusual to the setup.
I'm concerned that the code that updates the realm tables doesn't do any high level locking. Initially I tried fixing up the various update and insert queries, but I ran into at least 3 different deadlocks. So it seemed best to do an explicit row lock on the realm at the beginning of all the transactions. II'm attaching a diff. But I'm by no means sure this is actually relevant to our most common problem.