MySQL Bugs: #101115: Prepared transaction rollback would block server start

Bug #101115	Prepared transaction rollback would block server start
Submitted:	10 Oct 2020 8:51	Modified:	15 Oct 2020 13:01
Reporter:	zhang xiaojian	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S5 (Performance)
Version:	all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	binlog; xa transaction; rollback;

Description:
If a large transaction prepared but not persist to binlog files when server crashed abnormally, the transaction would rollback after server restart.

The problem is prepared transaction rollback would block server start and users cann't connect.. If there is a large transaction, server may need a long time to restart.

In my test, a simple table with one column, 100000000 rows need 13 minutes to rollback.

```
2020-10-10T08:13:14.053592Z 0 [Note] [MY-013034] [InnoDB] Transaction contains changes to 100000000 rows
2020-10-10T08:13:14.053600Z 0 [Note] [MY-013035] [InnoDB] 1 transactions in prepared state after recovery
2020-10-10T08:13:14.053608Z 0 [Note] [MY-010224] [Server] Found 1 prepared transaction(s) in InnoDB
2020-10-10T08:13:14.289973Z 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 201010 16:13:14
2020-10-10T08:13:14.290003Z 0 [Note] [MY-030040] [InnoDB] buf_dump thread created, id 140449535428352
^@^@2020-10-10T08:15:28.675170Z 0 [Note] [MY-011953] [InnoDB] Page cleaner took 8722ms to flush 51 and evict 0 pages
^@^@^@^@^@^@^@^@2020-10-10T08:24:07.323442Z 0 [Note] [MY-011953] [InnoDB] Page cleaner took 8621ms to flush 505 and evict 0 pages
^@^@^@2020-10-10T08:26:15.531953Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2020-10-10T08:26:15.545966Z 0 [Note] [MY-012487] [InnoDB] DDL log recovery : begin
2020-10-10T08:26:15.546001Z 0 [Note] [MY-012488] [InnoDB] DDL log recovery : end
2020-10-10T08:26:15.546022Z 0 [Note] [MY-011825] [InnoDB] innobase_post_recover: 0 0 0
``` 

If the transaction not prepared, just an active transaction, can be rollback at backgroud, so can we rollback the prepared transaction at backgroud ?

How to repeat:
create database test; 
use test; 
create table t (a int);

insert into t(a) value (1);
insert into t select a.* from t a, t b limit 1000000;
insert into t select a.* from t a, t b limit 1000000;

set @@global.debug = "+d, crash_after_flush_engine_log";

insert into t select a.* from t a, t b limit 100000000;

After crashed, server would spent a long time to be connected again..

Suggested fix:
NO

Hi Mr. xiaojian.

Thank you for your bug report.

However, this is not a bug.

You were not clear enough, but I suppose that when you write about prepared transactions, you are actually referring to the usage of XA PREPARE.

Well, you should read our Reference Manual. XA transactions that are not prepared, do not survive server crash, while those that are prepared do survive it. Hence, when you do not have prepared XA transaction, then you can't roll it back and hence, it is MUCH faster then when you have to roll it back.

And no, in that case,  it can not be done in the background.

Not a bug.

Hi, I agree with you that not a bug, but I think it's a performance issue.

The prepared transaction is not from the 'XA PREPARE' statement, it's internal XA transaction. If we enable binlog, the rw transaction would prepared in storage engine first and write to binlog file. 

The problem is server may crash after storage engine prepared and binlog is not fully sync to disk. The big transaction need to rollback after server restart, and server would block to wait the rollback finish. I think may be we can rollback prepared transaction at backgroud.

Hi Mr. xiaojian,

First of all, there is no such thing as internal XA transactions.

What we have within our InnoDB Storage Engine are normal SQL transactions, which also have two-phase commit.

And for those transactions, the same principle is valid, which is then after first phase of the commit is finished, then if crash or assert occur, repair is mandatory and only in the foreground. This is because of the fact that so many tablespaces have to be repaired and thus can not be used until restoration is finished.

Not a bug.

Hi, I just wonder if prepared transaction must rollback at foreground to wait tablespaces to be repaired, why active transactions can rollback at backgroud ?

Hi,

We have explained it to you already, but here we go again.

When tablespaces are corrupted with prepared, but not fully committed transaction(s), then all the tablespaces must be repaired first. Hence, during repair you can not run any other commands ....

Hi, thanks for the explanation.

IMHO, The differences between prepared transaction and active transactions are their trx state stored in undo header. If prepared transactions may corrupt the tablespaces, the active transactions won't do it ?

Is there an example to prove what you explained ?

We consider that we have provided you with sufficient info.

For more info, you can read our Reference Manual.

Not a bug.