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

MSSQL gets confused about transactions when doing quizzes under high load

XMLWordPrintable

    • MOODLE_28_STABLE, MOODLE_29_STABLE, MOODLE_30_STABLE
    • MOODLE_28_STABLE, MOODLE_29_STABLE
    • Hide

      0. Requirements SQL*Server database and both mssql and sqlsrv drivers.

      A. Test with the mssql driver.

      1. Log in as admin to your site, go to Admin -> Notifications (admin/index.php) page.
      2. Using the SQL*Server Management Studio or any other client execute:

      ALTER DATABASE your_database_name SET READ_COMMITTED_SNAPSHOT OFF;

      3. Reload the notifications page, you should see the server diagnosis message about to enable READ_COMMITTED_SNAPSHOT.
      4. Using the SQL*Server Management Studio or any other client execute:

      ALTER DATABASE your_database_name SET READ_COMMITTED_SNAPSHOT ON;

      5. Reload the notifications page, you should NOT see the server diagnosis message.

      B. Test with the sqlsrv driver.

      1. Repeat steps 1-5.

      Show
      0. Requirements SQL*Server database and both mssql and sqlsrv drivers. A. Test with the mssql driver. 1. Log in as admin to your site, go to Admin -> Notifications (admin/index.php) page. 2. Using the SQL*Server Management Studio or any other client execute: ALTER DATABASE your_database_name SET READ_COMMITTED_SNAPSHOT OFF; 3. Reload the notifications page, you should see the server diagnosis message about to enable READ_COMMITTED_SNAPSHOT. 4. Using the SQL*Server Management Studio or any other client execute: ALTER DATABASE your_database_name SET READ_COMMITTED_SNAPSHOT ON; 5. Reload the notifications page, you should NOT see the server diagnosis message. B. Test with the sqlsrv driver. 1. Repeat steps 1-5.

      Load testing our Moodle 2.9 using the LoadRunner software, and also JMeter.

      These are what we have tested with:

      Moodle 2.9 on Redhat 6.4
      Windows Server 2012 with MSSQL Server 2012
      PHP 5.4.44 using FreeTDS to connect to MSSQL.

      Moodle 2.9.1 on Amazon Linux (AWS)
      MSSQL Express (AWS)
      PHP 5.4.44 using FreeTDS to connect to MSSQL.

      The load test scenario is quite simple: login > entering course > entering quiz > starting a new quiz (5 multiple choice questions) > pressing next > trying to submit the quiz > logout

      We have tried from 10 - 250 concurrents (unique user account for each), much of them will got errors when trying to submit the quiz.

      250 concurrents causes our Apache to shutdown.

      The response from Loadrunner shows:

      HTTP Status-Code=404 (Not Found) for "https//www.ourserver.com/moodle/mod/quiz/processattempt.php"

      The HTML response shows:

      Error reading from database.

      Debug info: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. COMMIT TRANSACTION [NULL]

      Error code: dmlreadexception

      Stack trace:
      line 443 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 242 of /lib/dml/mssql_native_moodle_database.php: call to moodle_database->query_end()
      line 1425 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
      line 2408 of /lib/dml/moodle_database.php: call to mssql_native_moodle_database->commit_transaction()
      line 92 of /lib/dml/moodle_transaction.php: call to moodle_database->commit_delegated_transaction()
      line 192 of /mod/quiz/processattempt.php: call to moodle_transaction->allow_commit()

            stronk7 Eloy Lafuente (stronk7)
            montai Montai Settapokin
            Andrew Lyons Andrew Lyons
            Ryan Wyllie Ryan Wyllie
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.