9BtArHpX

1    /**
2     * Transfer funds from one account to another with lazy validation and support for arbitrary concurrency.
3     *
4     * Whether or not the transfer gets executed is decided upon its settlement, rather than submission.
5     * The transfer's validity may change between submission and settlement as prior transfers are executed.
6     * The transfer will be executed if and only if the transfer is valid upon settlement.
7     *
8     * At settlement, all of the following must be true for the transfer to validate and thus execute:
9     *  - The amount being transferred is greater than zero
10     *  - The source exists and has sufficient funds
11     *  - The target exists
12     *  - The source and the target are distinct
13     *
14     * TODO: Known Tech Debts
15     * - Create a utility to read SQL blobs from a file and statically load them to avoid hardcoded SQL.
16     *
17     * TODO: Possible Extensions:
18     * - Abstract this 1-to-1 transfer over a 1-to-N transfer and/or N-to-N transfer.
19     */
20    @Transaction
21    public int[] transferFromAccount(int src, int tar, int amt) {
22        return dbi.inTransaction((handle, transactionStatus) -> handle.createBatch()
23                // Setup
24                .add("DO $$ DECLARE valid_lock_count integer; BEGIN")
25
26                // Require a non-zero amount between distinct parties. Note that one or both participants may not exist.
27                .add("IF <amt> < 1 OR <src> = <tar> THEN " +
28                        "RAISE EXCEPTION 'Must transfer at least 1 unit between two distinct parties!'; " +
29                        "END IF;")
30
31                // Acquire row-level write-locks on both the source and target balances to avoid excessive table locks
32                .add("SELECT * INTO locks FROM accounts FOR UPDATE " +
33                        // Balances may change between lock contention and acquisition, thus we don't check them here.
34                        "WHERE id = <src> OR id = <tar> " +
35                        // Exclusive locks must be acquired in a consistent order across the entire system.
36                        // More info in Section 13.3.4 of https://www.postgresql.org/docs/current/explicit-locking.html
37                        "ORDER BY id;")
38
39                // Count the locks which were acquired on valid rows. Here, two valid locks imply a valid transfer.
40                .add("SELECT COUNT(id) INTO valid_lock_count FROM locks " +
41                        "WHERE (id = <src> AND balance >= <amt>) OR id = <tar>;")
42
43                // Assert our lock on both an existing target and existing source with sufficient funds, both distinct.
44                .add("IF valid_lock_count != 2 THEN " +
45                        // Implicitly release the locks; they are no longer needed as the transfer failed to validate.
46                        "RAISE EXCEPTION 'Expected two distinct users, with sufficient funds from the source.'; " +
47                        "END IF;")
48
49                // We've met all conditions necessary to subtract funds from our source (transfer order doesn't matter)
50                .add("UPDATE accounts " +
51                        "SET balance = balance - <amt> " +
52                        "WHERE id = <src>;")
53
54                // We've met all conditions necessary to add funds to our target (transfer order doesn't matter)
55                .add("UPDATE accounts " +
56                        "SET balance = balance + <amt> " +
57                        "WHERE id = <tar>;")
58                .add("END $$;")
59                .define("src", src)
60                .define("tar", tar)
61                .define("amt", amt)
62                .execute()
63        );
64    }