Description:
This bug report is conclusion of this Slack discussion between Sven Sandberg and Jakub Łopuszanski
Looks like there is no fundamental reason why we delay (sometimes for several hours!) opening for public until we finish rollback of a transaction requested by binlog recovery.
The startup logic is a bit complicated when binlog is enabled, as InnoDB and Binlog both have their own pieces of knowledge about the state of transactions before the crash, and have to compare the notes, and figure out how to bring the db to a logically consistent state.
To be more specific, InnoDB looks at the state in the Header of the Undo Log Segment associated with a transaction to learn its state.
(Note that if transaction has already committed or rolled back, then its Undo Logs might have already been removed, so there's nothing to find).
According to Sven Sandberg, the logic for deciding what to do with a transaction based on its state in Undo Log is something like this:
```
switch(State Undo Log Segment Header){
case TRX_UNDO_ACTIVE:
return "ROLLBACK";
case TRX_UNDO_PREPARED:
if (binlog contains XA PREPARE) {
// crash between P2 and P3
return "change to TRX_UNDO_PREPARED_IN_TC"
} else if (binlog contains XA COMMIT) {
// unusual case: crash between P1 and P2 when a transaction with the same XID was previously committed
return "ROLLBACK";
} else if (binlog contains XA ROLLBACK) {
// unusual case: crash between P1 and P2 when a transaction with the same XID was previously committed
return "ROLLBACK";
} else {
// crash between P1 and P2
return "ROLLBACK";
}
case TRX_UNDO_PREPARED_IN_TC:
if (binlog contains XA COMMIT) {
// crash between C1 and C2
return "COMMIT";
} else if (binlog contains XA ROLLBACK) {
// crash between R1 and R2 (the "rollback" analogies to C1 and C2)
return "ROLLBACK"
} else if (binlog contains XA PREPARE) {
// crash after P3
return "stay in TRX_UNDO_PREPARED_IN_TC state"
} else {
// crash after P3 + binlog rotation
return "stay in TRX_UNDO_PREPARED_IN_TC state"
}
}
```
(Note, there are a few more states defined in the enum: TRX_UNDO_CACHED, TRX_UNDO_TO_FREE and TRX_UNDO_TO_PURGE all imply the segment is not tied to any transaction, and TRX_UNDO_PREPARED_80028 is deprecated)
By P1, C1 etc., Sven is referring to his description:
"""
As of 8.0.29, the server uses a 2PC procedure between engine and binlog, in order to persist XA PREPARE atomically. This is not to be confused with the external 2PC that follows the XA protocol and is driven by a TC outside the server. Any 2PC has two parts: a commit protocol and a recovery procedure. The commit protocol for the 2PC for XA PREPARE follows the following steps:
P1. write TRX_UNDO_PREPARED
P2. write the transaction up to XA PREPARE to the binlog
P3. write TRX_UNDO_PREPARED_IN_TC (as of 8.0.29)
In comparison, the procedure for XA COMMIT is:
C1. write XA COMMIT to the binlog
C2. commit in InnoDB
* Before 8.0.29, it followed the same protocol, except there was no step P3, and it used TRX_UNDO_PREPARED_80028 instead of TRX_UNDO_PREPARED. In addition, the recovery procedure did not consider XA PREPARE / XA COMMIT statements in the binlog. So if the InnoDB recovery found TRX_UNDO_PREPARED, it would unconditionally keep the transaction prepared. That made it work correctly if crash happened between XA PREPARE and XA COMMIT, but would leave the server in an inconsistent state if crash happened between P1 and P2 or between C1 and C2. Of course that subsequently made replicas diverge etc.
* From 8.0.29, we have step P3, and we also have the necessary recovery procedure for the 2PC to work. The recovery procedure only reads the most recent binary log. Older logs may be purged so we have to design the procedure to work with only information from the most recent log. (In particular, we block binlog rotation between P1 and P3 and between C1 and C2.) Since recovery can only act on information in the most recent binlog, step P3 is necessary in order to distinguish the situation "crash after P1" from "crash after P3 + binlog rotation".
"""
(Note that this is about a scenario in which client sends explicitly "XA PREPARE" command, and that can happen long before it will send "XA COMMIT", so long, that binlog files can be rotated meanwhile, so the information about the successful prepare must be stored in InnoDB (as TRX_UNDO_PREPARED_IN_TC) for durability)
The problem with current approach is, that some of these "ROLLBACK"s are performed synchronously, preventing InnoDB from opening for public.
This may take hours, and prompted WL#15822 "Innodb: Log progress information while rolling back large transactions" as a temporary workaround.
One of scenarios where such an "synchronous rollback" is currently needed is when user attempted `XA PREPARE` followed by `XA ROLLBACK` just before the crash, and after restart we attempt recovery.
Here, if we permitted asynchronous rollback, we might face following issue described by Sven:
"""
In the case "TRX_UNDO_PREPARED_IN_TC" + "binlog contains XA ROLLBACK", the record that the transaction should rollback is only in the most recent binlog. If we rotate it, we lose the record. So then we can have a scenario like:
1. Server issues XA ROLLBACK and crashes between R1 and R2, i.e., has written XA ROLLBACK to the binlog but not yet rolled back in the engine.
2. Server starts again after the crash and tries to recover. It finds XA ROLLBACK in the binlog and starts an asynchronous process to rollback the transaction.
3. Server completes recovery. Then it also rotates the binary log.
4. Server crashes before the asynchronous process to rollback the transaction completes.
5. Server starts again after the second crash and tries to recover. This time it does not find XA ROLLBACK in the binlog, because it is in the second newest binlog only. It leaves the transaction in prepared state --> binlog (and replicas) are out of sync with engine.
"""
This hypothetical problem is avoided in trunk, by asking InnoDB to rollback synchronously, before continuing, so that the binlog file containing the XA ROLLBACK will not be rotated.
However, (to Jakub Łopuszański and Sven Sandberg) it looks like the only part that truly has to be guaranteed to finish (and be persisted to drive) is the change of the state in Undo Log Segment Header from TRX_UNDO_PREPARED_IN_TC to TRX_UNDO_ACTIVE which should be the first step of rollback logic.
Take a look once again at the `switch` pseudocode above, to verify, that this means the transaction will be guaranteed to be rolled back even if we crash and/or rotate the binlog file.
Also, it looks like all the other places in this `switch` which `return "ROLLBACK";` could as well do `return "lazily ROLLBACK at some later moment";` because the decision will stay the same no matter if we crash or rotate the binlog file - to illustrate the idea, take a look at:
```
case TRX_UNDO_PREPARED:
if (binlog contains XA PREPARE) {
// crash between P2 and P3
return "change to TRX_UNDO_PREPARED_IN_TC"
} else if (binlog contains XA COMMIT) {
// unusual case: crash between P1 and P2 when a transaction with the same XID was previously committed
return "ROLLBACK";
} else if (binlog contains XA ROLLBACK) {
// unusual case: crash between P1 and P2 when a transaction with the same XID was previously committed
return "ROLLBACK";
} else {
// crash between P1 and P2
return "ROLLBACK";
}
```
and suppose we are in the "(binlog contains XA COMMIT)" branch, and instead of rolling back immediately, we decide to do it 1h after opening for public.
What could go wrong? Well, we could perhaps crash. But then, during future recovery, we will still see the same TRX_UNDO_PREPARED in the Undo Log Segment Header.
Perhaps the binlog file will be rotated, but this only means that we will get into the final `else {` branch, for which the decission is the same: to rollback.
How to repeat:
IIUC you need binlog enabled (which is the default) and do something like this:
```
CREATE DATABASE test;
use test;
CREATE TABLE t (id INT PRIMARY KEY);
XA START 'a';
# do some write
INSERT INTO t (id) VALUES (1);
XA END 'a';
XA PREPARE 'a';
# crash during when the following command will call trx_undo_set_state_at_prepare() from trx_rollback_low()
XA ROLLBACK 'a';
```
Then, after restart you'll see rollback being called synchronously from:
```
trx_rollback_low(trx_t * trx) Line 189
trx_rollback_for_mysql(trx_t * trx) Line 276
innobase_rollback_trx(trx_t * trx) Line 5977
innobase_rollback_by_xid(handlerton * hton, xid_t * xid) Line 20095
`anonymous namespace'::recover_one_external_trx(const xarecover_st & info, handlerton & ht, const st_xarecover_txn & xa_trx, std::pair<std::tuple<unsigned __int64,unsigned __int64,unsigned __int64>,std::tuple<unsigned __int64,unsigned __int64,unsigned __int64>> & stats) Line 314
xa::recovery::recover_one_ht(THD * __formal, st_plugin_int * * plugin, void * arg) Line 213
plugin_foreach_with_mask(THD * thd, bool(*)(THD *, st_plugin_int * *, void *) * funcs, int type, unsigned int state_mask, void * arg) Line 2768
plugin_foreach_with_mask(THD * thd, bool(*)(THD *, st_plugin_int * *, void *) func, int type, unsigned int state_mask, void * arg) Line 2782
ha_recover(std::unordered_set<unsigned __int64,std::hash<unsigned __int64>,std::equal_to<unsigned __int64>,Mem_root_allocator<unsigned __int64>> * commit_list, Xa_state_list * xa_list) Line 328
binlog::Binlog_recovery::recover() Line 111
```
Suggested fix:
There's `trx_undo_set_state_at_prepare(...,bool rollback,..)` function, which given rollback=true changes the state of the transaction in the undo log segment header to TRX_UNDO_ACTIVE.
Then it is a matter of moving this transaction to the same "list" of transactions which will be processed by background trx_recovery_rollback_thread().
The "list" is implicit - this thread uses `trx_rollback_or_clean_recovered` which simply scans the `trx_sys->rw_trx_list` which lists all read-write transactions in the system - not just those recovered, but also those started by already connected clients. However it uses `trx_rollback_or_clean_resurrected(trx)` which checks `trx->is_recovered` to identify those which were recovered, and rolls them back, if they are in TRX_STATE_ACTIVE.
The main difficulty here will be to ensure that information about MDL locks (`to_rollback_trx_tables`) is properly updated before this thread is started.
There might also be some edge cases related to "trx->is_ddl" or "is_xa".
So, I don't claim this will be trivial.
But looks like it would be well worth it.
Description: This bug report is conclusion of this Slack discussion between Sven Sandberg and Jakub Łopuszanski Looks like there is no fundamental reason why we delay (sometimes for several hours!) opening for public until we finish rollback of a transaction requested by binlog recovery. The startup logic is a bit complicated when binlog is enabled, as InnoDB and Binlog both have their own pieces of knowledge about the state of transactions before the crash, and have to compare the notes, and figure out how to bring the db to a logically consistent state. To be more specific, InnoDB looks at the state in the Header of the Undo Log Segment associated with a transaction to learn its state. (Note that if transaction has already committed or rolled back, then its Undo Logs might have already been removed, so there's nothing to find). According to Sven Sandberg, the logic for deciding what to do with a transaction based on its state in Undo Log is something like this: ``` switch(State Undo Log Segment Header){ case TRX_UNDO_ACTIVE: return "ROLLBACK"; case TRX_UNDO_PREPARED: if (binlog contains XA PREPARE) { // crash between P2 and P3 return "change to TRX_UNDO_PREPARED_IN_TC" } else if (binlog contains XA COMMIT) { // unusual case: crash between P1 and P2 when a transaction with the same XID was previously committed return "ROLLBACK"; } else if (binlog contains XA ROLLBACK) { // unusual case: crash between P1 and P2 when a transaction with the same XID was previously committed return "ROLLBACK"; } else { // crash between P1 and P2 return "ROLLBACK"; } case TRX_UNDO_PREPARED_IN_TC: if (binlog contains XA COMMIT) { // crash between C1 and C2 return "COMMIT"; } else if (binlog contains XA ROLLBACK) { // crash between R1 and R2 (the "rollback" analogies to C1 and C2) return "ROLLBACK" } else if (binlog contains XA PREPARE) { // crash after P3 return "stay in TRX_UNDO_PREPARED_IN_TC state" } else { // crash after P3 + binlog rotation return "stay in TRX_UNDO_PREPARED_IN_TC state" } } ``` (Note, there are a few more states defined in the enum: TRX_UNDO_CACHED, TRX_UNDO_TO_FREE and TRX_UNDO_TO_PURGE all imply the segment is not tied to any transaction, and TRX_UNDO_PREPARED_80028 is deprecated) By P1, C1 etc., Sven is referring to his description: """ As of 8.0.29, the server uses a 2PC procedure between engine and binlog, in order to persist XA PREPARE atomically. This is not to be confused with the external 2PC that follows the XA protocol and is driven by a TC outside the server. Any 2PC has two parts: a commit protocol and a recovery procedure. The commit protocol for the 2PC for XA PREPARE follows the following steps: P1. write TRX_UNDO_PREPARED P2. write the transaction up to XA PREPARE to the binlog P3. write TRX_UNDO_PREPARED_IN_TC (as of 8.0.29) In comparison, the procedure for XA COMMIT is: C1. write XA COMMIT to the binlog C2. commit in InnoDB * Before 8.0.29, it followed the same protocol, except there was no step P3, and it used TRX_UNDO_PREPARED_80028 instead of TRX_UNDO_PREPARED. In addition, the recovery procedure did not consider XA PREPARE / XA COMMIT statements in the binlog. So if the InnoDB recovery found TRX_UNDO_PREPARED, it would unconditionally keep the transaction prepared. That made it work correctly if crash happened between XA PREPARE and XA COMMIT, but would leave the server in an inconsistent state if crash happened between P1 and P2 or between C1 and C2. Of course that subsequently made replicas diverge etc. * From 8.0.29, we have step P3, and we also have the necessary recovery procedure for the 2PC to work. The recovery procedure only reads the most recent binary log. Older logs may be purged so we have to design the procedure to work with only information from the most recent log. (In particular, we block binlog rotation between P1 and P3 and between C1 and C2.) Since recovery can only act on information in the most recent binlog, step P3 is necessary in order to distinguish the situation "crash after P1" from "crash after P3 + binlog rotation". """ (Note that this is about a scenario in which client sends explicitly "XA PREPARE" command, and that can happen long before it will send "XA COMMIT", so long, that binlog files can be rotated meanwhile, so the information about the successful prepare must be stored in InnoDB (as TRX_UNDO_PREPARED_IN_TC) for durability) The problem with current approach is, that some of these "ROLLBACK"s are performed synchronously, preventing InnoDB from opening for public. This may take hours, and prompted WL#15822 "Innodb: Log progress information while rolling back large transactions" as a temporary workaround. One of scenarios where such an "synchronous rollback" is currently needed is when user attempted `XA PREPARE` followed by `XA ROLLBACK` just before the crash, and after restart we attempt recovery. Here, if we permitted asynchronous rollback, we might face following issue described by Sven: """ In the case "TRX_UNDO_PREPARED_IN_TC" + "binlog contains XA ROLLBACK", the record that the transaction should rollback is only in the most recent binlog. If we rotate it, we lose the record. So then we can have a scenario like: 1. Server issues XA ROLLBACK and crashes between R1 and R2, i.e., has written XA ROLLBACK to the binlog but not yet rolled back in the engine. 2. Server starts again after the crash and tries to recover. It finds XA ROLLBACK in the binlog and starts an asynchronous process to rollback the transaction. 3. Server completes recovery. Then it also rotates the binary log. 4. Server crashes before the asynchronous process to rollback the transaction completes. 5. Server starts again after the second crash and tries to recover. This time it does not find XA ROLLBACK in the binlog, because it is in the second newest binlog only. It leaves the transaction in prepared state --> binlog (and replicas) are out of sync with engine. """ This hypothetical problem is avoided in trunk, by asking InnoDB to rollback synchronously, before continuing, so that the binlog file containing the XA ROLLBACK will not be rotated. However, (to Jakub Łopuszański and Sven Sandberg) it looks like the only part that truly has to be guaranteed to finish (and be persisted to drive) is the change of the state in Undo Log Segment Header from TRX_UNDO_PREPARED_IN_TC to TRX_UNDO_ACTIVE which should be the first step of rollback logic. Take a look once again at the `switch` pseudocode above, to verify, that this means the transaction will be guaranteed to be rolled back even if we crash and/or rotate the binlog file. Also, it looks like all the other places in this `switch` which `return "ROLLBACK";` could as well do `return "lazily ROLLBACK at some later moment";` because the decision will stay the same no matter if we crash or rotate the binlog file - to illustrate the idea, take a look at: ``` case TRX_UNDO_PREPARED: if (binlog contains XA PREPARE) { // crash between P2 and P3 return "change to TRX_UNDO_PREPARED_IN_TC" } else if (binlog contains XA COMMIT) { // unusual case: crash between P1 and P2 when a transaction with the same XID was previously committed return "ROLLBACK"; } else if (binlog contains XA ROLLBACK) { // unusual case: crash between P1 and P2 when a transaction with the same XID was previously committed return "ROLLBACK"; } else { // crash between P1 and P2 return "ROLLBACK"; } ``` and suppose we are in the "(binlog contains XA COMMIT)" branch, and instead of rolling back immediately, we decide to do it 1h after opening for public. What could go wrong? Well, we could perhaps crash. But then, during future recovery, we will still see the same TRX_UNDO_PREPARED in the Undo Log Segment Header. Perhaps the binlog file will be rotated, but this only means that we will get into the final `else {` branch, for which the decission is the same: to rollback. How to repeat: IIUC you need binlog enabled (which is the default) and do something like this: ``` CREATE DATABASE test; use test; CREATE TABLE t (id INT PRIMARY KEY); XA START 'a'; # do some write INSERT INTO t (id) VALUES (1); XA END 'a'; XA PREPARE 'a'; # crash during when the following command will call trx_undo_set_state_at_prepare() from trx_rollback_low() XA ROLLBACK 'a'; ``` Then, after restart you'll see rollback being called synchronously from: ``` trx_rollback_low(trx_t * trx) Line 189 trx_rollback_for_mysql(trx_t * trx) Line 276 innobase_rollback_trx(trx_t * trx) Line 5977 innobase_rollback_by_xid(handlerton * hton, xid_t * xid) Line 20095 `anonymous namespace'::recover_one_external_trx(const xarecover_st & info, handlerton & ht, const st_xarecover_txn & xa_trx, std::pair<std::tuple<unsigned __int64,unsigned __int64,unsigned __int64>,std::tuple<unsigned __int64,unsigned __int64,unsigned __int64>> & stats) Line 314 xa::recovery::recover_one_ht(THD * __formal, st_plugin_int * * plugin, void * arg) Line 213 plugin_foreach_with_mask(THD * thd, bool(*)(THD *, st_plugin_int * *, void *) * funcs, int type, unsigned int state_mask, void * arg) Line 2768 plugin_foreach_with_mask(THD * thd, bool(*)(THD *, st_plugin_int * *, void *) func, int type, unsigned int state_mask, void * arg) Line 2782 ha_recover(std::unordered_set<unsigned __int64,std::hash<unsigned __int64>,std::equal_to<unsigned __int64>,Mem_root_allocator<unsigned __int64>> * commit_list, Xa_state_list * xa_list) Line 328 binlog::Binlog_recovery::recover() Line 111 ``` Suggested fix: There's `trx_undo_set_state_at_prepare(...,bool rollback,..)` function, which given rollback=true changes the state of the transaction in the undo log segment header to TRX_UNDO_ACTIVE. Then it is a matter of moving this transaction to the same "list" of transactions which will be processed by background trx_recovery_rollback_thread(). The "list" is implicit - this thread uses `trx_rollback_or_clean_recovered` which simply scans the `trx_sys->rw_trx_list` which lists all read-write transactions in the system - not just those recovered, but also those started by already connected clients. However it uses `trx_rollback_or_clean_resurrected(trx)` which checks `trx->is_recovered` to identify those which were recovered, and rolls them back, if they are in TRX_STATE_ACTIVE. The main difficulty here will be to ensure that information about MDL locks (`to_rollback_trx_tables`) is properly updated before this thread is started. There might also be some edge cases related to "trx->is_ddl" or "is_xa". So, I don't claim this will be trivial. But looks like it would be well worth it.