Uploaded image for project: 'In-Portal CMS'
  1. In-Portal CMS
  2. INP-1835

Auto-retry database queries, that stumbled upon a lock/deadlock

    XMLWordPrintable

    Details

    • Type: Bug Report
    • Status: Needs Changes
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 5.2.2
    • Component/s: Database
    • Labels:
      None

      Description

      Part 1 - fixing error retry for iterators

      1. add the '$iterator_class = ''' parameter for the "\kDBConnection::showError" method
      2. when when doing a retry and "$iterator_class" parameter isn't empty, then execute "$this->GetIterator($sql, $key_field, $no_debug, $iterator_class)" code instead of "$this->Query($sql, $key_field, $no_debug)" code

      Part 2 - improving retry code

      1. add these settings to the "Database" sub-section of the "/system/config.php" file (specify them in the "Database Configuration" and "Update Database Configuration" steps of the installation wizard):
        1. DBErrorBackoffMaxRetryAttempts:
          1. title: Database Error Backoff Max Retry Attempts
          2. default: 3
          3. form control: textbox
        2. DBErrorBackoffLogicBaseTime:
          1. title: Database Error Backoff Logic Base Time (in milliseconds)
          2. default: 100
          3. form control: textbox
        3. DBEnableLockRetryDebugging:
          1. title: Database Lock Retry Debugging Enabled
          2. default: 0
          3. form control: radio buttons
      2. the default values of the above settings should be set via the "\kSystemConfig::getDefaults" method
      3. store values of the above settings as the protected properties of the "kDBConnection" class via the "kDBConnection::setup" method
      4. for all the places where kDBConnection/kDBConnectionDebug/kDBLoadBalancer classes are instantiated ensure, that their "setup" method called instead of the "Connect" method and values of above added settings are also passed to it
      5. for each of the database error handlers (specified during kDBConnection/kDBConnectionDebug/kDBLoadBalancer classes construction or via their "setErrorHandler" method) do this:
        1. add the "$throw_exception = null" parameter
        2. when "$throw_exception" is exactly "true", then throw the "RuntimeException"
      6. change the "kDBConnection::callErrorHandler" method like this:
        1. add the "$throw_exception = null" parameter
        2. pass on this parameter to the called error handler
      7. change the "\kDBConnection::showError" method like this:
        1. consider 1205 (lock) and 1213 (deadlock) errors are ones, that require retrying
        2. when a retriable error is encountered, then:
          1. call the "$this->callErrorHandler($sql, true)" instead of "$this->callErrorHandler($sql)" and catch the exception (we'll use it later)
          2. if no exception was caught, then rebuild it from error information as the "kLogger::handleSQLError" method does
          3. use the exponential formula for detecting a wait time (1st attempt: "DBErrorBackoffLogicBaseTime" time; 2nd+ attempt: 2^$attempt_number * "DBErrorBackoffLogicBaseTime"
          4. if it's a lock and "DBEnableLockRetryDebugging" setting is enabled, then add the caught exception to the System Log + additional info about the retry (retry number, total retries possible, wait time)
          5. if it's a disconnect issue, then reconnect
          6. execute Query/GetIterator method accordingly

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                alex Alex
                Reporter:
                alex Alex
                Developer:
                Alex
                Reviewer:
                Erik Snarski [Intechnic]
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 12.5h
                  12.5h