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

Mon Oct 10 11:13:01 PDT 2011

I don't have time to do a lot of testing. My problem is that it's by no means clear whether the same unexpected locking is going to occur for this as for a subselect. It seems safest to do it separately, particularly since in many cases many insert statements are being done for the same realm, and it's a shame to do a separate select for each.

I actually suspect this is a mysql bug, but that sort of doesn't matter.

On Oct 10, 2011, at 1:47:10 PM, Aaron Zeckoski wrote:

> Just like writing a select statement basically but a tiny bit
> different so something like:
> 
> UPDATE table JOIN table2 ON.... JOIN table3 ON.... SET a=1, b=2, ....
> WHERE c is not null ....
> 
> I suspect you already found it but this should be pretty fast and
> avoids a possibility of data changing between the select and the
> update. the only real trick is that the JOIN comes before the SET but
> once you get that it is easy.
> examples here: http://forums.mysql.com/read.php?6,156225,156271
> It may not be possible but I think this is the ideal to shoot for.
> 
> -AZ
> 
> 
> On Mon, Oct 10, 2011 at 1:06 PM, Hedrick Charles <hedrick at rutgers.edu> wrote:
>> Sorry, I don't know some of the odd SQL syntax that well. I see an example. That will making fixing it *much* easier than having to rewrite the calling code.
>> 
>> On Oct 10, 2011, at 12:37:11 PM, Aaron Zeckoski wrote:
>> 
>>> I would try to do it as a join if possible.
>>> -AZ
>>> 
>>> 
>>> On Mon, Oct 10, 2011 at 12:03 PM, Hedrick Charles <hedrick at rutgers.edu> wrote:
>>>> 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)
>>>>> 
>>>> 
>>>> _______________________________________________
>>>> sakai-dev mailing list
>>>> sakai-dev at collab.sakaiproject.org
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>> 
>>>> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>>>> 
>>> 
>>> 
>>> 
>>> --
>>> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
>> 
>> 
> 
> 
> 
> -- 
> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile