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