Optimistic concurrency control via account version?

[jasonzmiller]

Hello! Thanks so much for this awesome project. Really clear and easy to follow examples as well.

I'm trying to build optimistic concurrency control on top of pgledger. Here's a toy example to outline the case I'm trying to solve for:

• Two users each load an invoice showing a $1000 balance
• User A applies $200 payment -> invoice balance = $800
• User B, still looking at the stale $1000, applies $400 payment
• -> User B's decision was based on stale state.

Pessimistic locking wouldn't solve this problem because according to pgledger, each write is individually valid. This is a business/application-level concern, but I think that pgledger offers us a tool to solve it. pgledger_accounts tracks version that increments on each transfer involving an account. If an account's version has changed since the last time it was read, we knows another transfer has been created by someone else. We can leverage this: a writer will pass the version it last read, and pgledger creates the transfer if and only if the account's version still matches. (We get an elegant implementation of optimistic concurrency control where the account version is playing the role of row version.)

The way we're thinking of doing it is by implementing another function that accepts the account versions:

CREATE OR REPLACE FUNCTION pgledger_create_transfer_at_version(
    from_account_id TEXT,
    to_account_id TEXT,
    amount NUMERIC,
    from_account_version BIGINT,
    to_account_version BIGINT,
    event_at TIMESTAMPTZ DEFAULT NULL,
    metadata JSONB DEFAULT NULL
)
RETURNS SETOF PGLEDGER_TRANSFERS_VIEW
AS $$
BEGIN
    -- ...
    -- ...
    -- Capture the locked rows so we can assert versions
    SELECT * INTO from_account FROM pgledger_accounts WHERE pgledger_accounts.id = from_account_id;
    SELECT * INTO to_account FROM pgledger_accounts WHERE pgledger_accounts.id = to_account_id;
    
    -- Optimistic concurrency control: only proceed if each account's current
    -- version matches the version the caller read. Checked under the row locks
    -- acquired above and before any mutation, so a mismatch aborts the whole
    -- function with nothing applied. Raise on the first mismatch (from, then to);
    -- the caller re-reads balances and versions on conflict.
    IF from_account.version != from_account_version THEN
        RAISE EXCEPTION 'Account (id=%) version mismatch: expected %, found %',
            from_account.id, from_account_version, from_account.version;
    END IF;
    
    IF to_account.version != to_account_version THEN
        RAISE EXCEPTION 'Account (id=%) version mismatch: expected %, found %',
            to_account.id, to_account_version, to_account.version;
    END IF;

    -- ...
    -- ...
    -- rest of stuff that the existing pgledger_create_transfers does
END;
$$ LANGUAGE plpgsql;

Questions

1. Is there any plan to update the existing pgledger_create_transfer function to accept & verify account versions?
2. Curious if you/others may have run into the same problem and how it's been approached in the past.

All thoughts welcome! Thanks so much

[pgr0ss]

Hi, I haven't run into this situation before with pgledger but I see where you're coming from. However, I think you can probably do the same thing without making a new function. Here's what I'm thinking:

1. Start a database transaction
2. Make the pgledger transfer as normal
3. Check the account version == expected account version + 1 (since the transfer will increment the version)
4. If they don't match, rollback

I think this works because once you are in the database transaction and call pgledger_create_transfer, you will have locked the pgledger accounts.

If you do want a function to wrap all of this logic, though, I would probably try to still call pgledger_create_transfer rather than rewrite it. That way, you don't duplicate the logic and you can apply pgledger upgrades more easily in the future.

Hope this makes sense and helps your use case.