There are some places in our code where we are nesting transactions.
A code like:
$dbw->begin();
$dbw->insert( 'image', ... );
$article->doEdit( $pageText, $comment, EDIT_NEW | EDIT_SUPPRESS_RC );
$dbw->query( "UPDATE $site_stats SET ss_images=ss_images+1" );
$dbw->commit();
Is apparently safely grouped into a transaction, but the UPDATE won't be
in the same transaction as the insert, since Article::doEdit will itself
open a transaction, breaking the old one.
This is an unexpected result, and hard to realise, should the second
transaction lie several calls down (or even inside a hook!).
The transaction break, common to all our supported databases is not even
consistent among them:
* Mysql will commit the open transaction if you begin another one [1].
* Postgres will ignore the second begin (and issue a warning) if you are
inside a transaction [2].
* In sqlite an attempt to invoke the BEGIN command within a transaction
will fail with an error [3] (our driver forces a commit there, matching
mysql behavior).
* Oracle is not using a specific statement in our driver begin().
Calling begin() inside another transaction will be silently ignored
(matches postgres).
* Mssql only takes into account the outermost transaction [4] (matches
postgres).
* Ibm_db2 driver is changing the autocommit property like Oracle, thus
matching postgres.
I propose changing Database::begin() / commit() / rollback() to keep the
count in mTrxLevel and perform a savepoint instead of a BEGIN should it
be called inside another one.
Savepoints are a way to perform partial rollbacks inside a bigger
transaction. They are supported by all our databases [5] [6] [7] [8] [9]
[10]. We shouldn't even have problems with our 4.0.30
servers, since its support was added in mysql 4.0.14 [11].
1-http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html
2-http://www.postgresql.org/docs/9.0/static/sql-begin.html
3-http://sqlite.org/lang_transaction.html
4-http://msdn.microsoft.com/en-us/library/ms188929.aspx
5-http://dev.mysql.com/doc/refman/5.1/en/savepoint.html
6-http://www.postgresql.org/docs/9.0/static/sql-savepoint.html
7-http://sqlite.org/lang_savepoint.html
8-http://www.dba-oracle.com/t_savepoint.htm
9-http://msdn.microsoft.com/en-us/library/ms378414.aspx
10-http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb…
11-http://dev.mysql.com/doc/refman/4.1/en/savepoint.html