What happens to in-flight transactions during a database outage? Usually, work that had not committed is rolled back during recovery, while a durably committed transaction is recovered from the database log. But if the connection fails while COMMIT is in progress, the client may not know whether the transaction succeeded. The answer depends on whether the failure affected the database, the client connection or a distributed participant—and on the database’s durability settings.
What happens to a transaction in each state?
| Transaction state when the failure occurs | Typical outcome |
|---|---|
| Active but not committed | The database normally rolls back the unfinished work during recovery. MySQL documents that InnoDB rolls back transactions that were neither committed nor in XA PREPARE state when the server exited. PostgreSQL uses write-ahead logging (WAL) to recover to a consistent state. MySQL InnoDB recovery; PostgreSQL WAL. |
| Committed with normal synchronous durability | The database normally recovers the committed work by replaying its log, even if the changed data pages had not yet been written. PostgreSQL describes the normal synchronous behavior this way: “The client is therefore guaranteed that a transaction reported to be committed will be preserved, even in the event of a server crash immediately after.” This guarantee depends on synchronous commit behavior; asynchronous settings can change it. PostgreSQL WAL; PostgreSQL asynchronous commit. |
| Commit may have completed, but the client did not receive the reply | The outcome is unknown to the client: the server may have committed before the connection failed, or it may not have committed. A timeout or dropped connection alone does not establish which occurred. Oracle COMMIT reference. |
| Prepared in a distributed transaction | A participant can be left awaiting the final decision if communication fails during two-phase commit. Oracle calls this an in-doubt transaction. Recovery usually resolves it after communication resumes, but locks can remain while the outcome is unresolved. Oracle distributed transactions; Oracle transactions. |
Why the failure type and commit settings matter
A database-process crash, a failed host or storage system, a broken client connection, and a failed distributed participant are not interchangeable events. The key questions are which component failed, what state the transaction had reached, and what durability behavior was configured. For example, losing only the client connection can leave the server’s transaction outcome unknown to the client even if the database itself remains available.
As an Amazon Associate I earn from qualifying purchases.
Synchronous and asynchronous commits
With normal synchronous durability, a successful commit is expected to survive a server crash because the database has made the relevant log records durable before confirming success. PostgreSQL’s asynchronous commit mode can acknowledge a transaction before its WAL records are on disk, creating a brief window in which a crash can lose a recently acknowledged transaction. Oracle’s COMMIT WRITE NOWAIT option can likewise acknowledge before redo records are written. Check the database release and commit configuration rather than assuming every successful reply has identical durability semantics. PostgreSQL asynchronous commit; Oracle COMMIT reference.
What happens while the database recovers?
Recovery does not always mean the server is unavailable until every incomplete transaction has been undone. MySQL documents that InnoDB can accept new connections after applying redo while it rolls back incomplete transactions in a background thread. Those rollbacks can temporarily cause locking conflicts for new connections. MySQL InnoDB recovery.
#1 Best Overall
What should an application do if the connection drops during COMMIT?
- Treat the result as unknown. Do not assume the transaction failed just because the client timed out or lost its connection during
COMMIT. - Reconnect and check before retrying. Use a durable request ID, transaction ID or business-operation identifier to determine whether the operation took effect. The exact check depends on the database and application.
- Make retries safe where possible. Design retryable operations to be idempotent—for example, use a unique idempotency key so repeating a request cannot apply the same business action twice.
These steps address the gap between server-side completion and client receipt of the response. A client-side timeout is not, by itself, a reliable transaction-status check.
Quick Recap
Rank #4
- HP ProLiant DL360 G7 8B Server
- 2x X5650 2.66GHz 12-Cores Total
- 32GB RAM / 8x 146GB 10K 2.5in SAS Hard Drives
- P410 w/ 512MB
Rank #2
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
