Bring SQL transaction support back to MySQL
| Affects | Status | Importance | Assigned to | Milestone | |
|---|---|---|---|---|---|
| Mahara |
Triaged
|
Wishlist
|
Unassigned | ||
Bug Description
To resolve Bug 1514608 (Mahara locking when multiple simultaneous users are active), we disabled SQL transaction support in MySQL. Specifically, the "db_begin()", "db_rollback()", and "db_commit()" methods all check "is_myslq()" and return without doing anything if MySQL is in use.
As might be expected this has resulted in a few other MySQL-specific bugs popping up, due to race conditions.
So, it would be good to add transaction support back to our MySQL driver. However, I think to do so in a way that won't again cause concurrency issues, we need to be much more disciplined in how we use transactions. The MySQL problems were caused because we had very long-lived transactions. For instance, when copying a collection, we would open a transaction at the start of the process, and commit it at the end. Depending on the size of the collection and the pages in it, this could leave that transaction open for up to several minutes. From what I can tell, the MySQL InnoDB storage engine doesn't handle long-running transactions very well, so this causes a problem.
Ideally, then, we should use db_begin and db_commit close together only, like in the same function, enclosing some related SQL statements but *not* any calls to other functions that might balloon out in execution time (such as triggering an event).
Yet more ideally, we'd have some way of detecting and/or enforcing this automatically in Mahara's code. But I can't see any good way to do that in PHP. At most, we could track the amount of time that passes between class to db_begin() and db_commit(), and issue a warning message when a transaction has a long lifetime.
| Changed in mahara: | |
| importance: | Undecided → Medium |
| status: | New → Triaged |
| Changed in mahara: | |
| importance: | Medium → Wishlist |
| Alexander Del Ponte (delponte) wrote | #1 |
In some cases it also can lead to DB inconsistencies and in every of those cases additional code and computing ressources for a special handling are neccessary to prevent problems.
Currently I will have to circumvent this issue while preparing the core integration of the new Submissions plugin by doing exactly the mentioned hopefully temporarily solutions.
Cheers
Alex