Transactional behaviour


(Marcos) #1

Hi, I have a project where I need something like a bank system.
I need to replicate the classic example where I have to transfer money from one account to another, like the one on the documentation. Aa transaction is initiated, the account’s balace is queryed, then if it has funds the money transfer is done. This is the code i’m talking about:

func transferFunds(tx *sql.Tx, from int, to int, amount int) error {
    // Read the balance.
    var fromBalance int
    if err := tx.QueryRow(
        "SELECT balance FROM accounts WHERE id = $1", from).Scan(&fromBalance); err != nil {
        return err
    }

    if fromBalance < amount {
        return fmt.Errorf("insufficient funds")
    }

    // Perform the transfer.
    if _, err := tx.Exec(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from); err != nil {
        return err
    }
    if _, err := tx.Exec(
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to); err != nil {
        return err
    }
    return nil
}

I understand that since everything it is done in the scope of the transaction, both updates are goint to be done atomically. My doubt is about the SELECT, ¿is there any posiblity that the account’s balance is modified in the lapse between the SELECT and the UPDATE?

I’d like to know if the same behaviour is expected if I want to do a GROUP BY query. Following the example, instead of querying the the balance in the accounts table, I would be querying the ledger table, where I get the balances with a SUM and GROUP BY of all the historical movements in the account.


(Tim O'Brien) #2

Hey @marcos,

Based on my reading of the Go docs, I think you’re missing a couple things. You need to conduct both the SELECT and UPDATEs within a single transaction in order to avoid conflicts. Otherwise the balance could change between when you check it and when you update it. Each transaction needs a BEGIN statement, and to end with either a COMMIT or ROLLBACK (see conn.BeginTx, db.BeginTx, Tx.Commit, Tx.Rollback on the docs).

I wouldn’t recommend trying to get a balance by summing a ledger at this point - we’re not optimized for analytics queries at this point in time.

This example might be help since it implements the statements I mentioned above.

-Tim


(Marcos) #3

In the example i copied, everything was done inside the scope of a transaction (notice that tx is a *sql.Tx). The begin, commit and rollback are handled outside this function.

My questions were:

  1. Can CockroachDB guarantee that the balance of the account won’t change in the period of time between it was read with the SELECT and it was commited the transaction.

  2. Can I have the same guarantee if get the balance of the account querying something like “SELECT SUM(ammount) FROM ops WHERE id= $1”. I understand there’s going to be a performance impact.

Now I’m realizing that the first SELECT was just to check the funds… The UPDATE performs the read and write. Thanks!


(Tim O'Brien) #4

Hey @marcos,

All transactions are atomic and serializable. So that’s correct, the balance of the account will not change between SELECT and COMMIT.

The same is true of transactions containing aggregations, though as noted above this is not recommended.

Hope that helps,

Tim