Unresolved behavior of SQLAlchemy merge during retry scenarios

Goal

Implement a retry mechanism for write operations that avoids duplicate inserts or lost updates when a transaction is retried after a transient failure.

Constraints & Uncertainties

Using SQLAlchemy’s merge method to perform idempotent upserts, the session may automatically issue a flush that re‑executes an INSERT if the instance is detached between retries. Concurrent sessions can modify the same row, potentially causing a duplicate key error or a lost update. The autocommit execution option and with_for_update lock mode are not uniformly supported across dialects, and their interaction with retries is not fully documented.

Questions

• Which SQLAlchemy feature—merge, autocommit execution options, or async async_session.begin()—provides the most reliable path to retry without duplicating writes?
• What unresolved behavior remains when merge is used concurrently, especially regarding lost updates or duplicate key exceptions?
• How can the combination of autocommit and with_for_update be leveraged (or avoided) to mitigate race conditions during retries?