Re: Reliable and fast money transaction design

On Tue, Aug 28, 2007 at 05:48:50PM +0200, cluster wrote:
> 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

Why do you think you need to do anything special for this? As long as
you're doing these 3 steps in a single transaction, everything should be
fine. At most, you might need to set your transaction isolation level to
serializable, but I don't think that's actually needed.

> 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
>

--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)