Test against over spending

[pierrekttipay]

As a proof of concept. I would like to write some client code to test against double spending. Let me explain.

I would like to have this sequence of 3 events happen at a massive scale with the last 2 competing with each other:

1. OpenAccountX balance 100
2. Withdraw 50 from AccountX
3. Withdraw 60 from AccountX
   each time creating new Account (incrementing X) and repeating this over and over again.

My worry is if event-2 lookup the balance, see 100 while event-3 happen to do the same thing at the same time and see 100 as well. I want to test that one of the transaction will actually fail and will not end up with a negative balance.

In order to prevent that, the client will have to check if the balance will be > 0 after executing the withdraw and reject the transaction if otherwise.
Because the event-2 and event-3 compete at the same time. Some account will have a balance of 50, some will have a balance of 40 (because event-3 was applied before event-2 in that occurance)

Can someone please help me model this simple problem in either: Go, Python or Node please?

[ostafen]

Hey, @pierrekttipay, thanks for asking and opening the discussion.
Would you like to model the problem using the SQL or the key-value layer?

[pierrekttipay]

Hey @ostafen
Thank you for your quick response.
I would prefer SQL for this example.
Thanks for helping

[ostafen]

The following snippets creates an accounts table with two simple fields: id and balance. Then one account is inserted, with an initial balance of 65. Note the CHECK balance >= 0 part, which makes sure no update can result in a negative balance.

The snippet then spawns 10 concurrent goroutines, each trying to decrement the balance by 10 (they try as soon as a read conflict happens). If a unique constraint violation is detected the goroutine simply stops, as it would otherwise loop forever.

If all works, we expect the balance to be decremented at most 6 times, with a final balance of value of 5.

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"sync"

	"github.com/codenotary/immudb/embedded/sql"
	"github.com/codenotary/immudb/embedded/store"
)

func fatalOnErr(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	st, err := store.Open("./data", store.DefaultOptions().WithMultiIndexing(true))
	fatalOnErr(err)
	defer st.Close()

	e, err := sql.NewEngine(st, sql.DefaultOptions())
	fatalOnErr(err)

	ctx := context.Background()
	_, _, err = e.Exec(ctx, nil, `
		CREATE TABLE IF NOT EXISTS accounts(
			id INTEGER, 
			balance INTEGER, 
			
			CHECK (balance >= 0),

			PRIMARY KEY id
		)
	`, nil)
	fatalOnErr(err)

	_, _, err = e.Exec(ctx, nil, "UPSERT INTO accounts(id, balance) VALUES (1, 65)", nil)
	fatalOnErr(err)

	n := 10
	var wg sync.WaitGroup
	wg.Add(n)

	for i := 0; i < n; i++ {
		go func() {
			defer wg.Done()

			err := store.ErrTxReadConflict
			for err == store.ErrTxReadConflict {
				_, _, err = e.Exec(ctx, nil, "UPDATE accounts SET balance = balance - 10", nil)
			}

			if err != nil && !errors.Is(err, sql.ErrCheckConstraintViolation) {
				fatalOnErr(err)
			}
		}()
	}
	wg.Wait()

	reader, err := e.Query(ctx, nil, "SELECT * FROM accounts", nil)
	fatalOnErr(err)

	row, err := reader.Read(ctx)
	fatalOnErr(err)

	balance, _ := row.ValuesByPosition[1].RawValue().(int64)
	if balance != 5 {
		fatalOnErr(fmt.Errorf("unexpected balance: %d", balance))
	}
	fatalOnErr(reader.Close())
}

@pierrekttipay: let me know if that is helpful and if you need further assistance. Thanks!

[pierrekttipay]

Thanks @ostafen , I will check the response in detail.
The technique you used is fascinating. You used CHECK (balance >= 0)
I couldn't read anything about this CHECK functionality in the immudb documentation.

1. Is there something similar for KV?
2. Is there some other way to protect but at the statement UPDATE level instead of the TABLE CHECK level? My thinking is if some accounts are allowed $100 credit, $200 credits and other can not go into credit at all.
   I guess what I am asking is: is there a way to use TRANSACTION to protect ourselves at the UPDATE level? How would it looks like, please?

I think this would work well with interfacing with real 3rd party payment system. For instance:

• the payment will ask us if the customer has enough balance. We run the UPDATE and it worked so we authorise the payment.
• if for whatever reason the payment fail, we will be notified and re-credit the account.
• if other payments are happening at the same time on the same account the CHECK will make sure we reject the transactions that over spend.