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@xxxxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Attachment:
pgpxwYmAHJP5d.pgp
Description: PGP signature
