Postgresql – Implement “account” transactions in postgresql

postgresql

I am looking into on different ways a simple account functionality that can be implemented in Postgresql 9.5. I would like to be able to do it with maximum of "Repeatable read" as transaction isolation level. The reason why it should be "Repeatable read" isolation level is that we have detected a high level of collisions "serializable" isolation level and even with a retry strategy around 10 times within 10 seconds it still fails because of the amount of collisions.

Deposit money
Withdraw money
Cannot withdraw money if balance of the user is less than zero (sum of amount for user)
Balance must never be able to go below zero (sum of amount for user)

It should handle multiple connections and multiple users and the same user can initiate deposits and withdraws in different connections.

The only solution I currently have is to do something like the following:

For deposits:

Allow deposit money directly as an insert.

For withdraws:

Check balance > amount of withdraw before continue.
Insert withdraw money transaction with status initiated.
Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.
Update withdraw money transaction status completed if status is initiated.

Any materials or examples are welcomed.

Best Answer

Good Idea For deposits: Allow deposit money directly as an insert.
Bad Idea For withdraws: Check balance > amount of withdraw before continue.
- Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.

I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?

BEGIN;
UPDATE accounts
  SET balance = balance - 100
  WHERE name = 'Bob';
UPDATE accounts
  SET balance = balance + 100
  WHERE name = 'Sally';
END;

If the balance on accounts would go under 0 with the deduction, it'll simply fail there.
If it passes then you will see bob's balance go down for your transaction.
Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.

The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that record until the transaction is committed.

Related Solutions

PostgreSQL UPSERT – Idiomatic Implementation Guide

PostgreSQL now has UPSERT.

The preferred method according a similar StackOverflow question is currently the following:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

PostgreSQL – Implement Insert-if-Not-Found for Serializable Transactions

This is a bit of a FAQ. You'd find more information if you searched for ON DUPLICATE KEY UPDATE (the MySQL syntax), MERGE (the SQL-standard syntax), or UPSERT. It's surprisingly hard.

The best article I've seen on it yet is Depesz's "why is upsert so complicated". There's also the SO question Insert, on duplicate update (postgresql) which has suggestions but lacks explanation and discussion of the issues.

The short answer is that, yes:

It seems the only option I have is to abort the entire transaction and try again.

When using SERIALIZABLE transactions you just have to re-issue them when they fail. Which they will. By design - and much more frequently on Pg 9.1 and above because of greatly improved conflict detection. Upsert-like operations are very high conflict, so you may land up retrying quite a bit. If you can do your upserts in READ COMMITTED transactions instead it'll help, but you should still be prepared to retry because there are some unavoidable race conditions.

Let the transaction fail with a unique violation when you insert the conflicting row. If you get a SQLSTATE 23505 unique_violation failure from the transaction and you know you were attempting an upsert, re-try it. If you get a SQLSTATE 40001 serialization_failure you should also retry.

You fundamentally cannot do that retry within a PL/PgSQL function (without dirty hacks like dblink), it must be application side. If PostgreSQL had stored procedures with autonomous transactions then it'd be possible, but it doesn't. In READ COMMITTED mode you can check for conflicting inserts made since the transaction started, but not after the statement that calls the PL/PgSQL function started, so even in READ COMMITTED your "detect conflict with select" approach simply will not work.

Read depesz's article for a much better and more detailed explanation.

Best Answer

Related Solutions

PostgreSQL UPSERT – Idiomatic Implementation Guide

PostgreSQL – Implement Insert-if-Not-Found for Serializable Transactions

Related Question