Details
-
New Feature
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
6.2.0
-
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
- duplicates
-
MXS-3894 Invent a configuration option to allow transaction replay ignore checksum check
- Closed