Reliable and fast money transaction design

[cluster <skrald@xxxxxxxxxx>]

I need a way to perform a series of money transactions (row inserts) 
together with some row updates in such a way that integrity is ensured 
and performance is high.

I have two tables:
  ACCOUNTS (
     account_id int,
     balance int
  );

  TRANSACTIONS (
     transaction_id int,
     source_account_id int,
     destination_account_id int,
     amount int
  );

When a money transaction from account_id = 111 to account_id = 222 with 
the amount of 123 is performed, the following things must happen as an 
atomic event:
   1) INSERT INTO TRANSACTIONS
        (source_account_id, destination_account_id, amount)
        VALUES (111, 222, 123)
   2) UPDATE ACCOUNTS SET balance = balance-123 WHERE account_id=111
   3) UPDATE ACCOUNTS SET balance = balance+123 WHERE account_id=222

A lot of such money transactions will happen in parallel so I need 
ensure integrity of the rows in ACCOUNTS.
This might be done by creating an *immutable* function that performs the 
three steps but this will block unnecessarily if to completely unrelated 
money transactions are tried to be performed in parallel.

Any suggestions on how to perform step 1-3 while ensuring integrity?


QUESTION 2:

For various reasons I might need to modify the ACCOUNTS table to
    ACCOUNTS (
     account_id int,
     transaction_id int,
     balance int,
     <some other info>
  );

so that the balance for account_id=111 is given by
   SELECT balance FROM ACCOUNTS
   WHERE account_id=111
   ORDER BY transaction_id DESC
   LIMIT 1

How will that effect how I should perform the steps 1-3 above?

Thanks

Thanks

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend