MySQL Bugs: #75120: Undo jobs execute in the same transaction context of the failed job

Bug #75120	Undo jobs execute in the same transaction context of the failed job
Submitted:	5 Dec 2014 17:45
Reporter:	Alfranio Tavares Correia Junior	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Fabric	Severity:	S3 (Non-critical)
Version:	1.4.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL Fabric allows users to schedule procedures which is a chain of steps called jobs. If a job fails, a compensation operation is called, provided there is one, so that changes made by the job(s) may be reverted.

The idea might be used, for example, to remove files created by a job thus avoiding that they are left around after a failure consuming disk space.

begin trx
job       (creates file)
undo job  (deletes file)
rollback trx

The undo job is executed, however, in the transaction context of its associated job which will be eventually rolled back. This means that users cannot update the state store to revert the changes made by previous jobs in the chain. For example,

begin trx
job 1      (insert into t)
commit trx
begin trx
job 2      (has failed for any reason)
undo job 2 (delete from t)
rollback trx

Note that the effects of the delete will be rolled back.

How to repeat:
Check the code.

Suggested fix:
Rollback the job before executing the undo job which should have its own transactional context. For example,

begin trx
job 1      (inserts into t)
commit trx
begin trx
job 2      (has failed for any reason)
rollback trx
begin trx
undo job 2 (deletes from t)
commit trx

Posted by developer:
 
Perhaps it would be good, if one could chose, if to run undo actions in the same transaction or another.

A possible way would be to allow undo actions to create new jobs:

begin trx
job 1      (insert into t)
job 1      (create job 2)
commit trx

begin trx
job 2      (SET read_only = 1 on managed server)
job 2      (has failed for any reason)
undo job 2 (SET read_only = 0 on managed server)
undo job 2 (create job 3)
rollback trx

begin trx
job 3      (delete from t)
commit trx

Status updated to 'Won't fix' (Fabric is now covered under Oracle Lifetime Sustaining Support)