Okay, after further testing today I've confirmed that disabling Mahara's usage of explicit database transactions (i.e. "start transaction;" and "commit;") in MySQL resolves this problem. On the test server I was using, I was getting deadlocks with as few as 4 concurrent users copying a collection. After disabling transactions, I was able to run 50 concurrent users without any errors.
At that point it was taking 8.5 minutes for the copy operation to finish, so I didn't test any higher than that. So, we still have some performance & scaling work we could do, but it solves the problem for deadlocks. :) With further testing on the same server I also verified that the earlier patch changing the transaction isolation level from "REPEATABLE READ" to "READ COMMITTED" helps prevent deadlocks as well. When I disabled explicit transactions, and also rolled back to the default "REPEATABLE READ", I got deadlocks when I had 15 concurrent users copying the collection. So I think Mahara's immediate fix for the next minor release, has go to to be: 1. Keeping the "READ COMMITTED" patch in place. 2. Disabling Mahara's usage of explicit transactions, in MySQL. In the longer term we can look into all the places we use transactions, and maybe remove some of them. We shouldn't be putting huge operations, like the copying of a collection, inside of a database transaction. At least, not for MySQL, unless we can figure out how to make InnoDB act a lot more like Postgres in this regard. Cheers, Aaron -- 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/1514608 Title: MySQL deadlock issues with simultaneous users Status in Mahara: In Progress Bug description: The patch in Bug 1492919 resolved the deadlock issues for some users, but others are still reporting that they are getting deadlocks. This seems to be an issue unique to MySQL, and it can happen with as few as 2 or 3 concurrent users. It has been reported across all stable versions. Strangely, it seems to be a relatively recent issue (i.e. we never heard this with Mahara 1.8 or 1.7, etc), so this suggests it was triggered by something that changed fairly recently. One possible candidate is our switch to the mysqli driver (before that Mahara wasn't doing transactions at all, in mysql). See also: * https://mahara.org/interaction/forum/topic.php?id=7309 * https://mahara.org/interaction/forum/topic.php?id=7432 To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1514608/+subscriptions _______________________________________________ Mailing list: https://launchpad.net/~mahara-contributors Post to : [email protected] Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
