[Building Sakai] Sakai deadlocks

Thu Apr 24 01:59:33 PDT 2014

*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  |  *Skype*: gillmanjc

*Web*: www.opencollab.co.za

-- 

------------------------------
See OpenCollab email disclaimer at 
http://www.opencollab.co.za/email-disclaimer
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140424/2965a47f/attachment.html 