List:General Discussion« Previous MessageNext Message »
From:Luis Motta Campos Date:January 21 2013 10:05am
Subject:Re: store transaction rollback information
View as plain text  
On 26 Jul 2012, at 21:43, James Devine wrote:

> I have a large series of mysql changes(inserts/deletes/updates) taking
> place in a transaction.  After committing there may be some times where I
> need to roll those changes back later on.  Is there an easy way of
> determining what was changed in a transaction in a way I can store it and
> rollback later?


James, 

The way you describe it sounds like you have a modeling issue with your system. 

Committed transactions are not supposed to be rolled back.

Your System Architect has to arrange things in such a way that all the information
required to decide if a change to the database can be made permanent is available to the
application *before* COMMIT-time. Until then, you're supposed to hold your transaction
(and all locks resulting from it) open and uncommitted.

In other words: once a transaction is committed, the changes are permanent. Rolling it
back may still be possible, but it will be complicated and extremely expensive,
computationally speaking. I strongly recommend you to review your design choices.

I hope this helps.
Kind regards,
--
Luis Motta Campos
is a DBA, Foodie, and Photographer

Thread
store transaction rollback informationJames Devine26 Jul
  • Re: store transaction rollback informationLuis Motta Campos21 Jan