The Problem of Financial Transfers

Let's examine a money transfer between two accounts. At first glance, it seems straightforward: deducting an amount from one account and crediting it to another.

In practice, this procedure is more complex. If an error occurs after the deduction from the first account but before the transfer is finalized, it can lead to a loss of funds, causing customer dissatisfaction.

To illustrate, we will create a table for accounts with initial balances of 1000 and 500.

CREATE TABLE account (

id SERIAL PRIMARY KEY,

balance decimal NOT NULL CHECK (balance >= 0)

);

INSERT INTO account(balance) VALUES (1000), (500);

To initiate a transaction, we use the BEGIN command. Upon validating the changes, we then execute COMMIT.

BEGIN;

UPDATE account SET balance = 0 WHERE id = 1;

UPDATE account SET balance = 1500 WHERE id = 2;

COMMIT;

Alternatively, if an issue arises, we can revert the changes using ROLLBACK.

BEGIN;

UPDATE account SET balance = 0 WHERE id = 1;

UPDATE account SET balance = 1500 WHERE id = 2;

ROLLBACK;

Error Handling and Procedures

Since we cannot predict whether an error will occur, we can enhance our process through the use of stored procedures.

First, we need to verify the current data status. Attempting to deduct 1000 USD from account 1 again should fail, as this operation is invalid.

Assuming we mistakenly add 1000 USD to account 2 and then try to deduct it from account 1 without a transaction, the outcome would be catastrophic.

CREATE OR REPLACE PROCEDURE transfer_funds (

amount numeric,

from_account_id int,

to_account_id int

) language plpgsql

AS $$

BEGIN

-- Begin a transaction

BEGIN

-- Add the specified amount to the target account

UPDATE account SET balance = balance + amount WHERE id = to_account_id;

-- Deduct the specified amount from the source account

UPDATE account SET balance = balance - amount WHERE id = from_account_id;

-- Notify of a successful transfer

RAISE NOTICE 'Transfer successful.';

EXCEPTION

WHEN OTHERS THEN

-- Raise an error with a specific message

RAISE EXCEPTION 'Error during transfer';

END;

END;

$$;

When invoking this procedure, we can try to deduct funds from an account even when the balance is zero.

Note that the procedure runs within its own transaction, leading to an implicit commit upon completion. Whether we credit the account first or deduct afterward, the final balance remains consistent.

Thank you for engaging with my article! If you found this information useful and wish to join our expanding community, please follow us. We value your feedback and comments, so feel free to share your thoughts!

Stackademic 🎓

For more insights, consider following us on our various platforms: X | LinkedIn | YouTube | Discord. Explore additional content on Stackademic.com.