next up previous contents index
Next: make Up: Examples Previous: Examples   Contents   Index


Bank Account

Here is the classical Bank Account example as it is presented by ORACLE in [36, Ch.3, p.27]:
"A transaction is a user-defined series of logically related SQL operations. All changes brought by the SQL operations are either undone or made permanent at the same time.

You perform transactions with the COMMIT or ROLLBACK statements. You use these statements to ensure that either all or none of your changes are made to the database.

A simple example of a transaction involves transferring money from one bank account to another. The transaction, which typically requires two UPDATEs, is to debit account A and credit account B."

"..., after you credit account B, you issue the COMMIT command, making the changes permanent. Only then do the changes become visible to other users."

"..., if you cannot credit account B because of a logical problem, you execute the ROLLBACK statement. This undoes the change to account A, thereby restoring the original data."

Now, imagine a database table BankAccounts defined by:


/* Bank accounts are identified by unique Id's */
CREATE TABLE BankAccounts (
  Id <INTERNAL> NOT NULL,
  Balance MONEY NOT NULL
);
CREATE UNIQUE INDEX BankAccounts_IX1 ON BankAccounts(Id);

Then, here is how I would write the transaction (an ORACLE SQL*Plus script using PL/SQL):


DECLARE
  unable_to_deposit  EXCEPTION;
  unable_to_withdraw EXCEPTION;
BEGIN
  -- Transfer $100 from bank account <A> to bank account <B>
  UPDATE BankAccounts
   SET   Balance=Balance-100  -- Withdraw $100
   WHERE Id=<A>
   AND   Balance>=100;        -- , if possible
  IF SQL%ROWCOUNT<>1 THEN
    RAISE unable_to_withdraw;
  END IF;
  UPDATE BankAccounts
   SET   Balance=Balance+100  -- Deposit $100
   WHERE Id=<B>;
  IF SQL%ROWCOUNT<>1 THEN
    RAISE unable_to_deposit;
  END IF;
  COMMIT; -- End transaction making the changes permanent
EXCEPTION
  WHEN unable_to_withdraw OR unable_to_deposit THEN
    ROLLBACK; -- End transaction undoing any changes
END;
/

Note: In the above example BEGIN and END do not mark the beginning and end of the transaction. They mark the beginning and end of the PL/SQL-block. The transaction associated with the SQL*Plus-script above is implicitly begun before execution of the first SQL-statement (UPDATE ...) and is ended explicitly with the COMMIT or the ROLLBACK.

This is how the Bank Account example is presented in [52, p.145]:

"Now look at a modern banking application that updates an online data base in place. The customer calls up the bank using a PC with a modem with the intention of withdrawing money from one account and depositing it in another. The operation is performed in two steps:

1. Withdraw(amount,account1).

2. Deposit(amount,account2).

If the telephone line is broken after the first one but before the second one, the first account will have been debited but the second one will not have been credited. The money vanishes into thin air.

Being able to group these two operations in an atomic transaction would solve the problem. Either both would be completed, or neither would be completed. The key is rolling back to the initial state if the transaction fails to complete."

and [52, p.225-226]:

"The classical example of where transactions make programming much easier is in a banking system. Imagine that a certain bank account contains 100 dollars, and that two processes are each trying to add 50 dollars to it. In an unconstrained system, each process might simultaneously read the file containing the current balance (100), individually compute the new balance (150), and successively overwrite the file with this new value. The final result could either be 150 or 200, depending on the precise timing of the reading and writing. By grouping all the operations into a transaction, interleaving cannot occur and the final result will always be 200."

The two different situations could be sketched:

Situation 1 - Interleaving processes
Process 1 Process 2
READ(amount,account)  
  READ(amount,account)
WRITE((amount+$50),account)  
  WRITE((amount+$50),account)
...  
  ...

Situation 2 - Isolated transactions
Process 1 Process 2
BEGIN_TRANSACTION  
READ(amount,account)  
WRITE((amount+$50),account)  
END_TRANSACTION  
  BEGIN_TRANSACTION
  READ(amount,account)
  WRITE((amount+$50),account)
  END_TRANSACTION


next up previous contents index
Next: make Up: Examples Previous: Examples   Contents   Index

michael@garfield.dk
2000-10-13