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

Mon Oct 10 10:33:30 PDT 2011

Deadlocks like this in mysql are not necessarily a problem if they're
caused by frequent updates. They are basically a side-effect of the
indexing strategy and the correct solution is just retry the transaction
which I think the underlying code already does, going back to about
Sakai 2.2 and added by Glenn G after extensive discussion about the
issue.

So the question is whether there's some specific functional issue here,
or you're just seeing these in the innodb status.

Cheers
Stephen 

-- 
Stephen Marquard, Acting Director
Centre for Educational Technology, University of Cape Town
http://www.cet.uct.ac.za
Email / IM (Jabber/XMPP): stephen.marquard at uct.ac.za
Phone: +27-21-650-5037 Cell: +27-83-500-5290 

>>> Hedrick Charles <hedrick at rutgers.edu> 10/10/2011 7:06 PM >>> 
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

_______________________________________________
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"

###

UNIVERSITY OF CAPE TOWN 

This e-mail is subject to the UCT ICT policies and e-mail disclaimer
published on our website at
http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from
+27 21 650 9111. This e-mail is intended only for the person(s) to whom
it is addressed. If the e-mail has reached you in error, please notify
the author. If you are not the intended recipient of the e-mail you may
not use, disclose, copy, redirect or print the content. If this e-mail
is not related to the business of UCT it is sent by the sender in the
sender's individual capacity.

###