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

Optionally ignore LAST_INSERT_ID() in transaction_replay



    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 6.2.0
    • N/A
    • readwritesplit
    • None


      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:

      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

      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.


        Issue Links



              markus makela markus makela
              markus makela markus makela
              0 Vote for this issue
              1 Start watching this issue



                Git Integration

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