Agree that deadlocks are not necessarily a bad thing and retrying is something to work considering. I've now had chance to look at what's going on and I *think* I understand what's going on now...
MySQL defaults to a transaction isolation level of 'REPEATABLE READ' (the transaction snapshot is stable from the first read) compared to the default in Oracle and PostgreSQL of 'READ COMMITTED'. As a REPEATABLE READ level causes any index lock to be held for the entire transaction then two page/collection copy operations working on the various view tables are going to end up in deadlock. Some possible approaches: 1) Altering the transaction isolation for page copy operations Force the transaction isolation level to 'read committed' for page copy operations. This should prevent the deadlock that is appearing in 15.04 (and 1.10, 1.9, 1.8 and likely back further) when run on MySQL. A crude patch to a branch of 15.04 is below. If I test using a JMeter test plan that simulates 30 users initiating page copies - without the patch I can trigger deadlocks, with the patch I've not managed to hit a deadlock. https://github.com/rboyatt/mahara/tree/bug1492919-approach1 2) Setting transaction isolation application wide As above but setting the transaction isolation level for MySQL to be the same as when running on PostgreSQL (i.e. read committed'). I'm afraid I don't know Mahara well enough to understand the full implications of this change. Others will be much better placed to advise here! 3) More complex approach is addressing the issue that any of the transactions in the application might still fail due to deadlock. I couldn't find anywhere in the code that attempts transaction retry but perhaps I've missed it? So perhaps longer-term looking at transaction retries and how some of the longer operations in transactions lock the various elements of the DB. -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1492919 Title: Deadlock issues when 20-30 users copying collections & pages at the same time Status in Mahara: Triaged Bug description: Multiple users on the forum have reported deadlock issues when there are many users (20-30) copying pages & collections at the same time. See https://mahara.org/interaction/forum/topic.php?id=7309&offset=0&limit=10 Note that 20 to 30 users is around the number you'd hit if you're running a whole class of users through a training session at the same time. The three reported cases of this are all in 15.04, so it may be a problem introduced in that version. The problem has been reported in both Postgres & MySQL. To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1492919/+subscriptions _______________________________________________ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
