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

Delegated transactions not working properly on MySQL

    XMLWordPrintable

    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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              stronk7 Eloy Lafuente (stronk7)
              Reporter:
              jsegarra Juan Segarra Montesinos
              Integrator:
              Aparup Banerjee
              Tester:
              Aparup Banerjee
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

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