Re: Nested transaction - I am a bank ??

Thapliyal, Deepak wrote:
> 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