Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-3901

Optionally ignore LAST_INSERT_ID() in transaction_replay

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 6.2.0
    • Fix Version/s: N/A
    • Component/s: readwritesplit
    • Labels:
      None

      Description

      There are cases where the actual auto-generated ID for auto-increment fields is not used by the application and if it were to change during the retrying of the transaction, it would not affect the correctness of the application. To support this, readwritesplit should be able to ignore the last_insert_id value in the OK packet as well as any naive SELECT LAST_INSERT_ID() queries done by frameworks. Since this affects the correctness of some transactions, this must be enabled only on a case-by-case basis where the user knows it is safe to ignore the auto-generated ID.

      One extremely simple example would be a transaction that selects from a table to generate a derived value and then finally inserts it into a different table:

      START TRANSACTION;
      SELECT id, balance FROM accounts WHERE user = 'sam' FOR UPDATE;
      UPDATE accounts SET balance = balance - 100 WHERE id = 123;
      INSERT INTO withdrawals(user_id, amount) VALUES (id, -100);
      -- This is where the connection fails and a replay is done
      COMMIT;
      

      The SELECT must return an identical result for the UPDATE and INSERT to be correct. As the INSERT is the last statement in the transaction, the actual ID generated for the field is not relevant in this case and can be safely ignored. Currently a replay of the INSERT will most likely fail if another transaction was completed after the original transaction started and the replayed one finished replaying. If the last_insert_id value was ignored, the transaction would succeed with no danger of incorrect behavior.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              markus makela markus makela
              Reporter:
              markus makela markus makela
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.