This issue is about to describe the problem happening with current sqllrv moodle driver (using the MS driver for PHP) to use Moodle under SQL*Server databases.

Along the last months, it has been reported in various issues, in different places, and it's affecting more and more, while we add more transactions to code base.

Basically, this is the history:

1) By default SQL*Server does not allow to have more than one recordset opened at the same time. Aka, any code doing this, will fail:

• open recordset
• open recordset

2) To solve that limitation MS invented the "Multiple Active Result Sets" mode (named MARS, good name), so the combination above started to work (apparently by introducing a noticeable slowdown, but works).

3) Obviously, in the Moodle driver, we are using that mode on all connections.

4) The MARS mode, has some severe limitations about what can be done and what cannot. One of them is, literally, killing us. Cannot use transactions if there is any recordset open. Aka, any code doing this, will fail:

• open recordset
• start transaction

5) While in Moodle 2.0 (when the driver was created), we weren't using transactions in many places, it has been a long walk since then, and everyday we are using transactions more and more. All those places are breaking when the sqlsrv driver is used.

So, it's time to solve this problem. Possible solutions are:

A) Drop support of the driver and turn everybody to use the FreeTDS alternative. Somehow those guys manage to workaround that problem and, with their driver, all tests are passing (note we added some specific tests at MDL-34130). I read somewhere, time ago, that they read the whole recordset in memory, just to later simulate iteration from there. Inefficient but works and that way they workaround both the problem exposed in 1) and 4) above.

B) Solve that in the Moodle sqlsrv driver, so each time a transaction is going to be stated, we read ALL the opened recodsets and put them in memory (and closing them), emulating iteration from there (similar to the FreeTDS solution), only applied for 4). Hopefully, memory won't grow a lot (if opened recordsets aren't too big) and speed should not get much impact. It seems this is pretty doable (in fact we have already one candidate patch for it, thanks Petr!).

C) Ask to MS to introduce some mode into their MS driver for PHP ("Will Transactions Finally Work under MARS, aka WTF-W-MARS"). If it can be implemented by us @ B) in our driver), surely it's also implementable by them into their driver. That would fix, once and for all, one of the major drawbacks of SQL*Server trying to run on pair with other, more "standard" RDBMS where the limitations commented above in 1) and 4) are, simply, ridiculous.

And that is all, ciao