How to conditional update SQL tables?

Question

I have two database tables like so:

CREATE TABLE IF NOT EXISTS accounts (
  id        INT PRIMARY KEY NOT NULL,
  balance   INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS transactions (
  from      INT FOREIGN KEY REFERENCES accounts(id),
  to        INT FOREIGN KEY REFERENCES accounts(id),
  amount    INT NOT NULL
)

In my application logic, whenever a new transaction request comes in, I want to check first if the from account has sufficient balance, then I want to subtract the amount from the from account, add amount to the to account, and add a new row to the transactions table.

However, I want to do all of this atomically, for two reasons:

Prevent race conditions. If two requests come in to transfer $5 from account 1 to account 2, and account 1 only has $5 total, I want to prevent double spending of the balance. Thus, the checking of sufficient balance and subsequent updating of balances has to happen atomically.
Prevent inconsistent state if application crashes.

Is it possible to do this in a single SQL statement? assume we are using Postgres.

I know for example I can do UPDATE accounts SET balance = balance - 6 WHERE id = 1 and balance > 6; to check if account has sufficient balance and simultaneously update the balance at the same time.

I've also heard of something called select ... for update but I'm not sure if this can help me here.

Also, the result of the query should indicate whether the transaction was successful, so I can display some error message to client if it is not?

Does this help? https://stackoverflow.com/questions/2708237/php-mysql-transactions-examples/17607619 — kmoser, Mar 17 '21 at 05:23
*assume we are using PostGres.* If so then tag [postgresql] and untag [mysql]. — Akina, Mar 17 '21 at 05:25
@kmoser not really, this just shows how to use transactions. How does this solve my problem? I still need to have some way of checking if balance is sufficient before adding to the to account — shmth, Mar 17 '21 at 05:29
@shmth Transactions let you treat multiple SQL statements as an atomic operation. Isn't that what you want to do? — kmoser, Mar 17 '21 at 05:31
@kmoser But I also want to prevent race conditions as I mentioned in the question. Transactions do not help with this, since two transactions can interleave. — shmth, Mar 17 '21 at 05:43
@shmth You can lock the tables/rows before performing the transaction, to ensure only one process can access them at a time. — kmoser, Mar 17 '21 at 05:53
@kmoser how to I lock a single row for a transaction? I definitely don't want to lock the entire table, since let's imagine we have many many transactions happening per second and locking entire table would severely limit the throughput — shmth, Mar 17 '21 at 05:57
@shmth [MySQL, update multiple tables with one query](https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query) and [How to lock a single row](https://stackoverflow.com/questions/3878672/how-to-lock-a-single-row/3878726) — kmoser, Mar 17 '21 at 05:59

Laurenz Albe · Accepted Answer · 2021-03-18T02:58:36.280

1

The solution is not to cram everything into a single SQL statement. Apart from being complicated, it will not necessarily protect you from race conditions.

Use database transactions. To avoid anomalies, either lock everything you read with SELECT ... FOR UPDATE against concurrent modifications, or use the REPEATABLE READ Isolation level and repeat the transaction if you get a serialization error.

To protect yourself from deadlocks, make sure that you always lock the account with the lower id first.

Here is a pseudo-code example:

EXEC SQL START TRANSACTION;

if (id1 < id2) {
    EXEC SQL SELECT balance INTO :bal1 FROM accounts
         WHERE id = :id1 FOR UPDATE;
    EXEC SQL SELECT balance INTO :bal2 FROM accounts
         WHERE id = :id2 FOR UPDATE;
} else {
    EXEC SQL SELECT balance INTO :bal2 FROM accounts
         WHERE id = :id2 FOR UPDATE;
    EXEC SQL SELECT balance INTO :bal1 FROM accounts
         WHERE id = :id1 FOR UPDATE;
}

if (bal1 < amount) {
    EXEC SQL ROLLBACK;
    throw_error('too little money on account');
}

EXEC SQL UPDATE accounts SET balance = :bal1 - :amount WHERE id = :id1;
EXEC SQL UPDATE accounts SET balance = :bal1 + :amount WHERE id = :id2;
EXEC SQL INSERT INTO transaction ("from", "to", amount)
    VALUES (:id1, :id2, :amount);

EXEC SQL COMMIT;

edited Mar 18 '21 at 02:58

answered Mar 17 '21 at 06:56

Laurenz Albe

209,280
17
206
263

Thanks, a couple followup questions: 1. how can I first check if the balance of first user is sufficient, before proceeding to make the updates to the tables? 2. Do the sql queries return some status so that I know whether the operation actually succeeded or not? – shmth Mar 17 '21 at 07:07
1. Run a `SELECT`. 2. If a statement fails, it will always cause an error. – Laurenz Albe Mar 17 '21 at 07:17
Are you able to provide an example of SQL statement? I still don't understand how I can do 1. In pseudo code, I want something like: ``` select * from accounts where id = i; if balance > 6: update accounts set balance = balance - 6 where id = 1 update accounts set balance = balance + 6 where id = 2 insert into transactions 1, 2, 6 ``` – shmth Mar 17 '21 at 21:00
2

Another option would be to not check sufficient balance in your code. Instead put a check constraint on the column. So: alter table accounts add constraint bal_ge_0 check (balance >= 0); – Belayer Mar 18 '21 at 04:28

How to conditional update SQL tables?

1 Answers1