[Building Sakai] Sakai deadlocks

Thu Apr 24 07:07:46 PDT 2014

Hi Jaco

Unlike Oracle, some mysql deadlocks are "expected", i.e. they can occur when there is nothing wrong with the semantics of the database transactions being executed. This is a result of index gap locking, so a series of concurrent updates or inserts in parallel transactions can trigger a deadlock.

Sakai has code to deal with this in BasicSqlService, which is why you see explicit logs and retries. You can make mysql not do index gap locking by setting this parameter in /etc/my.cnf:

innodb_locks_unsafe_for_binlog=1

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog

Regards
Stephen

---
Stephen Marquard, Learning Technologies Co-ordinator
Centre for Innovation in Learning and Teaching (CILT, formerly CET and HAESDU)
University of Cape Town
http://www.cet.uct.ac.za<http://www.cet.uct.ac.za/>
stephen.marquard at uct.ac.za<mailto:stephen.marquard at uct.ac.za>
Phone: +27-21-650-5037 Cell: +27-83-500-5290

From: sakai-dev-bounces at collab.sakaiproject.org [mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Jaco Gillman
Sent: 24 April 2014 11:00 AM
To: Sakai Dev Mail List
Subject: [Building Sakai] Sakai deadlocks

Hi all

We have build a process for a Sakai 2.9.3 instance that creates Course Sites, adds users, create groups for those sites and and add users to these groups.

On my local instance I have tested this process numerous times and the upload work 100%.

The moment we deployed the Sakai instance on a physical Production server, connected to another physical DB server we ran into deadlocks. Here is some of what we experience:

(2014-04-24 10:31:57,019 http-bio-8080-exec-9_org.sakaiproject.db.impl.BasicSqlService) - WARN: Sql.dbWrite(): deadlock: error code: 1213 sql: delete from SAKAI_SITE_GROUP_PROPERTY where SITE_ID = ? binds:  f5acc756-2e71-400b-9f8d-9c63c9a6cd3d com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
(2014-04-24 10:31:57,019 http-bio-8080-exec-9_org.sakaiproject.db.impl.BasicSqlService) - WARN: transact: deadlock: rolling back: site:f5acc756-2e71-400b-9f8d-9c63c9a6cd3d
(2014-04-24 10:31:57,019 http-bio-8080-exec-9_org.sakaiproject.db.impl.BasicSqlService) - WARN: transact: deadlock: retrying (1 / 5): site:f5acc756-2e71-400b-9f8d-9c63c9a6cd3d

We used the mysql "show innodb engine status" cmd which gave us the output below:

Up till now, we have made changes to the MySQL config setting (my.cnf), tried to run the same process using my local Sakai deployment instance connected to the same DB server as what the Production instance is using which was successful. Other developers in the team also manage to run the Process without any issues.

Have anyone experienced the same (wrt deadlocks on that tables, or just deadlocks in general in Sakai) as what I describe here? Any suggestions?

------------------------
LATEST DETECTED DEADLOCK
------------------------
140421 19:06:26
*** (1) TRANSACTION:
TRANSACTION FA2C2, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 149 lock struct(s), heap size 31160, 1634 row lock(s), undo log entries 1079
MySQL thread id 24, OS thread handle 0x7f1f5d42e700, query id 8410598 41.77.153.228 apc_user update
insert into SAKAI_SITE_GROUP_PROPERTY( GROUP_ID, NAME, VALUE, SITE_ID ) values (x'36303436393939652D363338642D346638362D393362302D663136343461313236386163',x'67726F75705F70726F705F7773657475705F63726561746564',x'74727565',x'65386139373463632D373132612D343862362D623861382D383164366265326532613633')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 6321 n bits 192 index `PRIMARY` of table `sakai_apc`.`sakai_site_group_property` trx id FA2C2 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 20 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 30; hex 36303638376339652d303361312d346563342d383462662d356263306563; asc 60687c9e-03a1-4ec4-84bf-5bc0ec; (total 36 bytes);
 1: len 25; hex 67726f75705f70726f705f7773657475705f63726561746564; asc group_prop_wsetup_created;;
 2: len 6; hex 0000000f7aa7; asc     z ;;
 3: len 7; hex 180000177f0181; asc        ;;
 4: len 30; hex 36356237623565652d656138382d346166632d383932652d633364336564; asc 65b7b5ee-ea88-4afc-892e-c3d3ed; (total 36 bytes);
 5: len 4; hex 74727565; asc true;;

*** (2) TRANSACTION:
TRANSACTION FA2C4, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
150 lock struct(s), heap size 31160, 1885 row lock(s), undo log entries 1247
MySQL thread id 25, OS thread handle 0x7f1f5d4b0700, query id 8410974 41.77.153.228 apc_user update
insert into SAKAI_SITE_GROUP_PROPERTY( GROUP_ID, NAME, VALUE, SITE_ID ) values (x'38393461343732322D353438352D343135662D393837352D303138333733633664383035',x'67726F75705F70726F705F7773657475705F63726561746564',x'74727565',x'65663863326534632D353862362D343137322D613437322D336564393634636262323031')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 6321 n bits 192 index `PRIMARY` of table `sakai_apc`.`sakai_site_group_property` trx id FA2C4 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 30; hex 35396565326237312d343736362d343862322d393362322d643666653236; asc 59ee2b71-4766-48b2-93b2-d6fe26; (total 36 bytes);
 1: len 25; hex 67726f75705f70726f705f7773657475705f63726561746564; asc group_prop_wsetup_created;;
 2: len 6; hex 0000000f6323; asc     c#;;
 3: len 7; hex 11000005820fa4; asc        ;;
 4: len 30; hex 64623632633562612d386137352d346332302d393938612d376533633835; asc db62c5ba-8a75-4c20-998a-7e3c85; (total 36 bytes);
 5: len 4; hex 74727565; asc true;;

Record lock, heap no 20 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 30; hex 36303638376339652d303361312d346563342d383462662d356263306563; asc 60687c9e-03a1-4ec4-84bf-5bc0ec; (total 36 bytes);
 1: len 25; hex 67726f75705f70726f705f7773657475705f63726561746564; asc group_prop_wsetup_created;;
 2: len 6; hex 0000000f7aa7; asc     z ;;
 3: len 7; hex 180000177f0181; asc        ;;
 4: len 30; hex 36356237623565652d656138382d346166632d383932652d633364336564; asc 65b7b5ee-ea88-4afc-892e-c3d3ed; (total 36 bytes);
 5: len 4; hex 74727565; asc true;;

Record lock, heap no 32 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 30; hex 36326230666532612d316234352d346332372d613436342d373761343963; asc 62b0fe2a-1b45-4c27-a464-77a49c; (total 36 bytes);
 1: len 25; hex 67726f75705f70726f705f7773657475705f63726561746564; asc group_prop_wsetup_created;;
 2: len 6; hex 0000000f6ebe; asc     n ;;
 3: len 7; hex 77000029be33f0; asc w  ) 3 ;;
 4: len 30; hex 31613864346564332d643366622d346639662d613937322d346636353964; asc 1a8d4ed3-d3fb-4f9f-a972-4f659d; (total 36 bytes);
 5: len 4; hex 74727565; asc true;;

Regards,

Jaco Gillman

Java Developer

opencollab

Tel: +27 21 970 4017  |  Fax: +27 21 914 3098

Email: jaco at opencollab.co.za<mailto:jaco at opencollab.co.za>  |  Skype: gillmanjc

Web: www.opencollab.co.za<http://www.opencollab.co.za/>

________________________________
See OpenCollab email disclaimer at http://www.opencollab.co.za/email-disclaimer
________________________________
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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140424/097e7628/attachment.html 