Hi,
Assume I have a bank app.. When customer withdraws $10 from his accouint I
have to do following --> update account_summary table [subtract $10 from his account]
--> update account detail_table [with other transaction details]
Requirement: either both transactions should succeed or both transactions should
be rolled back in case of failure.
Both actions you mentioned are not [or are unlikely to be implemented as] two separate transactions, but a single transaction (and thus the subject "nested transaction" has nothing to do with this.
Nested transaction are usually used in complex operations.
Save points can be used to implement nested transaction.
Since we're using a bank as example, consider a bank with 1 million accounts. At the end of the month, it needs to calculate and post interest for each account. The whole operation takes 10 hours. If we use a single transaction for this, then if the machine/database crashes, the whole unfinished transaction will be rolled back. If the db is back up, but then fails again in the middle of this giant transaction, it will be rolled back again. And perhaps again... and again... and thus it will never finishes.
With save points (and nested transactions) we can save in the middle of transaction and later rolls back to the last save point instead of beginning the transaction all over.
-- dave
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
