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

Mon Oct 10 10:47:10 PDT 2011

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