Concurrent read value if thread process happen

Hibernate ORM
1

Hello, i’m new using hibernate. I have this situation. The flow is sum all amount on single field “amount” then store the value to next current balance on Table InvoiceItems

amount     |     type    |    account_id    | current_balance
   100           topup            A                100
    -1           usage            A                99
    5            topup            B                 5
   -3            usage            B                2
   -3            usage            A                96

then i sum the value of account A with this query function :

> public BigDecimal getBalance(String accountid) {
> 		return (BigDecimal)this.session.createQuery("select SUM(amount) from InvoiceItems WHERE account_id = '" + accountid + "' ").setLockMode(accountid, LockMode.OPTIMISTIC).getSingleResult();
>     }

another class call query function :

BigDecimal balance = ido.getBalance(accountid);

InvoiceItems invoiceItemsUSAGE = new InvoiceItems();

invoiceItemsUSAGE.setAmount(BigDecimal.valueOf(jsonRecv.getLong("amount")));
invoiceItemsUSAGE.setType("USAGE");	
invoiceItemsUSAGE.setCurrent_balance(balance.subtract(BigDecimal.valueOf(jsonRecv.getLong("amount"))));

db.session().save(invoiceItemsUSAGE);

tx.commit();

the movement of current balance is fine, but when thread process like 3 thread almost run same time (just different .121 millisecond) the current_balance movement not consistent, it’s like the process read same value before the transaction commit.

amount     |     type    |    account_id    | current_balance   |    time
   100           topup            A                100            2019-09-18 17:21:40.015714
    -1           usage            A                99             2019-09-18 18:21:41.011114
    5            topup            B                5              2019-09-18 19:23:30.013314
   -3            usage            B                2              2019-09-18 20:34:40.022714
   -3            usage            A                96             2019-09-18 21:21:40.015714
   -1            usage            A                95             2019-09-18 22:21:40.115714
   -1            usage            A                95             2019-09-18 22:21:40.121714
   -1            usage            A                95             2019-09-18 22:21:40.132114
   -1            usage            A                95             2019-09-18 22:21:40.145114
   -1            usage            A                91             2019-09-18 23:55:40.153714
   -1            usage            A                90             2019-09-18 24:41:40.015714

i have try with lock on query

> public BigDecimal getBalance(String accountid) {
> 		return (BigDecimal)this.session.createQuery("select SUM(amount) from InvoiceItems WHERE account_id = '" + accountid + "' ").setLockMode(accountid, LockMode.OPTIMISTIC).getSingleResult();
>     }

any clue ?

2

i have try with set the postgresql database with :

ALTER DATABASE billing SET default_transaction_isolation = 'serializable';
ALTER USER postgres SET default_transaction_isolation = 'serializable'

and set connection.isolation to 8, but when hit with thread got error :

ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on identification as a pivot, during conflict out checking.
Hint: The transaction might succeed if retried.

still not correct result, any clue ?