[Building Sakai] we've got a deadlock in SAKAI_REALM

[Hedrick Charles]

It looks like the solution is to rewrite these to do the selects first and not as subselects. (In general I'm always suspicious of subselects in Mysql.) Does that sound right?

On Oct 10, 2011, at 12:01:15 PM, Hedrick Charles wrote:

> This is happening all the time.
> 
> *** (1) TRANSACTION:
> TRANSACTION 4 3807092751, ACTIVE 52 sec, OS thread id 31279 starting index read
> mysql tables in use 2, locked 1
> LOCK WAIT 121 lock struct(s), heap size 14320, 246 row lock(s), undo log entries 1
> MySQL thread id 133431, query id 13186746291 sakai-prod4.oirt.rutgers.edu 172.16.75.29 sakaiuser Updating
> update SAKAI_REALM set PROVIDER_ID = null,MAINTAIN_ROLE = (select MAX(ROLE_KEY) from SAKAI_REALM_ROLE where ROLE_NAME = x'496E7374727563746F72'),CREATEDBY = x'61316232303464312D623232342D343233632D303061612D663135336131346466616162',MODIFIEDBY = x'6D67726164696E',CREATEDON = '2007-05-11 10:08:24',MODIFIEDON = '2011-10-10 11:04:38' where ( REALM_ID = x'2F736974652F36303063313134302D333839302D343237342D303063612D646431376433373130376261' )
> *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 0 page no 1166143 n bits 184 index `PRIMARY` of table `sakai`.`SAKAI_REALM` trx id 4 3807092751 lock_mode X locks rec but not gap waiting
> Record lock, heap no 111 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
> 0: len 4; hex 80009ad9; asc     ;; 1: len 6; hex 0004e1f2808a; asc       ;; 2: len 7; hex 00006700150110; asc   g    ;; 3: len 30; hex 2f736974652f36303063313134302d333839302d343237342d303063612d; asc /site/600c1140-3890-4274-00ca-;...(truncated); 4: SQL NULL; 5: len 4; hex 80000007; asc     ;; 6: len 30; hex 61316232303464312d623232342d343233632d303061612d663135336131; asc a1b204d1-b224-423c-00aa-f153a1;...(truncated); 7: len 5; hex 61646d696e; asc admin;; 8: len 8; hex 8000124107af4398; asc    A  C ;; 9: len 8; hex 8000124a758ecbde; asc    Ju   ;;
> 
> *** (2) TRANSACTION:
> TRANSACTION 4 3807117357, ACTIVE 2 sec, OS thread id 31830 starting index read, thread declared inside InnoDB 500
> mysql tables in use 3, locked 3
> 22 lock struct(s), heap size 3024, 37 row lock(s)
> MySQL thread id 134020, query id 13186748464 sakai-prod6.oirt.rutgers.edu 172.16.75.31 sakaiuser statistics
> 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'38336531383735662D303936302D343030642D613334312D323139653437636334373962',  (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = x'616363657373'), x'31', x'30')
> *** (2) HOLDS THE LOCK(S):
> RECORD LOCKS space id 0 page no 1166143 n bits 184 index `PRIMARY` of table `sakai`.`SAKAI_REALM` trx id 4 3807117357 lock mode S locks rec but not gap
> Record lock, heap no 111 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
> 0: len 4; hex 80009ad9; asc     ;; 1: len 6; hex 0004e1f2808a; asc       ;; 2: len 7; hex 00006700150110; asc   g    ;; 3: len 30; hex 2f736974652f36303063313134302d333839302d343237342d303063612d; asc /site/600c1140-3890-4274-00ca-;...(truncated); 4: SQL NULL; 5: len 4; hex 80000007; asc     ;; 6: len 30; hex 61316232303464312d623232342d343233632d303061612d663135336131; asc a1b204d1-b224-423c-00aa-f153a1;...(truncated); 7: len 5; hex 61646d696e; asc admin;; 8: len 8; hex 8000124107af4398; asc    A  C ;; 9: len 8; hex 8000124a758ecbde; asc    Ju   ;;
> 
> *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 0 page no 1167807 n bits 280 index `AK_SAKAI_REALM_ID` of table `sakai`.`SAKAI_REALM` trx id 4 3807117357 lock mode S locks rec but not gap waiting
> Record lock, heap no 103 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
> 0: len 30; hex 2f736974652f36303063313134302d333839302d343237342d303063612d; asc /site/600c1140-3890-4274-00ca-;...(truncated); 1: len 4; hex 80009ad9; asc     ;;
> 
> *** WE ROLL BACK TRANSACTION (2)
>