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.adm... 11-http://dev.mysql.com/doc/refman/4.1/en/savepoint.html