[Building Sakai] slightly different deadlock

[Hedrick Charles]

Here's one on just SAKAI_REALM_RL_GR. It wouldn't be so bad if it didn't cause all reads of SAKAI_REALM_RL_GR to hang. I'm kind of surprised at that.

It looks like this fairly straightforward INSERT attempts to get a shared lock and then upgrade it to exclusive. This causes obvious problems if two threads do the same thing at the same time.


*** (1) TRANSACTION:
TRANSACTION 4 3826138716, ACTIVE 50 sec, OS thread id 30971 inserting
mysql tables in use 3, locked 3
LOCK WAIT 8 lock struct(s), heap size 1216, 4 row lock(s)
MySQL thread id 131400, query id 13271312947 sakai-prod1.oirt.rutgers.edu 172.16.75.16 sakaiuser Sending data
INSERT INTO SAKAI_REALM_RL_GR (REALM_KEY, USER_ID, ROLE_KEY, ACTIVE, PROVIDED) VALUES ( (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = x'2F736974652F36303063313134302D333839302D343237342D303063612D646431376433373130376261'), x'39336633303631652D353634652D346662632D393562372D366337386630393664613165',  (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = x'53747564656E74'), x'31', x'30')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3160597 n bits 288 index `PRIMARY` of table `sakai`.`SAKAI_REALM_RL_GR` trx id 4 3826138716 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 70 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80009ad9; asc     ;; 1: len 30; hex 39336639666364642d303463392d346438342d613963662d343230333936; asc 93f9fcdd-04c9-4d84-a9cf-420396;...(truncated); 2: len 6; hex 000000003481; asc     4 ;; 3: len 7; hex 800013401c0ff5; asc    @   ;; 4: len 4; hex 80000008; asc     ;; 5: len 1; hex 31; asc 1;; 6: len 1; hex 30; asc 0;;

*** (2) TRANSACTION:
TRANSACTION 4 3826138710, ACTIVE 50 sec, OS thread id 30912 inserting, thread declared inside InnoDB 1
mysql tables in use 3, locked 3
8 lock struct(s), heap size 1216, 4 row lock(s)
MySQL thread id 131341, query id 13271312939 sakai-prod1.oirt.rutgers.edu 172.16.75.16 sakaiuser Sending data
INSERT INTO SAKAI_REALM_RL_GR (REALM_KEY, USER_ID, ROLE_KEY, ACTIVE, PROVIDED) VALUES ( (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = x'2F736974652F36303063313134302D333839302D343237342D303063612D646431376433373130376261'), x'39336633303631652D353634652D346662632D393562372D366337386630393664613165',  (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = x'53747564656E74'), x'31', x'30')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3160597 n bits 288 index `PRIMARY` of table `sakai`.`SAKAI_REALM_RL_GR` trx id 4 3826138710 lock mode S locks gap before rec
Record lock, heap no 70 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80009ad9; asc     ;; 1: len 30; hex 39336639666364642d303463392d346438342d613963662d343230333936; asc 93f9fcdd-04c9-4d84-a9cf-420396;...(truncated); 2: len 6; hex 000000003481; asc     4 ;; 3: len 7; hex 800013401c0ff5; asc    @   ;; 4: len 4; hex 80000008; asc     ;; 5: len 1; hex 31; asc 1;; 6: len 1; hex 30; asc 0;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3160597 n bits 288 index `PRIMARY` of table `sakai`.`SAKAI_REALM_RL_GR` trx id 4 3826138710 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 70 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80009ad9; asc     ;; 1: len 30; hex 39336639666364642d303463392d346438342d613963662d343230333936; asc 93f9fcdd-04c9-4d84-a9cf-420396;...(truncated); 2: len 6; hex 000000003481; asc     4 ;; 3: len 7; hex 800013401c0ff5; asc    @   ;; 4: len 4; hex 80000008; asc     ;; 5: len 1; hex 31; asc 1;; 6: len 1; hex 30; asc 0;;

*** WE ROLL BACK TRANSACTION (2)
------------