VTGate write retries after ambiguous tablet failures risk duplicate non-idempotent DML

Answer

When a tablet disappears after MySQL has committed a single‑statement write but before the client receives the response, VTGate cannot tell whether the write succeeded. If the connection drops, VTGate may automatically retry the statement against a newly elected primary. For non‑idempotent DML (INSERT without a unique key, UPDATE … SET col = col + 1, etc.) this retry can apply the same change a second time, producing duplicates.

Confirmed facts

• Vitess has no way to determine commit status when the connection is lost before the reply.
• Automatic retry is only guaranteed safe for read‑only queries and for transactions that never reached the commit phase.
• Tablet buffering during PlannedReparentShard holds requests while a new primary is elected, shrinking the ambiguous‑failure window but not removing it entirely.

Likely explanation

The failure occurs after MySQL commits the statement but before the network ACK reaches VTGate. VTGate interprets the timeout as a tablet failure, triggers failover, and, if retry is enabled, re‑sends the original statement. Because the statement is not idempotent, the second execution creates a duplicate row or increments a counter twice.

Steps to mitigate

1. Enable client‑side idempotency: generate a unique request ID (UUID) and store it in a dedup table together with the write in a single transaction. On retry the dedup insert fails (unique key) and the write is skipped or detected.
2. If schema changes are possible, add a unique constraint or use INSERT … ON DUPLICATE KEY UPDATE / UPSERT patterns so that a repeat is harmless.
3. For counter‑style updates, restructure to insert an event row with the request ID and aggregate later, which is naturally idempotent.
4. Review VTGate retry flags for your Vitess version (e.g., -retry_enabled, -buffer_size, -buffer_on_failover). If the version allows disabling retries for DML outside transactions, set -retry_enabled=false or scope it to read‑only queries.
5. Ensure the buffering window (-buffer_wait_time or similar) is long enough to cover typical failover times; monitor vtgate logs for buffered‑request metrics.

Missing diagnostic detail

To give a precise flag recommendation, I need to know the exact Vitess major version you are running (e.g., v13, v14, v15). Flag names and defaults differ between releases, and the version determines whether -retry_enabled applies to single‑statement DML.