mysql - Transactions when writing to two or more different data-storages -

let's suppose have 2 data storages: mysql(innodb) & redis. need write data both storages , in transaction.

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

if error occures on $mysql->commit() have our data in redis , unable rollback it. best practices?

mysql 5.0.3 , supports xa, redis not, can't perform distributed transactions in traditional sense both participators understand prepare, commit , rollback semantics.

in case, since have 1 non-xa compliant participant (redis), redis work last. if redis operation successful, commit mysql. if redis not successful, rollback mysql. in terms of best practice, you're on right track since have little choice otherwise. did not how many operations you're doing against redis, if it's one, you're fine, if you're doing multiple redis operations multi/exec , fails, you'll need rollback mysql and undo redis operations successful since multi/exec not atomic.