Once a transaction has been committed, we cannot undo its effect by aborting it.
We have the log file that has all information to undo a committed transaction, so why is this not possible? We should be able do the same with a committed transaction as we can with an aborted one.
Best Answer
An aborted transaction in the past has never been "seen" by other transactions, therefore it has had no effect on any data currently in the database now!
A committed transaction (call it X) IS seen by other transactions (say Y and Z... and potentially many others) and their actions (
INSERTs,UPDATEs orDELETEs) MAY have depended on the fact that X was, in fact, committed and further transactions (A, B, C, D....) may have depended on Y and Z.There is (potentially) a huge cascade of transactions that may or may not have had a different outcome depending on preceding transactions - these confounding effects are at least exponential and maybe factorial or even worse.
You would rapidly need more bits on disk than there are atoms on the planet after a relatively short time to keep track of all potential outcomes of this cascade. In a chess game there are
**69,353,270,203,366**(69.3 trillion) possible moves (open to debate - see page) in the first 10 (both players) - so one can only imagine the number of possible scenarios if you have even a small number of people performingCrUDoperations (r in lower case here because you can't rollback a read (SELECT) operation as no data changes)!That's why there isn't a mechanism to roll back committed transactions - of course you could just roll back that one transaction X - IF it still left the database in a LOGICALLY consistent state - i.e. according to the DDL, Foreign Keys consistent,
CHECKconstraints &c.BUT it might not be consistent from the business/application point of view - stock quantities (just one of a myriad variables that I can think of) might be incorrect... the number of other potential errors is also huge! Think about any triggers that mightn't have been fired when they should have been or vice versa.
It's a galactic scale SNAFU just waiting to happen and is the reason such functionality (thankfully) is not implemented by any of the major RDBMS providers - and nor is it likely to be! The mere fact that it's impossible would not, of course, be an impediment to companies claiming to offer such functionality - the words "snake-oil" spring to mind!