Re: Nested transaction - I am a bank ??

On Tuesday 13 January 2004 17:47, 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.

In database terms, the two operations together are one transaction. You do 
something like:

BEGIN;
INSERT INTO detail (acct_num,trans_type,trans_time,notes) VALUES 
(1,'CASHOUT',now(),'blah');
UPDATE account_summary SET amount=amount-10 WHERE acct_num = 1;
COMMIT;

Now, if one (or both) of those were written as a function, that function's 
effects would still be bound by the transaction. All operations(*) take place 
within a transaction in PG, either explicitly as above or implicitly with one 
per statement.

What you can't do is have a function that does something like:

LOOP 1..10
  BEGIN;
  -- do something ten times, each time in its own transaction
  COMMIT;
END LOOP

(*) except for a couple of bits like vacuum, truncate(?) and similar.
-- 
  Richard Huxton
  Archonet Ltd