[SQL] Is a thread-safe ledger insert possible in Postgres?

I am looking to create a ledger database table where each row insert is immutable and reflects the latest current table for an account. For example, if I had a table like this:

CREATE TABLE account_ledger
(
id SERIAL PRIMARY KEY,
account_id BIGINT not null,
transaction_id VARCHAR(255),
transaction_amount DECIMAL(10, 2) not NULL,
current_balance DECIMAL(10, 2) not null constraint positive_balance CHECK (current_balance >= 0)
);


I want to write a query that

1. selects the last (most current) row for the account id
2. Uses a default balance of "0" if no row exists
3. Inserts a new row reflecting the up to date balance being the transaction_amount + current_balance of the latest row

For example, if the state of the db was:

id | acco_id | txn_id | txn_amount | cur_balance
1 | 123 | A1 | 100 |100


an INSERT for a 10 credit would look like:

id | acco_id | txn_id | txn_amount | cur_balance
1 | 123 | A1 | 100 |100
2 | 123 | A2 | 10 |110


I was looking to write a query that would provide perfect consistent behavior to maintain data integrity. However, whatever query I attempt to run in a multi-threaded environment exhibits the kind of behavior that does not provide accurate results after several concurrent inserts.

Here is the latest query I am using:

WITH latest_data as (
select
wl.current_balance as latest_balance,
wl.transaction_id as latest_transaction_id
from account_ledger al
where al.account_id = :accountId order by al.id desc limit 1
-- I've tried with and with out the row lock here
for update
)
-- insert using values obtained from last row in latest_data
insert into account_ledger (account_id, transaction_id, transaction_amount, current_balance)
SELECT :accountId, :transactionId, :transactionAmount, coalesce (latest_data.latest_balance + :transactionAmount, :transactionAmount)
-- left join on potentially empty table from WITH clause above
from (select 1 as empty) e
left join latest_data on true
where :transactionAmount >= 0 or latest_data.latest_balance > ABStransactionAmount)
and (
-- insert only when the latest_transaction_id equals the txn_id we obtained from above
latest_data.latest_transaction_id is null or
latest_data.latest_transaction_id = (select transaction_id from account_ledger cl where cl.account_id = :accountId order by cl.id desc limit 1)
)


If I do a series of 15 concurrent inserts of $10, I would expect a current_balance of $150. However, I instead get a value like $140 or even $130. I tried a variety of Isolation levels- Read uncommitted, read committed, and repeatable read and none have performed properly. Here is some example output with 15 concurrent inserts using Read_Uncommitted:

id | accoId|txnId | parentTxnId. |txnAmount|balance
790 263611 263611-3 new 10.00 10.00
792 263611 263611-5 263611-3 10.00 20.00
796 263611 263611-9 263611-5 10.00 30.00
798 263611 263611-7 263611-5 10.00 30.00
799 263611 263611-1 263611-7 10.00 40.00
800 263611 263611-11 263611-1 10.00 50.00
808 263611 263611-15 263611-11 10.00 60.00
815 263611 263611-13 263611-15 10.00 70.00
825 263611 263611-25 263611-17 10.00 110.00
826 263611 263611-29 263611-25 10.00 120.00
828 263611 263611-23 263611-29 10.00 130.00
819 263611 263611-19 263611-13 10.00 80.00
822 263611 263611-27 263611-19 10.00 90.00
824 263611 263611-17 263611-27 10.00 100.00
831 263611 263611-21 263611-23 10.00 140.00


Is there a way to get consistent results with the right isolation level and/or query? What is the best way to reason about inconsistent behavior with the current query?

Continue reading...