Postgresql – Making sure balance field is always consistently updated with triggers

acidpostgresql

This is my database schema, related to the problem.

enter image description here

This is one of the triggers:

CREATE OR REPLACE FUNCTION "public"."update_balance_bet"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
    DECLARE
    currentBalance INTEGER;
    BEGIN

    currentBalance = (SELECT balance FROM "public"."Users" WHERE id=NEW."UserId");
    UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);
    RETURN NEW;
END
$BODY$

CREATE TRIGGER bet_trigger
    AFTER INSERT ON "public"."Bets"
    FOR EACH ROW
    EXECUTE FUNCTION update_balance_bet();

Basically from my app I insert records into table Bets, Bonuses, Withdrawals and Deposits, then the rest of the work is done by triggers. My questions is whether the mechanism I used will guarantee consistency in all cases, or should I use raw locking? Am I right in assumption that inserting into Bets table is one transaction along with trigger function updating balance, so in case something goes wrong with insertion into Bets, balance wont be updated OR if something wrong goes with balance updating, new record wont be inserted into Bets table?

Best Answer

First, this UPDATE is buggy:

 UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);

because it updates the entire "Users" table, which is obviously not what's wanted.

Let's assume that a WHERE clause is added and the code becomes:

DECLARE
currentBalance INTEGER;
BEGIN

currentBalance = (SELECT balance FROM "public"."Users" WHERE id=NEW."UserId");

UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);
  WHERE id=NEW."UserId"

RETURN NEW;

This code is subject to a race condition under the default isolation level (Read Committed). Say this transaction is T1 and another one is T2. If T2 was updating the balance for this user, and it commits between the SELECT and the UPDATE, the UPDATE of T1 will overwrite the row, leading the change of T2 being lost and the balance being wrong.

To implement this logic in a concurrent-safe way, the recommendation of the SQL standard is to use transactions at the serializable isolation level. See Transaction Isolation in PostgreSQL documentation. Alternatively, you may implement your own locking strategy at the less isolated levels to avoid race conditions, but it's harder to get it right.

Q: Do I right in assumption that inserting into Bets table is one transaction along with trigger function updating balance, so in case something goes wrong with insertion into Bets, balance wont be updated OR if something wrong goes with balance updating, new record wont be inserted into Bets table

Yes, but that's a virtue of transactions, not of triggers. Normally, you get atomicity by putting the list of SQL statements between a BEGIN...COMMIT pair, not by moving them into a trigger. The point of triggers is to implement side-effects, not the main logic. Quoting Tom Kyte's The Trouble with Triggers excellent post:

Triggers should be viewed with a skeptic’s eye. If the trigger is in place to enforce entity integrity, be very suspicious of it. Think about multiuser conditions. Think about what happens when two or three people operate on similar data at, or at about, the same time.

[...]

Triggers should be the exception, not the rule. They should be used only when you cannot do something any other way. Given the concurrency issues, the problems with doing nontransactional operations in them, and the maintenance problems, triggers are something to use sparingly, if at all.

Related Question