To reproduce:

Mysql with Innodb:

0. A table named onetable has some records in it
1. Start a delegated transaction
2. Execute: $DB->delete_records('onetable');
3. Open a new connection with mysql client
4. Perform a SELECT over onetable

What you'll see is that onetable is empty... but it shouldn't because I've not performed a commit on moodle and we're in READ COMMITTED isolation level.

All records in the table should be returned.

Rationale:

Some SQL statements in mysql cause an implicit commit. This should be taken into account because code that relies on transactions can not be working as expected.

This is what i've found (Mysql+Innodb)

1. Start a delegated transaction

2. Execute: $DB->delete_records('onetable');

...

delete_records() optimizes the deletion by issuing a TRUNCATE TABLE statement... in mysql this performs an implicit commit ( http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html ) so statements executed after delete_records() are not performed atomically nor READ COMMITTED isolated.

Maybe TRUNCATE TABLE should not be issued inside a transaction... or maybe some warn should be issued... I don't what's the best general approach

What do yo think?

Thanks in advance