Transactions when writing to two or more different data-storages

Question

Let's suppose that we have two data storages: MySQL(innodb) & Redis. We need to write some data to both storages and do it in transaction.

try {
  $Mysql->transaction();   //start mysql transaction
  $Mysql->somecommands();  //exec some sql
  $Redis->multi();         //start redis transaction
  $Redis->somecommands();  //exec some redis commands
  $Redis->exec()           //redis commit
  $Mysql->commit();        //mysql commit
} catch (Exception $e) {
  $Mysql->rollback();      //mysql rollback
  $Redis->discard();       //redis rollback
}

If some error occures on $Mysql->commit() we already have our data in redis and unable to rollback it. What are the best practices?

Answer 1

MySQL 5.0.3 and up supports XA, but Redis does not, so you can't perform distributed transactions in the traditional sense where both participators understand PREPARE, COMMIT and ROLLBACK semantics.

In your case, since you only have 1 non-XA compliant participant (redis), just do the redis work last. If the redis operation is successful, commit MySQL. If redis is not successful, rollback MySQL. So in terms of best practice, you're on the right track since you have little choice otherwise. You did not say how many operations you're doing against redis, if it's only one, you're fine, but if you're doing multiple redis operations with MULTI/EXEC and something fails, you'll need to rollback MySQL and undo the redis operations that were successful since MULTI/EXEC is not atomic.