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

Optionally ignore LAST_INSERT_ID() in transaction_replay

    XMLWordPrintable

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 6.2.0
    • N/A
    • readwritesplit
    • 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

              markus makela markus makela
              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.