Are upsert-transactions possible with yugabyteDB? Reading a value, and based on the value doing writes. Addressing double spending problem

General
1

I’ve got a question to transactions: This transaction in the example is insufficient, or? Because: we are not reading/checking first, if John has money. Imagine he has 0 savings instead of 1000 savings. Then his checking account will be -200, and that’s not good.
Is there a possibility, to read first, and then make with e.g a if-check, sure that he has enough money, and then writing the stuff?
This is also called an “upsert”. Because it’s still one single transaction. There are 2 ways how upsert-transactions are usually handled: the field is locked and no one can edit it while the transaction is being done, and: the transaction aborts if the field is getting altered/changed while the transaction runs.

image
image823×901 71.5 KB

thanks a lot in advance!!

here is an upsert done in dgraph as an example if I described bad what I mean with upsert.
the example is not the best, but in dgraph you could also query for Johns money balance, and then decide based on his balance (if he has enough money), if you do the transaction or abort it

image
image824×320 21.5 KB

2

Hi Juri,

I don’t think I understand what you mean with upsert here. UPSERT or MERGE is the combination of INSERT or UPDATE depending on the existence of the row. We can do that with INSERT … ON CONFLICT… but this example is only about updates and supposes that the account exists.

If you want to prevent a negative balance, there are two ways.

One is declarative. Just add:

alter table accounts add constraint balance_must_be_postitive check(balance>=0)

and you will get:

ERROR:  new row for relation "accounts" violates check constraint "balance_must_be_postitive"
DETAIL:  Failing row contains (John, checking, -900).

if the balance becomes negative, then the transaction is canceled and all is rolled back.

The second is procedural. You can SELECT before if you are in serializable (or repeatable read) isolation level. Or better, you can use the RETURNING clause:

  UPDATE accounts SET balance = balance - 200
      WHERE account_name='John' AND account_type='savings' returning balance;

then your application reads the new balance and cancels the transaction if positive.

I’m a big fan of the declarative way because, once declared, no need to worry anymore in the code. And no risk that a new use case forgets to check the balance. But some people prefer to avoid business logic in the database and like to code it in the application.

1 Like