Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-29198

Delegated transactions not working properly on MySQL

    Details

    • Database:
      MySQL
    • Testing Instructions:
      Hide

      A) To be tested under the five supported drivers: mysqli (InnoDB only), postgres, mssql, sqlsrv and oracle
      B) To be tested under 21_STABLE ! (fix is 100% the same in other branches)

      1) Run DB functional tests
      2) TEST: Ignoring other errors, no error should be shown related with transactions (specially test_concurent_transactions ones).

      Show
      A) To be tested under the five supported drivers: mysqli (InnoDB only), postgres, mssql, sqlsrv and oracle B) To be tested under 21_STABLE ! (fix is 100% the same in other branches) 1) Run DB functional tests 2) TEST: Ignoring other errors, no error should be shown related with transactions (specially test_concurent_transactions ones).
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      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

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  10/Oct/11