You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
MySQL crash recovery can spend a very long time rolling back a large prepared transaction in the server main startup thread. During that time, mysqld has started as a process but the server is still unavailable to users.
One common production trigger is a large transaction that fills the disk while writing its binary log. With the default binlog_error_action=ABORT_SERVER, a fatal binlog write error aborts the server. After restart, binlog crash recovery finds that the transaction was prepared in InnoDB but its XID was not durably recorded as committed in the binary log, so the transaction must be rolled back. If the transaction is very large, synchronous rollback can block startup for hours.
This proposal makes rollback of such binlog-recovered prepared DML transactions asynchronous:
During binlog recovery, the main thread only changes the recovered prepared transaction into a recovered active transaction and persists that state transition.
The existing InnoDB recovery rollback background thread performs the expensive row-by-row undo work.
The transaction outcome remains unchanged. Transactions that binlog recovery decides to roll back are still rolled back. The optimization moves the expensive rollback work out of the startup-critical path after the recovery decision has been made crash-safe.
User / Developer Stories
As a DBA, I want the server could provide services as soon as possible at startup.
Proposed Scope
Add an optional storage-engine callback for recovery-time asynchronous rollback of a prepared XID.
Use the callback during binlog / XA recovery only after the transaction coordinator decision says the internal prepared transaction must be rolled back.
Implement the callback in InnoDB by converting eligible prepared DML transactions into recovered active transactions
Keep prepared DDL rollback synchronous, because DDL log recovery and dictionary recovery require the DDL side effects to be settled before startup continues.
Keep the existing synchronous rollback_by_xid path as a fallback for engines without the callback.
Add crash tests for first restart, repeated crash, DDL/DML mixed recovery.
The final transaction result MUST be identical to current recovery behavior.
A crash after the prepared-to-active transition and before rollback completion MUST still recover the transaction as active and roll it back.
Engines that do not implement the new callback MUST keep using rollback_by_xid.
Non-functional Requirements
Crash-recovery startup time SHOULD be reduced from rollback-time scale to decision-time scale for large prepared DML transactions that must be rolled back.
Impact Areas
SQL syntax or statements
Configuration options or system variables
Command-line options or utilities
User-visible behavior
Observability
Security or privilege model
Protocol or replication behavior
Upgrade / downgrade compatibility
Performance or resource usage
Files, persistence, or metadata formats
APIs or internal interfaces
Testing or QA coverage needs
Summary of the Approach
Today the recovery path is effectively:
recover prepared XIDs from storage engines
recover committed internal XIDs from the binary log
for each internal prepared XID:
if XID is in the recovered binary log commit set:
commit_by_xid()
else:
rollback_by_xid() # synchronous and can be very slow
The proposed path is:
recover prepared XIDs from storage engines
recover committed internal XIDs from the binary log
for each internal prepared XID:
if XID is in the recovered binary log commit set:
commit_by_xid()
else if engine supports recovery async rollback
recover_rollback_by_xid() # make rollback decision durable, hand off undo
else:
rollback_by_xid()
For InnoDB DML transactions, recover_rollback_by_xid() does not perform the full rollback. It performs only the fast, crash-safe handoff:
Locate the recovered prepared transaction by XID.
Convert the undo state from prepared to active.
Flush redo up to the transition LSN.
Change the in-memory transaction state to TRX_STATE_ACTIVE.
Let trx_recovery_rollback_thread roll back the transaction asynchronously.
For DDL transactions, the callback keeps using the synchronous rollback path because later DDL recovery phases depend on the dictionary and physical file effects being resolved.
A minor optimization: flush redo at then end bo binlog recovery instead of flush redo in each rollback call.
Crash Safety
The crucial crash-safety rule is that binlog recovery must not forget the prepared XID until InnoDB has made the alternative recovery path durable.
If MySQL changes a transaction from prepared to active only in memory and then crashes again, the next restart may find:
the transaction still prepared in InnoDB redo, but
the binlog recovery state already advanced so the old in-use binlog is no longer used to decide this XID.
That can strand a prepared transaction. To avoid this, the prepared-to-active transition must be written to redo and flushed before binlog recovery completes. After that point, a repeated crash is safe because InnoDB will recover the transaction as active and roll it back through the existing background recovery rollback logic.
The recovery rollback thread also must not exit too early. Binlog recovery can discover prepared transactions and hand them off while startup is still in progress. The background rollback thread should remain alive until the recovery phase that can hand off transactions has finished.
We also has another optimization which speeds the process to acquire metadata lock for rolling back a large. It seems the optimization has been implemented in 26.7.
The server calls this only after transaction coordinator recovery has decided that the internal prepared XID must be rolled back.
The storage engine may either roll back immediately or make the transaction rollbackable by its own crash-recovery rollback mechanism.
If the callback is missing or returns an error, the server can report the recovery failure using existing recovery error handling.
In InnoDB, the callback should:
Return XAER_NOTA or an appropriate XA error if the XID is not found.
Roll back DDL transactions synchronously.
For DML transactions, move undo logs from prepared to active, flush the redo for that change, and mark the transaction active in memory.
Block Diagram
flowchart TD
A[Crash recovery starts] --> B[Scan binary log recovery state]
B --> C[Recover prepared XIDs from storage engines]
C --> D{Internal XID found in binlog commit set?}
D -- Yes --> E[Commit prepared transaction]
D -- No --> F{Async recovery rollback supported?}
F -- No --> G[Rollback synchronously in startup thread]
F -- Yes --> H{DDL transaction?}
H -- Yes --> G
H -- No --> I[Update undo state to 'ACTIVE']
I --> J[Flush redo to transition LSN]
J --> K[Mark in-memory trx ACTIVE]
K --> L[Background recovery rollback thread undoes rows]
E --> M[Continue startup]
G --> M
L --> M
Pre-flight Checklist
Primary Contact Name
Libing Song
Primary Contact Email
slb.songlibing@gmail.com
Company / Organization
Alibaba
Role
Software Engineer
Additional Authors / Contributors
Component
Server
Target Release (Optional)
Future Release
Roadmap Section
Performance & Observability
Related Issues / Pull Requests / References (Optional)
PR: mysql/mysql-server#711
No response
Executive Summary
MySQL crash recovery can spend a very long time rolling back a large prepared transaction in the server main startup thread. During that time,
mysqldhas started as a process but the server is still unavailable to users.One common production trigger is a large transaction that fills the disk while writing its binary log. With the default
binlog_error_action=ABORT_SERVER, a fatal binlog write error aborts the server. After restart, binlog crash recovery finds that the transaction was prepared in InnoDB but its XID was not durably recorded as committed in the binary log, so the transaction must be rolled back. If the transaction is very large, synchronous rollback can block startup for hours.This proposal makes rollback of such binlog-recovered prepared DML transactions asynchronous:
The transaction outcome remains unchanged. Transactions that binlog recovery decides to roll back are still rolled back. The optimization moves the expensive rollback work out of the startup-critical path after the recovery decision has been made crash-safe.
User / Developer Stories
Proposed Scope
rollback_by_xidpath as a fallback for engines without the callback.Out of Scope / Future Work
References
https://mariadb.com/resources/blog/rollback-prepared-transactions-asynchronously-during-binlog-crash-recovery/
Functional Requirements
rollback_by_xid.Non-functional Requirements
Impact Areas
Summary of the Approach
Today the recovery path is effectively:
The proposed path is:
For InnoDB DML transactions,
recover_rollback_by_xid()does not perform the full rollback. It performs only the fast, crash-safe handoff:TRX_STATE_ACTIVE.trx_recovery_rollback_threadroll back the transaction asynchronously.For DDL transactions, the callback keeps using the synchronous rollback path because later DDL recovery phases depend on the dictionary and physical file effects being resolved.
A minor optimization: flush redo at then end bo binlog recovery instead of flush redo in each rollback call.
Crash Safety
The crucial crash-safety rule is that binlog recovery must not forget the prepared XID until InnoDB has made the alternative recovery path durable.
If MySQL changes a transaction from prepared to active only in memory and then crashes again, the next restart may find:
That can strand a prepared transaction. To avoid this, the prepared-to-active transition must be written to redo and flushed before binlog recovery completes. After that point, a repeated crash is safe because InnoDB will recover the transaction as active and roll it back through the existing background recovery rollback logic.
The recovery rollback thread also must not exit too early. Binlog recovery can discover prepared transactions and hand them off while startup is still in progress. The background rollback thread should remain alive until the recovery phase that can hand off transactions has finished.
We also has another optimization which speeds the process to acquire metadata lock for rolling back a large. It seems the optimization has been implemented in 26.7.
User Interface
No change
Configuration / Knobs
No change
Observability
No response
User Procedure
No response
Security Considerations
No response
Compatibility and Behavior Changes
No response
Block Diagram
Handler Interface Specification
Add an optional storage-engine callback:
Semantics:
In InnoDB, the callback should:
XAER_NOTAor an appropriate XA error if the XID is not found.Block Diagram
flowchart TD A[Crash recovery starts] --> B[Scan binary log recovery state] B --> C[Recover prepared XIDs from storage engines] C --> D{Internal XID found in binlog commit set?} D -- Yes --> E[Commit prepared transaction] D -- No --> F{Async recovery rollback supported?} F -- No --> G[Rollback synchronously in startup thread] F -- Yes --> H{DDL transaction?} H -- Yes --> G H -- No --> I[Update undo state to 'ACTIVE'] I --> J[Flush redo to transition LSN] J --> K[Mark in-memory trx ACTIVE] K --> L[Background recovery rollback thread undoes rows] E --> M[Continue startup] G --> M L --> MInterface Specification
No response
Proposed Implementation Plan
No response
QA Notes
No response