Is it safe to insert the newest row with SELECT INTO?

Question

Trying to keep rows consistent then referencing previous rows.

CREATE TABLE transactions (
    id         int     PRIMARY KEY
       GENERATED BY DEFAULT AS IDENTITY,

    account_id bigint  REFERENCES account,
    change     integer NOT NULL,
    balance    integer NOT NULL,
)

Query from the client is basically

SELECT balance
FROM transactions
WHERE account_id = X
ORDER BY id DESC
LIMIT 1;

INSERT INTO transactions (account_id, change, balance)
VALUES (X, 100, $balance + 100);

This code will produce incorrect results on concurrent inserts. So, the question is how to make balance calculation always correct?

Will INSERT INTO ... SELECT(...) be enough?

Answer 1

It's not safe with this schema at all.

INSERT INTO ... SELECT(...)

Will execute a query in a single transaction but that transaction does not lock the table you're SELECTing from against future INSERTs. The problem here is that SELECT itself has ORDER BY id DESC LIMIT 1; There is no way under this schema to do that without table-locking. The database doesn't understand that insertion-dynamics behind id (which no database does). Because of this at best you can implicitly lock the single row you get with the SELECT, but there is no guarantee that that's the newest row by the time you get to INSERT.

So what are the solutions here,

1. Explicit table-level locking (don't do this).
2. Locking a shared resource for example SELECT FROM accounts WHERE account_id = x FOR UPDATE the FOR UPDATE acquires a row-level exclusive lock on the row. If this statement is in the INSERT INTO .. SELECT then future SELECT FOR UPDATEs on that row will block and wait. Alternatively it can be anywhere in the same transaction before the read from the transaction table. This is what I would do.
3. Predicate locking may also work in SERIALIZABLE mode, but only because there is an id = x on the SELECT. But, to be honest I'm not sure and I don't use that mode because I prefer less voodoo and by extension an aggregate on the table like sum(txns) will certainly not trigger the predicate lock, and then it just gets super confusing.

As a side note, when you do

INSERT INTO transactions (account_id, change, balance)
VALUES (X, 100, $balance + 100);

That is not safe. But if the balance was stored on the accounts table (which we have no reason to believe exists),

UPDATE accounts
SET balance = balance + 100
WHERE account_id = $1;

Would be safe.

Answer 2

You could kind of make it work if you included a change_id in the table, had a unique index on (account_id, change_id), and always incremented change_id by 1 from the previous value for that account_id. Then on conflicting inserts to the same account_id, one will fail with a unique constraint violation and need to re-try.

You could do it in one insert into ... select statement. Or you can do it in two separate statements as long as they are in one transaction, and the first statement selects both balance and change_id simultaneously.

I think it is horrible design. But it can work. Really you have two entities, a transaction and an account, and so should have two tables rather than trying to cram them into one.

Answer 3

There are two ways you can go about doing this.

• Get an exclusive lock on the row you are reading from when you do your first select and release it only after your new record has been inserted and committed. This will prevent other processes from reading the same record and updating based on that and giving an incorrect final balance. This method means you can update the table as needed but only one account at a time.
• Get a lock on the table when doing the process. This will have the same overall results but it will also mean that only one process can update the table at once.

The best method for this would be to calculate the balance dynamically as needed as that will ensure it is always up to date and you won't have any race conditions where multiple processes are trying to update the balance at the same time and calculating an incorrect balance because of this.

Note it would also be possible to create a process that calculates and updates calculated balances at scheduled intervals and it can be set up to only do it for accounts with changes in the time frame.